ExcelUtils导出工具类

ExcelUtils 导出工具类

package com.ruqi.travel.user.utils;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
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.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.List;

/**
 * @DESCRIPTION: excel导出
 * @author:zhaiqw
 * @date 2019/11/11
 */
public class ExcelUtils {

    public static OutputStream getOutputStream(HttpServletResponse response) throws IOException {
        String fileName = DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now());
        //response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.setHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    }

    /**
     * 重构excel导出自定义文件名。
     *
     * @param response
     * @param fileName
     * @return
     * @throws IOException
     */
    public static OutputStream getOutputStream(HttpServletResponse response, String fileName) throws IOException {
        String encode = URLEncoder.encode(fileName, "UTF-8");
        String contentDisposition = "attachment;filename=" + encode + ".xlsx";
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", contentDisposition);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.setHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    }

    /**
     * excel导出
     *
     * @param response
     * @param includeColumns
     * @param dataList
     * @param clazz
     * @param <T>
     */
    public static <T> void excelSimpleWrite(HttpServletResponse response, Collection<String> includeColumns,
                                            List<T> dataList, Class<T> clazz) throws IOException {
        // 设置头部及内容样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = initStyleStrategy();

        synchronized (clazz) {
            ExcelWriterBuilder writer = EasyExcelFactory.write(getOutputStream(response), clazz);
            writer.registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .includeColumnFiledNames(includeColumns)
                    .sheet("Sheet1")
                    .doWrite(dataList);
        }
    }

    /**
     * 重构excel导出工具类，可以自定义文件名字和页名
     *
     * @param fileName
     * @param sheetName
     * @param response
     * @param excludeColumns
     * @param dataList
     * @param clazz
     * @param <T>
     * @throws IOException
     */
    public static <T> void excelSimpleWrite(String fileName, String sheetName, HttpServletResponse response, Collection<String> excludeColumns, List<T> dataList, Class<T> clazz) throws IOException {
        // 设置头部及内容样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = initStyleStrategy();

        synchronized (clazz) {
            ExcelWriterBuilder writer = EasyExcelFactory.write(getOutputStream(response, fileName), clazz);
            writer.registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .excludeColumnFiledNames(excludeColumns)
                    .sheet(sheetName)
                    .doWrite(dataList);
        }
    }

    /**
     * 设置头部及内容样式策略
     *
     * @return
     */
    private static HorizontalCellStyleStrategy initStyleStrategy() {
        WriteCellStyle contentCellStyle = new WriteCellStyle();
        contentCellStyle.setWrapped(true);
        contentCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(new WriteCellStyle(), contentCellStyle);
    }



}