百万数据导出生成一个Excel多个sheet

因为公司禁止使用第三方工具，所以使用的原生poi来实现的。（如果公司没要求，建议使用阿里的esayExcel）

一、创建导出类的扫描注解，判断是否是导出的信息

@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
    /**
     * 列索引
     * @return
     */
    public int columnIndex() default 0;

    /**
     * 列名
     * @return
     */
    public String columnName() default "";
}

二、创建导出对应的java类，并加上面的自定义注解；

@Data
public class BillMeituanPaymentOrderDto {

    @ExcelColumn(columnIndex = 0,columnName = "产品类型")
    private String productType;

    @ExcelColumn(columnIndex = 1,columnName = "车型")
    private String carType;

    @ExcelColumn(columnIndex = 2,columnName = "订单号")
    private String orderNo;

    @ExcelColumn(columnIndex = 3,columnName = "城市")
    private String city;

    @ExcelColumn(columnIndex = 4,columnName = "订单支付状态")
    private String orderPayState;

    @ExcelColumn(columnIndex = 5,columnName = "订单创建时间")
    private String orderTime;

    @ExcelColumn(columnIndex = 6,columnName = "订单支付完成时间")
    private String payTime;

    @ExcelColumn(columnIndex = 7,columnName = "服务商订单id")
    private String operatorOrderId;

    @ExcelColumn(columnIndex = 8,columnName = "订单是否归属当月账期")
    private String isCurrentMonth;

    @ExcelColumn(columnIndex = 9,columnName = "是否垫付")
    private String isAdvance;
}

三、生成公用的excel导出util类：ExportExcelUtils

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

@Slf4j
public class ExportExcelUtils {

  /**
     *  将数据填充到excel里
     * @param workbook  要生成excel
     * @param list  填充数据集合
     * @param isCreateTitle 是否创建标题
     * @param isCreateSheet 是否创建sheet
     * @param sheetName  sheet名称
     * @return
     * @throws IOException
     */
    public static SXSSFWorkbook updateWorkbook(SXSSFWorkbook workbook, List<?> list, boolean isCreateTitle, boolean isCreateSheet, String sheetName) throws IOException {
        SXSSFSheet sheet;
        // 设置工作表的名称
        if (isCreateSheet) {
            sheet = workbook.createSheet(sheetName);
        } else {
            sheet = workbook.getSheet(sheetName);
        }
        // 获取实体所有属性
        Field[] fields = list.get(0).getClass().getDeclaredFields();
        ExcelColumn excelColumn;
        // 列索引
        int index = 0;
        // 行
        SXSSFRow row;
        //创建标题
        if (isCreateTitle) {
            Font font = workbook.createFont();
            //设置标题高度、字体
            font.setFontHeightInPoints((short) 16);
            font.setFontName("宋体");
            font.setBold(true);
            // 创建单元格标题样式
            CellStyle styleTitle = workbook.createCellStyle();
            styleTitle.setAlignment(HorizontalAlignment.CENTER);
            // 设置标题的下左右边框
            styleTitle.setBorderBottom(BorderStyle.THIN);
            styleTitle.setBorderLeft(BorderStyle.THIN);
            styleTitle.setBorderRight(BorderStyle.THIN);
            styleTitle.setFont(font);
            //设置标题背景颜色
            styleTitle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            styleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 创建第1行
            row = sheet.createRow(0);
            // 列名称
            String name;
            // 创建表头
            for (Field f : fields) {
                // 是否是注解
                if (f.isAnnotationPresent(ExcelColumn.class)) {
                    // 获取注解
                    excelColumn = f.getAnnotation(ExcelColumn.class);
                    // 获取列索引
                    index = excelColumn.columnIndex();
                    // 列名称
                    name = excelColumn.columnName();
                    //设置表单元格宽度值
                    sheet.setColumnWidth(index, (name.getBytes().length + 2) * 256);
                    //设置标题单元格高度
                    row.setHeightInPoints(35);
                    // 创建单元格
                    creCell(row, index, name, styleTitle);
                }
            }
        }
        // 创建单元格样式
        CellStyle style = workbook.createCellStyle();
        // 居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置上下左右边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 行索引  因为表头已经设置，索引行索引从1开始
        int rowIndex = sheet.getLastRowNum()+1;
        for (Object obj : list) {
            // 创建新行，索引加1,为创建下一行做准备
            row = sheet.createRow(rowIndex++);
            for (Field f : fields) {
                // 设置属性可访问
                f.setAccessible(true);
                // 判断是否是注解
                if (f.isAnnotationPresent(ExcelColumn.class)) {
                    // 获取注解
                    excelColumn = f.getAnnotation(ExcelColumn.class);
                    // 获取列索引
                    index = excelColumn.columnIndex();
                    //设置表单元格宽度值
                    try {
                        creCell(row, index, String.valueOf(f.get(obj)), style);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }

                }
            }
        }
        return workbook;
    }

    /**
     *  将excel生成到指定的路径
     * @param workbook
     * @param filepath 路径
     * @param filename 文件名称
     * @return
     */
    public static boolean exportExe(SXSSFWorkbook workbook, String filepath, String filename) {
        boolean success = false;
        FileOutputStream output = null;
        try {
            //判断是否存在目录. 不存在则创建
            isChartPathExist(filepath);
            //输出Excel文件
            output = new FileOutputStream(filepath + "/" + filename);
            //写入磁盘
            workbook.write(output);
            success = true;
        } catch (Exception e) {
            log.info("导出错误", e);
        } finally {
            if (workbook != null) {
                //消除生成excel表格生成的临时文件
                workbook.dispose();
            }
            Streams.close(workbook);
            Streams.close(output);
        }
        return success;
    }

