使用POI中的SXSSFWorkbook做excel导出，并自定义模板导出

1. 引入依赖

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

2. 简单导出实现

package cn.sto.station.twin.common.excel;
 
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/**
 * SXSSFWorkbook导出
 *
 * @date 2022-05-30 10:50
 */
@Slf4j
public class SXSSFWorkbookExporter {
 
    /**
     * 表头
     */
    private String[] headerNames;
    /**
     * 工作簿
     */
    private Workbook workBook;
    /**
     * excel工作表
     */
    private Sheet sheet;
 
    /**
     *
     * @param headerNames 表头
     * @param sheetName sheet的名称
     */
    public SXSSFWorkbookExporter(String[] headerNames, String sheetName) {
        this.headerNames = headerNames;
        // 创建一个工作簿 内存中保持100条数据, 超出的部分刷新到磁盘上
        workBook = new SXSSFWorkbook(100);
        // 创建一个工作表sheet
        sheet = workBook.createSheet(sheetName);
        initHeader();
    }
 
    /**
     * 初始化表头信息
     */
    private void initHeader() {
        // 创建第一行
        Row row = sheet.createRow(0);
        Cell cell = null;
        // 创建表头
        for (int i = 0; i < headerNames.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headerNames[i]);
            setHeaderCellStyle(cell);
        }
    }
 
    /**
     * 设置表头单元格样式
     *
     * @param cell 单元格
     */
    public void setHeaderCellStyle(Cell cell) {
        // 设置样式
        CellStyle cellStyle = workBook.createCellStyle();
        // 设置字体居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体
        Font font = workBook.createFont();
        font.setFontName(HSSFFont.FONT_ARIAL);
        // 设置加粗
        font.setBold(true);
        // 设置字号
        font.setFontHeightInPoints((short) 13);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }
 
    /**
     *
     * @param datas 数据,每一个map都是一行
     * @param keys key[i]代表从map中获取keys[i]的值作为第i列的值,如果传的是null默认取表头
     */
    public void createTableRows(List<Map<String, Object>> datas, String[] keys) {
        for (int i = 0, length_1 = datas.size(); i < length_1; i++) {
            if (ArrayUtils.isEmpty(keys)) {
                keys = headerNames;
            }
            // 创建行(从第二行开始)
            Map<String, Object> data = datas.get(i);
            Row row = sheet.createRow(i + 1);
            Cell cell = null;
            for (int j = 0, length_2 = keys.length; j < length_2; j++) {
                // 单元格获取map中的key
                String key = keys[j];
                String value = MapUtils.getString(data, key, "");
                cell = row.createCell(j);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(value);
            }
 
        }
    }
 
    /**
     * 根据表头自动调整列宽度
     */
    public void autoAllSizeColumn() {
        // 如果是SXSSFSheet，需要调用trackAllColumnsForAutoSizing方法一次
        if (sheet instanceof SXSSFSheet) {
            SXSSFSheet tmpSheet = (SXSSFSheet) sheet;
            tmpSheet.trackAllColumnsForAutoSizing();
        }
        for (int i = 0, length = headerNames.length; i < length; i++) {
            sheet.autoSizeColumn(i);
        }
    }
 
    /**
     * 将数据写出到excel中
     *
     * @param outputStream
     */
    public void exportExcel(OutputStream outputStream) {
        // 导出之前先自动设置列宽
        this.autoAllSizeColumn();
        try {
            workBook.write(outputStream);
        } catch (IOException e) {
            log.error(" exportExcel error", e);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }
 
    /**
     * 将数据写出到excel中
     *
     * @param outputFilePath
     */
    public void exportExcel(String outputFilePath) {
        // 导出之前先自动设置列宽
        this.autoAllSizeColumn();
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(outputFilePath);
            workBook.write(outputStream);
        } catch (IOException e) {
            log.error(" exportExcel error", e);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }
 
    public static void main(String[] args) {
        test();
    }
 
    private static void test() {
        SXSSFWorkbookExporter hssfWorkExcel = new SXSSFWorkbookExporter(new String[] { "姓名", "年龄" }, "人员基本信息");
        List<Map<String, Object>> datas = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Map data = new HashMap<>();
            data.put("name", "name" + i);
            data.put("age", "age" + i);
            datas.add(data);
        }
        hssfWorkExcel.createTableRows(datas, new String[] { "name", "age" });
 
        try {
            hssfWorkExcel.exportExcel(new FileOutputStream(new File("e:/test1.xlsx")));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
 
}

3.自定义模板导出

public void customizationExcel(Map<String, String> dateMap, List<Map<String, String>> dateMapList, HttpServletResponse response) {
        try {
            //新建工作簿
            SXSSFWorkbook workbook = new SXSSFWorkbook(100);
            Sheet sheet = workbook.createSheet();//工作薄名称
            //设置每格数据的样式
            Font ParamFontStyle = workbook.createFont();
            CellStyle cellParamStyle = workbook.createCellStyle();
            cellParamStyle.setAlignment(HorizontalAlignment.CENTER);//垂直居中
            cellParamStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
            cellParamStyle.setWrapText(false);//自动换行
            ParamFontStyle.setFontHeightInPoints((short) 11);//字体大小
            ParamFontStyle.setFontName("宋体");
            cellParamStyle.setFont(ParamFontStyle);

            //设置表头的样式
            Font ParamFontStyle1 = workbook.createFont();
            CellStyle cellParamStyle1 = workbook.createCellStyle();
            cellParamStyle1.setAlignment(HorizontalAlignment.LEFT);
            cellParamStyle1.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);
            cellParamStyle1.setWrapText(false);//自动换行
            ParamFontStyle1.setFontHeightInPoints((short) 15);
            ParamFontStyle1.setFontName("黑体");
            ParamFontStyle1.setBold(true);//是否打开加粗
            cellParamStyle1.setFont(ParamFontStyle1);

            //设置标题的样式
            Font ParamFontStyle2 = workbook.createFont();
            CellStyle cellParamStyle2 = workbook.createCellStyle();
            cellParamStyle2.setAlignment(HorizontalAlignment.CENTER);//垂直居中
            cellParamStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
            cellParamStyle2.setWrapText(true);//自动换行
            ParamFontStyle2.setFontHeightInPoints((short) 11);
            ParamFontStyle2.setFontName("黑体");
            ParamFontStyle2.setBold(true);
            cellParamStyle2.setFont(ParamFontStyle2);

            //设置标题的样式
            Font ParamFontStyle3 = workbook.createFont();
            CellStyle cellParamStyle3 = workbook.createCellStyle();
            cellParamStyle3.setAlignment(HorizontalAlignment.CENTER);//垂直居中
            cellParamStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
            cellParamStyle3.setWrapText(true);//自动换行
            ParamFontStyle3.setFontHeightInPoints((short) 18);
            ParamFontStyle3.setFontName("黑体");
            ParamFontStyle3.setBold(true);
            cellParamStyle3.setFont(ParamFontStyle3);

            //定义列的宽度
            //sheet.setDefaultColumnWidth(40 * 1024);默认sheet1.CreateRow(0).Height = 200*20;//宽度
            Short height = 390;
            sheet.setDefaultRowHeight((Short) height);
            sheet.setColumnWidth(0, 5000);
            sheet.setColumnWidth(1, 5000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 5000);
            sheet.setColumnWidth(4, 5000);
            sheet.setColumnWidth(5, 5000);
            sheet.setColumnWidth(6, 5000);
            sheet.setColumnWidth(7, 5000);
            sheet.setColumnWidth(8, 5000);
            sheet.setColumnWidth(9, 5000);
            sheet.setColumnWidth(10, 5000);
            sheet.setColumnWidth(11, 5000);
            sheet.setColumnWidth(12, 5000);

            //日期
            Row rows1 = sheet.createRow(0);
            Cell cell1 = rows1.createCell(0);
            Cell cell7 = rows1.createCell(1);

            //科室
            Cell cell2 = rows1.createCell(2);
            Cell cell8 = rows1.createCell(3);

            //班次
            Cell cell3 = rows1.createCell(6);
            Cell cell9 = rows1.createCell(7);

            //时间
            Cell cell4 = rows1.createCell(8);
            Cell cell10 = rows1.createCell(9);

            //全天班次
            Row rows5 = sheet.createRow(1);
            Cell cell5 = rows5.createCell(0);
            Cell cell11 = rows5.createCell(1);

            //白班
            Row rows6 = sheet.createRow(2);
            Cell cell6 = rows6.createCell(0);
            Cell cell12 = rows6.createCell(1);

            //合并单元格
            CellRangeAddress region = new CellRangeAddress(0, 0, 3, 5);
            sheet.addMergedRegion(region);
            CellRangeAddress region1 = new CellRangeAddress(1, 1, 1, 12);
            sheet.addMergedRegion(region1);
            CellRangeAddress region2 = new CellRangeAddress(2, 2, 1, 12);
            sheet.addMergedRegion(region2);
            CellRangeAddress region3 = new CellRangeAddress(0, 0, 9, 12);
            sheet.addMergedRegion(region3);

            //标题单元格风格
            cell1.setCellStyle(cellParamStyle2);
            cell2.setCellStyle(cellParamStyle2);
            cell3.setCellStyle(cellParamStyle2);
            cell4.setCellStyle(cellParamStyle2);
            cell5.setCellStyle(cellParamStyle2);
            cell6.setCellStyle(cellParamStyle2);
            //对应值单元格风格
            cell7.setCellStyle(cellParamStyle);
            cell8.setCellStyle(cellParamStyle);
            cell9.setCellStyle(cellParamStyle);
            cell10.setCellStyle(cellParamStyle);
            cell11.setCellStyle(cellParamStyle);
            cell12.setCellStyle(cellParamStyle);

            //标题值
            cell1.setCellValue("交班日期:");
            cell2.setCellValue("科室:");
            cell3.setCellValue("班次:");
            cell4.setCellValue("时间:");
            cell5.setCellValue("全天班次:");
            cell6.setCellValue("班制:");
            //TODO 填充数据
            cell7.setCellValue(dateMap.get("交班日期"));
            cell8.setCellValue(dateMap.get("科室"));
            cell9.setCellValue(dateMap.get("班次"));
            cell10.setCellValue((Objects.equals(dateMap.get("开始时间"), null) ? "" : dateMap.get("开始时间") + " - " + (Objects.equals(dateMap.get("结束时间"), null) ? "" : dateMap.get("结束时间"))));
            cell11.setCellValue(dateMap.get("全天班次"));
            cell12.setCellValue(dateMap.get("白班"));

            //列名
            Row row1 = sheet.createRow(3);
            Cell row1Cell0 = row1.createCell(0);
            row1Cell0.setCellValue("类别");
            row1Cell0.setCellStyle(cellParamStyle2);
            Cell row1Cell = row1.createCell(1);
            row1Cell.setCellValue("床号");
            row1Cell.setCellStyle(cellParamStyle2);
            Cell row1Cell1 = row1.createCell(2);
            row1Cell1.setCellValue("姓名");
            row1Cell1.setCellStyle(cellParamStyle2);
            Cell row1Cell2 = row1.createCell(3);
            row1Cell2.setCellValue("出生日期");
            row1Cell2.setCellStyle(cellParamStyle2);
            Cell row1Cell3 = row1.createCell(4);
            row1Cell3.setCellValue("入院日期");
            row1Cell3.setCellStyle(cellParamStyle2);
            Cell row1Cell4 = row1.createCell(5);
            row1Cell4.setCellValue("诊断");
            row1Cell4.setCellStyle(cellParamStyle2);
            Cell row1Cell5 = row1.createCell(6);
            row1Cell5.setCellValue("现状");
            row1Cell5.setCellStyle(cellParamStyle2);
            Cell row1Cell6 = row1.createCell(7);
            row1Cell6.setCellValue("背景");
            row1Cell6.setCellStyle(cellParamStyle2);
            Cell row1Cell7 = row1.createCell(8);
            row1Cell7.setCellValue("评估");
            row1Cell7.setCellStyle(cellParamStyle2);
            Cell row1Cell8 = row1.createCell(9);
            row1Cell8.setCellValue("建议");
            row1Cell8.setCellStyle(cellParamStyle2);
            Cell row1Cell9 = row1.createCell(10);
            row1Cell9.setCellValue("P班");
            row1Cell9.setCellStyle(cellParamStyle2);
            Cell row1Cell10 = row1.createCell(11);
            row1Cell10.setCellValue("N班");
            row1Cell10.setCellStyle(cellParamStyle2);
            Cell row1Cell11 = row1.createCell(12);
            row1Cell11.setCellValue("交/接班护士");
            row1Cell11.setCellStyle(cellParamStyle2);


            //TODO 填充数据
            int sheet1Colume = 0;
            for (Map<String, String> hospitalExcel : dateMapList) {
                Row row2 = sheet.createRow(sheet1Colume + 4);//下标从0开始，对应excel从第4行插入列表数据值

                Cell row2Cell = row2.createCell(0);
                row2Cell.setCellStyle(cellParamStyle);
                row2Cell.setCellValue(hospitalExcel.get("类别"));

                Cell row2Cell1 = row2.createCell(1);
                row2Cell1.setCellStyle(cellParamStyle);
                row2Cell1.setCellValue(hospitalExcel.get("cw"));

                Cell row2Cell2 = row2.createCell(2);
                row2Cell2.setCellStyle(cellParamStyle);
                row2Cell2.setCellValue(hospitalExcel.get("xm"));

                Cell row2Cell3 = row2.createCell(3);
                row2Cell3.setCellStyle(cellParamStyle);
                row2Cell3.setCellValue(hospitalExcel.get("bir"));

                Cell row2Cell4 = row2.createCell(4);
                row2Cell4.setCellStyle(cellParamStyle);
                row2Cell4.setCellValue(hospitalExcel.get("rysj"));

                Cell row2Cell5 = row2.createCell(5);
                row2Cell5.setCellStyle(cellParamStyle);
                row2Cell5.setCellValue(hospitalExcel.get("zd"));

                Cell row2Cell6 = row2.createCell(6);
                row2Cell6.setCellStyle(cellParamStyle);
                row2Cell6.setCellValue(hospitalExcel.get("xz"));

                Cell row2Cell7 = row2.createCell(7);
                row2Cell7.setCellStyle(cellParamStyle);
                row2Cell7.setCellValue(hospitalExcel.get("bj"));

                Cell row2Cell8 = row2.createCell(8);
                row2Cell8.setCellStyle(cellParamStyle);
                row2Cell8.setCellValue(hospitalExcel.get("pg"));

                Cell row2Cell9 = row2.createCell(9);
                row2Cell9.setCellStyle(cellParamStyle);
                row2Cell9.setCellValue(hospitalExcel.get("jy"));

                Cell row2Cell10 = row2.createCell(10);
                row2Cell10.setCellStyle(cellParamStyle);
                row2Cell10.setCellValue(hospitalExcel.get("Pb"));

                Cell row2Cell11 = row2.createCell(11);
                row2Cell11.setCellStyle(cellParamStyle);
                row2Cell11.setCellValue(hospitalExcel.get("Nb"));

                Cell row2Cell12 = row2.createCell(12);
                row2Cell12.setCellStyle(cellParamStyle);
                row2Cell12.setCellValue((Objects.equals(hospitalExcel.get("jbhs"), null) ? "" : (hospitalExcel.get("jbhs")) + "/" + (Objects.equals(hospitalExcel.get("sbhs"), null) ? "" : hospitalExcel.get("sbhs"))));

                sheet1Colume++;
            }
            String fileName = new String(DateUtil.now().getBytes(), "UTF-8") + ".xls";
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setCharacterEncoding("UTF-8");
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            try {
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

导出效果!