[Java]Spring/SpringBoot生成Excel文件并提供下载

在Spring或者SpringBoot程序里，生成Excel文件比较方便，Spring已经通过org.springframework.web.servlet.view.document.AbstractXlsxView提供了对Excel的支持，我们只需要实现它就可以。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

package com.terrynow.util;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.StringUtils;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @date 2021-2-2 11:10
 * @description
 */
public class ExcelView extends AbstractXlsxView {

    //这里的Key用在HashMap里的key，携带value数据，在Controller里作为Map<String, Object> model传给ModelAndView，
    //然后在下面的方法buildExcelDocument里取出来，组装成excel
    //所以可以根据自己的需要，自定义一些key，来自定义你的Excel
    public static final String KEY_EXCEL_FILE_NAME = "KEY_EXCEL_FILE_NAME";
    public static final String KEY_EXCEL_SHEET_NAME = "KEY_EXCEL_SHEET_NAME";
    public static final String KEY_EXCEL_TITLE = "KEY_EXCEL_FILE_TITLE";
    //表头，数据是一个String数组
    public static final String KEY_EXCEL_HEADERS = "KEY_EXCEL_HEADERS";
    //Excel的主体数据，value是一个List<String[]>
    public static final String KEY_EXCEL_DATA = "KEY_EXCEL_DATA";
    public static final String KEY_EXCEL_BORDER = "KEY_EXCEL_DATA_BORDER";

    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
                                      HttpServletRequest httpServletRequest, HttpServletResponse response) {
        String fileName = (String) model.get(KEY_EXCEL_FILE_NAME);
        String sheetName = (String) model.get(KEY_EXCEL_SHEET_NAME);
        if (StringUtils.isEmpty(sheetName)) {
            sheetName = fileName;
        }
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xlsx\"; charset=utf-8");

        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("黑体");//默认好像宋体，粗体不太明显？
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(font);

        boolean border = model.get(KEY_EXCEL_BORDER) != null;
        if (border) {
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
        }

        CellStyle borderStyle = workbook.createCellStyle();
        if (border) {
            borderStyle.setBorderBottom(BorderStyle.THIN);
            borderStyle.setBorderLeft(BorderStyle.THIN);
            borderStyle.setBorderRight(BorderStyle.THIN);
            borderStyle.setBorderTop(BorderStyle.THIN);
        }

        String[] header = (String[]) model.get(KEY_EXCEL_HEADERS);
        List<String[]> data = (List<String[]>) model.get(KEY_EXCEL_DATA);

        // create excel xls sheet
        Sheet sheet = workbook.createSheet(sheetName);

        int rowNumber = 0;

        String title = (String) model.get(KEY_EXCEL_TITLE);
        if (!StringUtils.isEmpty(title)) {
            Font fontForTitle = workbook.createFont();
//            fontForTitle.setBold(true);
            fontForTitle.setFontHeightInPoints((short) 24);
            CellStyle cellStyleForTitle = workbook.createCellStyle();
            cellStyleForTitle.setAlignment(HorizontalAlignment.LEFT);
            cellStyleForTitle.setFont(fontForTitle);
            cellStyleForTitle.setAlignment(HorizontalAlignment.CENTER);

            Row titleRow = sheet.createRow(rowNumber++);
            Cell cell = titleRow.createCell(0);
            cell.setCellStyle(cellStyleForTitle);
            cell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));
        }

        // create header row
        Row headerRow = sheet.createRow(rowNumber++);
        for (int i = 0; i < header.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(header[i]);
        }

        // Create data cells
        for (String[] datum : data) {
            Row courseRow = sheet.createRow(rowNumber++);
            for (int i = 0; i < datum.length; i++) {
                Cell cell = courseRow.createCell(i);
                cell.setCellStyle(borderStyle);
                cell.setCellValue(datum[i]);
            }
        }

        // 如果有title的，用row0做调整，否则row1做调整
        autoSizeColumns(workbook, 0, StringUtils.isEmpty(title) ? 0 : 1);

        // 每一列都是文本格式
        // see https://stackoverflow.com/questions/6510012/apache-poi-for-excel-setting-the-cell-type-to-text-for-an-entire-column
        DataFormat fmt = workbook.createDataFormat();
        CellStyle textStyle = workbook.createCellStyle();
        textStyle.setDataFormat(fmt.getFormat("@"));
        for (int i = 0; i < header.length; i++) {
            sheet.setDefaultColumnStyle(i, textStyle);
        }
    }

    //用这个实现每一列宽度自动调整
    private void autoSizeColumns(Workbook workbook, int targetSheetNumber, int targetRow) {
//        int numberOfSheets = workbook.getNumberOfSheets();
//        for (int i = 0; i < numberOfSheets; i++) {
//            Sheet sheet = workbook.getSheetAt(i);
        Sheet sheet = workbook.getSheetAt(targetSheetNumber);
        if (sheet.getPhysicalNumberOfRows() > targetRow) {
            Row row = sheet.getRow(targetRow);
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();
                sheet.autoSizeColumn(columnIndex);
            }
        }
//        }
    }
}

@Controller
public class BomController {

    @RequestMapping("/xxx.xlsx")
        public ModelAndView listPipesExcel() {
            HashMap<String, Object> model = new HashMap<>();

            map.put(ExcelView.KEY_EXCEL_FILE_NAME, "文件名称");
            map.put(ExcelView.KEY_EXCEL_SHEET_NAME, "工作表名称");

            String[] header = new String[]{"编号", "名称", "备注"};
            map.put(ExcelView.KEY_EXCEL_HEADERS, header);

            List<String[]> data = new ArrayList();
            data2.add(new String[]{"1","名称1","备注1"});
            data2.add(new String[]{"2","名称2","备注2"});
            map.put(ExcelView.KEY_EXCEL_DATA, data);

            ExcelView viewExcel = new ExcelView();
            return new ModelAndView(viewExcel, model);
    }

}

如何生成普通 Excel 文件，请参考：https://blog.terrynow.com/2021/02/03/generate-excel-file-using-apache-poi/

如何合并单元格，详见 https://blog.terrynow.com/2021/02/11/apache-poi-merge-cells/