SpringBoot導(dǎo)出Excel的四種方式小結(jié)

更新時(shí)間：2024年12月16日 10:29:58 作者：brokeSky

近期接到了一個(gè)小需求,要將系統(tǒng)中的數(shù)據(jù)導(dǎo)出為Excel,且能將Excel數(shù)據(jù)導(dǎo)入到系統(tǒng),對(duì)于大多數(shù)研發(fā)人員來說,這算是一個(gè)最基本的操作了,本文就給大家總結(jié)一下SpringBoot導(dǎo)出Excel的四種實(shí)現(xiàn)方式,需要的朋友可以參考下

方法一：hutools

該方法通過自研的一套Excel注解，在實(shí)體類上添加注解，然后用一套工具類(見源碼中com.leixi.excel.util.excelOne)實(shí)現(xiàn)實(shí)體類列表的導(dǎo)出。優(yōu)點(diǎn)是不用建模板，調(diào)用簡(jiǎn)單，缺點(diǎn)是遷移麻煩。有好幾個(gè)配套的文件如注解，工具類等，想要在別的項(xiàng)目中使用，就涉及到文件的拷貝。老實(shí)說，不太優(yōu)雅，而且更可惜的是，這些方法里居然沒有封裝導(dǎo)出方法。下面給出實(shí)體類創(chuàng)建，方法調(diào)用的代碼，供各位參考（相關(guān)源碼會(huì)在文中提供）

//引入依賴

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.12</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>

//controller方法，不得不說，調(diào)用起來是真的方便
    @PostMapping(value = "/exportData")
    public ResponseEntity<byte[]> exportData(String fileName) {
        List<ExcelOneDto> list = CommonUtil.buildDemoExcel(ExcelOneDto.class);
        Workbook workbook = new DefaultWriteHandler<ExcelOneDto>().write("sheet名稱",list, ExcelOneDto.class);
        return CommonUtil.exportWorkbook(workbook, fileName);
    }
 
 //實(shí)體類
@Data
public class ExcelOneDto {
    @ExcelCell(priority = "A", cellTitle = "編號(hào)")
    private Integer code;
 
    @ExcelCell(priority = "B", cellTitle = "名稱")
    private String name;
 
 
    @ExcelCell(priority = "C", cellTitle = "詳情")
    private String desc;
 
    @ExcelCell(priority = "D", cellTitle = "備注")
    private String remark;
}

這是導(dǎo)出的測(cè)試結(jié)果：

方法二：easyexcel

現(xiàn)在使用頻率較高的是用easyexcel實(shí)現(xiàn)的。經(jīng)過一番了解，我將easyexcel的實(shí)現(xiàn)方式也列出來作為參考。該方法的優(yōu)點(diǎn)是使用方便，功能強(qiáng)大！本文中我只實(shí)現(xiàn)了最簡(jiǎn)單的導(dǎo)入導(dǎo)出，easyexcel還支持很多如嵌套導(dǎo)入，合并行列等高級(jí)操作，相關(guān)實(shí)現(xiàn)可以參考大神的博客【精選】Spring Boot 集成 EasyExcel 3.x 優(yōu)雅實(shí)現(xiàn)Excel導(dǎo)入導(dǎo)出_easyexcel3-CSDN博客。以下是我學(xué)習(xí)了他的博客后，整理的最基本的代碼：

//引入maven依賴
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.3</version>
        </dependency>

