Excel导出全家桶代码源码

package com.util; import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.EasyExcelFactory; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang.time.DateFormatUtils; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.util.StringUtils; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.security.SecureRandom; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.Date; import java.util.List; import java.util.Map; import java.util.Objects; import java.util.concurrent.ArrayBlockingQueue; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import java.util.concurrent.ThreadPoolExecutor; import java.util.concurrent.TimeUnit; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; @Slf4j public class ExcelUtil { public static final String INCLINED_ROD = "/"; private static Sheet initSheet; static { initSheet = new Sheet(1, 0); initSheet.setSheetName("sheet"); //设置自适应宽度 initSheet.setAutoWidth(Boolean.TRUE); } public static class SheetBean<T> { private Map<String, Object> beanMap; private List<T> beanList; public Map<String, Object> getBeanMap() { return beanMap; } public void setBeanMap(Map<String, Object> beanMap) { this.beanMap = beanMap; } public List<T> getBeanList() { return beanList; } public void setBeanList(List<T> beanList) { this.beanList = beanList; } } public static class ExcelListener extends AnalysisEventListener { private List<Object> data = new ArrayList<>(); //逐行解析，object : 当前行的数据 @Override public void invoke(Object object, AnalysisContext context) { //当前行 context.getCurrentRowNum() if (object != null) { data.add(object); } } //解析完所有数据后会调用该方法 @Override public void doAfterAllAnalysed(AnalysisContext context) { //解析结束销毁不用的资源 } public List<Object> getData() { return data; } public void setData(List<Object> data) { this.data = data; } } /** * 设置返回前端文件名 * @param response response * @param fileName 文件名,包含后缀 * @return OutputStream * @throws Exception Exception */ public static OutputStream getOutputStreamFileName(HttpServletResponse response, String fileName) throws Exception{ response.reset(); String fileType = fileName.split("\\.")[1].toLowerCase(); switch (fileType){ case "doc": response.setContentType("application/msword");//设置生成的文件类型 break; case "docx": response.setContentType("application/msword");//设置生成的文件类型 break; case "xls": response.setContentType("application/vnd.ms-excel");//设置生成的文件类型 break; case "xlsx": response.setContentType("application/vnd.ms-excel");//设置生成的文件类型 break; case "pdf": response.setContentType("application/pdf");//设置生成的文件类型 break; case "zip": response.setContentType("application/zip");//设置生成的文件类型 break; case "dbf": response.setContentType("application/x-dbf");//设置生成的文件类型 break; default: return response.getOutputStream(); } response.setCharacterEncoding("UTF-8");//设置文件头编码方式和文件名 response.setHeader("Content-Disposition", "attachment;filename=" + new String(URLEncoder.encode(fileName, "UTF-8").getBytes("utf-8"), "ISO8859-1")); return response.getOutputStream(); } /** * 按指定大小，分隔集合，将集合按规定个数分为n个部分 * @param list 集合 * @param len 拆分个数 * @param <T> 泛型 * @return List<List<T>> */ public static <T> List<List<T>> splitList(List<T> list, int len) { if (list == null || list.isEmpty() || len < 1) { return Collections.emptyList(); } List<List<T>> result = new ArrayList<>(); int size = list.size(); int count = (size + len - 1) / len; for (int i = 0; i < count; i++) { List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1))); result.add(subList); } return result; } /** * 获取文件输出流 * @param filePath 文件路径，不需加/ * @param fileName 文件名称 * @return 输出流 * @throws FileNotFoundException file not found */ public static OutputStream getFileOutputStream(String filePath, String fileName) throws FileNotFoundException { return new FileOutputStream(filePath + INCLINED_ROD + fileName); } /** * 获取文件输出流 * @param filePathAndName 文件路径+文件名称 * @return 输出流 * @throws FileNotFoundException file not found */ public static OutputStream getFileOutputStream(String filePathAndName) throws FileNotFoundException { return new FileOutputStream(filePathAndName); } /** * 生成Excel表格 * @param outputStream 流 * @param data 数据 * @param head 表头 */ public static void writeExcelByList(OutputStream outputStream, List<List<Object>> data, List<String> head){ writeExcelByList(outputStream, data, head,null); } /** * 生成Excel表格，生成文件 * @param filePath 文件路径 * @param fileName 文件名称 * @param data 数据 * @param head 表头 */ public static void writeExcelByList(String filePath, String fileName, List<List<Object>> data, List<String> head) throws Exception{ writeExcelByList(getFileOutputStream(filePath, fileName), data, head,null); } /** * �