Pit collection:

Spring boot integrates easypoi and uses its template to export functions and problems encountered_ Blog of the first Prince of Bazhong - CSDN blog_ easypoi springboot1. Background recently, I'm making a demand to export excel using poi. Because the required excel is complex, I'm going to use the template export function of easypoi to realize it. 2. Use 2.1 integration <-- excel--> <!-- If the easypoi integrated springboot package, easypoi spring boot starter, is directly introduced here, spring boot sta is required for startuphttps://blog.csdn.net/weixin_38312502/article/details/105627498 Style settings

easyPoi export excel tool class and set excel export style (border, background color, font)_ zhouqu3790 blog - CSDN blog * * usage * * 1. When using, you can pass the style tool class ExcelStyleUtil as a parameter. Exportparams exportparams = new exportparams (& amp; amp; quot; title name & amp; amp; quot;, & amp; quot; sheet name & amp; amp; quot;, exceltype. Xssf); exportParams.https://blog.csdn.net/zhouqu3790/article/details/82688580 I use 4.1.2

Template export in progress

1. The multi-layer loop is useless because there will be confusion, partial data loss or empty data

2. Do not merge the cells in the template. There will be confusion, partial data loss or empty and other problems.

two point one   My method is to assign the same value to the cells to be merged in the template, and then merge them in the code

two point two   Vertical consolidation:

//Merge cells with the same content vertically
// 3 is the number of rows to start merging
// 0 is the column to be merged
PoiMergeCellUtil.mergeCells(sheet, 3, 0);

two point three   Horizontal consolidation:

//Merge cells with the same content horizontally
CellRangeAddress craOne = new CellRangeAddress(0,3,2, 4);
//Cellrangeaddress (which line starts, which line ends, which column starts, and which column ends)
sheet.addMergedRegion(craOne);

2.3.1   Horizontal merging in the loop, I performed this operation on each line in the code. Otherwise, all rows will be merged into one cell

3. When setting the style, you need to set each cell

CellStyle style = workbook.createCellStyle();
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.CENTER);


row.getCell(i).setCellStyle(style);

4. When there is data after the list, if the second part of the data is written directly in the template, the second part of the data will overwrite the data of the list

4.1 my processing method is: write the list in the template and write the data of the second part in the code

Row newRow = sheet.createRow(sheet.getLastRowNum());
newRow.setHeightInPoints(50);
newRow.createCell(0).setCellValue("Value 1");
newRow.createCell(1).setCellValue("Value 2");

ps: actually, it's written here. I think I might as well draw all the code instead of templates

5. Insert a new row of data into the existing data

five point one   If it is the last line, you can directly add sheet.createrow

five point two   If it is inserted into the middle, move it

// Sheet.shiftrows (insert row, the last row of current data, move several rows, copy row height, reset original row height);
sheet.shiftRows(12, sheet.getLastRowNum(), 1 ,true,false);

This sentence is to move all the data below the inserted line down one line, so as to leave an empty line to insert new data and prevent overwriting

This can also be used to delete a row of data and replace the moved rows with negative numbers

6. Simple template export code:

First prepare the template

Then the code snippet reference is as follows

TemplateExportParams params = new TemplateExportParams(templateUrl);
params.setScanAllsheet(true);

Workbook workbook = ExcelExportUtil.exportExcel(sheetMap, params);
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
// Then you can insert some specially processed data or merge cells
// Skip if you don't need it
workbook.write(response.getOutputStream());
workbook.close();