poi动态导出excel(.xlsx)

Apache POI官网地址

依赖

<!-- excel工具 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

工具类

package com.fct.web.controller.tool;

import com.google.common.base.Strings;
import lombok.Data;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;

@Data
public class ExcelExportUtil {
    //表头
    private String title;
    //各个列的表头
    private List<String> headList;
    //各个列的元素key值
    private List<String> headKey;
    // 各个列的宽度
    private List<Integer> headWidth;
    //需要填充的数据信息
    private List<HashMap> data;
    //字体大小
    private int fontSize = 14;
    //行高
    private int rowHeight = 30;
    //列宽
    private int columWidth = 200;
    //工作表
    private String sheetName = "sheet";
    // 文件名
    private String fileName;

    XSSFWorkbook wb;

    XSSFSheet sheet;

    XSSFCellStyle headCellStyle;

    HttpServletResponse response;

    public ExcelExportUtil exportExport(HttpServletResponse response) throws IOException {
        this.response = response;
        wb = new XSSFWorkbook();
        int size = data.size();
        int count = (size - 1) / 50000 + 1;
        for (int i = 0; i < count; i++) {
            sheet = wb.createSheet(sheetName + (i + 1));
            exportExport(sheet,
                    data.subList(i * 50000, ((i + 1) * 50000 > size ? size : 50000 * (i + 1))));
        }
        return this;
    }

    private void exportExport(XSSFSheet sheet, List<HashMap> data) throws IOException {

        //检查参数配置信息
        checkConfig();
        XSSFCellStyle cellStyle = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();
        //这样才能真正的控制单元格格式，@就是指文本型
        cellStyle.setDataFormat(format.getFormat("@"));

        XSSFRow headRow = sheet.createRow(0);
        //设置列头元素
        for (int i = 0; i < headList.size(); i++) {
            Integer width = 15;
            if (headWidth != null && headWidth.size() >= headList.size()) {
                width = headWidth.get(i);
            }
            sheet.setColumnWidth(i, 256 * width + 184);
            XSSFCell cellHead = headRow.createCell(i);
            cellHead.setCellValue(headList.get(i));
            cellHead.setCellStyle(headCellStyle);
        }

        //开始写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            XSSFRow row = sheet.createRow(a);
            HashMap map = data.get(i);
            XSSFCell cell;
            for (int j = 0; j < headKey.size(); j++) {
                cell = row.createCell(j);
                Object valueObject = map.get(headKey.get(j));
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    cell.setCellValue(((Integer) (valueObject)).floatValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    cell.setCellValue(((BigDecimal) (valueObject)).floatValue());
                } else {
                    //取出的数据是字符串直接赋值
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(Strings.isNullOrEmpty(String.valueOf(valueObject)) ? "" : String.valueOf(valueObject));
                }
            }
            a++;
        }
    }

    public void flushExplorer() throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/x-msdownload");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        wb.write(response.getOutputStream());
    }

    /**
     * 检查数据配置问题
     *
     * @throws IOException 抛出数据异常类
     */
    protected void checkConfig() throws IOException {
        if (headKey == null) {
            throw new IOException("表头不能为空");
        }

        if (headWidth != null && headWidth.size() < headKey.size()) {
            throw new IOException("设置宽度的列数必须超过表头列数");
        }

        if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {
            throw new IOException("字体、宽度或者高度不能为负值");
        }

        if (Strings.isNullOrEmpty(sheetName)) {
            throw new IOException("工作表表名不能为NULL");
        }
        createDefaultHeadStyle();
    }

    public void createDefaultHeadStyle() {
        //创建表头样式
        headCellStyle = wb.createCellStyle();
        //居中
        headCellStyle.setAlignment(HorizontalAlignment.LEFT);
        //背景色
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());

        //字体
        XSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        //字体增粗
        font.setBold(true);
        headCellStyle.setFont(font);
        // 下边框
        headCellStyle.setBorderBottom(BorderStyle.THIN);
        // 左边框
        headCellStyle.setBorderLeft(BorderStyle.THIN);
        // 上边框
        headCellStyle.setBorderTop(BorderStyle.THIN);
        // 右边框
        headCellStyle.setBorderRight(BorderStyle.THIN);
    }

}

