Java POI实现Excel导出

ExcelFormatUtil样式工具类

package com.ymf.invoice.common.utils;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.List;

/**
 * @ClassName: ExcelFormatUtil
 * @author: 〆、dyh
 * @since: 2023/1/11 17:34
 */

public class ExcelFormatUtil {
    /**
     * 设置报表头样式
     *
     * @param workbook
     * @return
     */
    public static CellStyle headSytle(SXSSFWorkbook workbook) {
        // 设置style1的样式，此样式运用在第二行
        CellStyle style1 = workbook.createCellStyle();// cell样式
        // 设置单元格背景色，设置单元格背景色以下两句必须同时设置
        style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置填充样式
        style1.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);// 设置填充色
        // 设置单元格上、下、左、右的边框线
        style1.setBorderBottom(BorderStyle.THIN);
        style1.setBorderLeft(BorderStyle.THIN);
        style1.setBorderRight(BorderStyle.THIN);
        style1.setBorderTop(BorderStyle.THIN);
        Font font1 = workbook.createFont();// 创建一个字体对象
        font1.setFontName("Arial");// 设置字体的名称
        font1.setFontHeightInPoints((short) 16);// 设置字体的高度
        font1.setBold(true);// 粗体显示
        font1.setColor(IndexedColors.WHITE.index);
        style1.setFont(font1);// 设置style1的字体
        style1.setWrapText(true);// 设置自动换行
        style1.setAlignment(HorizontalAlignment.CENTER);// 设置单元格字体显示居中（左右方向）
        style1.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格字体显示居中(上下方向)
        return style1;
    }

    /**
     * 设置报表体样式
     *
     * @param wb
     * @return
     */
    public static CellStyle contentStyle(SXSSFWorkbook wb) {

        // 报表体样式
        CellStyle style1 = wb.createCellStyle();// cell样式
        // 设置单元格上、下、左、右的边框线
        style1.setBorderBottom(BorderStyle.THIN);
        style1.setBorderLeft(BorderStyle.THIN);
        style1.setBorderRight(BorderStyle.THIN);
        style1.setBorderTop(BorderStyle.THIN);
        style1.setWrapText(true);// 设置自动换行
        style1.setAlignment(HorizontalAlignment.CENTER);// 设置单元格字体显示居中（左右方向）
        style1.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格字体显示居中(上下方向)
        style1.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style1.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style1.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style1.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style1.setFont(dataFont);
        return style1;
    }

    /**
     * 设置报表标题样式
     *
     * @param workbook
     * @return
     */
    public static CellStyle titleSytle(SXSSFWorkbook workbook, short color, short fontSize) {
        // 设置style1的样式，此样式运用在第一行
        CellStyle style1 = workbook.createCellStyle();// cell样式

        if (color != HSSFColor.HSSFColorPredefined.WHITE.getIndex()) {
            style1.setFillForegroundColor(color);// 设置填充色
        }
        // 设置单元格上、下、左、右的边框线
        style1.setBorderBottom(BorderStyle.THIN);
        style1.setBorderLeft(BorderStyle.THIN);
        style1.setBorderRight(BorderStyle.THIN);
        style1.setBorderTop(BorderStyle.THIN);
        Font font1 = workbook.createFont();// 创建一个字体对象
        font1.setFontName("Arial");// 设置字体的名称
        font1.setFontHeightInPoints(fontSize);// 设置字体的高度
        font1.setBold(true);// 粗体显示
        style1.setFont(font1);// 设置style1的字体
        style1.setWrapText(true);// 设置自动换行
        style1.setAlignment(HorizontalAlignment.CENTER);// 设置单元格字体显示居中（左右方向）
        style1.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格字体显示居中(上下方向)
        return style1;
    }

    /**
     * 设置表头
     *
     * @param sheet
     */
    public static void initTitleEX(SXSSFSheet sheet, CellStyle header, List<String> title, List<Integer> titleLength) {
        SXSSFRow row0 = sheet.createRow(1);
        row0.setHeight((short) 800);
        for (int j = 0; j < title.size(); j++) {
            SXSSFCell cell = row0.createCell(j);
            //设置每一列的字段名
            cell.setCellValue(title.get(j));
            cell.setCellStyle(header);
            sheet.setColumnWidth(j, titleLength.get(j));
        }
    }

}

Controller

    @PostMapping("/export")
    @ApiOperation("导出发票")
    private void export(HttpServletResponse response, InvoiceImgDto invoiceImgDto) {
        invoiceImgService.export(response, invoiceImgDto);
    }

