生成多个sheet的Excel

1、controller接口定义

    @ApiOperation("导出")
    @PostMapping("/export/file")
    public void export(@RequestBody List<String> fundIds, HttpServletResponse response) throws Exception {
        investorService.export(fundIds, response);
    }

2、调用service

void exportO32Rpa(List<String> fundIds, HttpServletResponse response) throws Exception;

3、实现service

 @Override
public void exportO32Rpa(List<String> fundIds, HttpServletResponse response) throws Exception {
    //excel名称
    ExportExcelUtil.setHeader("O32基金-资产单元-组合.xlsx", response);
    //查询出三个sheet的数据
    List<FundinfoEntity> fundinfos = investorMapper.queryFundInfo(fundIds);
    List<AssetEntity> assets = investorMapper.getAssetInfo(fundIds);
    List<CombiEntity> combis = investorMapper.getCombiInfo(fundIds);
    o32RpaExportService.export(response.getOutputStream(), fundinfoEntities, assetEntities, combiEntities);
}

    void export(OutputStream outputStream, List<FundinfoEntity> fundinfoEntities, List<AssetEntity> assetEntities,List<CombiEntity> combiEntities) throws IOException;

//定义表格头
private static final String FUNDHEADS = "基金编号,基金代码,基金简称,基金全称,结算参与人编号,财务对账,投资方向,TA编号,TA数据处理模式,TA金额交收方式"

@Override
    public void export(OutputStream outputStream, List<RpaFundinfoEntity> fundinfoEntities, List<RpaAssetEntity> assetEntities, List<RpaCombiEntity> combiEntities) throws IOException {
        ExcelWriter excelWriter = ExcelWriter.instanceXlsx(100);
        outputFundInfo(excelWriter, fundinfoEntities);
        outputAsset(excelWriter, assetEntities);
        outputCombi(excelWriter, combiEntities);
        excelWriter.flushAndClose(outputStream);
    }
    private void outputFundInfo(ExcelWriter excelWriter, List<RpaFundinfoEntity> fundinfoEntities) {
        excelWriter.createSheet("基金");
        excelWriter.createRow();
        Arrays.stream(FUNDHEADS.split(",")).forEach(item -> excelWriter.addHeads(item));
        if (CollectionUtils.isEmpty(fundinfoEntities)) {
            return;
        }
        fundinfoEntities.stream().forEach(item -> {
            excelWriter.createRow();
            excelWriter.createCellObject(item.getO32FundId());
            excelWriter.createCellObject(item.getO32FundCode());
            excelWriter.createCellObject(item.getO32FundCaption());
            excelWriter.createCellObject(item.getO32FundName());
            //以下雷同
            。。。。。。
        });
    }
//三个方法雷同
//outputAsset
//outputCombi

//关闭流
public void flushAndClose(OutputStream outputStream) throws IOException {
        if (outputStream == null) {
            throw new RuntimeException("OutputStream can not be null");
        }
        this.workbook.write(outputStream);
        IOUtils.closeQuietly(outputStream);
        IOUtils.closeQuietly(workbook);
    }

4、工具类

package com.cpic.investor.support.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;


public class ExcelWriter {
    private AtomicInteger rowIndex = null;
    private AtomicInteger cellIndex = null;
    private Workbook workbook;
    private Sheet sheet;
    private Row row;
    private CellStyle cellStyle;
    private CellStyle numCellStype;
    private String decimalFormat;

    private ExcelWriter(int rowAccessWindowSize, boolean isXls) throws FileNotFoundException {
        initialize(rowAccessWindowSize, isXls);
    }

    public static ExcelWriter instanceXls() throws FileNotFoundException {
        return new ExcelWriter(0, true);
    }

    public static ExcelWriter instanceXlsx(int rowAccessWindowSize) throws FileNotFoundException {
        return new ExcelWriter(rowAccessWindowSize, false);
    }

    public void setCellStyle(CellStyle cellStyle) {
        this.cellStyle = cellStyle;
    }

    public void setAutoSizeColumn(int column) {
        this.sheet.autoSizeColumn(column, true);
    }

    public void setDecimalFormat(String decimalFormat) {
        this.decimalFormat = decimalFormat;
    }

    private void initialize(int rowAccessWindowSize, boolean isXls) throws FileNotFoundException {
        if (!isXls && rowAccessWindowSize <= 0) {
            throw new RuntimeException("rowAccessWindowSize must ge 0");
        }
        if (isXls) {
            workbook = new HSSFWorkbook();
        } else {
            workbook = new SXSSFWorkbook(rowAccessWindowSize);
        }
        if (StringUtils.isBlank(decimalFormat)) {
            decimalFormat = "#,##0;-#,##0";
        }
        numCellStype = workbook.createCellStyle();
        DataFormat dataFormat = workbook.createDataFormat();
        numCellStype.setDataFormat(dataFormat.getFormat(decimalFormat));
        rowIndex = new AtomicInteger();
        cellIndex = new AtomicInteger();
    }

    public CellStyle createStyle() {
        return this.workbook.createCellStyle();
    }

    /**
     * 创建Sheet
     *
     * @param name
     */
    public void createSheet(String name) {
        this.sheet = this.workbook.createSheet(name);
        rowIndex.set(0);
    }

    /**
     * 创建行
     */
    public void createRow() {
        this.row = this.sheet.createRow(rowIndex.getAndIncrement());
        cellIndex.set(0);
    }

    /**
     * 获取当前行数
     *
     * @return
     */
    public int getRowNum() {
        return rowIndex.get();
    }

    /**
     * 新增表头
     *
     * @param headName
     */
    public void addHeads(String headName) {
        createCellText(headName);
    }

    /**
     * 刷新并关闭流
     *
     * @throws IOException
     */
    public void flushAndClose(OutputStream outputStream) throws IOException {
        if (outputStream == null) {
            throw new RuntimeException("OutputStream can not be null");
        }
        this.workbook.write(outputStream);
        IOUtils.closeQuietly(outputStream);
        IOUtils.closeQuietly(workbook);
    }

    /**
     * 创建单元格
     *
     * @param value
     */
    public void createCellText(Object value) {
        String resultValue = value == null ? "" : value.toString();
        Cell cell0 = this.row.createCell(this.cellIndex.getAndIncrement());
        cell0.setCellStyle(this.cellStyle);
        cell0.setCellValue(resultValue);
    }

    /**
     * 创建单元格
     *
     * @param value
     */
    public void createCellNum(Object value) {
        Cell cell0 = this.row.createCell(this.cellIndex.getAndIncrement());
        cell0.setCellStyle(this.numCellStype);
        cell0.setCellValue(Double.parseDouble(value.toString()));
    }

    /**
     * 创建单元格
     *
     * @param value
     */
    public void createCellFormula(String value) {
        Cell cell0 = this.row.createCell(this.cellIndex.getAndIncrement());
        cell0.setCellStyle(this.cellStyle);
        cell0.setCellFormula(value);
    }

    public void createLink(String link, String title) {
        createCellFormula("HYPERLINK(\"#" + link + "\",\"" + title + "\")");
    }

    public void createCellObject(Object o) {
        if (o instanceof Number) {
            createCellNum(o);
        } else if (o instanceof Date) {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            createCellText(simpleDateFormat.format(o));
        } else {
            createCellText(o);
        }
    }

    public void createCellDate(Date o) {
        Cell cell0 = this.row.createCell(this.cellIndex.getAndIncrement());
        cell0.setCellStyle(this.cellStyle);
        cell0.setCellValue(o);
    }
}