报表导出编码思路解析:
- 按照一定查询条件,将满足条件的记录查询出来
- 创建workbook,并命名,如sheet1
- 使用增强for循环逐一遍历对象,先设置好表头,接着按照对象的字段创建单元格Cell并完成赋值操作
- 使用HttpServletRespone对象,获取输出流 OutputStream out = response.getOutputStream(); 而这也就意味着Controller层对应的方法是不需要返回值的,即返回值定义为 void
- 给Excel文件命名
- 调用 workbook.write(out) 将报表输出客户端。
先引入Jar依赖(项目是Spring Boot + Gradle,使用gradle语法引入依赖):
compile group: 'org.apache.poi', name: 'poi', version: '3.17'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17'
接下来,展示数据导出的整个流程:
/**
* @return void
* @throws
* @description 导出库存点报备数据列表
* @params [request, response]
*/
@Override
public void exportStockReportData(StockReportSearchRequest request, HttpServletResponse response) {
// 属性名称数据集
List<String> fieldNames = Arrays.asList("id", "dealerName", "address", "createdAt", "updatedAt");
// 组装经销商id列表
assembleDealerInfoIds(request);
// 获取库存点报备数据列表
List<StockReportResponse> stockReportResList = adminStockReportDAO.listStockReport(request);
try {
// 自定义Excel文件名 - 兼容".xlsx"格式
String fileName = "stock-reports";
String fileNamePlusSuffix = fileName.concat(".xls");
Workbook workbook = POIUtil.createWorkbook(fileNamePlusSuffix);
if (CollectionUtil.isEmpty(stockReportResList)) {
logger.info("AdminStockReportServiceImpl.exportStockReportData ========== 查无库存点报备数据 ========== ");
// 导出 空Excel文件
POIUtil.writeDataListToFile(workbook, stockReportResList, fieldNames);
} else {
// 关联属性值的数据组装
assembleAssociateFields(stockReportResList);
List<StockReportExportResponse> stockReportExportResList = new ArrayList<>();
for (StockReportResponse stockReportInfo : stockReportResList) {
if (Objects.isNull(stockReportInfo)) continue;
StockReportExportResponse stockReportExportInfo = new StockReportExportResponse();
BeanUtils.copyProperties(stockReportInfo, stockReportExportInfo);
// 数据类型转换
stockReportExportInfo.setCreatedAt(DateTimeUtil.getDateFormat(stockReportInfo.getCreatedAt()));
stockReportExportInfo.setUpdatedAt(DateTimeUtil.getDateFormat(stockReportInfo.getUpdatedAt()));
stockReportExportResList.add(stockReportExportInfo);
}
// 导出 携带数据的Excel文件
POIUtil.writeDataListToFile(workbook, stockReportExportResList, fieldNames);
}
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileNamePlusSuffix);
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
logger.error("AdminStockReportServiceImpl.exportStockReportData ========== 库存点报备报表导出异常:[{}] ========== ", e.getMessage());
logger.error("AdminStockReportServiceImpl.exportStockReportData ========== 完整的异常信息:[{}] ========== ", e);
}
}
/**
* @Description POI工具类
* @Author blake
* @Date 2019-01-25 14:51
* @Version 1.0
*/
public class POIUtil {
private static final Logger logger = LoggerFactory.getLogger(POIUtil.class);
/**
* @return void
* @throws
* @description 填充workbook工作表
* @params [workbook, dataList, fieldNames]
*/
public static <T> void writeDataListToFile(Workbook workbook, List<T> dataList, List<String> fieldNames) {
// 新建sheet并给予命名
Sheet sheet = workbook.createSheet("sheet1");
// 组装workbook工作表的数据行
assembleRowDataInWorkbook(dataList, fieldNames, sheet);
}
/**
* @return org.apache.poi.ss.usermodel.Workbook
* @throws
* @description 创建workbook工作表
* @params [fileNamePlusSuffix]
*/
public static Workbook createWorkbook(String fileNamePlusSuffix) throws Exception {
// 校验Excel文件格式,并按需创建workbook实例
return checkFileTypeOfExcel(fileNamePlusSuffix);
}
/**
* @return void
* @throws
* @description 组装workbook工作表的数据行
* @params [dataList, fieldNames, sheet]
*/
private static <T> void assembleRowDataInWorkbook(List<T> dataList, List<String> fieldNames, Sheet sheet) {
// 绕过Java反射机制,通过JSON attr 实现Bean对象的属性填充
for (int dataIndex = 0; dataIndex <= dataList.size(); dataIndex++) {
Row row = sheet.createRow(dataIndex);
JSONObject jsonObj = null;
if (dataIndex != 0) {
// Bean => JSONObject
jsonObj = JSONObject.fromObject(dataList.get(dataIndex - 1));
}
for (int fieldIndex = 0; fieldIndex < fieldNames.size(); fieldIndex++) {
// 第一列单元格
Cell cell = row.createCell(fieldIndex);
// 属性名称
String fieldName = fieldNames.get(fieldIndex);
// 第一行
if (dataIndex == 0) {
// 表头文案
cell.setCellValue(fieldName);
} else {
// 将bean对象转换成JSON对象
String content = jsonObj.getString(fieldName);
if (content == null) {
content = "";
}
cell.setCellValue(content);
}
}
}
}
/**
* @return org.apache.poi.ss.usermodel.Workbook
* @throws
* @description 校验Excel文件格式
* @params [fileNamePlusSuffix]
*/
private static Workbook checkFileTypeOfExcel(String fileNamePlusSuffix) throws Exception {
Workbook workbook;
if (fileNamePlusSuffix.endsWith("xlsx")) {
logger.info("POIUtil.checkFileTypeOfExcel ============ 当前Excel文件为2007版本 ============");
workbook = new XSSFWorkbook();
} else if (fileNamePlusSuffix.endsWith("xls")) {
logger.info("POIUtil.checkFileTypeOfExcel ============ 当前Excel文件为2003版本 ============");
workbook = new HSSFWorkbook();
} else {
throw new Exception("invalid file name, should be xls or xlsx");
}
return workbook;
}
}