Easy Excel工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * EasyExcel工具类
 *
 * @author axue
 */
public class EasyExcelUtil {

    /**
     * 文件下载
     *
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link Class<>}
     * <p>
     * 2. 设置返回的 参数
     * <p>
     * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     *
     * @param response       response
     * @param exportFileName 文件名稱
     * @param sheetName      表名称
     * @param clazz          表头模板
     * @param list           数据
     * @throws IOException IOException
     */
    public static void writeWeb(HttpServletResponse response, String exportFileName, String sheetName, Class<?> clazz,
                                List<?> list) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet(sheetName).doWrite(list);
    }

    /**
     * 设置下拉框选择器
     *
     * @param response       response
     * @param exportFileName 文件名稱
     * @param sheetName      表名称
     * @param clazz          表头模板
     * @param list           数据
     * @throws IOException IOException
     */
    public static void writeWeb(HttpServletResponse response, String exportFileName, String sheetName, Class<?> clazz, WriteHandler writeHandler,
                                List<?> list) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(writeHandler).sheet(sheetName).doWrite(list);
    }

    /**
     * 自定义表头导出
     *
     * @param response       response
     * @param exportFileName 文件名称
     * @param sheetName      表名称
     * @param head           头信息
     * @param list           数据
     * @throws IOException IOException
     */
    public static void writeWeb(HttpServletResponse response, String exportFileName, String sheetName, List<List<String>> head,
                                List<?> list) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream()).head(head).registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(list);
    }

    /**
     * 模板填充导出
     *
     * @param exportFileName   导出名称
     * @param templateFileName 模板地址
     * @param data             数据
     */
    public static void fillWriteWeb(HttpServletResponse response, String exportFileName, String templateFileName,
                                    String sheetName, List<?> data) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(response.getOutputStream());
        EasyExcel.write(bufferedOutputStream).withTemplate(new ClassPathResource(templateFileName).getInputStream())
                .sheet(sheetName).doFill(data);
    }

    /**
     * 复杂的模板填充导出
     *
     * @param exportFileName   导出名称
     * @param templateFileName 模板地址
     * @param data             列表数据 {.}
     * @param map              指定数据 {}
     */
    public static void fillWriteWeb(HttpServletResponse response, String exportFileName, String templateFileName,
                                    List<?> data, Map<String, Object> map) throws IOException {
        ExcelWriter excelWriter = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String fileName = URLEncoder.encode(exportFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(response.getOutputStream());
            excelWriter = EasyExcel.write(bufferedOutputStream).withTemplate(new ClassPathResource(templateFileName).getInputStream()).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(data, writeSheet);
            excelWriter.fill(map, writeSheet);
        } finally {
            // 千万别忘记关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

}