aeasyexcel 操作 Excel

一、简述

easyexcel (阿里开源项目)是一款快速、简单避免OOM的 java 处理 Excel 工具。maven依赖如下：

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.4</version>
</dependency>

二、实现已有Excel模板下载

模板配置路径如图：

下载代码分三步，主要分为加载资源->读取资源->写入响应流：

@RestController
@RequestMapping("/export")
public class ExportController {
    @GetMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) throws Exception {
        ClassPathResource classPathResource = new ClassPathResource("templates/easy.xls");
        InputStream inputStream = classPathResource.getInputStream();
        Workbook workbook = new HSSFWorkbook(inputStream);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-Disposition", "attachment;filename=" +
                            URLEncoder.encode("easy.xls", "utf-8"));
        response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}

三、写入数据并生成文件

定义模型映射对象 UserExcelModel：

@Data
public class UserExcelModel extends BaseRowModel implements Serializable {
    @ExcelProperty(value = "用户名", index = 0)
    public String name;
    @ExcelProperty(value = "年龄", index = 1)
    public Integer age;
    @ExcelProperty(value = "手机号", index = 2)
    public String mobile;
    @ExcelProperty(value = "性别", index = 3)
    public String sex;
    public UserExcelModel(String name, Integer age, String mobile, String sex) {
        this.name = name;
        this.age = age;
        this.mobile = mobile;
        this.sex = sex;
    }
}

导出流程：定义列标题->创建sheet->自定义字体和风格->构造数据->写入数据->写入到浏览器响应流

@RestController
@RequestMapping("/export")
public class ExportController {
    @GetMapping("/exportData")
    public void exportData(HttpServletResponse response) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        String[] columnNames = {"用户名", "年龄", "手机号", "性别"};
        Sheet sheet = workbook.createSheet();
        Font titleFont = workbook.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        titleStyle.setFont(titleFont);
       
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(titleStyle);
        }
        //模拟数据
        List<UserExcelModel> dataList = new ArrayList<>();
        dataList.add(new UserExcelModel("张三", 12, "13811021203", "男"));
        dataList.add(new UserExcelModel("李四", 12, "13811021204", "男"));
        dataList.add(new UserExcelModel("王五", 12, "13811021205", "男"));
        dataList.add(new UserExcelModel("赵六", 12, "13811021206", "男"));

        //创建数据行并写入值
        for (int j = 0; j < dataList.size(); j++) {
            UserExcelModel userExcelModel = dataList.get(j);
            int lastRowNum = sheet.getLastRowNum();
            Row dataRow = sheet.createRow(lastRowNum + 1);
            dataRow.createCell(0).setCellValue(userExcelModel.getName());
            dataRow.createCell(1).setCellValue(userExcelModel.getAge());
            dataRow.createCell(2).setCellValue(userExcelModel.getMobile());
            dataRow.createCell(3).setCellValue(userExcelModel.getSex());
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-Disposition", "attachment;filename=" + 
                            URLEncoder.encode("easyExport.xls", "utf-8"));
        response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}