当前位置: article > 正文

Springboot下使用easyExcel玩转Excel表的导出_springboot easyexcel下载改成是否支持文本格式

作者：IT小白 | 2024-07-17 03:20:11

踩

springboot easyexcel下载改成是否支持文本格式

1、Excel表格的导出：

1、搭建一个springboot项目:

https://blog.csdn.net/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());

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/837722