调用示例

/**
 * @author: CL
 * Description: 导出最新一次计算的数据
 * @date: 2021/6/11 15:28
 * @params: [response]
 * @return: void
*/
@Override
public void exportCalculateData(HttpServletResponse response) {
    try {
        //查询版本号
        ResultCondition resultCondition = enterpriseDataMapper.queryDataVersion();
        //根据版本号查询最近一次计算原始数据
        List<ResultVo> resultVos = enterpriseDataMapper.queryResult(resultCondition.getResultConditionId());
        //查询指标
        for (ResultVo resultVo : resultVos) {
            List<ResultQuota> resultQuotas = enterpriseDataMapper.queryResultQuota(resultVo.getResultId());
            resultVo.setQuotaList(resultQuotas);
        }
        ExcelExportUtil excelExportUtil = new ExcelExportUtil();
        // 表头固定列
        List<String> headList = new ArrayList<>();
        headList.add("企业名称");
        headList.add("年份");
        headList.add("城市");
        headList.add("区县");
        headList.add("行业");
        // 表头固定key
        List<String> headKey = new ArrayList<>();
        headKey.add("enterpriseName");
        headKey.add("dataYear");
        headKey.add("cityName");
        headKey.add("regionName");
        headKey.add("industryName");
        System.out.println("resultVos"+resultVos);
        ResultVo resultVo = resultVos.get(0);
        List<ResultQuota> quotaList = resultVo.getQuotaList();
        for (ResultQuota resultQuota : quotaList) {
            headList.add(resultQuota.getQuotaName());
            headKey.add(resultQuota.getQuotaId());
        }
        headList.add("E值");
        headKey.add("e");
        ArrayList<HashMap> hashMapList = new ArrayList<>();
         DateFormat format = new SimpleDateFormat("yyyy");
        for (ResultVo vo : resultVos) {
            HashMap<String, Object> resultHashMap = new HashMap<>();
            resultHashMap.put("enterpriseName",vo.getEnterpriseName());
            resultHashMap.put("dataYear", format.format(vo.getDataYear()));
            resultHashMap.put("cityName",vo.getCityName());
            resultHashMap.put("regionName",vo.getRegionName());
            resultHashMap.put("industryName",vo.getIndustryName());
            resultHashMap.put("e",vo.getE());
            List<ResultQuota> quotas = vo.getQuotaList();
            for (ResultQuota quota : quotas) {
                resultHashMap.put(quota.getQuotaId(),quota.getQuotaValue());
            }
            hashMapList.add(resultHashMap);
        }
        System.out.println("hashMapList:"+hashMapList);
        excelExportUtil.setHeadList(headList);
        excelExportUtil.setHeadKey(headKey);
        excelExportUtil.setData(hashMapList);
        excelExportUtil.setFileName("CalculateData");
        excelExportUtil.exportExport(response).flushExplorer();
    } catch (Exception e) {
        log.error("导出数据失败！",e);
    }
}

注：
HSSF，XSSF和SXSSF的区别
HSSF是POI工程对Excel 97(-2007)文件操作的纯Java实现
XSSF是POI工程对Excel 2007 OOXML (.xlsx)文件操作的纯Java实现
从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的API----SXSSF

SXSSF通过一个滑动窗口来限制访问Row的数量从而达到低内存占用的目录，但是千万注意，XSSF可以访问所有行，但是SXSSF中，
旧的行数据不再出现在滑动窗口中并变得无法访问，与此同时写到磁盘上。
在自动刷新的模式下，可以指定窗口中访问Row的数量，从而在内存中保持一定数量的Row。当达到这一数量时，在窗口中产生新的Row数据，并将低索引的数据从窗口中移动到磁盘中。
或者，滑动窗口的行数可以设定成自动增长的。它可以根据需要周期的根据一次明确的flushRow(int keepRows)调用来进行修改。
与XSSF的对比

在一个时间点上，只可以访问一定数量的数据
不再支持Sheet.clone()
不再支持公式的求值