Springboot下使用easyExcel玩转Excel表的导出

ゝ一纸荒年。 2023-02-13 08:12 6阅读 0赞

# 1、Excel表格的导出： #

**1、搭建一个springboot项目:**

> [https://blog.csdn.net/LemonSnm/article/details/87894940][https_blog.csdn.net_LemonSnm_article_details_87894940]

**2、maven中加入EasyExcel所需包**

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

**2、模拟学生实体类Student**

package com.lemon.entity;
    
    
    import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
    import com.alibaba.excel.annotation.ExcelProperty;
    
    /**
     * 学生类
     * @author lemon
     * @since 2020-05-26
     */
    @ExcelIgnoreUnannotated()
    public class Student {
        
        private Integer myId;
    
        @ExcelProperty(value = "学号",index = 0) //0 对应导出Excel表格的第一列
        private Integer id;
    
        @ExcelProperty(value = "姓名",index = 1)
        private String name;
    
        @ExcelProperty(value = "性别",index = 2)
        private String sex;
    
        @ExcelProperty(value = "班级",index = 3)
        private String grade;
    
        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", grade='" + grade + '\'' +
                    '}';
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public String getGrade() {
            return grade;
        }
    
        public void setGrade(String grade) {
            this.grade = grade;
        }
    
    
    }

**3、导出功能controller：**

package com.lemon.controller;
    
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.util.StringUtils;
    import com.lemon.easyutils.EasyExcelListener;
    import com.lemon.entity.Student;
    
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import java.io.FileInputStream;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * 测试controller
     * @author lemon
     * @since 2020/5/26 0026
     */
    @Controller
    @RequestMapping("/test")
    public class TestController {
    
    /**
     * 主页面
     * @author lemon
     * @since 2020/5/26 0026
     */
        @RequestMapping("/index")
        public String index(){
            return "/views/index";
        }
    
        /**
         * 导出
         * @author lemon
         * @since 2020/5/26 0026
         */
        @RequestMapping("/export")
        public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
    
    
            //模拟需要导出的数据
            List<Student> list = new ArrayList<Student>();
            Student student = new Student();
            student.setId(1);
            student.setName("1321");
            student.setSex("男");
            student.setGrade("一年级");
            for (int i = 0; i < 5; i++) {
                list.add(student);
            }
    
            //设置并导出
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
    
            //设置文件名
            SimpleDateFormat fDate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
            String fileName = "导出测试表" + fDate.format(new Date()) + ".xlsx";
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            EasyExcel.write(response.getOutputStream(), Student.class).sheet("sheet1").doWrite(list);
        }
    
    }

**4、导出功能的页面：**

<html>
    <head>
        <title>测试</title>
    </head>
    
    <body>
    <button onclick="myExport()" >导出</button>
    </body>
    </html>
    
    <script>
    
        function myExport() {
            window.location.href = "/test/export"
        }
    </script>

**5、测试：**

> **输入**[http://localhost:8081/test/index][http_localhost_8081_test_index]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70][]

> 点击导出 选择导出位置：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 1][]

> 一下为输出内容格式设置：

# 6、设置单元格格式为文本 #

import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    
    import java.util.List;
    
    /**
     * 设置单元格格式
     * @author lemon
     * @since 2020/8/11 0011
     */
    public class EasyExcelRowWriteHandlerUtils implements CellWriteHandler {
    
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    
        }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
            //设置单元格格式为文本
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            DataFormat dataFormat = workbook.createDataFormat();
            cellStyle.setDataFormat(dataFormat.getFormat("@"));
            cell.setCellStyle(cellStyle);
            //设置0-30列为文本形式
            for (int i = 0; i < 30; i++) {
                writeSheetHolder.getSheet().setDefaultColumnStyle(i, cellStyle);
            }
    
    
        }
    
        @Override
        public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    
        }
    }

> **用法：**

EasyExcelFactory.write(response.getOutputStream(),
    Student.class).registerWriteHandler(new
    EasyExcelRowWriteHandlerUtils()).sheet().doWrite(new ArrayList());

# 7、导出表格带下拉框 #

import com.alibaba.excel.write.handler.SheetWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
    import org.apache.poi.ss.usermodel.DataValidation;
    import org.apache.poi.ss.usermodel.DataValidationConstraint;
    import org.apache.poi.ss.usermodel.DataValidationHelper;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.xssf.usermodel.XSSFDataValidation;
    
    import java.util.Map;
    
    /**
     * 设置单元格下拉框
     * @author lemon
     * @since 2020/7/27
     */
    public class EasyExcelSheetSelectUtils implements SheetWriteHandler {
    
        //第0列开始 设置某列的下拉内容
        private Map<Integer,String[]> mapDropDown;
    
        public EasyExcelSheetSelectUtils(Map<Integer, String[]> mapDropDown) {
            this.mapDropDown = mapDropDown;
        }
    
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    
        }
    
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    
            Sheet sheet = writeSheetHolder.getSheet();
            ///开始设置下拉框 //设置下拉框
            DataValidationHelper helper = sheet.getDataValidationHelper();
            for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
                /***起始行、终止行、起始列、终止列**/
                CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());
                /***设置下拉框数据**/
                DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
                /***处理Excel兼容性问题**/
                if (dataValidation instanceof XSSFDataValidation) {
                    dataValidation.setSuppressDropDownArrow(true);
                    dataValidation.setShowErrorBox(true);
                } else {
                    dataValidation.setSuppressDropDownArrow(false);
                }
                sheet.addValidationData(dataValidation);
            }
        }
    }

> **用法：**

//性别下拉框设置
                String[] sexStr = new String[]{"男","女"};
                //设置下拉框内容
                Map<Integer,String []> mapDropDown = new HashMap<>();
                //设置在第五列
                mapDropDown.put(5,sexStr);
                //设置在第7列
                mapDropDown.put(7,sexStr);
    
                EasyExcelFactory.write(response.getOutputStream(), Student.class).registerWriteHandler(new EasyExcelSheetSelectUtils(mapDropDown)).sheet().doWrite(new ArrayList());

[https_blog.csdn.net_LemonSnm_article_details_87894940]: https://blog.csdn.net/LemonSnm/article/details/87894940
[http_localhost_8081_test_index]: http://localhost:8081/test/index
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200610132323110.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200610132445480.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t,size_16,color_FFFFFF,t_70