preface

This article will show you how to use EasyExcel custom interceptor to add an increment of ordinal column to the final Excel file. The result is as follows:

In addition, the full code sample used in this article has been uploaded to GitHub.

implementation

In this article, we define an interceptor that inherits AbstractRowWriteHandler to add an index column to the final export result. By modifying the Map content of the source code to save the header title to leave space for the index column, we show the final code first:

/** * Custom Excel row processor, add ordinal column **@author butterfly
 * @dateThe 2020-09-05 * /
@Component
public class AddNoHandler extends AbstractRowWriteHandler {

	private boolean init = true;

	@Override
	public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
		if (init) {
			// Modify the map where the header and corresponding field information are stored, move the contents of the map one bit to the right, and reserve the newly added sequence number as the first column
			ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.excelWriteHeadProperty();
			Map<Integer, Head> headMap = excelWriteHeadProperty.getHeadMap();
			Map<Integer, ExcelContentProperty> contentMap = excelWriteHeadProperty.getContentPropertyMap();
			int size = headMap.size();
			for (int current = size; current > 0; current--) {
				int previous = current - 1;
				headMap.put(current, headMap.get(previous));
				contentMap.put(current, contentMap.get(previous));
			}
			// empty the first column
			headMap.remove(0);
			contentMap.remove(0);
			// You only need to change the map once, so use the init variable to control
			init = false; }}@Override
	public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
		// Add ordinal column after row creation
		Cell cell = row.createCell(0);
		int rowNum = row.getRowNum();
		if (rowNum == 0) {
			cell.setCellValue(ExcelConstant.TITLE);
		} else{ cell.setCellValue(rowNum); }}@Override
	public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
		if (row.getLastCellNum() > 1) {
			// Set the style of the newly added ordinal column to the default style
			row.getCell(0).setCellStyle(row.getCell(1).getCellStyle()); }}}Copy the code

The afterRowCreate and afterRowDispose methods are easy to understand, one for controlling the content of new ordinal columns and one for controlling the style of new columns. The content in beforeRowCreate is a little more complicated, and is used to empty a new ordinal column. Also, because beforeRowCreate is called every time a row is created, but the original Map that stores the header information only needs to be modified once, this is done using init variable control.

Train of thought

By looking at the com. Alibaba. Excel. Write. Executor. ExcelWriteAddExecutor addOneRowOfDataToExcel method in the class of source code, AfterRowCreate and beforeRowCreate are called before and after a new row is added. AfterRowDispose is called after a row is added. This is how the interceptor works.

private void addOneRowOfDataToExcel(Object oneRowData, int n, int relativeRowIndex,
                                    Map<Integer, Field> sortedAllFiledMap) {
    // If the row is empty, return directly
    if (oneRowData == null) {
        return;
    }
    // Create a data row object and call the interceptor before and after the row
    WriteHandlerUtils.beforeRowCreate(writeContext, n, relativeRowIndex, Boolean.FALSE);
    Row row = WorkBookUtil.createRow(writeContext.writeSheetHolder().getSheet(), n);
    WriteHandlerUtils.afterRowCreate(writeContext, row, relativeRowIndex, Boolean.FALSE);
    // Populate the row with the entity data content
    if (oneRowData instanceof List) {
        addBasicTypeToExcel((List) oneRowData, row, relativeRowIndex);
    } else {
        // We'll continue to look at this method below
        addJavaObjectToExcel(oneRowData, row, relativeRowIndex, sortedAllFiledMap);
    }
    // After the row is created, the corresponding interceptor is invoked
    WriteHandlerUtils.afterRowDispose(writeContext, row, relativeRowIndex, Boolean.FALSE);
}
Copy the code

We can modify the contents of headMap and contentMap to achieve the final effect, just look at the addJavaObjectToExcel method code in this class to see why:

private void addJavaObjectToExcel(Object oneRowData, Row row, int relativeRowIndex,
                                  Map<Integer, Field> sortedAllFiledMap) {
        WriteHolder currentWriteHolder = writeContext.currentWriteHolder();
    	// Map your own entity data to beanMap
        BeanMap beanMap = BeanMap.create(oneRowData);
        Set<String> beanMapHandledSet = new HashSet<String>();
        int cellIndex = 0;
        // If it's a class it needs to be cast by type
        if (HeadKindEnum.CLASS.equals(writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadKind())) {
            // We are modifying the headMap and contentPropertyMap contents here
            Map<Integer, Head> headMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadMap();
            Map<Integer, ExcelContentProperty> contentPropertyMap =
                writeContext.currentWriteHolder().excelWriteHeadProperty().getContentPropertyMap();
            // Iterate over all column headers and insert data
            for (Map.Entry<Integer, ExcelContentProperty> entry : contentPropertyMap.entrySet()) {
                // Get the subscript of the cell, and then insert the contents into the specified column
                cellIndex = entry.getKey();
                ExcelContentProperty excelContentProperty = entry.getValue();
                String name = excelContentProperty.getField().getName();
                if(! beanMap.containsKey(name)) {continue;
                }
                // Control the contents of the cellHead head = headMap.get(cellIndex); WriteHandlerUtils.beforeCellCreate(writeContext, row, head, cellIndex, relativeRowIndex, Boolean.FALSE); Cell cell = WorkBookUtil.createCell(row, cellIndex); WriteHandlerUtils.afterCellCreate(writeContext, cell, head, relativeRowIndex, Boolean.FALSE); Object value = beanMap.get(name); CellData cellData = converterAndSet(currentWriteHolder, excelContentProperty.getField().getType(), cell, value, excelContentProperty, head, relativeRowIndex); WriteHandlerUtils.afterCellDispose(writeContext, cellData, cell, head, relativeRowIndex, Boolean.FALSE); beanMapHandledSet.add(name); }}// the following is omitted
    }
Copy the code

other

With the above custom interceptor, you can write a simple demo to test it:

/** * Excel download controller **@author butterfly
 * @dateThe 2021-09-05 * /
@RestController
public class ExcelController {

    /** * Add serial number column test **@param response response
     */
    @GetMapping("/col")
    public void col(HttpServletResponse response) {
        try {
            List<Student> students = getStudentList();
            EasyExcel.write(response.getOutputStream(), Student.class)
                    .registerWriteHandler(new AddNoHandler())
                    .sheet()
                    .doWrite(students);
        } catch(Exception e) { System.out.println(ExcelConstant.DOWNLOAD_FAILED); }}/** * Generate student list **@returnStudent list */
    private List<Student> getStudentList(a) {
        return Arrays.asList(
                new Student("2021090101"."Zhang".19),
                new Student("2021090102"."Bill".18),
                new Student("2021090103"."Two".20)); }}Copy the code

