EasyExcel导出简单使用

工具类封装

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

public class EasyExcelUtils {
    /**
     * @Title: exportExcel
     * @Param: [data, response, clazz]
     * @return: void
     **/
    public static <T> void exportExcel(List<?> data, HttpServletResponse response,Class<T> clazz){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = sdf.format(new Date());//文件名称
        String sheetName = "sheet1";

        try {
            OutputStream outputStream = response.getOutputStream();
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx;");
            response.setContentType("application/msexcel;charset=UTF-8");// 设置类型
            response.setHeader("Pragma", "No-cache");// 设置头
            response.setHeader("Cache-Control", "no-cache");// 设置头
            response.setDateHeader("Expires", 0);// 设置日期头

            // 表头样式
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 设置表头居中对齐
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 内容样式
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 设置内容靠左对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            EasyExcel.write(outputStream,clazz)
                    .head(clazz)
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .sheet(0, sheetName)
                    .doWrite(data);

            outputStream.flush();
        }catch (IOException e){

        }finally {
            try{
                response.getOutputStream().close();
            }catch (IOException e){

            }
        }
    }
}

实体类

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

import java.util.Date;

@ExcelIgnoreUnannotated//忽略未注解（如果需要忽略的项比较少可以在忽略项上使用@ExcelIgnore）
public class Student {
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    @NumberFormat
    private Integer age;

    @ExcelProperty("性别")
    private String gender;

    @ExcelProperty("爱好")
    private String hobby;

    @ExcelProperty("出生日期")
    @DateTimeFormat("yyyy/MM/dd")
    @ColumnWidth(20)
    private Date dateOfBirth;

//    @ExcelIgnore//忽略项（如果需要忽略的项比较多可以在类上使用@ExcelIgnoreUnannotated）
    private String irrelevant;

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

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

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getHobby() {
        return hobby;
    }

    public void setHobby(String hobby) {
        this.hobby = hobby;
    }

    public Date getDateOfBirth() {
        return dateOfBirth;
    }

    public void setDateOfBirth(Date dateOfBirth) {
        this.dateOfBirth = dateOfBirth;
    }

    public String getIrrelevant() {
        return irrelevant;
    }

    public void setIrrelevant(String irrelevant) {
        this.irrelevant = irrelevant;
    }
}

测试代码：

import com.example.demo.model.Student;
import com.example.demo.service.EasyExcelService;
import com.example.demo.utils.easyExcel.EasyExcelUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Controller
@RequestMapping("/easyExcel")
public class EasyExcelController {
    /**
     * @Title: exportExcel
     * @Description: 导出
     * @Param: [request, response]
     * @return: void
     **/
    @RequestMapping("/exportExcel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response){
        //数据查询
        List<Student> data = new ArrayList<>();
        for(int i = 0; i < 10; i++){
            Student s = new Student();
            s.setName("张"+i);
            s.setAge(i + 10);
            s.setGender("男");
            s.setHobby("篮球");
            s.setDateOfBirth(new Date());
            data.add(s);
        }

        //导出
        EasyExcelUtils.exportExcel(data,response,Student.class);
    }
}

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>主页</title>
</head>
<body>
    <h1>这是主页</h1>
    <div>
        <a href="/easyExcel/exportExcel">导出EasyExcel</a>
    </div>
</body>
</html>