使用EasyExcel将数据写入Excel表中

心已赠人 2024-03-26 02:24 39阅读 0赞

最近工作中遇到了需要将较多的数据写入Excel中, 使用了阿里的EasyExcel来实现功能, 下面仅用少量的数据来演示:

1.  sql脚本:

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for demo_data
    -- ----------------------------
    DROP TABLE IF EXISTS `demo_data`;
    CREATE TABLE `demo_data`  (
      `id` int(11) NOT NULL,
      `date` datetime(0) NULL DEFAULT NULL,
      `number` int(1) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of demo_data
    -- ----------------------------
    INSERT INTO `demo_data` VALUES (1, '2023-02-27 15:19:01', 1);
    INSERT INTO `demo_data` VALUES (2, '2023-02-27 15:19:17', 2);
    INSERT INTO `demo_data` VALUES (3, '2023-02-27 15:20:12', 3);
    INSERT INTO `demo_data` VALUES (4, '2023-02-27 15:20:14', 4);
    INSERT INTO `demo_data` VALUES (5, '2023-02-27 15:20:17', 5);
    INSERT INTO `demo_data` VALUES (6, '2023-02-27 15:20:20', 6);
    INSERT INTO `demo_data` VALUES (7, '2023-02-27 15:20:23', 7);
    INSERT INTO `demo_data` VALUES (8, '2023-02-27 15:20:26', 8);
    INSERT INTO `demo_data` VALUES (9, '2023-02-27 15:20:29', 9);
    INSERT INTO `demo_data` VALUES (10, '2023-02-27 15:20:31', 10);
    
    SET FOREIGN_KEY_CHECKS = 1;

![测试数据][17dd713963634bf197b38be6f3ef3d70.png_pic_center]  
2. pom.xml

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.2.0</version>
            </dependency>

3.实体类

package com.itstone.mok.easyexcel;
    
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import com.alibaba.excel.annotation.write.style.ContentRowHeight;
    import com.alibaba.excel.annotation.write.style.HeadRowHeight;
    import com.baomidou.mybatisplus.annotation.TableField;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import java.util.Date;
    
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @HeadRowHeight(15) // 表头行高
    @ContentRowHeight(15) // 行高
    @ColumnWidth(15)  // 列宽
    public class DemoData {
        
    
        /**
         * 对应导出表中的表头标题
         */
        @ExcelProperty(index = 0, value = "字符串标题")
        private String id;
    
        @ExcelProperty(index = 1, value = "日期标题")
        private Date date;
    
        @ExcelProperty(index = 2, value = "数字标题")
        private Double number;
    
        @ExcelIgnore // 忽略该字段
        @TableField(exist = false)
        private String ignore;
    
    }

4.Mapper文件:

package com.itstone.mok.easyexcel;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import org.apache.ibatis.annotations.Mapper;
    
    @Mapper
    public interface DemoDataMapper extends BaseMapper<DemoData> {
         }

5.测试类:

package com.itstone.mok.easyexcel;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 一、如果数据量特别大, 可以将数据分开写在同一张Excel表的不同的Sheet中:
     */
    @RestController
    public class EasyExcelTest06 {
        
    
        @Autowired
        private DemoDataMapper demoDataMapper;
    
        @GetMapping("/ee/test1")
        public void getExcelFile(HttpServletResponse response) throws Exception {
        
            // 数据的总量(要写入Excel文件中的数据)
            int count = demoDataMapper.selectList(null).size();
            // 如果数据大于5条就分页(Sheet)
            List<DemoData> demoDataList = new ArrayList<>(5);
            // 总Sheet数
            int num = count / 5;
            // 输出流
            OutputStream outputStream = null;
            try {
        
                response.setCharacterEncoding("UTF-8");
                String excelName = URLEncoder.encode("test", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + excelName + ".xlsx");
                // 以字节流的形式输出响应正文
                outputStream = response.getOutputStream();
    
                try (ExcelWriter excelWriter = EasyExcel.write(outputStream, DemoData.class).build()) {
        
                    // 调用写入
                    for (int i = 0; i <= num; i++) {
        
                        // 每次写入都要创建WriteSheet, 这里注意必须指定sheetNo, 并且sheetName必须不一样
                        WriteSheet writeSheet = EasyExcel.writerSheet(i, "demo" + (i + 1)).build();
                        // 判断是否需要多sheet写入
                        if (count > 5) {
        
                            QueryWrapper<DemoData> qw = new QueryWrapper<DemoData>().last("limit 5 offset " + i * 5);
                            demoDataList = demoDataMapper.selectList(qw);
                            excelWriter.write(demoDataList, writeSheet);
                        } else {
        
                            demoDataList = demoDataMapper.selectList(null);
                            excelWriter.write(demoDataList, writeSheet);
                        }
                    }
                }
                // 清空集合
                demoDataList.clear();
            } catch (IOException ex) {
        
                throw new RuntimeException(ex);
            } finally {
        
                if (outputStream != null) {
        
                    try {
        
                        outputStream.close();
                    } catch (IOException ex) {
        
                        throw new RuntimeException(ex);
                    }
                }
            }
        }
    }

package com.itstone.mok.easyexcel;
    
    import com.alibaba.excel.EasyExcel;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    
    /**
     * 二、如果能确定总数据量比较少, 那么可以不分Sheet, 直接写在一个Sheet中:
     */
    @RestController
    public class EasyExcelTest07 {
        
        
        @Autowired
        private DemoDataMapper demoDataMapper;
    
        @GetMapping("/ee/test2")
        public void write(HttpServletResponse response) {
        
            try {
        
                List<DemoData> demoDataList = demoDataMapper.selectList(null);
                response.setContentType("application/vnd.openxmlformats-officedocument.speadsheetml.sheet");
                response.setCharacterEncoding("UTF-8");
                // 这里URLEncoder.encode可以防止中文乱码, 与EasyExcel无关
                long timeStamp = System.currentTimeMillis();
                String fileName = URLEncoder.encode("demoData" + timeStamp, "UTF-8").replaceAll("\\+", "%20");
                response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
                EasyExcel.write(response.getOutputStream(), DemoData.class).sheet("设备信息").doWrite(demoDataList);
            } catch (IOException e) {
        
                throw new RuntimeException(e);
            }
        }
    
    }

[17dd713963634bf197b38be6f3ef3d70.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/0b25657641ff402cb3a9b6d30918acfa.png