Poi实现Excel导出
Appache Poi提供了HSSFWorkbook操作2003版本的Excel文件, XSSFWorkbook操作2007版Excel文件.
简单的具体实现在网上有很多案例可以参考学习, 我就不写入门案例了, 下面我会将自己工作中用到的场景封装成工具类记录下来, 供以后翻看复习.
说明: 以下代码基于poi-3.17版本实现, poi-3.17及以上版本相比3.17以下版本样式设置的api改动比较大, 可能存在样式设置报错等, 请参考poi版本升级问题优化
1. 导出Excel文件
Excel文件导出操作在我们工作中有很多场景都会用到. 如报表数据下载, 消费记录下载等…
下面针对不同场景, 封装了不同的工具类, 但底层都是用的基础的公共api, 我使用的是HSSFWorkbook, 如果要生成2007版的Excel文件, 只需将HSSFWorkbook替换成XSSFWorkbook即可.
1.1 场景1
生成一个excel文件，一个sheet表,不压缩
com.poi.util.PoiUtil#exportSheetOneExcel
/**
* 生成一个excel文件，一个sheet表,不压缩
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象
* @throws MyException
*/
public static void exportSheetOneExcel(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap) throws MyException {
PoiUtil.exportDataToExcel(resultList, titleMap, dataMap, 0, 0, false, false, false);
}
1.2 场景2
生成一个excel文件，一个sheet表，压缩
com.poi.util.PoiUtil#exportSheetOneExcelZip
/**
* 生成一个excel文件，一个sheet表, 压缩
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象
* @throws MyException
*/
public static void exportSheetOneExcelZip(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap) throws MyException {
PoiUtil.exportDataToExcel(resultList, titleMap, dataMap, 0, 0, false, false, true);
}
1.3 场景3
生成一个excel文件，多个sheet表,不压缩
com.poi.util.PoiUtil#exportSheetsOneExcel
/**
* 生成一个excel文件，多个sheet表,不压缩
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象
* @param dataNumPerSheet 生成多个sheet表格时,每个sheet表格dataNumPerSheet条数据记录
* @throws MyException
*/
public static void exportSheetsOneExcel(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap,
int dataNumPerSheet) throws MyException {
PoiUtil.exportDataToExcel(resultList, titleMap, dataMap, 0, dataNumPerSheet,
false, true, false);
}
1.4 场景4
生成一个excel文件，多个sheet表, 压缩
com.poi.util.PoiUtil#exportSheetsOneExcelZip
/**
* 生成一个excel文件，多个sheet表, 压缩
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象
* @param dataNumPerSheet 生成多个sheet表格时,每个sheet表格dataNumPerSheet条数据记录
* @throws MyException
*/
public static void exportSheetsOneExcelZip(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap,
int dataNumPerSheet) throws MyException {
PoiUtil.exportDataToExcel(resultList, titleMap, dataMap, 0, dataNumPerSheet,
false, true, true);
}
1.5 场景5
生成多个excel文件，一个sheet表，不压缩 ; 因为生成的是多个excel文件,所以无法将多个文件名返回给前端下载, 只能存在临时下载目录.
com.poi.util.PoiUtil#exportSheetExcels
/**
* 生成多个excel文件，一个sheet表，不压缩 ; 因为生成的是多个excel文件,所以无法将多个文件名返回给前
* 端下载, 只能存在临时下载目录
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataNumPerExcel 生成多个excel文件时,每个excel文件dataNumPerExcel条数据记录
* @throws MyException
*/
public static void exportSheetExcels(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
int dataNumPerExcel) throws IOException {
logger.info("+++++++++++++++++开始获取数据行数据");
List<List<String>> contentList = ExportDataToExcelService.getDetailList(resultList,
titleMap);
logger.info("+++++++++++++++++开始获取标题行数据");
List<String> titleList = ExportDataToExcelService.getTitleList(titleMap);
ExportDataToExcelService.toWriteBatchExcelByOneSheet(titleList, contentList,
dataNumPerExcel);
}
1.6 场景6
生成多个excel文件，一个sheet表，压缩 数据量大时,建议用该版本.
/**
* 生成多个excel文件，一个sheet表，压缩 数据量大时,建议用该版本
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象
* @param dataNumPerExcel 生成多个excel文件时,每个excel文件dataNumPerExcel条数据记录
* @throws MyException
*/
public static void exportSheetExcelsZip(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap,
int dataNumPerExcel) throws MyException {
PoiUtil.exportDataToExcel(resultList, titleMap, dataMap, dataNumPerExcel, 0,
true, false, true);
}
1.7 场景7
生成多个excel文件，多个sheet表，不压缩; 因为生成的是多个excel文件,所以无法将多个文件名返回给前端下载, 只能存在临时下载目录.
com.poi.util.PoiUtil#exportSheestExcels
/**
* 生成多个excel文件，多个sheet表，不压缩; 因为生成的是多个excel文件,所以无法将多个文件名返回给前端
* 下载, 只能存在临时下载目录
* 既然可以生成多个excel并压缩，就没有必要分多个sheet表了，不建议使用
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataNumPerExcel 生成多个excel文件时,每个excel文件dataNumPerExcel条数据记录
* @param dataNumPerSheet 生成多个sheet表格时,每个sheet表格dataNumPerSheet条数据记录
* @throws MyException
*/
public static void exportSheestExcels(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
int dataNumPerExcel,
int dataNumPerSheet) throws IOException {
logger.info("+++++++++++++++++开始获取数据行数据");
List<List<String>> contentList = ExportDataToExcelService.getDetailList(resultList,
titleMap);
logger.info("+++++++++++++++++开始获取标题行数据");
List<String> titleList = ExportDataToExcelService.getTitleList(titleMap);
ExportDataToExcelService.toWriteBatchExcelByBatchSheet(titleList, contentList, dataNumPerSheet,dataNumPerExcel);
}
1.8 场景8
生成多个excel文件，多个sheet表，压缩. 没必要每个excel文件分多个sheet表 , 意义不大.
com.poi.util.PoiUtil#exportSheestExcelsZip
/**
* 生成多个excel文件，多个sheet表，压缩
* 既然可以生成多个excel并压缩，就没有必要分多个sheet表了，不建议使用
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象
* @param dataNumPerExcel 生成多个excel文件时,每个excel文件dataNumPerExcel条数据记录
* @param dataNumPerSheet 生成多个sheet表格时,每个sheet表格dataNumPerSheet条数据记录
* @throws MyException
*/
public static void exportSheestExcelsZip(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap,
int dataNumPerExcel,
int dataNumPerSheet) throws MyException {
PoiUtil.exportDataToExcel(resultList, titleMap, dataMap, dataNumPerExcel,
dataNumPerSheet, true, true, true);
}
2. 公共方法
2.1 下载EXCEL文件
根据上面不同的场景, 公共方法的参数不同, 导出的文件形式不同.
com.poi.util.PoiUtil#exportDataToExcel
/**
* 在服务器上生成EXCEL文件, 然后下载
*
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象
* @param dataNumPerExcel 生成多个excel文件时,每个excel文件dataNumPerExcel条数据记录
* @param dataNumPerSheet 生成多个sheet表格时,每个sheet表格dataNumPerSheet条数据记录
* @param batchSheetFlag false:一个excel生成一个sheet表格;
* true: 一个excel生成多个sheet表格
* @param batchExcelFlag false:生成一个excel文件, true:生成多个excel文件
* @param zipFlag false: 不压缩zip文件 ;true:压缩zip文件
*/
public static void exportDataToExcel(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap,
int dataNumPerExcel, int dataNumPerSheet, boolean batchExcelFlag,
boolean batchSheetFlag, boolean zipFlag) throws MyException {
if (MapUtils.isEmpty(titleMap)) {
throw new MyException(CSISERRORCODE.TITLE_DATA_EMPTY_ERROR_CODE,
CSISERRORCODE.TITLE_DATA_EMPTY_ERROR_INFO);
}
ExportDataToExcelService.createAndWriteExcelFile(resultList, titleMap,
dataMap, dataNumPerExcel, dataNumPerSheet, batchExcelFlag,
batchSheetFlag, zipFlag);
}
com.poi.service.ExportDataToExcelService#createAndWriteExcelFile
/**
* 在服务器上生成EXCEL文件, 然后下载
* @param resultList 数据行的查询结果集
* @param titleMap 封装标题行的数据集合
* @param dataMap 上下文根的数据对象 任何要用到的变量可以存入这个里面传入使用
* @param dataNumPerExcel 生成多个excel文件时,每个excel文件dataNumPerExcel条数据记录
* @param dataNumPerSheet 生成多个sheet表格时,每个sheet表格dataNumPerSheet条数据记录
* @param batchExcelFlag false:生成一个excel文件, true:生成多个excel文件
* @param batchSheetFlag false:一个excel生成一个sheet表格;
* true: 一个excel生成多个sheet表格
* @param zipFlag false: 不压缩zip文件 ;true:压缩zip文件
*/
public static void createAndWriteExcelFile(List<HashMap<String, Object>> resultList,
LinkedHashMap<String, Object> titleMap,
Map<String, Object> dataMap,
int dataNumPerExcel, int dataNumPerSheet,
boolean batchExcelFlag,
boolean batchSheetFlag,
boolean zipFlag) {
//下面导出功能实现方式是在服务器上生成EXCEL文件, 然后下载
logger.info("+++++++++++++++++开始获取数据行数据");
List<List<String>> detailList = ExportDataToExcelService.getDetailList(resultList,
titleMap);
logger.info("+++++++++++++++++开始获取标题行数据");
List<String> titleList = ExportDataToExcelService.getTitleList(titleMap);
/**
* 获取要下载的文件名全路径
* 线程安全问题:
* 第一次获取下载文件名先删除服务器上原来的历史文件,节省服务器内存, 但需要考虑高并发场景,
* 互相被删除后导致无法下载, 因为服务器临时下载目录是共享的
* 解决方案:
* 可以在服务器临时下载主目录下面生成多级子目录(日期目录/用户ID+yyyyMMddHHmmssSSS目录),
* 每次获取下载文件路径时先不删除服务器历史临时文件,
* 可以设置定时(每10分钟)去删除当天日期目录下的用户ID+yyyyMMddHHmmssSSS子目录, 这样即使
* 是高并发多线程下载也不会删除其他人的临时文件,
* 因为下载的临时文件目录名称精确到用户ID+毫秒级, 同一个用户除非练就了超人的点击速度,否则不
* 会影响.
*/
String writeFileName = ExportDataToExcelService.getWriteFilePath(true, dataMap);
// 创建excel文件
ExportDataToExcelService.writeExcel(dataMap, writeFileName, titleList, detailList, dataNumPerExcel, dataNumPerSheet, batchExcelFlag, batchSheetFlag, zipFlag);
}
2.2 封装数据行
com.poi.service.ExportDataToExcelService#getDetailList
/**
* 组装数据行数据
*
* @param resultList
* @param titleMap 标题数据 key-value形式, key是标题英文字段名，value是标题中文名称
* @return
*/
public static List<List<String>> getDetailList(List<HashMap<String, Object>> resultList, LinkedHashMap<String, Object> titleMap) {
List<List<String>> detailList = new ArrayList<List<String>>();
for (HashMap<String, Object> result : resultList) {
List<String> infoList = new ArrayList<String>();
Set<String> titleKeys = titleMap.keySet();
for (String titleKey : titleKeys) {
infoList.add(String.valueOf(result.get(titleKey)));
}
detailList.add(infoList);
}
return detailList;
}
2.3 封装标题行
com.poi.service.ExportDataToExcelService#getTitleList
/**
* 组装标题行
*
* @param titleMap 标题数据 key-value形式, key是标题英文字段名，value是标题中文名称
* @return 返回excel表头，标题行展示title对应的字段中文名称
* 注意：此处的titleMap一定要是LinkedHashMap类型，保证添加的key顺序不变, 不让map自动按照key
* 进行排序，因为标题的顺序要按照我们自己添加的顺序生成
*/
public static List<String> getTitleList(LinkedHashMap<String, Object> titleMap) {
List<String> titleList = new ArrayList<String>();
Set<String> titleKeys = titleMap.keySet();
for (String titleKey : titleKeys) {
titleList.add((String) titleMap.get(titleKey));
}
return titleList;
}
2.4 获取文件下载路径
com.poi.service.ExportDataToExcelService#getWriteFilePath
/**
* 获取文件的完整路径
*
* @param deleteFlag true：删除指定目录下的所有文件; false:不删除
* @return
*/
public static String getWriteFilePath(boolean deleteFlag, Map<String, Object> dataMap) {
// .xls , .xlsx
String fileType = (String) dataMap.get(CSISCONSTANT.EXCEL_FILE_TYPE);
fileType = StringUtils.isEmpty(fileType) ? CSISCONSTANT.EXCEL07_EXTENSION :
fileType; // 没指定文件类型默认xlsx
String dirPath = parentPath;
if (deleteFlag) {
/**
* 高并发场景会存在问题，如果多个请求同时进入，会互相删除对方写到服务器的文件，导致无法
* 下载
* 导致问题的原因：
* 1. 存放临时文件的服务器下载目录parentPath是共享的
* 2。高并发的多线程环境
* 解决思路：
* 1. 存放临时文件的服务器下载目录在共享的parentPath下面新增子级目录，子级目录要唯一；可以
* 是当前时间(精确到ms)+用户id ,例如： yyyyMMdd/yyyyMMddHHmmssSSS+userId
* 2. 用户下载请求在获取下载文件路径时暂不删除临时文件，上面第1点新增了2级子目录，可以延时扫
* 描 parentPath/yyyyMMdd/目录下的子目录，删除已经下载完成的临时文件及临时文件夹
*/
FileUtil.deleteAllFile(dirPath);
}
// 获取文件名
String fileName = "panda" + TimeDayTransferUtil.formatDateTime(new Date(),
CSISCONSTANT.FORMATYYYYMMDDHHMMSS) +
DataHandleUtil.getRandomNumber(8) +
fileType;
return parentPath + fileName;
}
2.5 删除下载目录临时文件
com.util.FileUtil#deleteAllFile
/**
* 删除指定目录下的所有文件
* @param path
*/
public static void deleteAllFile(String path) {
File file = new File(path);
if (!file.exists()) {
file.mkdirs();
}
// 获取该目录下的所有文件,包含子目录
String[] fileArray = file.list();
for (int i = 0; i < fileArray.length; i++) {
// 得到每一个文件, 可能是目录或文件
String tempFileName = path + fileArray[i];
File tempFile = new File(tempFileName);
// 如果是文件就直接删除
if (tempFile.isFile()) {
tempFile.delete();
}
// 如果是目录, 先删除子文件,再删除空目录
if (tempFile.isDirectory()) {
deleteAllFile(tempFileName + "/");
tempFile.delete();
}
}
}
2.6 生成excel公共方法
com.poi.service.ExportDataToExcelService#writeExcel
/**
* 生成excel文件
* 在拦截器的响应方法中拦截FILE_DOWNLOAD_NAME，将生成的excel文件写入响应流，然后前端异步去服
* 务器上下载文件
*
* @param dataMap 上下文根的数据对象
* @param writeFileName
* @param titleList 标题行的数据集合
* @param contentList 数据行的查询结果集
* @param dataNumPerExcel 生成多个excel文件时,每个excel文件dataNumPerExcel条数据记录
* @param dataNumPerSheet 生成多个sheet表格时,每个sheet表格dataNumPerSheet条数据记录
* @param batchExcelFlag false:生成一个excel文件, true:生成多个excel文件
* @param batchSheetFlag false:一个excel生成一个sheet表格;
* true: 一个excel生成多个sheet表格
* @param zipFlag false: 不压缩zip文件 ;true:压缩zip文件
*/
public static void writeExcel(Map<String, Object> dataMap, String writeFileName,
List<String> titleList,
List<List<String>> contentList,
int dataNumPerExcel, int dataNumPerSheet,
boolean batchExcelFlag, boolean batchSheetFlag,
boolean zipFlag) {
FileOutputStream out = null;
// .xls , .xlsx
String fileType = (String) dataMap.get(CSISCONSTANT.EXCEL_FILE_TYPE);
fileType = StringUtils.isEmpty(fileType) ? CSISCONSTANT.EXCEL07_EXTENSION :
fileType; // 没指定文件类型默认xlsx
long temp = System.currentTimeMillis();
try {
// 生成多个Excel文件, 每num条数据生成一个excel文件,无法同时返回多个excel文件给前端,必须将
// 多个excel文件压缩成一个zip文件返回给前端
if (batchExcelFlag) {
// 将多个excel文件压缩到zip文件
String zipFileName = ExportDataToExcelService.getZipFilePath();
if (batchSheetFlag) {
// 多个excel多个sheet, 因为是生成多个excel文件, 所以文件名后面会重新生成
toWriteBatchExcelByBatchSheet(titleList, contentList, dataNumPerSheet,
dataNumPerExcel);
} else {
// 多个excel一个sheet, 因为是生成多个excel文件, 所以文件名后面会重新生成
toWriteBatchExcelByOneSheet(titleList, contentList, dataNumPerExcel);
}
logger.info("+++++++++++组装EXCEL耗时：" + (System.currentTimeMillis() -
temp));
long temp2 = System.currentTimeMillis();
// 在循环完之后一起用一个zip流压缩到一个zipFile里面
// 取与writeFileName 同级目录下的所有excel文件,一起进行压缩到zip文件 -->> 因为前端界
// 面只能一次下载一个文件,所以需要将生成的多个excel文件压缩成一个文件
ExportDataToExcelService.writeZipExcel(new File(writeFileName).getParent(),
zipFileName);
// FILE_DOWNLOAD_NAME 用于拦截器的响应方法中将服务器的文件写入响应流中提供给前端界面读
// 取下载
dataMap.put(CSISCONSTANT.FILE_DOWNLOAD_NAME, new File(zipFileName));
logger.info("+++++++++++压缩EXCEL耗时：" + (System.currentTimeMillis() -
temp2));
} else {
// 生成一个excel
out = new FileOutputStream(writeFileName);
if (batchSheetFlag) {
// 生成一个excel多个sheet
toWritePerExcelByBatchSheet(out, titleList, contentList,
dataNumPerSheet, fileType);
} else {
// 生成一个excel一个sheet
toWritePerExcelByOneSheet(out, titleList, contentList, fileType);
}
// 生成一个excel文件可以选择性的压缩
if (zipFlag) {
// 一个excel也压缩
String zipFileName = ExportDataToExcelService.getZipFilePath();
ExportDataToExcelService.writeZipExcel(
new File(writeFileName).getParent(), zipFileName);
dataMap.put(CSISCONSTANT.FILE_DOWNLOAD_NAME, new File(zipFileName));
} else {
// 不压缩, 直接返回excel文件给前端
dataMap.put(CSISCONSTANT.FILE_DOWNLOAD_NAME, new File(writeFileName));
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
2.7 生成多个excel文件多个sheet表
com.poi.service.ExportDataToExcelService#toWriteBatchExcelByBatchSheet
/**
* 组装excel文件，生成多个excel文件多个sheet表
*
* @param titleList 标题行
* @param contentList 数据行-单元格
* @param dataNumPerSheet 每个sheet表的数据量
* @param dataNumPerExcel 每个excel文件的数据量
* @throws IOException
*/
public static void toWriteBatchExcelByBatchSheet(List<String> titleList,
List<List<String>> contentList,
int dataNumPerSheet,
int dataNumPerExcel) throws IOException {
FileOutputStream out = null;
// 生成excel的个数
int loopNum = contentList.size() / dataNumPerExcel;
int remainder = contentList.size() % dataNumPerExcel; // 余数
for (int i = 0; i < loopNum; i++) {
String writeFileName = getBatchFilePath();
out = new FileOutputStream(writeFileName);
createWorkbookByBatchSheet(out, titleList, contentList.subList(
i * dataNumPerExcel, (i + 1) * dataNumPerExcel), dataNumPerSheet);
}
if (remainder != 0) {
String writeFileName = getBatchFilePath();
out = new FileOutputStream(writeFileName);
createWorkbookByBatchSheet(out, titleList, contentList.subList(
loopNum * dataNumPerExcel,
loopNum * dataNumPerExcel + remainder), dataNumPerSheet);
}
}
2.8 生成一个excel文件多个sheet表
com.poi.service.ExportDataToExcelService#createWorkbookByBatchSheet
/**
* 生成一个excel文件多个sheet表
*
* @param out 输出流
* @param titleList 标题行
* @param contentList 数据行-单元格
* @param dataNumPerSheet 每个sheet表的数据量
* @throws IOException
*/
private static void createWorkbookByBatchSheet(FileOutputStream out,
List<String> titleList,
List<List<String>> contentList,
int dataNumPerSheet) throws IOException {
// 多个sheet表
// 创建一个工作簿-->>1个excel
HSSFWorkbook wb = new HSSFWorkbook(); // 创建工作簿
// 生成sheet表的个数
int loopNum = contentList.size() / dataNumPerSheet;
int remainder = contentList.size() % dataNumPerSheet; // 余数
for (int i = 0; i < loopNum; i++) {
// 第二步 创建sheet
HSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
// 创建标题行
createHeadRow(wb, sheet, titleList);
// 创建数据行
createDataRow(wb, sheet, contentList.subList(i * dataNumPerSheet,
(i + 1) * dataNumPerSheet));
}
if (remainder != 0) {
// 第二步 创建sheet
HSSFSheet sheet = wb.createSheet("sheet" + (loopNum + 1));
// 创建标题行
createHeadRow(wb, sheet, titleList);
// 创建数据行
createDataRow(wb, sheet,
contentList.subList(loopNum * dataNumPerSheet,
loopNum * dataNumPerSheet + remainder));
}
// 把相应的excel工作簿存盘
wb.write(out);
out.flush(); // 刷新
if (null != out) {
out.close();
}
}
2.9 生成多个excel文件一个sheet表
com.poi.service.ExportDataToExcelService#toWriteBatchExcelByOneSheet
/**
* 组装excel文件，生成多个excel文件一个sheet表
*
* @param titleList 标题行
* @param contentList 数据行-单元格
* @param dataNumPerExcel 一个excel文件的数据量
* @throws IOException
*/
public static void toWriteBatchExcelByOneSheet(List<String> titleList,
List<List<String>> contentList,
int dataNumPerExcel) throws IOException {
FileOutputStream out = null;
// 生成excel的个数
int loopNum = contentList.size() / dataNumPerExcel;
int remainder = contentList.size() % dataNumPerExcel; // 余数
for (int i = 0; i < loopNum; i++) {
String srcFileName = getBatchFilePath();
out = new FileOutputStream(srcFileName);
createWorkbookByOneSheet(out, titleList,
contentList.subList(i * dataNumPerExcel,
(i + 1) * dataNumPerExcel));
}
if (remainder != 0) {
String srcFileName = getBatchFilePath();
out = new FileOutputStream(srcFileName);
createWorkbookByOneSheet(out, titleList,
contentList.subList(loopNum * dataNumPerExcel,
loopNum * dataNumPerExcel + remainder));
}
}
2.10 生成一个excel文件一个sheet表
com.poi.service.ExportDataToExcelService#createWorkbookByOneSheet
/**
* 生成一个excel文件一个sheet表
* 2003版本 {@link HSSFWorkbook}
* 2007版本 {@link org.apache.poi.xssf.usermodel.XSSFWorkbook}
* 2003版本只支持0-65535行记录写入, 2007版本支持104万多行写入, 2003版本会比2007版本写入性能快一
* 些, 但2007版本是将所有数据加载到内存后一次写入到文件,容易内存溢出.
* poi提供了基于磁盘操作的{@link org.apache.poi.xssf.streaming.SXSSFWorkbook}, 可以指定一次
* 写入的数量(默认100条),会在磁盘中产生一个临时文件
*
* @param out 输出流
* @param titleList 标题行
* @param contentList 数据行-单元格
* @throws IOException
*/
private static void createWorkbookByOneSheet(FileOutputStream out,
List<String> titleList,
List<List<String>> contentList) throws IOException {
// 创建一个工作簿-->>1个excel
HSSFWorkbook wb = new HSSFWorkbook(); // 创建2003版工作簿
// 第二步 创建sheet
HSSFSheet sheet = wb.createSheet();
// 创建标题行
createHeadRow(wb, sheet, titleList);
// 创建数据行
createDataRow(wb, sheet, contentList);
// 把相应的excel工作簿存盘
wb.write(out);
out.flush(); // 刷新
if (null != out) {
out.close();
}
}
2.11 生成一个excel文件多个sheet表
com.poi.service.ExportDataToExcelService#toWritePerExcelByBatchSheet
/**
* 组装excel文件, 生成一个excel文件多个sheet表
*
* @param out
* @param titleList 标题行
* @param contentList 单元格数据
* @param dataNumPerSheet 每个sheet表的数据量
* @throws IOException
*/
private static void toWritePerExcelByBatchSheet(FileOutputStream out,
List<String> titleList,
List<List<String>> contentList,
int dataNumPerSheet, String fileType) throws IOException {
// 第一步 创建Workbook
Workbook wb = getWorkbook(fileType); // 根据fileType获取HSSF或XSSF工作簿
// 生成sheet表的个数
int loopNum = contentList.size() / dataNumPerSheet;
int remainder = contentList.size() % dataNumPerSheet; // 余数
for (int i = 0; i < loopNum; i++) {
// 第二步 创建sheet
Sheet sheet = wb.createSheet("sheet" + (i + 1));
// 创建标题行
createHeadRow(wb, sheet, titleList);
// 创建数据行
createDataRow(wb, sheet, contentList.subList(i * dataNumPerSheet,
(i + 1) * dataNumPerSheet));
}
if (remainder != 0) {
// 第二步 创建sheet
Sheet sheet = wb.createSheet("sheet" + (loopNum + 1));
// 创建标题行
createHeadRow(wb, sheet, titleList);
// 创建数据行
createDataRow(wb, sheet,
contentList.subList(loopNum * dataNumPerSheet,
loopNum * dataNumPerSheet + remainder));
}
// 把相应的excel工作簿存盘
wb.write(out);
out.flush(); // 刷新
if (null != out) {
out.close();
}
}
2.12 生成一个excel文件一个sheet表
com.poi.service.ExportDataToExcelService#toWritePerExcelByOneSheet
/**
* 组装Excel文件, 生成一个excel文件一个sheet表
* 该方法下载的excel文件基于2003版excel的HSSFWorkbook生成
*
* @param out
* @param titleList 标题行
* @param contentList 数据行
* @throws IOException
*/
private static void toWritePerExcelByOneSheet(FileOutputStream out,
List<String> titleList,
List<List<String>> contentList, String fileType) throws IOException {
Workbook wb = getWorkbook(fileType); // 根据fileType获取HSSF或XSSF工作簿
Sheet sheet = wb.createSheet(); // 创建一个sheet表
createHeadRow(wb, sheet, titleList); // 创建标题行
createDataRow(wb, sheet, contentList); // 创建数据行(单元格)
wb.write(out); // 把相应的excel在工作簿存盘
out.flush(); // 刷新
if (null != out) {
out.close();
}
}
2.13 创建HSSF或XSSF工作簿
com.poi.service.ExportDataToExcelService#getWorkbook
/**
* 根据要生成的文件类型创建HSSF或者XSSF工作簿
* @param fileType .xls .xlsx
* @return
*/
public static Workbook getWorkbook(String fileType) {
Workbook wb = null;
switch (fileType) {
case CSISCONSTANT.EXCEL03_EXTENSION:
wb = new HSSFWorkbook(); // 创建工作簿 2003版excel
break;
case CSISCONSTANT.EXCEL07_EXTENSION:
default:
wb = new XSSFWorkbook(); // 创建工作簿 2007版excel
break;
}
return wb;
}
2.14 创建标题行
com.poi.service.ExportDataToExcelService#createHeadRow
/**
* 创建标题行
*
* @param wb
* @param sheet
* @param titleList
*/
private static void createHeadRow(Workbook wb, Sheet sheet, List<String> titleList) {
//标题行字体
Font font = createFonts(wb, true, "宋体", false, (short) 200);
// 获取单元格样式
CellStyle cellStyle = getHeadCellStyle(wb, font);
// 设置边框
cellStyle.setBorderLeft(BorderStyle.THIN);// 设置左边框
cellStyle.setBorderRight(BorderStyle.THIN); // 设置右边框
cellStyle.setBorderBottom(BorderStyle.THIN); // 设置下边框
cellStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.index);// 设置填充颜色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充样式
DataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@")); // 把导出的excel表格内容设置成文本
// 创建标题行
Row titleRow = sheet.createRow(0);
// 列序号
int cellCount = 0;
// 循环创建标题行的单元格
for (String title : titleList) {
//设置列宽度
if (StringUtils.isEmpty(title)) {
sheet.setColumnWidth(cellCount, 30 * 150);
} else {
title = title.trim(); // 去空处理
sheet.setColumnWidth(cellCount, 35 * 256 + 200);
}
// 创建单元格
createCell(wb, titleRow, cellCount, title, cellStyle);
cellCount++;
}
}
2.15 创建数据行
com.poi.service.ExportDataToExcelService#createDataRow
/**
* 创建数据行
*
* @param wb
* @param sheet
* @param contentList
*/
private static void createDataRow(Workbook wb, Sheet sheet,
List<List<String>> contentList) {
// 行数
int rowNum = contentList.size();
// 列数 取第一行的size
int cellNum = contentList.get(0).size();
for (int i = 0; i < rowNum; i++) {
// 创建数据行
Row dataRow = sheet.createRow(i + 1); // 第一行是标题行，所以要+1
for (int j = 0; j < cellNum; j++) {
// 创建单元格填充数据
Cell cell = dataRow.createCell(j);
cell.setCellValue(contentList.get(i).get(j));
}
}
}
2.16 创建单元格
com.poi.service.ExportDataToExcelService#createCell
/**
* 创建单元格并设置单元格样式, 值
*
* @param wb
* @param row
* @param column
* @param value
* @param cellStyle
*/
private static void createCell(Workbook wb, Row row, int column, String value,
CellStyle cellStyle) {
Cell cell = row.createCell(column);
cell.setCellValue(value);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
}
2.17 压缩文件公共方法
com.poi.service.ExportDataToExcelService#writeZipExcel
/**
* 将源文件或源文件夹下的所有文件压缩写入目标文件：zip文件
*
* @param srcFile 源文件或源文件夹
* @param zipFileName 目标压缩文件
*/
public static void writeZipExcel(String srcFile, String zipFileName) {
logger.info("srcFile to zip is:" + srcFile);
ZipOutputStream zipOut = null;
try {
File zipFile = new File(zipFileName);
// 判断zip文件是否存在,不存在就创建
if (!zipFile.exists()) {
zipFile.createNewFile();
}
// zip输出流封装了压缩到的目标文件 zipFile
zipOut = new ZipOutputStream(new FileOutputStream(zipFile));
doCompress(new File(srcFile), zipOut); // 开始压缩文件
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (zipOut != null) {
try {
zipOut.close(); // 所有文件压缩完成后在这里关闭压缩输出流
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
com.poi.service.ExportDataToExcelService#doCompress
/**
* 将srcFile文件或文件夹下的所有文件压缩到zipFile文件
*
* @param srcFile 源文件或文件夹
* @param zipOut zip压缩输出流,已经封装了压缩文件
* @throws IOException
*/
private static void doCompress(File srcFile,
ZipOutputStream zipOut) throws IOException {
if (srcFile.isDirectory()) {
File[] files = srcFile.listFiles();
for (File file : files) {
if (file != null && (file.getName().endsWith(CSISCONSTANT.EXCEL03_EXTENSION)
|| file.getName().endsWith(
CSISCONSTANT.EXCEL07_EXTENSION))) {
// 压缩文件, 循环将srcFile文件夹下面的所有文件压缩完成后, 在外层关闭流资源,
// 不然后面的文件压缩不进去
doCompressToZip(file, zipOut);
}
}
} else {
// 是文件,直接压缩
doCompressToZip(srcFile, zipOut);
}
}
com.poi.service.ExportDataToExcelService#doCompressToZip
/**
* @param file 要压缩的源文件
* @param zipOut 压缩文件的输出流,已经封装了压缩的目标文件zipFile
* @throws IOException
*/
private static void doCompressToZip(File file,
ZipOutputStream zipOut) throws IOException {
if (file.exists()) {
logger.info("file to zip is:" + file.getName());
byte[] buffer = new byte[1024];
FileInputStream fis = new FileInputStream(file);
zipOut.putNextEntry(new ZipEntry(file.getName()));
// 读取->压缩
int len = 0;
while ((len = fis.read(buffer)) > 0) {
zipOut.write(buffer, 0, buffer.length);
}
zipOut.closeEntry();
// 在循环调用压缩完成之后在外层关闭流,不然后面的文件压缩不进去
// zipOut.close();
fis.close();
}
}
3. 测试导出Excel文件
3.1 测试代码
com.test.poi.PoiExcelTest#testExportExcelByPoi
/**
* 测试下载excel
* 一般是从db查询大数据后，将数据写入excel文件，提供给用户下载
*/
@Test
public void testExportExcelByPoi() throws MyException, IOException {
// 标题行
LinkedHashMap<String, Object> titleMap = new LinkedHashMap<>();
titleMap.put("ROWID", "序号");
titleMap.put("name", "姓名");
titleMap.put("age", "年龄");
titleMap.put("gender", "性别");
// 数据行
List<HashMap<String, Object>> resultList =
new ArrayList<HashMap<String, Object>>();
/**
* 模拟数据行数
* 65535 2003版本的xls一次最大只能写入65535行记录，耗时 1817ms 1926ms
*/
long start = System.currentTimeMillis();
int rows = 20;
for (int i = 0; i < rows; i++) {
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("ROWID", i + 1);
map.put("name", "admin" + i);
map.put("age", 30);
map.put("gender", i % 2 == 0 ? "男" : "女");
resultList.add(map);
}
// resultList.clear();
Map<String, Object> dataMap = new HashMap<String, Object>();
//dataMap.put(CSISCONSTANT.EXCEL_FILE_TYPE, CSISCONSTANT.EXCEL07_EXTENSION);
dataMap.put(CSISCONSTANT.EXCEL_FILE_TYPE, CSISCONSTANT.EXCEL03_EXTENSION);
// 导出数据到excel文件
// 生成一个excel文件，一个sheet表,不压缩
PoiUtil.exportSheetOneExcel(resultList, titleMap, dataMap);
// 生成一个excel文件，一个sheet表，压缩
//PoiUtil.exportSheetOneExcelZip(resultList, titleMap, dataMap);
// 生成一个excel文件，多个sheet表，不压缩
// PoiUtil.exportSheetsOneExcel(resultList, titleMap, dataMap, 5);
// 生成一个excel文件，多个sheet表，压缩
// PoiUtil.exportSheetsOneExcelZip(resultList, titleMap, dataMap, 5);
// 生成多个excel文件，一个sheet表，不压缩
// PoiUtil.exportSheetExcels(resultList, titleMap, 10);
// 生成多个excel文件，一个sheet表，压缩 数据量大时,建议用该版本 ********
// PoiUtil.exportSheetExcelsZip(resultList, titleMap, dataMap, 10);
// 生成多个excel文件，多个sheet表，不压缩
// PoiUtil.exportSheestExcels(resultList, titleMap, 10, 5);
// 生成多个excel文件，多个sheet表，压缩 既然可以生成多个excel并压缩，就没有必要分多个sheet表
// 了，不建议使用
// PoiUtil.exportSheestExcelsZip(resultList, titleMap, dataMap, 10, 5);
long end = System.currentTimeMillis();
System.out.println("总耗时：" + (end - start) + "ms");
logger.info("dataMap:" + dataMap);
}
3.2 测试结果
生成文件panda2021091721524417685259.xls
4. Web端测试
4.1 编写controller
package com.poi.controller;
import com.constant.CSISCONSTANT;
import com.exception.MyException;
import com.poi.entity.Employee;
import com.poi.service.ExcelReaderImpl;
import com.poi.util.PoiUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
/**
* 类描述：文件上传下载
* @Author wang_qz
* @Date 2021/8/14 21:07
* @Version 1.0
*/
@Controller
@RequestMapping("/excel")
public class ExcelController {
@RequestMapping("/toExcelPage2")
public String todownloadPage2() {
return "excelPage2";
}
/**
* 经过服务器临时下载目录中转的实现
* @param response
* @throws MyException
* @throws IOException
*/
@RequestMapping("/downloadExcel2")
public void downloadExcel2(HttpServletResponse response)
throws MyException, IOException {
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 设置防止中文名乱码
String filename = URLEncoder.encode("用户信息", "utf-8");
// 文件下载方式(附件下载还是在当前浏览器打开)
response.setHeader("Content-disposition", "attachment;filename="
+ filename + CSISCONSTANT.EXCEL03_EXTENSION);
// 封装标题行
LinkedHashMap<String, Object> titleMap = new LinkedHashMap<>();
titleMap.put("ROWID", "序号");
titleMap.put("name", "姓名");
titleMap.put("age", "年龄");
titleMap.put("gender", "性别");
// 封装数据行
List<HashMap<String, Object>> resultList =
new ArrayList<HashMap<String, Object>>();
for (int i = 0; i < 20; i++) {
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("ROWID", i + 1);
map.put("name", "admin" + i);
map.put("age", 30);
map.put("gender", i % 2 == 0 ? "男" : "女");
resultList.add(map);
}
Map<String, Object> dataMap = new HashMap<String, Object>();
dataMap.put(CSISCONSTANT.EXCEL_FILE_TYPE, CSISCONSTANT.EXCEL03_EXTENSION);
// 生成一个excel文件，一个sheet表,不压缩
PoiUtil.exportSheetOneExcel(resultList, titleMap, dataMap);
File downloadFile = (File) dataMap.get(CSISCONSTANT.FILE_DOWNLOAD_NAME);
// 因为将文件存在了服务器的临时下载目录,所以需要读取服务器上的文件写入响应流中
FileInputStream read = new FileInputStream(downloadFile);
ServletOutputStream out = response.getOutputStream();
byte[] bys = new byte[1024];
while (read.read(bys) != -1) {
out.write(bys, 0, bys.length);
out.flush();
}
out.close();
}
}
4.2 编写jsp页面
webapp/WEB-INF/jsp/excelPage2.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>测试excel文件下载</title>
</head>
<body>
<h3>点击下面链接, 进行excel文件下载</h3>
<a href="<c:url value='/excel/downloadExcel2'/>">Excel文件下载</a>
<hr/>
<hr/>
</body>
</html>
启动tomcat, 访问http://localhost:8080/excel/toExcelPage2, 进入测试页面
4.3 测试结果
下载文件panda2021091722001941479157.xls
相关推荐
个人博客
欢迎各位访问我的个人博客: https://www.crystalblog.xyz/
345
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