service实现类

   //工作博对象
    private SXSSFWorkbook wb;
    //工作表对象
    private SXSSFSheet sheet;

    //报表头样式
    private CellStyle header;
    //报表体样式
    private CellStyle content;
    //报表标题样式
    private CellStyle title;
    
    private List<String> strs;
    
  public void export(HttpServletResponse response, InvoiceImgDto invoiceImgDto) {
        List<InvoiceImgExcel> excelList = getInvoiceImgExcels(invoiceImgDto);
        exportExcel(response, excelList, "发票签收递送清单", "发票签收递送清单");
        //若依原生
//        ExcelUtil<InvoiceImgExcel> excelUtil = new ExcelUtil<>(InvoiceImgExcel.class);
//        excelUtil.exportExcel(response, excelList, "发票数据", "发票签收递送清单");
    }

    /**
     * 创建一个工作簿
     */
    public void createWorkbook(String sheetName) {
        this.wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中
        this.sheet = this.wb.createSheet();
        //设置工作表名称
        this.wb.setSheetName(0, sheetName);
        this.title = ExcelFormatUtil.titleSytle(this.wb, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), (short) 17);//报表标题样式
        this.header = ExcelFormatUtil.headSytle(this.wb);// 报表头样式
        this.content = ExcelFormatUtil.contentStyle(this.wb);// 报表体样式
    }

    /**
     * 创建excel第一行标题
     */
    public void createTitle(String sheetTitle) {
        SXSSFRow titleRow = this.sheet.createRow(0);
        titleRow.setHeightInPoints(30);
        SXSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(this.title);
        titleCell.setCellValue(sheetTitle);
        //起始行，结束行，起始列，结束列（6是结束列的索引）  合并单元格
        this.sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), 6));
    }

    /**
     * 创建excel第二行表头
     */

    private void createHeader() {
        // 每一列字段名
        strs = Arrays.asList("序号", "供应商名称", "发票号码", "金额（元）", "开票日期", "签收日期", "备注");
        // 字段名所在表格的宽度
        List<Integer> ints = Arrays.asList(5000, 5000, 5000, 5000, 5000, 5000, 5000);
        // 设置表头样式
        ExcelFormatUtil.initTitleEX(this.sheet, this.header, strs, ints);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param response   返回数据
     * @param list       导出数据集合
     * @param sheetName  工作表的名称
     * @param sheetTitle 工作表的标题
     * @return 结果
     */
    private void exportExcel(HttpServletResponse response, List<InvoiceImgExcel> list, String sheetName, String sheetTitle) {
        //设置HttpServletResponse响应类型为xlsx
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//设置HttpServletResponse响应编码为utf-8
        response.setCharacterEncoding("utf-8");
        //初始化属性
        log.info(">>>>>>>>>>>>>>>>>>>>开始>>>>>>>>>>");
//        创建一个工作簿
        createWorkbook(sheetName);
//      创建excel第一行标题
        createTitle(sheetTitle);
//      创建excel第二行表头
        createHeader();
        log.info(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>");
        //处理业务
        handleBusiness(list);
        //            将HttpServletResponse 输出流 写入到工作薄对象中，达到前端响应流可以下载的效果
        try {
            this.wb.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }

    private void handleBusiness(List<InvoiceImgExcel> list) {

        if (list != null && list.size() > 0) {
//            记录行合并的起始行
            int constant = 0;

            log.info(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>");
            for (int i = 0; i < list.size(); i++) {
                constant = getConstant(list, constant, i);
            }
        }

        //统计及行尾信息
        //=========统计行
        int rowCode = list.size() + 2;
        SXSSFRow row = sheet.createRow(rowCode);
        SXSSFCell cell = row.createCell(0);
        cell.setCellValue("合计"); // 合计
        cell.setCellStyle(header);
        //起始行，结束行，起始列，结束列
        this.sheet.addMergedRegion(new CellRangeAddress(rowCode, rowCode, 0, 1));

        for (int i = 1; i < strs.size(); i++) {
            cell = row.createCell(i);
            cell.setCellStyle(header);
        }
//            发票张数
        cell = row.getCell(2);
        cell.setCellValue("发票" + list.size() + "张");
        //统计金额
        cell = row.getCell(3);
        BigDecimal reduce = list.stream().map(InvoiceImgExcel::getTaxIncludeDamountinfigures).reduce(BigDecimal.ZERO, BigDecimal::add);
        cell.setCellValue(ObjectUtil.isNotEmpty(reduce) ? String.valueOf(reduce) : null);
        //==============行尾信息
        int rowNumber = list.size() + 2 + 1;
        SXSSFRow sheetRow = sheet.createRow(rowNumber);
        sheetRow.setHeightInPoints(30);
        SXSSFCell sxssfCell = sheetRow.createCell(0);
        sxssfCell.setCellValue("递送人签字：");

        sxssfCell.setCellStyle(title);

        sxssfCell = sheetRow.createCell(1);
        sxssfCell.setCellStyle(title);
        sxssfCell = sheetRow.createCell(2);
        sxssfCell.setCellStyle(title);
        //        //起始行，结束行，起始列，结束列
        this.sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 1, 2));
        sxssfCell = sheetRow.createCell(3);
        sxssfCell.setCellValue("接收人签字：");
        sxssfCell.setCellStyle(title);
        sxssfCell = sheetRow.createCell(4);
        sxssfCell.setCellStyle(title);
        sxssfCell = sheetRow.createCell(5);
        sxssfCell.setCellStyle(title);
        //        //起始行，结束行，起始列，结束列
        this.sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 4, 6));
        sxssfCell = sheetRow.createCell(6);
        sxssfCell.setCellStyle(title);
        log.info(">>>>>>>>>>>>>>>>>>>>结束遍历数据组装单元格内容>>>>>>>>>>");

    }

    private int getConstant(List<InvoiceImgExcel> list, int constant, int i) {
        InvoiceImgExcel invoiceImgExcel = list.get(i);
        //i + 2  从第三行开始，因为第一行标题，第二行表头
        int initRow = i + 2;
        SXSSFRow row = sheet.createRow(initRow);

        SXSSFCell cell = row.createCell(0);
        setCell(invoiceImgExcel, row, cell);
        //合并单元格 第一次循环默认不合并并且后面每次循环与上一次比较如果不相等就执行合并单元格操作(这时候结束行要减去1)或者循环最后一行（这时候结束行不应该减去1）也进行合并单元格，重新赋值起始行
        if (i != 0 && !list.get(i).getSellerName().equals(list.get(i - 1).getSellerName()) || list.size() - 1 == i) {
            //合并单元格区域必须为2个或2个以上的单元格，一个单元格进行合并时会报错。故一个单元格不合并，但是记录行合并的起始行得更新
            if (2 + constant != initRow - 1 || list.size() - 1 == i) {
                //起始行，结束行，起始列，结束列
                this.sheet.addMergedRegion(new CellRangeAddress(2 + constant, list.size() - 1 == i && list.get(i).getSellerName().equals(list.get(i - 1).getSellerName()) ? initRow : initRow - 1, 1, 1));
                constant = i;
            } else {
                constant = i;
            }
        }

        return constant;
    }

    private void setCell(InvoiceImgExcel invoiceImgExcel, SXSSFRow row, SXSSFCell cell) {
        cell.setCellValue(invoiceImgExcel.getSerialNumber()); // 序号
        cell.setCellStyle(content);

        //初始值
        cell = row.createCell(1);
        cell.setCellValue(invoiceImgExcel.getSellerName()); // 供应商名称
        cell.setCellStyle(content);
        cell = row.createCell(2);
        cell.setCellValue(invoiceImgExcel.getInvoiceNumber()); // 发票号码
        cell.setCellStyle(content);

        cell = row.createCell(3);
        cell.setCellValue(ObjectUtil.isNotEmpty(invoiceImgExcel.getTaxIncludeDamountinfigures()) ? String.valueOf(invoiceImgExcel.getTaxIncludeDamountinfigures()) : null); // 金额（元）
        cell.setCellStyle(content);

        cell = row.createCell(4);
        cell.setCellValue(invoiceImgExcel.getIssueDate()); // 开票日期
        cell.setCellStyle(content);

        cell = row.createCell(5);
        cell.setCellValue(ObjectUtil.isNotEmpty(invoiceImgExcel.getSignInTime()) ? DateUtil.getLocalDateTimeStr(invoiceImgExcel.getSignInTime()) : null); // 签收日期
        cell.setCellStyle(content);

        cell = row.createCell(6);
        cell.setCellValue(invoiceImgExcel.getSignInMark()); // 备注
        cell.setCellStyle(content);
    }