Then there is the front-end test code:

<! DOCTYPEhtml>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>File download test</title>
    <script src="https://cdn.staticfile.org/axios/0.21.2/axios.min.js"></script>
</head>
<body>
    <button onclick="col()">Add ordinal column tests</button>
    <script>
        function col() {
            download('http://localhost:8080/col'.'col.xlsx')}function download(url, name) {
            axios({
                url: url,
                responseType: 'blob'
            }).then((response) = > {
                const URL = window.URL.createObjectURL(response.data)
                const tempLink = document.createElement('a')
                tempLink.style.display = 'none'
                tempLink.href = URL
                tempLink.setAttribute('download', name)
                if (typeof tempLink.download === 'undefined') {
                    tempLink.setAttribute('target'.'_blank')}document.body.appendChild(tempLink)
                tempLink.click()
                document.body.removeChild(tempLink)
                window.URL.revokeObjectURL(URL)
            })
        }
    </script>
</body>
</html>
Copy the code

conclusion

This is one way to dynamically add an autoordinal column. You can also add an autoordinal column to an entity and modify the table data, or you can just set the autoordinal data in afterRowCreate without modifying the header Map data. Another through custom templates, then fill the template to achieve the same effect, can refer to https://www.yuque.com/easyexcel/doc/fill, but the two methods all need to modify the original data, or need to increase the operating definition template, here no longer.