Java Excel导出(文件模版和代码设置文件格式)

1、使用自定义的Excel模版

        将excel文件放在代码的src的resource目录下，不建议使用绝对路径获取文件，使用getResourceAsStream（文件名）去获取模版文件，可以避免部署代码后文件地址改变找不到文件。

        try {
                // 根据文件名获取文件内容
                inputStream = Objects.requireNonNull(ClassUtils
                                .getDefaultClassLoader())
                        .getResourceAsStream("家庭信息 - 模板.xlsx");

            } catch (Exception e) {
                e.printStackTrace();
            }

        定义excel段落相对应的实体类,初始化设置文件格式和文件名

            workbook = new XSSFWorkbook(inputStream);  // 假设是.xlsx格式
            Sheet sheet = workbook.getSheetAt(0);  // 获取第一个工作表
            String fileName = null;
            try {
                fileName = URLEncoder.encode("家庭信息表", "UTF-8");
            } catch (UnsupportedEncodingException e) {
                throw new RuntimeException(e);
            }

        设置开始导出数据行数，每列的数据保存到新的excel中，关闭工作簿和流

int startRowNum = 1;
//根据数据写人表格
List<FamilyMessageVO> familyMessageVOList = getFamilyMessage();
for (int i = 0; i < familyMessageVOList.size(); i++) {
    FamilyMessageVO familyMessageVO = familyMessageVOList.get(i);
    Row row = sheet.createRow(i + startRowNum);
    row.createCell(0).setCellValue(familyMessageVO.getFamilyId());
    row.createCell(1).setCellValue(familyMessageVO.getFamilyName());
    row.createCell(2).setCellValue(familyMessageVO.getFamilyNumber());
}
// 将更改保存到新的Excel文件中
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
try (OutputStream outputStream = response.getOutputStream()) {
    workbook.write(outputStream);
}
// 关闭工作簿
workbook.close();
inputStream.close();

        设置sheet的名称 workbook.setSheetName(0, "家庭信息");

        每列数据数据局中,使用CellStyle方法

CellStyle centerCellStyle = workbook.createCellStyle();
// 设置水平居中
centerCellStyle.setAlignment(HorizontalAlignment.CENTER); 
// 设置垂直居中
centerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); 



// 设置开始导出数据行数
int startRowNum = 1;
//根据数据写人表格
List<FamilyMessageVO> familyMessageVOList = getFamilyMessage();
for (int i = 0; i < familyMessageVOList.size(); i++) {
    FamilyMessageVO familyMessageVO = familyMessageVOList.get(i);
    Row row = sheet.createRow(i + startRowNum);

    Cell cell0 = row.createCell(0);
    cell0.setCellValue(familyMessageVO.getFamilyId());
    cell0.setCellStyle(centerCellStyle);

    Cell cell1 = row.createCell(1);
    cell1.setCellValue(familyMessageVO.getFamilyName());
    cell1.setCellStyle(centerCellStyle);

    Cell cell2 = row.createCell(2);
    cell2.setCellValue(familyMessageVO.getFamilyNumber());
    cell2.setCellStyle(centerCellStyle);
}

 2、使用代码设置excel文件格式

        代码修改点

                1.changeEstimateData 是获取导入的数据

                2.designEstimateExcelHeaders 设置每个列的格式

        // 设置文件名称
        String fileName = null;
        String encodedFileName = null;
        try {
            fileName = URLEncoder.encode("文件名_", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }

        // 通过工具类创建writer，创建xlsx格式
        ExcelWriter writer = ExcelUtil.getWriter(true);
        designEstimateExcelHeaders(writer);

        List<ExportEstimateFullView> list = changeEstimateData(dtoList);
        // 一次性写出内容
        writer.write(list, false);

        // 设计 response 对象值
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        encodedFileName = "attachment;filename*=utf-8''" + fileName + ".xlsx";
        response.setHeader("Content-Disposition", encodedFileName);
        try {
            // 写出到客户端
            writer.flush(response.getOutputStream(), true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            writer.close();
        }

        设置文件格式：setColumnWidth方法设置列宽，merge可以合并单元格设置列格式，writer.passRows(3);跳过指定行数

private void designEstimateExcelHeaders(ExcelWriter writer) {
        writer.setColumnWidth(-1, 15);
        writer.setColumnWidth(0, 30);
        writer.setColumnWidth(3, 25);
        writer.setColumnWidth(4, 50);
        writer.setColumnWidth(5, 40);
        writer.setColumnWidth(6, 40);
        writer.merge(0, 2, 0, 0, "预估结算单号", false);
        writer.merge(0, 2, 1, 1, "会计周期", false);
        writer.merge(0, 2, 2, 2, "结算周期", false);
        writer.merge(0, 2, 3, 3, "合同编码", false);
        writer.merge(0, 2, 4, 4, "合同名称", false);
        writer.merge(0, 2, 5, 5, "客户/供应商", false);
        writer.merge(0, 2, 6, 6, "发票抬头", false);
        writer.passRows(3);
}