    /**
     * 导出设置返回头
     *
     * @param response
     * @param excelName
     */
    public static void setHeader(HttpServletResponse response, String excelName) {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-type", "application/xls;charset=UTF-8");
        try {
            String fileName = new String(excelName.getBytes("UTF-8"),
                    "ISO8859-1");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        } catch (UnsupportedEncodingException e) {
            log.error("设置返回头出错啦,", e);
        }

    }

    /**
     * 创建单元格
     *
     * @param row
     * @param c
     * @param cellValue
     * @param style
     */
    public static void creCell(Row row, int c, String cellValue, CellStyle style) {
        Cell cell = row.createCell(c);
        cell.setCellValue(cellValue);
        cell.setCellStyle(style);
    }

   /**
     * 生成excel到指定路径
     *
     * @param wb
     * @param path
     * @throws Exception
     */
    public static void generateExcelToPath(HSSFWorkbook wb, String path) throws Exception {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(path);
            wb.write(fos);
        } finally {
            if (fos != null) {
                fos.flush();
                fos.close();
            }
            if (wb != null) {
                wb.close();
            }
        }
    }

    /**
     * 判断文件夹是否存在，如果不存在则新建
     *
     * @param dirPath 文件夹路径
     */
    public static void isChartPathExist(String dirPath) {
        File file = new File(dirPath);
        if (!file.exists()) {
            file.mkdirs();
        }
    }

    public static boolean delAllFile(String path) {
        boolean flag = false;
        File file = new File(path);
        if (!file.exists()) {
            return flag;
        }
        if (!file.isDirectory()) {
            return flag;
        }
        String[] tempList = file.list();
        File temp = null;
        for (int i = 0; i < tempList.length; i++) {
            if (path.endsWith(File.separator)) {
                temp = new File(path + tempList[i]);
            } else {
                temp = new File(path + File.separator + tempList[i]);
            }
            if (temp.isFile()) {
                temp.delete();
            }
            if (temp.isDirectory()) {
                delAllFile(path + "/" + tempList[i]);// 先删除文件夹里面的文件
                flag = true;
            }
        }
        return flag;
    }
}

四、使用

@Slf4j
@Service
public class BizExportMeituanExcelService {

    //默认10分钟
    protected int workSeconds = 60 ;
    @Autowired
    private WorkLock workLock;
    @Autowired
    private WorkLogService workLogService;\
　　//这里是application里的配置生成文件路径信息，并设置了默认值
    @Value("${test.bill.file:/app/data/bill}")
    private String axebillFile;

    /**
     * 批量导出Excel
     *
     * @param response
     * @param req 为生成excel的过滤条件，这个自己根据需求定义
     * @throws IOException
     */
    public JsonResult exportExcel(HttpServletResponse response, ExportBillMeituanReq req) throws IOException {
        workLock.lock(workSeconds);
        JsonResult result = new JsonResult();
        if (StringUtils.isBlank(req.getActNo()) || StringUtils.isBlank(req.getChannel())) {
            result.setSuccess(false);
            result.setMessage("账期和产品类型不能为空！");
            return result;
        }

        String msg = "";
        Date startTime = new Date();
        try {
            boolean success = false;
            String actNo = req.getActNo();
            String channel = req.getChannel();
            List<String> cityList = settleDayService.getCityList(actNo,channel);
            //如果账期内有交易的城市，才生成excel
            if (!CollectionUtils.isEmpty(cityList)) {
                String filepath = getAxebillFile() + "/meituan/" + actNo + "/" + channel;
                ExportExcelUtils.delAllFile(filepath);
                for (String s : cityList) {
                    success = exportSingleExcel(response, actNo, s, channel, filepath);
                }
            } else {
                result.setSuccess(true);
                result.setMessage("请先点击【生成结算单】，或者没有数据！");
                return result;
            }

            if (success) {
                result.setSuccess(true);
                result.setMessage("生成成功！");
            } else {
                result.setSuccess(false);
                result.setMessage("生成失败！");
            }
        } catch (Exception e) {throw e;
        } finally {
            workLock.unLock();
        }
        return result;
    }

    /**
     * 生成单个excel表格
     */
    private boolean exportSingleExcel(HttpServletResponse response, String actNo, String city, String channel, String filepath) throws IOException {
        //单张表格的各个sheet
        // 创建Excel工作簿对象
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        //查询3个sheet的数据
        List<BillMeituanPaymentOrderDto> paymentOrderTotalList = new ArrayList<>();
        int total = paymentOrderService.getTotal(actNo, city,channel);
        int pageSize = 10000;

        for (int i = 0; i < (total + pageSize - 1) / pageSize; i++) {
　　　　　　　　　　//这里是分页查要导出的询数据
            List<BillMeituanPaymentOrderDto> paymentOrderList = paymentOrderService.getPaymentOrderList(actNo, city, channel, i*pageSize, pageSize);
            if (i == 0) {
　　　　　　　　　　//将要导出的数据生成excel
                ExportExcelUtils.updateWorkbook(workbook, paymentOrderList, true, true, "订单明细");
            } else {
                ExportExcelUtils.updateWorkbook(workbook, paymentOrderList, false, false, "订单明细");
            }
        }

        List<BillMeituanSettleDayDto> settleDayList = settleDayService.getSettleDayList(actNo, city,channel);
        if (!CollectionUtils.isEmpty(settleDayList)) {
            ExportExcelUtils.updateWorkbook(workbook, settleDayList, true, true, "账单汇总");
        }

        //设置生成excel的名称
        String filename = actNo + "-" + city + "-" + channel + "账单.xlsx";
        return ExportExcelUtils.exportExe(workbook, filepath, filename);
    }

}