1. Foreword
The project also required exporting complex style tables and exporting different data according to different divisions. I also thought about and learned easyexcel and poi, and the results are as follows:
1. Merge multi-level headers
2. Header shading + table border
3. Merge cells with the same data
4. Add remarks to the last line
2. Renderings
Directly to the picture
3. Dependence
Note that Lombok annotations need to be introduced in the text.
Easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
4. Specific implementation
1. Test class description
Annotation:
@ContentRowHeight(25) //Content row height
@HeadRowHeight(20)//Header row height
@ColumnWidth(15) column width
@ExcelProperty(value = {“User Information”,”Administrative Division”,”Municipality”}, index = 1)Header information:
The same headers in value will be automatically merged. The index must be set according to your own needs, otherwise the order of the fields will be messed up.
In the example, the user information is the first-level header, the administrative division is the second-level header, and the city, county and city levels are the third-level header.
Code
package com.hua.bo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import java.io.Serializable;
/**
* @Author su
* @Date 2023-09-15 14:47
* @Description
**/
@Data
@ContentRowHeight(25) //Content row height
@HeadRowHeight(20)//Header row height
public class ReportUserInfoBO implements Serializable {
@ColumnWidth(5)//Width
@ExcelProperty(value = {"User Information","Serial Number"}, index = 0)
private String id;
@ColumnWidth(10)
@ExcelProperty(value = {"User information","Administrative division","Municipality"}, index = 1)
private String sjmc;
@ColumnWidth(15)
@ExcelProperty(value = {"User information","Administrative division","County (city, district)"}, index = 2)
private String qxmc;
@ColumnWidth(15)
@ExcelProperty(value = {"User information","User name"}, index = 3)
private String userName;
@ColumnWidth(15)
@ExcelProperty(value = {"User information","Total number of orders"}, index = 4)
private String sumNum;
}
2. Control layer entrance
Description: This code is mainly used to create data and handle exceptions in case of failure exceptions and convert them into json format for easy front-end display. Just copy the serial number according to your needs.
@GetMapping("/excelTest")
public void excel(HttpServletRequest req, HttpServletResponse res,@RequestParam("xzqhdm") String xzqhdm) throws Exception {
try {
log.info("Start export");
res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
res.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("test", "UTF-8").replaceAll("\ + ", " ");
res.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
List<ReportUserInfoBO> userList = new ArrayList<>();
ReportUserInfoBO bo;
//Create data
for (int i = 1; i < 30; i + + ) {
bo = new ReportUserInfoBO();
if (i < 10) {
bo.setQxmc("Qiaoxi");
bo.setSjmc("Shijiazhuang");
} else {
bo.setQxmc("Qiaodong");
bo.setSjmc("Qinhuangdao");
}
bo.setSumNum(String.valueOf(i * (i + 9)));
bo.setUserName("Test" + i);
userList.add(bo);
}
//Imitate the database to retrieve data without serial number assignment
for (int i = 0; i < userList.size(); i + + ) {
userList.get(i).setId(String.valueOf(i + 1));
}
excelUtil.ReportExcel(userList, res);
log.info("Export successful");
} catch (Exception e) {
log.error(e.getMessage());
res.reset();
res.setContentType("application/json");
res.setCharacterEncoding("utf-8");
String origin = req.getHeader("Origin");
res.setHeader("Access-Control-Allow-Origin", origin);
res.getWriter().println(JSON.toJSONString(SysResult.fail(e.getMessage())));
}finally {
res.getOutputStream().close();
}
3. Export tool ExcelUtil
illustrate:
Writing data requires a nested List, so I prepared the list beizhu. WriteCellStyle will be used in MergeLastWriteHandler to set the style, font, shading, border, etc. here.
mergeColumeIndex is the parameter required to merge municipal fields
mergeRowIndex is the parameter required to merge city-level fields
public void ReportExcel(List<ReportUserInfoBO> listVo, HttpServletResponse response) throws IOException {
//Remarks at the end
List<List<String>> beizhu = new ArrayList<>();
List<String> list = new ArrayList<>();
//Add a description at the end. When merging the last row of cells, assign the value to MergeLastWriteHandler.
list.add("Description");
beizhu.add(list);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//Title font size
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(contentWriteFont);
//title yellow shading
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
//frame
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
// Center horizontally
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// Center vertically
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// Set automatic line wrapping. "\
" needs to be added to the prerequisite content to be effective.
contentWriteCellStyle.setWrapped(true);
//Initialize style
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
//Parameters required to merge cells
//Merge coordinates
int[] mergeColumeIndex = {1, 1};
//Start merging after the second line
int mergeRowIndex = 1;
try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ReportUserInfoBO.class).autoCloseStream(Boolean.FALSE)
//style style
.registerWriteHandler(horizontalCellStyleStrategy)
//Merge description and assign value
.registerWriteHandler(new MergeLastWriteHandler())
//Automatically merge city-level cells. Note that something went wrong with the two input parameters.
.registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex))
.build()) {
WriteSheet sheet = EasyExcel.writerSheet("Test").build();
excelWriter.write(listVo, sheet);
excelWriter.write(beizhu, sheet);
}
}
4. Merge tool classes
1. Merge a certain field
Learn from the code of your predecessors and note that different versions may have inconsistent cell methods.
mergeColumnIndex is the range of cells you want to merge
mergeRowIndex: From which row should the merge begin?
package com.hua.Util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
public class ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelMergeUtil() {
}
public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//Current row
int curRowIndex = cell.getRowIndex();
//Current column
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i + + ) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* Merge current cells upward
*
* @param writeSheetHolder
* @param cell current cell
* @param curRowIndex current row
* @param curColIndex current column
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellType()== CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// Compare the current cell data with the previous cell data
Boolean dataBool = preData.equals(curData);
//It should be noted here that the data in the second column of each row is obtained and compared with the data in the first column of the previous row. If they are equal and merged, the value in getCell is the subscript of the column where the name is located.
Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
if (dataBool & amp; & amp; bool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() & amp; & amp; !isMerged; i + + ) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// If the previous cell has been merged, remove the original merged unit first, and then add the merged unit again.
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// If the previous cell has not been merged, add a new merged cell
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
2. Incorporate instructions at the end of the article
In excelutil, a list named beizhu will be inserted at the end. This method may determine whether to merge cells based on the value of beizhu.
package com.hua.Util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class MergeLastWriteHandler extends AbstractMergeStrategy {
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
String strValue = cell.getStringCellValue();
if (StringUtils.equals(strValue,"Description")){
//If the last line is a description, reassign it
cell.setCellValue("Note: This table is only for testing and learning! Let's take a look at the final effect");
//Get the last row of the table
int lastRowNum = sheet.getLastRowNum();
//Merge Cells
CellRangeAddress region = new CellRangeAddress(lastRowNum,lastRowNum,0,4);
sheet.addMergedRegion(region);
}
}
}
5. Test
Test tool sends save request
save
Final Results
