Java生成并下载Excel文件-工具类
1.设计思路
Excel文件内容分为：表头和数据内容，需要下载Excel文件，先需要查询数据，将需要导出的数据生成Excel文件，然后才能通过输出流下载
2.代码实现
2.1.生成Excel文件
a. 给定fps路径、Excel文件名，新建一个空文件对象File，调用生成文件方法，调用FileUtil工具类将文件以流的形式写入response
public void exportTaskDetailFile(ErrHandleTaskReq req, HttpServletResponse response) throws IOException {
String fileName = String.format("%s_%s.%s", req.getId(), System.currentTimeMillis(), "xlsx");
String dirPath = String.format("%s/%s", ConfigUtil.getConf("fps.filepath"), LocalDate.now().format(WeDateUtils.FMT_DATE_SHORT));
File dir = new File(dirPath);
if (!dir.exists()) {
dir.mkdirs();
}
File excelFile = new File(String.format("%s/%s", dirPath, fileName));
generateDetailFile(excelFile, req.getId());//将数据写入Excel文件
FileUtil.writeResponse(excelFile, response);//打印输出流
}
b. 将数据写入Excel文件
查询并封装需要的生成的Excel文件数据，调用Excel工具类生成Excel文件
private void generateDetailFile(File excelFile, String taskId) throws IOException {
String sheetName = "差错文件需要处理明细";
String[] headName = EXPORT_TASK_DETAIL_NAME.toArray(new String[EXPORT_TASK_DETAIL_NAME.size()]);
String[] headKey = EXPORT_TASK_DETAIL_KEY.toArray(new String[EXPORT_TASK_DETAIL_KEY.size()]);
int batchSize = 1000;
int count = depositDailyAdjustRecordDao.selectCountByTaskId(taskId);
Function<Integer, List<ExportTaskDetailFileBo>> queryFunction = i -> {
List<DepositDailyAdjustRecordEntityWithBLOBs> list = depositDailyAdjustRecordDao.selectListByTaskId(taskId, i * batchSize, batchSize);
List<ExportTaskDetailFileBo> returnList = new ArrayList<>();
list.stream().forEach(entity -> {
ExportTaskDetailFileBo bo = new ExportTaskDetailFileBo();
BeanUtils.copyProperties(entity, bo);
DepositDailyRecordEntity oriEntity = JsonUtil.fromJson(entity.getOriBizData(), DepositDailyRecordEntity.class);
DepositDailyRecordEntity newEntity = JsonUtil.fromJson(entity.getNewBizData(), DepositDailyRecordEntity.class);
if (oriEntity != null) {
bo.setOriPassFlag(YesNoEnum.get(oriEntity.getPassFlag()).getRemark());
bo.setOriCardLevel(oriEntity.getCardLevel());
bo.setOriDayEndAmt(oriEntity.getDayEndAmt());
}
if (newEntity != null) {
bo.setPassFlag(YesNoEnum.get(newEntity.getPassFlag()).getRemark());
bo.setCardLevel(newEntity.getCardLevel());
bo.setDayEndAmt(newEntity.getDayEndAmt());
}
bo.setState(String.format("%s-%s", entity.getState(), TxnStatusEnum.get(entity.getState()).getRemark()));
bo.setDealStep(String.format("%s-%s", entity.getDealStep(), FileDealStep4PointAdjustEnum.get(entity.getDealStep()).getRemark()));
bo.setErrType(String.format("%s-%s", ErrType4DepositDailyAdjustRecordEnum.get(entity.getErrType()).getCode(),
ErrType4DepositDailyAdjustRecordEnum.get(entity.getErrType()).getRemark()));
bo.setHandlerType(String.format("%s-%s", HandleType4DepositDailyAdjustRecordEnum.get(entity.getHandlerType()).getCode(),
HandleType4DepositDailyAdjustRecordEnum.get(entity.getHandlerType()).getRemark()));
bo.setCreateTime(DateUtils.localTimeFormat(entity.getCreateTime(), WeDateUtils.DATETIME_LONG));
bo.setUpdateTime(DateUtils.localTimeFormat(entity.getUpdateTime(), WeDateUtils.DATETIME_LONG));
returnList.add(bo);
});
return returnList;
};
// 生成Excel文件
ExcelUtil.writeExcel(excelFile, sheetName, headName, headKey, count, batchSize, queryFunction);
}
c. 生成Excel文件
分批次调用Function查询结果，并将数据写入Excel文件
public static <T> boolean writeExcel(File excelFile, String sheetName, String[] headName, String[] headKey, int count,
int batchSize, Function<Integer, List<T>> queryFunction) throws IOException {
// 生成工作簿并写入数据
return ExcelUtil.traverse(excelFile, workbook -> {
// 生成表头
generateExcelTitle(workbook, sheetName, headName, headKey, count);
// 生成数据
int times = count / batchSize + 1;// 需要写入次数
for (int i = 0; i < times; i++) {
// 从sheetName页第batchSize*i+1追加写入数据
int startRow = batchSize * i + 1;
List<T> dataList = queryFunction.apply(i);
generateExcelData(workbook, sheetName, headKey, dataList, startRow);
}
});
}
d. 生成工作簿Workbook并写入数据
1). 新建工作簿(XLS/XLSX类型)
private static Workbook getWorkbook(File excelFile) {
String extension = excelFile.getAbsolutePath().substring(excelFile.getAbsolutePath().lastIndexOf(".") + 1);
switch (extension) {
case XLS:
return new HSSFWorkbook();
case XLSX:
return new SXSSFWorkbook();//写入文件时使用流式版XFFS，防止大文件导出时内存溢出
default:
throw new IllegalArgumentException(String.format("%s|%s", "EXCEL文件扩展名有误", extension));
}
}
2). 调用Consumer实现，向工作簿写入数据，并写回原始文件excelFile
public static boolean traverse(File excelFile, Consumer<Workbook> consumer) throws IOException {
Workbook workbook = null;
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(excelFile);
// 新建工作簿(XLS/XLSX类型)
workbook = getWorkbook(excelFile);
// 调用Consumer实现，向工作簿写入数据
consumer.accept(workbook);
// 工作簿写回原始文件excelFile
workbook.write(outputStream);
return true;
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != outputStream) {
outputStream.close();
}
} catch (Exception e) {
OpLogUtil.logOpStepException("关闭流", "异常", e);
}
}
}
e. 向工作簿Workbook写入Excel表头数据
将定义的Excel表头名称写入工作簿
public static void generateExcelTitle(Workbook workbook, String sheetName, String[] headName, String[] headKey, int count) {
if (count <= 0) {
throw new BusinessException("数据为空，无需生成");
}
if (headName.length == 0 || headKey.length == 0) {
throw new BusinessException("表头为空，无需生成");
}
if (headName.length != headKey.length) {
throw new BusinessException("表头数与要求生成数不一致");
}
// 往sheetName页写一行表头数据
writeSheetRows(workbook, sheetName, Collections.singletonList(Arrays.asList(headName)), 0);
}
f. 向工作簿Workbook写入Excel内容数据
转换需要写入的每一行数据的类型后，追加写入工作簿
public static void generateExcelData(Workbook workbook, String sheetName, String[] headKey, List dataList, int startRow) {
List<List<Object>> dataObjList = new ArrayList<>();
try {
Iterator iterator = dataList.listIterator();
while (iterator.hasNext()) {
List<Object> data = new ArrayList<>();
Object obj = iterator.next();
Field[] fields = obj.getClass().getDeclaredFields();
for (int j = 0; j < headKey.length; j++) {
for (int i = 0; i < fields.length; i++) {
if (fields[i].getName().equals(headKey[j])) {
fields[i].setAccessible(true);
if (fields[i].get(obj) == null) {
data.add("");
break;
}
data.add(fields[i].get(obj).toString());
break;
}
}
}
dataObjList.add(data);
}
} catch (Exception e) {
OpLogUtil.logOpStepException("Excel数据转换", "异常", e);
}
// 往sheetName页写多行数据
writeSheetRows(workbook, sheetName, dataObjList, startRow);
}
g. 往工作簿sheetName写入多行数据（生成Excel文件的核心代码）
1). 遍历一行数据，往Row创建一行的单元格
private static void writeRow(List<Object> rowObj, Row row) {
for (int i = 0; i < rowObj.size(); i++) {
row.createCell((short) i).setCellValue(rowObj.get(i).toString());
}
}
2). 遍历多行数据，递归生成Sheet页
/**
* 由于xls表格页有最大长度的限制（65535），因此每6w条数据分成一页
*
* 说明：startRow(开始行号)必须大于等于currRows(当前sheet页已有数据行数)，否则，
* 条件i + currRows >= MAX_ROWS_LENGTH 一直满足，递归方法进入死循环，导致 StackOverflowError
*
* @param workbook 工作簿
* @param sheetName sheet名称
* @param dataObjList 待处理数据
* @param startRow 开始行号
*/
private static void writeSheetRows(Workbook workbook, String sheetName, List<List<Object>> dataObjList, int startRow) {
String newSheetName = startRow < MAX_ROWS_LENGTH ? sheetName : sheetName + startRow / MAX_ROWS_LENGTH;
Sheet sheet = workbook.getSheet(newSheetName);
if (sheet == null) {
sheet = workbook.createSheet(newSheetName);
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeight((short) (2 * 256));
}
// 当前sheet页已有数据行数
int currRows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < dataObjList.size(); i++) {
if (i + currRows >= MAX_ROWS_LENGTH) {
writeSheetRows(workbook, sheetName,
new ArrayList(dataObjList.subList(i, dataObjList.size())), i + startRow);
break;
}
List<Object> rowObj = dataObjList.get(i);
Row row = sheet.createRow(i + currRows);
ExcelUtil.writeRow(rowObj, row);
}
}
2.2.下载Excel文件
将文件以输出流形式写入response对象
public static void writeResponse(File file, HttpServletResponse response) throws IOException {
OutputStream out = null;
try {
response.reset();
response.setContentType("text/plain;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(file.getName(), "UTF-8"));
long fileLength = file.length();
String length = String.valueOf(fileLength);
response.setHeader("Content_Length", length);
response.setHeader("code", "0");
out = response.getOutputStream();
out.write(FileUtils.readFileToByteArray(file));
out.flush();
} catch (IOException e) {
OpLogUtil.logOpStepException("文件下载，操作结果返回","异常",e);
throw e;
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
OpLogUtil.logOpStepException("文件下载", "下载异常", e);
throw e;
}
}
}
}
3.END
发布此文，以便学习、记录并回顾项目实战中使用到的新技能或技术知识，如：JDK1.8新特性Function、Consumer、obj.getClass()反射，谨以此文，仅供参考！
博主开发过程中遇到的问题：
海量数据导出excel时，使用XSSFWorkbook可能会抛出nested exception is java.lang.OutOfMemoryError（内存溢出）异常，解决办法：使用流式版XSSFWorkbook(SXSSFWorkbook)
参考文献链接：
https://zhuanlan.zhihu.com/p/367119379
https://www.cnblogs.com/qlqwjy/p/10188076.html
本文来自博客园，作者：冰枫丶，转载请注明原文链接：https://www.cnblogs.com/lqsblog/p/15045436.html
