2021-11-10

这里记录一个easyExcel导出excel的功能

1. 首先我们要先下载jar包`在这里插入代码片

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.2</version>
        </dependency>

2、看看我的整体目录结构，因为好多人都不知道应该返回什么值，其实导出是不需要返回任何值的就是一个单纯的下载功能，将List集合放入就行了

3、我这里只是写了Controller层，你会发现在导出的功能中我们只需要一个参数：HttpServletResponse，所以说当你在controller层调用service层传入一个HttpServletResponse，然后在实现层利用这个参数，实现层里面的代码跟controller代码是一样的，都是没有返回值的，只需要调用就行了，实现层调用mapper层返回一个数据的List集合就行了。

package com.zhou.controller;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.zhou.pojo.Student;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;


@RequestMapping("export")
@Controller
public class ExportExcelController {

    @RequestMapping("exportExcel")
    public void export(HttpServletResponse response) throws IOException {

        try {

            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            String fileName = URLEncoder.encode("单元测试","UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");

            //设置背景颜色
            WriteCellStyle writeCellStyle = new WriteCellStyle();
            writeCellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());

            //设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)13);
            headWriteFont.setBold(true);

            //设置头居中
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(writeCellStyle, contentWriteCellStyle);


            writeCellStyle.setWriteFont(headWriteFont);

            EasyExcel.write(response.getOutputStream(),Student.class)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .sheet("模板")
                    .doWrite(getData());

//            EasyExcel.write(response.getOutputStream()).needHead(true)
//                    .excelType(ExcelTypeEnum.XLSX)
//                    .registerWriteHandler(getStyleStrategy())
//                    .sheet(0, "Assignment Total Report")
//                    .doWrite(getData());
        }catch (Exception e){

            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            Map<String,String> map = new HashMap<>();
            map.put("str","failure");
            map.put("msg","下载文件失败"+e.getLocalizedMessage());
            response.getWriter().println(map.toString());

        }


    }

    private List<Student> getData(){

        List<Student> list = new ArrayList<>();
        for (int i = 0; i <10 ; i++) {
            Student student = new Student();
            student.setId(i);
            student.setName("zhou"+i);
            student.setAge("1"+i);
            student.setBirthday(new Date().toString());
            student.setSex(i % 2 == 0 ?"女" : "男");
            list.add(student);
        }
        return list;
    }

4、看看实体类

package com.zhou.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import lombok.Data;

@Data
@ColumnWidth(value = 20)
public class Student {

    @ExcelProperty(value = {"学生信息","id"})
    private int id;
    @ExcelProperty(value = {"学生信息","姓名"})
    private String name;
    @ExcelProperty(value = {"学生信息","年龄"})
    private String age;
    @ExcelProperty(value = {"学生信息","性别"})
    private String sex;
    @ExcelProperty(value = {"学生信息","出生日期"})
    @DateTimeFormat(value = "yyyy-MM-dd")
    private String birthday;

    public Student(int id, String name, String age, String sex, String birthday) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.birthday = birthday;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public Student() {
    }
}

5、我这里使用的是postman测试的，会导出response.xlsx的文件名,因为这是postman默认的，当你用浏览器访问就没事了。
6、导出的内容：

7、匆匆忙忙写的，写的不足望谅解