POI百万级excel导出开箱即用项目

package com.baichuan.excel.service; import cn.hutool.core.collection.CollectionUtil; import cn.hutool.core.date.DateUtil; import cn.hutool.core.util.StrUtil; import com.baichuan.excel.enums.ExcelColumn; import com.baichuan.excel.utils.ColumnInfo; import com.baichuan.excel.utils.Excel2007Bean; import com.baichuan.excel.utils.ExcelBean; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.springframework.core.annotation.AnnotationUtils; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Field; import java.nio.charset.StandardCharsets; import java.util.*; import java.util.concurrent.ConcurrentHashMap; /** * @author baichuan */ @Service public class ExcelService { /** * key-缓存类全限定名 * value-字段Field的集合(包括父类对象的字段) */ private static final Map<String, List<Field>> CACHE_FIELDS_MAP = new ConcurrentHashMap<>(); /** * 导出文件 * * @param request * @param response */ public void export(List records, Class clazz, HttpServletRequest request, HttpServletResponse response) { if (CollectionUtil.isNotEmpty(records)) { //1、创建导出对象 ExcelBean excelBean = createExcelBean(); //2、执行导出 export(excelBean, records, clazz); //3、往访问端写出excel文件 writeResponse(excelBean, request, response); } } /** * 获取导出Bean * * @return */ private ExcelBean createExcelBean() { //轻量级SXSSF导出 SXSSFWorkbook wb = new SXSSFWorkbook(); //创建sheet Sheet sheet = wb.createSheet(); //创建首行 sheet.createRow(0); //冻结首行 sheet.createFreezePane(0, 1); Excel2007Bean excel2007Bean = new Excel2007Bean(); excel2007Bean.setWorkbook(wb); excel2007Bean.setSheet(sheet); excel2007Bean.setHeadStyle(createHeadStyle(wb)); excel2007Bean.setRowStyle(createRowStyle(wb)); return excel2007Bean; } /** * 创建头样式 * * @param wb * @return */ private CellStyle createHeadStyle(Workbook wb) { CellStyle headStyle = wb.createCellStyle(); //首行字体 Font headFont = wb.createFont(); headFont.setFontName("宋体"); //加粗 headFont.setBold(true); //字体大小 headFont.setFontHeightInPoints((short) 12); headStyle.setFont(headFont); //左右居中 headStyle.setAlignment(HorizontalAlignment.CENTER); //上下居中 headStyle.setVerticalAlignment(VerticalAlignment.CENTER); return headStyle; } /** * 创建行样式 * * @param wb * @return */ private CellStyle createRowStyle(Workbook wb) { CellStyle headStyle = wb.createCellStyle(); //左右居中 headStyle.setAlignment(HorizontalAlignment.CENTER); return headStyle; } /** * 导出 * * @param excelBean 导出对象 * @param records 导出数据 * @param clazz 导出Class */ private void export(ExcelBean excelBean, List records, Class clazz) { Sheet sheet = excelBean.getSheet(); Row headRow = sheet.createRow(0); if (records == null) { //集合对象为空，获取不了对象数据 throw new UnsupportedOperationException("excel导出对象信息为空"); } //获取需要导出的列 if (clazz == null) { //导出Class为空，获取不了对象头数据 throw new UnsupportedOperationException("excel导出Class为空"); } if (records.size() > excelBean.getRowLimit()) { throw new UnsupportedOperationException("excel导出数据量超限"); } List<Field> fields = getAllFields(clazz); List<ColumnInfo> columnInfos = new ArrayList<>(fields.size()); for (Field field : fields) { field.setAccessible(true); ExcelColumn annotation = AnnotationUtils.findAnnotation(field, ExcelColumn.class); if (annotation != null) { columnInfos.add(new ColumnInfo(field, StrUtil.blankToDefault(annotation.name(), field.getName()), annotation.index(), annotation.dateFormat())); } } if (CollectionUtil.isEmpty(columnInfos)) { //导出的对象没有字段使用注解 throw new UnsupportedOperationException("excel导出的对象没有字段使用ExcelColumn注解"); } //排序 CollectionUtil.sort(columnInfos, Comparator.comparing(ColumnInfo::getColumnIndex)); int columnSize = columnInfos.size(); if (columnSize > excelBean.getColLimit()) { throw new UnsupportedOperationException("excel导出字段超限"); } //创建首行 CellStyle headStyle = excelBean.getHeadStyle(); for (int i = 0; i < columnSize; i++) { Cell cell = headRow.createCell(i); cell.setCellValue(columnInfos.get(i).getColumnName()); cell.setCellStyle(headStyle); //设置列的宽度 sheet.setColumnWidth(i, 4200); } //创建行数据 if (CollectionUtil.isNotEmpty(records)) { int size = records.size() + 1; CellStyle rowStyle = excelBean.getRowStyle(); //代表有多少行数据 for (int i = 1; i < size; i++) { Row row = sheet.createRow(i); //代表有多少列数据 for (int j = 0; j < columnSize; j++) { Cell cell; //查到值 Object value = null; try { value = columnInfos.get(j).getField().get(records.get(i - 1)); } catch (Exception e) { e.printStackTrace(); } if (value != null) { if (value instanceof Date) { cell = row.createCell(j); cell.setCellValue(DateUtil.format((Date) value, columnInfos.get(j).getDateFormat())); cell.setCellStyle(rowStyle); } else { cell = row.createCell(j); cell.setCellValue(value.toString()); cell.setCellStyle(rowStyle); } } } } } } /** * 获取所有字段 * * @param classz * @return */ private static List<Field> getAllFields(Class classz) { List<Field> fieldList = new ArrayList<>(); Class tempClass = classz; if (CACHE_FIELDS_MAP.get(classz.getTypeName()) != null) { return CACHE_FIELDS_MAP.get(classz.getTypeName()); } //当父类为null的时候说明到达了最上层的父类(Object类). while (tempClass != null) { fieldList.addAll(Arrays.asList(tempClass.getDeclaredFields())); //得到父类,然后赋给自己 tempClass = tempClass.getSuperclass(); } CACHE_FIELDS_MAP.put(classz.getTypeName(), fieldList); return fieldList; } /** * 写出excel文件数据，并删除本地的文件 */ private void writeResponse(ExcelBean ex