//controller:
    @PostMapping(value = "/exportData")
    public void exportData(String fileName, HttpServletResponse response) {
        try {
            this.setExcelResponseProp(response, fileName);
            List<ExcelFourDto> list = CommonUtil.buildDemoExcel(ExcelFourDto.class);;
            EasyExcel.write(response.getOutputStream())
                    .head(ExcelFourDto.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(fileName)
                    .doWrite(list);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
 
    @PostMapping("/importData")
    @SneakyThrows
    public Object importUserExcel(@RequestPart("file") MultipartFile file) {
            List<ExcelFourDto> list = EasyExcel.read(file.getInputStream())
                    .head(ExcelFourDto.class).sheet().doReadSync();
            return list;
    }
    
    /**
     * 設(shè)置響應(yīng)結(jié)果
     *
     * @param response    響應(yīng)結(jié)果對(duì)象
     * @param rawFileName 文件名
     * @throws UnsupportedEncodingException 不支持編碼異常
     */
    private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    } 
 
// 實(shí)體類
@Data
public class ExcelFourDto {
    @ExcelProperty("編號(hào)")
    @ColumnWidth(20)
    private Integer code;
 
    @ExcelProperty("名稱")
    @ColumnWidth(20)
    private String name;
 
    @ExcelProperty("詳情")
    @ColumnWidth(20)
    private String desc;
 
    @ExcelProperty("備注")
    @ColumnWidth(20)
    private String remark;
}

以下是測(cè)試結(jié)果：

方法三：ExcelImportUtil工具類

該方法的優(yōu)點(diǎn)：不用excel模板，導(dǎo)出導(dǎo)出方便。缺點(diǎn)是封裝的不夠好。研發(fā)人員使用時(shí)需要對(duì)ExcelImportUtil進(jìn)行一定的加工。下面是我封裝后的代碼：

//maven依賴

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>

//control類的導(dǎo)入導(dǎo)出
 
    @PostMapping(value = "/importData")
    public Object importManageData(@RequestPart("file") MultipartFile file) throws Exception {
        return CommonUtil.getExcelDataFromFile(file, ExcelTwoDto.class, 1,2);
    }
 
    @PostMapping(value = "/exportData")
    public ResponseEntity<byte[]> exportData(@RequestParam("fileName") String fileName){
        List<ExcelTwoDto> list  = CommonUtil.buildDemoExcel(ExcelTwoDto.class);
        List<Map<String, Object>> exportView = CommonUtil.buildExportView("測(cè)試Title", "測(cè)試Sheet",list);
        Workbook workbook = ExcelExportUtil.exportExcel(exportView, ExcelType.XSSF);
        ExcelStyleUtil.addNotExistCell(workbook, 0, 3);
        return CommonUtil.exportWorkbook(workbook, fileName);
    }
    
//實(shí)體類 
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelTwoDto {
 
    @Excel(name = "第一列" ,fixedIndex = 0, orderNum = "0")
    private String codeOne;
    @Excel(name = "第二列" ,fixedIndex = 1, orderNum = "1", width = 50)
    private String codeTwo;
    @ExcelCollection(name = "第三列" , orderNum = "2")
    private List<ExcelChildDto> codeThree;
 
    @Excel(name = "第四列" ,fixedIndex = 4, orderNum = "4", width = 50)
    private String codeFour;
    @Excel(name = "第五列" ,fixedIndex = 5, orderNum = "5")
    private String codeFive;
    @Excel(name = "第六列A", groupName = "第六列匯總" ,fixedIndex = 6, orderNum = "6")
    private String codeThreeOne;
    @Excel(name = "第六列B", groupName = "第六列匯總" ,fixedIndex = 7, orderNum = "7")
    private String codeThreeTwo;
    @Excel(name = "第六列C", groupName = "第六列匯總" ,fixedIndex = 8, orderNum = "8")
    private String codeThreeThree;
}
 
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelChildDto {
    @Excel(name = "編碼" ,fixedIndex = 0, orderNum = "0")
    private String code;
    @Excel(name = "名稱" ,fixedIndex = 1, orderNum = "1", width = 50)
    private String name;
}

導(dǎo)出測(cè)試結(jié)果：

方法四：Excel模板導(dǎo)出

第三種方案是通過excel模板來實(shí)現(xiàn)的方法，現(xiàn)在基本已經(jīng)廢棄了，之所以放在這里是為了做一個(gè)參照，讓大家看看不同的實(shí)現(xiàn)方法的區(qū)別。這種方式通過創(chuàng)建一個(gè)Excel模板和對(duì)應(yīng)模板的實(shí)體類來實(shí)現(xiàn)文件導(dǎo)出，優(yōu)點(diǎn)是實(shí)體類里不需要加注解了，而且在excel里可以設(shè)計(jì)一些style和格式，缺點(diǎn)是配置excelTemplate太麻煩了。配置繁瑣，且過程中如出現(xiàn)參數(shù)名錯(cuò)誤，符號(hào)錯(cuò)誤等，調(diào)試起來很不方便，如果要增減字段，實(shí)體類和excel都得調(diào)整。下面給出編寫的代碼：

// controller方法
    @PostMapping(value = "/exportData")
    public ResponseEntity<byte[]> exportData(@RequestParam("fileName") String fileName) {
        TemplateExportParams params = new TemplateExportParams("templates/template.xlsx");
        params.setScanAllsheet(true);
        Map<String, Object> dataMap = new HashMap<>();
        List<ExcelThreeDto> list  = CommonUtil.buildDemoExcel(ExcelThreeDto.class);
        dataMap.put("titleName", "標(biāo)題名稱");
        dataMap.put("desc", "備注名稱");
        dataMap.put("dataList", list);
        Workbook workbook = ExcelExportUtil.exportExcel(params, dataMap);
        ExcelStyleUtil.setAutoHeight(workbook, 0, 4);
        return CommonUtil.exportWorkbook(workbook, fileName);
    }
    
//實(shí)體類
    @Data
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    public class ExcelThreeDto {
        private Integer code;
        private String name;
        private String desc;
        private String remark;
    }

Excel模板的配置

導(dǎo)出的結(jié)果：

由于年代久遠(yuǎn)，我沒能找到對(duì)應(yīng)的模板導(dǎo)入的方法，于是在網(wǎng)上搜索了相關(guān)資料，編寫了一個(gè)較通用的文件導(dǎo)入方法：

//maven依賴
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>

/**
     * excel 導(dǎo)入
     */
    @PostMapping("/importData")
    public Object upload(@RequestParam(name = "file") MultipartFile file) {
        return CommonUtil.importExcel(file, 3, ExcelThreeDto.class);
    }
 
    /**
     * 通用的excel導(dǎo)入的方法
     *
     * @param file 導(dǎo)入文件
     * @param startRow 第幾行開始讀數(shù)據(jù)
     * @param clazz 導(dǎo)入后轉(zhuǎn)成的實(shí)體類
     * @return
     * @param <T> 
     */
    @SneakyThrows
    public static <T> List<T> importExcel(MultipartFile file, Integer startRow, Class<T> clazz) {
        Workbook wb = new XSSFWorkbook(file.getInputStream());
        //1.2.獲取Sheet
        Sheet sheet = wb.getSheetAt(0);
        List<T> list = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        for (int rowNum = startRow; rowNum <= sheet.getLastRowNum(); rowNum++) {
            //根據(jù)索引獲取每一個(gè)行
            Row row = sheet.getRow(rowNum);
            T obj = clazz.newInstance();
            for (int cellNum = 0; cellNum < row.getLastCellNum() && cellNum< fields.length; cellNum++) {
                Cell cell = row.getCell(cellNum);
                Object value = CommonUtil.getCellValue(cell);
                Field field = fields[cellNum];
                field.setAccessible(true);
                // 注意，這里是為了演示，只分析了Integer和String的寫法，實(shí)際上要根據(jù)Field數(shù)據(jù)的枚舉分類處理
                if (field.getType().equals(Integer.class) && value != null) {
                    field.set(obj, new Double(value.toString()).intValue());
                } else {
                    field.set(obj, value);
                }
            }
            list.add(obj);
        }
        return list;
    }

導(dǎo)入的結(jié)果還是蠻令人滿意的：