依赖
<!-- 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()
不再支持公式的求值