Java export Excel (non-template) can export multiple sheets
I wasted a lot of effort in order to write exports. Today, I suddenly remembered that I uploaded this method. I have also written template exports before, so this is not a template.
method
- 1. Export excel (single title, single sheet)
- 2. Contain multiple sheets. The title in each sheet is the same
- 3. Contains multiple sheets. The titles in each sheet are inconsistent.
- And there are two other export formats.
Things to note
Just change PageData to Map! ! !
<!--rely-->
<dependency>
<groupId></groupId>
<artifactId>-api</artifactId>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId> </groupId>
<artifactId> httpclient </artifactId>
<version>4.5.9</version>
</dependency>
<!--json-lib -->
<dependency>
<groupId>-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>jxls-core</artifactId>
<version>1.0-RC-1</version>
</dependency>
import ;
import .*;
import .*;
import ;
import ;
import ;
import ;
import ;
import .*;
import ;
import ;
import .*;
/**
* @Auther: sjj
* @Date: 2019/5/29 16:50
* @ClassName: ExcelUtil
* @Description: Export excel
*/
public class ExcelUtil {
/**
* Export excel
* @param pds The data to be exported
* @param pd Exporting public data of excel must include title (title), headers (separated by commas, exporting headers), fileName (file name, just write the name, and the splicing will be processed here)
* @param request
* @param response
* @return
*/
public static void exportData(List<LinkedHashMap<Object,Object>> pds, PageData pd, HttpServletRequest request, HttpServletResponse response) {
if(()<1){
// return StatusCode.IS_NULL;
return;
}
try {
HSSFWorkbook workbook = new HSSFWorkbook();
//Set the title HSSFSheet sheet = (("title"));
//headers represents the header of the first row in the excel table String[] headers=("headers").split(",");
//Table header font Font headerFont = ();
("Microsoft Yahei");
(HSSFFont.COLOR_NORMAL);
(true);
//Create a cell and set the value table header Set the table header to center HSSFCellStyle styleMain = ();
//Center the level ();
(headerFont);
//Export style HSSFCellStyle cellStyle_C = ();
// Automatic line wrap cellStyle_C.setWrapText(true);
cellStyle_C.setAlignment();
//Create the title line HSSFRow row = (0);
//Add table header in excel table for(int i=0;i<;i++){
HSSFCell cell = (i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
(text);
(styleMain);
}
int rowNum = 1;
//Transfer the collection data for(LinkedHashMap<Object,Object> p :pds){
//Create rows in turn HSSFRow row1 = (rowNum);
int i=0;
//Transfer data for (Object key: ()){
//Create columns in rows and put data HSSFCell cell = (i);
String value=((key));
(value!=null?value:"");
if("null".equals(value)){
("");
}
(cellStyle_C);
i++;
}
((short) (26*10));
rowNum++;
}
//Automatically adjust column width for (int i = 0;i <;i++){
if(i==-2){
(i, 20 * 256);
break;
}
if(i==3){
(i, 20 * 256);
continue;
}
(i, true);
}
//Get output stream OutputStream out = ();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyy year MM month dd day HH hour mm minute ss seconds");
//Clear the cache ();
//Set the encoding format of the corresponding content ("UTF-8");
//1.Export name String fileName =("fileName")+(new Date());
//The file name is garbled //Get browser information and convert it to capitalization String agent = ("User-Agent").toUpperCase();
//IE browser and Edge browser if (("MSIE") > 0 || (("GECKO")>0 && ("RV:11")>0)) {
fileName = (fileName, "UTF-8");
} else { //Other browsers fileName = new String(("UTF-8"), "iso-8859-1");
}
("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//Define the output type ("application/msexcel");
//Save Excel file (out);
//Close the file stream ();
} catch (Exception e) {
();
// return ;
}
// return ;
}
/**
* Export excel contains multiple sheets. The title in each sheet is consistent.
* @param pds The data to be exported
* @param titles Export excel public data, including title (title) and headers (separated by commas, export headers)
* @param fileName (File name, just write the name, and the splicing will be processed here)
* @param request
* @param response
* Note that the data order in titles should be consistent with that in pds. Otherwise, the title and content will not match when exporting.
* @return
*/
public static void exportSheetData(Map<String,List<List<LinkedHashMap<Object,Object>>>> pds, List<PageData> titles,
String fileName,HttpServletRequest request, HttpServletResponse response) throws IOException {
if(()<1){
// return StatusCode.IS_NULL;
return;
}
HSSFWorkbook workbook = new HSSFWorkbook();
//Get output stream OutputStream out = ();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyy year MM month dd day HH hour mm minute ss seconds");
//Rename fileName+=(new Date());;
//Clear the cache ();
//Set the encoding format of the corresponding content ("UTF-8");
//The file name is garbled //Get browser information and convert it to capitalization String agent = ("User-Agent").toUpperCase();
//IE browser and Edge browser if (("MSIE") > 0 || (("GECKO")>0 && ("RV:11")>0)) {
fileName = (fileName, "UTF-8");
} else { //Other browsers fileName = new String(("UTF-8"), "iso-8859-1");
}
("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//Define the output type ("application/msexcel");
int sheetNum=0;
for(String nameKey:()){
List<List<LinkedHashMap<Object,Object>>> data=(nameKey);
//Set the title HSSFSheet sheet = ();
//The name of the construction site/consumption site is the sheet name (sheetNum,nameKey);
//Table header font Font headerFont = ();
("Microsoft Yahei");
(HSSFFont.COLOR_NORMAL);
(true);
//Create a cell and set the value table header Set the table header to center HSSFCellStyle styleMain = ();
//Center the level ();
(headerFont);
//Export style HSSFCellStyle cellStyle_C = ();
// Automatic line wrap cellStyle_C.setWrapText(true);
cellStyle_C.setAlignment();
int titleNum=0;
int rowNum = 1;
int startRow=0;
for(List<LinkedHashMap<Object,Object>> list:data){
//Put data //Create the title line //Main title HSSFRow title = null;
//Line Title HSSFRow row = null;
//To avoid overwriting the content by title if(rowNum==1){
title=(0);
row = (rowNum);
rowNum++;
}else{
//Let the title and the previous line have two empty lines startRow=rowNum+2;
title=(startRow);
row = (rowNum+3);
rowNum+=4;
}
PageData pd=(titleNum);
//headers represents the header of the first row in the excel table String[] headers=("headers").split(",");
//Create main title HSSFCell tieleCell = (0);
(("title"));
(styleMain);
for(int i=1;i<=;i++){
HSSFCell cell = (i);
// HSSFRichTextString text = new HSSFRichTextString(headers[i]);
("");
(styleMain);
}
// The merge date occupies two rows (4 parameters, namely the start row, the end row, the start column, and the end column) // Rows and columns are counted from 0, and the beginning and end will be merged // Here are two lines of dates in Excel CellRangeAddress region = new CellRangeAddress(startRow, startRow, 0, -1);
(region);
//The main title creation ends
//Add table header in excel table for(int i=0;i<;i++){
HSSFCell cell = (i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
(text);
(styleMain);
}
//Table header addition ends
//Transfer the collection data for(LinkedHashMap<Object,Object> p :list){
//Create rows in turn HSSFRow row1 = (rowNum);
int i=0;
//Transfer data for (Object key: ()){
//Create columns in rows and put data HSSFCell cell = (i);
String value=((key));
(value!=null?value:"");
if("null".equals(value)){
("");
}
(cellStyle_C);
i++;
}
((short) (26*10));
rowNum++;
}
//Automatically adjust column width for (int i = 0;i <;i++){
if(i==-2){
(i, 20 * 256);
break;
}
if(i==3){
(i, 20 * 256);
continue;
}
(i, true);
}
titleNum++;
// }
}
sheetNum++;
}
//Save Excel file (out);
//Close the file stream ();
}
/**
* Export excel contains multiple sheets The titles in each sheet are inconsistent
* @param pds The data to be exported
* @param titles Export excel public data, which must include title (title) and headers (separated by commas, exporting the header), and the List<PageData> is inconsistent
* @param fileName (File name, just write the name, and the splicing will be processed here)
* @param request
* @param response
* Note that the data order in titles should be consistent with that in pds. Otherwise, the title and content will not match when exporting.
* @return
*/
public static void exportSheetTitle(LinkedHashMap<String,List<List<LinkedHashMap<Object,Object>>>> pds, LinkedList<List<PageData>> titles,
String fileName,HttpServletRequest request, HttpServletResponse response) throws IOException {
if(()<1){
// return StatusCode.IS_NULL;
return;
}
HSSFWorkbook workbook = new HSSFWorkbook();
//Get output stream OutputStream out = ();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyy year MM month dd day HH hour mm minute ss seconds");
//Rename fileName+=(new Date());;
//Clear the cache ();
//Set the encoding format of the corresponding content ("UTF-8");
//The file name is garbled //Get browser information and convert it to capitalization String agent = ("User-Agent").toUpperCase();
//IE browser and Edge browser if (("MSIE") > 0 || (("GECKO")>0 && ("RV:11")>0)) {
fileName = (fileName, "UTF-8");
} else { //Other browsers fileName = new String(("UTF-8"), "iso-8859-1");
}
("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//Define the output type ("application/msexcel");
int sheetNum=0;
for(String nameKey:()){
List<List<LinkedHashMap<Object,Object>>> data=(nameKey);
//Set the title HSSFSheet sheet = ();
//The name of the construction site/consumption site is the sheet name (sheetNum,nameKey);
//Table header font Font headerFont = ();
("Microsoft Yahei");
(HSSFFont.COLOR_NORMAL);
(true);
//Create a cell and set the value table header Set the table header to center HSSFCellStyle styleMain = ();
//Center the level ();
(headerFont);
//Export style HSSFCellStyle cellStyle_C = ();
// Automatic line wrap cellStyle_C.setWrapText(true);
cellStyle_C.setAlignment();
int titleNum=0;
int rowNum = 1;
int startRow=0;
for(List<LinkedHashMap<Object,Object>> list:data){
//Put data //Create the title line //Main title HSSFRow title = null;
//Line Title HSSFRow row = null;
//To avoid overwriting the content by title if(rowNum==1){
title=(0);
row = (rowNum);
rowNum++;
}else{
//Let the title and the previous line have two empty lines startRow=rowNum+2;
title=(startRow);
row = (rowNum+3);
rowNum+=4;
}
//Transf the title in each sheet for(PageData pd:(sheetNum)){
//headers represents the header of the first row in the excel table String[] headers=("headers").split(",");
//Create main title HSSFCell tieleCell = (0);
(("title"));
(styleMain);
for(int i=1;i<=;i++){
HSSFCell cell = (i);
// HSSFRichTextString text = new HSSFRichTextString(headers[i]);
("");
(styleMain);
}
// The merge date occupies two rows (4 parameters, namely the start row, the end row, the start column, and the end column) // Rows and columns are counted from 0, and the beginning and end will be merged // Here are two lines of dates in Excel CellRangeAddress region = new CellRangeAddress(startRow, startRow, 0, -1);
(region);
//The main title creation ends
//Add table header in excel table for(int i=0;i<;i++){
HSSFCell cell = (i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
(text);
(styleMain);
}
//Table header addition ends
//Transfer the collection data for(LinkedHashMap<Object,Object> p :list){
//Create rows in turn HSSFRow row1 = (rowNum);
int i=0;
//Transfer data for (Object key: ()){
//Create columns in rows and put data HSSFCell cell = (i);
String value=((key));
(value!=null?value:"");
if("null".equals(value)){
("");
}
(cellStyle_C);
i++;
}
((short) (26*10));
rowNum++;
}
//Automatically adjust column width for (int i = 0;i <;i++){
if(i==-2){
(i, 20 * 256);
break;
}
if(i==3){
(i, 20 * 256);
continue;
}
(i, true);
}
}
titleNum++;
// }
}
sheetNum++;
}
//Save Excel file (out);
//Close the file stream ();
}
/**
* Export excel
* @qkp
*
* @param pds The data to be exported
* @param pd Export excel public data, including templateFile (template file), fileName (file name, just write the name, and the splicing will be processed here)
* @param mergeCells MergeCells Cell array [firstRow, lastRow, firstCol, lastCol]
* @param request
* @param response
* @return
*/
public static void exportData(Map<Object,Object> pds, PageData pd,int []mergeCells, HttpServletRequest request, HttpServletResponse response) {
if(()<1){
return;
}
try {
XLSTransformer transformer = new XLSTransformer();
InputStream in = new FileInputStream(new File(("templateFile")));
XSSFWorkbook workbook =(XSSFWorkbook)(in, pds);
Sheet sheet = (0);
//The logic required for merging cells (new CellRangeAddress(mergeCells[0], mergeCells[1], mergeCells[2], mergeCells[3]));// Functions for merging cells
//Table header font Font headerFont = ();
("Microsoft Yahei");
(HSSFFont.COLOR_NORMAL);
(true);
//Create a cell and set the value table header Set the table header to center
//Export style XSSFCellStyle cellStyle_C = ();
// Automatic line wrap cellStyle_C.setWrapText(true);
cellStyle_C.setAlignment();
//Get output stream OutputStream out = ();
//Clear the cache ();
//Set the encoding format of the corresponding content ("UTF-8");
//1.Export name String fileName =("fileName");
//The file name is garbled //Get browser information and convert it to capitalization String agent = ("User-Agent").toUpperCase();
//IE browser and Edge browser if (("MSIE") > 0 || (("GECKO")>0 && ("RV:11")>0)) {
fileName = (fileName, "UTF-8");
} else { //Other browsers fileName = new String(("UTF-8"), "iso-8859-1");
}
("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
//Define the output type //("application/msexcel");
("application/");
//Save Excel file (out);
//Close the file stream ();
} catch (Exception e) {
();
}
}
/**
* Export excel
* @author qkp
* @param pds The data to be exported
* @param pd Exporting public data of excel must include title (title), headers (separated by commas, exporting headers), fileName (file name, just write the name, and the splicing will be processed here)
* @param request
* @param response
* @return
*/
public static void exportData2(List<LinkedHashMap<Object,Object>> pds, PageData pd, HttpServletRequest request, HttpServletResponse response) {
if(()<1){
return;
}
try {
HSSFWorkbook workbook = new HSSFWorkbook();
//Set the title HSSFSheet sheet = ();
//headers represents the header of the first row in the excel table String[] headers=("headers").split(",");
//Table header font Font headerFont = ();
("Microsoft Yahei");
(HSSFFont.COLOR_NORMAL);
(true);
//Create a cell and set the value table header Set the table header to center HSSFCellStyle styleMain = ();
//Center the level ();
(headerFont);
();
(());
();
(());
();
(());
();
(());
//Export style HSSFCellStyle cellStyle_C = ();
// Automatic line wrap cellStyle_C.setWrapText(true);
cellStyle_C.setAlignment();
cellStyle_C.setBorderBottom();
cellStyle_C.setBottomBorderColor(());
cellStyle_C.setBorderTop();
cellStyle_C.setTopBorderColor(());
cellStyle_C.setBorderLeft();
cellStyle_C.setLeftBorderColor(());
cellStyle_C.setBorderRight();
cellStyle_C.setRightBorderColor(());
//Create column title row HSSFRow row = (0);
//Title line style HSSFCellStyle titleStyle = ();
//Table header font Font titleFont = ();
("Microsoft Yahei");
(HSSFFont.COLOR_NORMAL);
(true);
((short)18);
(titleFont);
();
HSSFCell titleCell =(0);
(("title"));
(titleStyle);
//The logic required for merging cells (new CellRangeAddress(0, 0, 0,-1 ));// Functions for merging cells (titleStyle);
(56);
// List question row = (1);
(39);
//Add table header in excel table for(int i=0;i<;i++){
HSSFCell cell = (i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
(text);
(styleMain);
}
int rowNum = 2;
//Transfer the collection data for(LinkedHashMap<Object,Object> p :pds){
//Create rows in turn HSSFRow row1 = (rowNum);
int i=0;
//Transfer data for (Object key: ()){
//Create columns in rows and put data HSSFCell cell = (i);
String value=((key));
(value!=null?value:"");
if("null".equals(value)){
("");
}
(cellStyle_C);
i++;
}
//((short) (26*10));
rowNum++;
}
//Automatically adjust column width for (int i = 0;i <;i++){
if(i==-2){
(i, 20 * 256);
break;
}
if(i==3){
(i, 20 * 256);
continue;
}
(i);
//(i, true);
}
//Get output stream OutputStream out = ();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyy year MM month dd day HH hour mm minute ss seconds");
//Clear the cache ();
//Set the encoding format of the corresponding content ("UTF-8");
//1.Export name String fileName =("fileName")+(new Date());
//The file name is garbled //Get browser information and convert it to capitalization String agent = ("User-Agent").toUpperCase();
//IE browser and Edge browser if (("MSIE") > 0 || (("GECKO")>0 && ("RV:11")>0)) {
fileName = (fileName, "UTF-8");
} else { //Other browsers fileName = new String(("UTF-8"), "iso-8859-1");
}
("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//Define the output type ("application/msexcel");
//Save Excel file (out);
//Close the file stream ();
} catch (Exception e) {
();
// return ;
}
// return ;
}
}Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.