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

1、Excel表格的导出：

1、搭建一个springboot项目:

https://blog.youkuaiyun.com/LemonSnm/article/details/87894940

 2、maven中加入EasyExcel所需包

        <!-- https://mvnrepository.com/artifact/com.alibaba/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

点击导出 选择导出位置：

 

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

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());