一. 背景:
小熙最近在整理报表导出相关业务。之前有使用 hutool 工具,但是表格样式方面需求比较复杂,编写比较麻烦,而且当数据量比较大的时候支持不太好。所以小熙为此自己封装了模板导出,以及相关流程。
二. 坐标:
1. poi坐标:
<!-- easypoi -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
<!-- 因为其他依赖,这里排除了guava,你也可以不排除 -->
<exclusions>
<exclusion>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
2.hutool坐标:
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.2.3</version>
</dependency>
三. 工具类:
1. Excel导出工具类:
import cn.hutool.core.map.MapUtil;
import javassist.NotFoundException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.lang.Nullable;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.util.Date;
import java.util.Map;
/**
* 程熙cjp:https://blog.csdn.net/weixin_41133233
*
* @author chengxi
* @date 2020/9/14 11:27
*/
@Slf4j
public class ExcelUtils {
private static final String SUFFIX_2003 = ".xls";
private static final String SUFFIX_2007 = ".xlsx";
public static Workbook setWorkbookValue(@Nullable Workbook workbook, MultipartFile file) {
String originalFilename = file.getOriginalFilename();
try {
if (originalFilename.endsWith(SUFFIX_2003)) {
workbook = new HSSFWorkbook(file.getInputStream());
} else if (originalFilename.endsWith(SUFFIX_2007)) {
workbook = new XSSFWorkbook(file.getInputStream());
}
} catch (Exception e) {
log.error(originalFilename + "格式错误, 未找到对应格式");
e.printStackTrace();
}
return workbook;
}
/**
* 预配置类,需要使用请配置
*/
@Deprecated
public static class PreConfigured {
private static XSSFWorkbook wb;
public static XSSFCellStyle getCenterStyle() {
if (PreConfigured.wb == null) {
log.error("没有配置ExcelUtils相关预配置项,XSSFWorkbook为null");
throw new NullPointerException("没有配置ExcelUtils相关预配置项,XSSFWorkbook为null");
}
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
public static Boolean setWb(XSSFWorkbook wb) {
if (wb == null) {
log.error("设置ExcelUtils相关预配置项,XSSFWorkbook为null");
throw new NullPointerException("设置ExcelUtils相关预配置项,XSSFWorkbook为null");
}
if (PreConfigured.wb == null) {
synchronized (PreConfigured.class) {
if (PreConfigured.wb == null) {
PreConfigured.wb = wb;
return true;
}
}
}
return false;
}
public static void clearWb() {
if (PreConfigured.wb != null) {
PreConfigured.wb = null;
}
}
}
/**
* 下载导出Excel模板
*
* @param templatePath
* @param exceptionMessageMap
* @return
* @throws Exception
*/
public static ExportInfoDTO downloadExcelTemplate(String templatePath, Map<String, String> exceptionMessageMap) throws Exception {
File file = new File(templatePath);
if (file.exists()) {
InputStream inputStream = null;
XSSFWorkbook wb = null;
try {
//输入流读取文件
inputStream = new FileInputStream(file);
//读取excel模板
wb = new XSSFWorkbook(inputStream);
} catch (FileNotFoundException e) {
log.error(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.READ_INPUT_STREAM_EXCEPTION), e);
throw new FileNotFoundException(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.READ_INPUT_STREAM_EXCEPTION) + e.getMessage());
} catch (IOException e) {
e.printStackTrace();
log.error(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.READ_EXPORT_TEMPLATE_EXCEPTION), e);
throw new IOException(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.READ_EXPORT_TEMPLATE_EXCEPTION) + e.getMessage());
} finally {
// 关闭输入流
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
log.error("文件输入流关闭失败:{}", e.getMessage());
}
}
return new ExportInfoDTO().setWb(wb);
}
log.error(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.DOWNLOAD_EXCEPTION) + templatePath);
throw new NotFoundException(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.DOWNLOAD_EXCEPTION) + templatePath);
}
/**
* 下载导出Excel模板 (开启工具类的相关预配置)
*
* @param templatePath
* @param exceptionMessageMap
* @return
* @throws Exception
*/
@Deprecated
public static ExportInfoDTO downloadExcelTemplateDefaultStartPreConfig(String templatePath, Map<String, String> exceptionMessageMap) throws Exception {
ExportInfoDTO exportInfoDTO = ExcelUtils.downloadExcelTemplate(templatePath, exceptionMessageMap);
ExcelUtils.PreConfigured.setWb(exportInfoDTO.getWb());
return exportInfoDTO;
}
/**
* 关闭导出Excel的流
*
* @param exportInfoDTO
* @param exceptionMessageMap
* @throws Exception
*/
public static void closeExcelStream(ExportInfoDTO exportInfoDTO, Map<String, String> exceptionMessageMap) throws Exception {
XSSFWorkbook wb = exportInfoDTO.getWb();
OutputStream out = exportInfoDTO.getOut();
try {
if (wb != null && out != null) {
wb.write(out);
out.flush();
}
} catch (IOException e) {
log.error(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.WRITE_EXPORT_EXCEL_IO_EXCEPTION), e);
throw new IOException(MapUtil.getStr(exceptionMessageMap, ExceptionConstant.WRITE_EXPORT_EXCEL_IO_EXCEPTION) + e.getMessage());
} finally {
// 关闭输出流
if (wb != null) {
wb.close();
}
if (out != null) {
out.close();
}
}
}
/**
* 设置单元格内容
* 注意先set Excel XSSFWorkbook(预配置,如果没有的话) 然后再使用
*
* @param row
* @param valueIndex
* @param trueValue
* @param falseValue
* @param resultBoolean
*/
public static XSSFCell setCellValue(XSSFRow row, Integer valueIndex, String trueValue, String falseValue, Boolean resultBoolean) {
if (resultBoolean == null) {
return null;
}
String value = resultBoolean ? trueValue : falseValue;
XSSFCell cell = row.getCell(valueIndex);
if (cell == null) {
ExcelUtils.createCenterCell(row, valueIndex).setCellValue(value);
} else {
cell.setCellValue(value);
}
return cell;
}
/**
* 获取默认居中样式
* @param wb
* @return
*/
public static XSSFCellStyle queryDefaultCenterXSSFCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
/**
* 设置单元格内容居中
* 注意先set Excel XSSFWorkbook(预配置,如果没有的话) 然后再使用
*
* @param cell
*/
@Deprecated
public static XSSFCell setCellCenter(XSSFCell cell) {
cell.setCellStyle(PreConfigured.getCenterStyle());
return cell;
}
/**
* 设置单元格内容居中
*
* @param cell
*/
public static XSSFCell setCellCenter(XSSFCell cell, XSSFCellStyle cellStyle) {
cell.setCellStyle(cellStyle);
return cell;
}
/**
* 创建居中单元格
* 注意先set Excel XSSFWorkbook(预配置,如果没有的话) 然后再使用
*
* @param row
* @param columnIndex
* @return
*/
@Deprecated
public static XSSFCell createCenterCell(XSSFRow row, Integer columnIndex) {
XSSFCell cell = row.createCell(columnIndex);
return ExcelUtils.setCellCenter(cell);
}
/**
* 创建居中单元格
*
* @param row
* @param columnIndex
* @return
*/
public static XSSFCell createCenterCell(XSSFRow row, Integer columnIndex, XSSFCellStyle cellStyle) {
XSSFCell cell = row.createCell(columnIndex);
return ExcelUtils.setCellCenter(cell, cellStyle);
}
/**
* sheet中合并单元行
*/
public static CellRangeAddress mergeCellLines(XSSFSheet sheet, Integer startRow, Integer lastRow, Integer firstCol, Integer lastCol){
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow,lastRow,firstCol,lastCol);
sheet.addMergedRegion(cellRangeAddress);
return cellRangeAddress;
}
/**
* 获取 excel中时间(这里只是基础,还可以根据 HSSFDateUtil的 api进行拓展)
*
* @param doubleDate
* @return
*/
public static Date queryExcelDate(Double doubleDate) {
return HSSFDateUtil.getJavaDate(doubleDate);
}
/**
* 启用 Excel模板公式
*
* @param sheet
*/
public static void enableExcelFormula(XSSFSheet sheet) {
// 如果这行没有了,整个公式都不会有自动计算的效果的
sheet.setForceFormulaRecalculation(true);
}
/**
* 启用 Excel模板公式
*
* @param wb
*/
public static void enableExcelFormula(XSSFWorkbook wb) {
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
enableExcelFormula(wb.getSheetAt(i));
}
}
/**
* 获取 Excel中 string类型数据
*
* @param row
* @param index
* @return
*/
public static String queryExcelStringData(Row row, Integer index) {
if (row.getCell(index) != null) {
row.getCell(index).setCellType(CellType.STRING);
return row.getCell(index).getStringCellValue();
}
return null;
}
/**
* 导出 Excel模板(不设置数据,常用于导出导入模板)
*
* @param response
* @param paramMap
* @param templatePath
* @throws Exception
*/
public static void exportExcelTemplate(HttpServletResponse response, Map<String, String> paramMap, String templatePath) throws Exception {
// public static final String Report_Name = "Report_Name";
ServletOutputStream outputStream = ExportUtils.extractExport(response, paramMap, paramMap.get(StringConstant.Report_Name));
// 导出Excel模板
ExportInfoDTO exportInfoDTO = downloadExcelTemplate(templatePath, paramMap);
// 设置相关导出数据
exportInfoDTO.setOut(outputStream);
// 关流(注意不要忘记了)
ExcelUtils.closeExcelStream(exportInfoDTO, paramMap);
}
}
2. 导出其他封装工具类:
import cn.hutool.core.io.IORuntimeException;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelWriter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Map;
/**
* 如果不想拓展,可把此类聚合在 Excel工具类中
* @author chengxi
* @date 2020/9/9 17:04
*/
@Slf4j
public class ExportUtils {
/**
* 抽取导出前置配置
*
* @param response
* @param exceptionMessageMap
* @param fileName
* @return
* @throws Exception
*/
public static ServletOutputStream extractExport(HttpServletResponse response, Map<String, String> exceptionMessageMap, String fileName) throws Exception {
if (StringUtils.isBlank(fileName)) {
log.error("下载文件名称不能为空, 缺少文件名称参数,当前参数为:" + exceptionMessageMap);
throw new NullPointerException("下载文件名称不能为空: " + exceptionMessageMap.get(ExceptionConstant.NUll_FILE_NAME_EXCEPTION) + ", 缺少文件名称参数,当前参数为:" + exceptionMessageMap);
}
try {
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// 防止乱码
fileName = URLEncoder.encode(fileName, String.valueOf(StandardCharsets.UTF_8));
fileName += LocalDateUtils.getNowTime(LocalDateUtils.defaultYMD);
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream outputStream = response.getOutputStream();
return outputStream;
} catch (Exception ex) {
log.error(exceptionMessageMap.get(ExceptionConstant.DOWNLOAD_EXCEPTION), ex);
throw new InterruptedException(exceptionMessageMap.get(ExceptionConstant.DOWNLOAD_EXCEPTION) + ex);
}
}
/**
* 关闭huTool工具导出流
*
* @param writer
* @param out
*/
public static void closeHuToolExportStream(ExcelWriter writer, OutputStream out) {
try {
if (writer != null && out != null) {
writer.flush(out, true);
}
} catch (IORuntimeException e) {
e.printStackTrace();
log.error("关闭导出流异常:" + e.getMessage());
} finally {
if (writer != null) {
// 关闭writer,释放内存
writer.close();
}
if (out != null) {
//此处记得关闭输出Servlet流
IoUtil.close(out);
}
}
}
}
3. 导出封装DTO:
import lombok.Data;
import lombok.experimental.Accessors;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.OutputStream;
/**
* @author chengxi
* @date 2020/10/29 09:47
*/
@Data
@Accessors(chain = true)
public class ExportInfoDTO {
private XSSFWorkbook wb;
private OutputStream out;
}
4.导出规约接口:
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.lang.ref.WeakReference;
import java.util.Collection;
import java.util.Map;
/**
* @author chengxi
* @date 2020/10/30 11:52
*/
public interface ExcelService <R> {
/**
* 设置Excel导出数据规约
*
* @param wb
* @param searchConditionDTO
* @throws Exception
*/
void setExcelExportData(XSSFWorkbook wb, R searchConditionDTO) throws Exception;
/**
* 获取模板路径
*
* @param requestParamDTO
* @return
*/
String queryDownloadTemplatePath(R requestParamDTO);
/**
* 导出方法
*
* @param response
* @param requestParamDTO
* @param paramMap
* @throws Exception
*/
default void export(HttpServletResponse response, R requestParamDTO, Map<String, String> paramMap) throws Exception {
// 这里的 String 常量可聚合到自己项目的常量 util中(因为只有一条就单独拿出来解释)
// public static final String Report_Name = "Report_Name";
ServletOutputStream outputStream = ExportUtils.extractExport(response, paramMap, paramMap.get(StringConstant.Report_Name));
// 导出Excel模板
ExportInfoDTO exportInfoDTO = ExcelUtils.downloadExcelTemplate(queryDownloadTemplatePath(requestParamDTO), paramMap);
// 设置数据值
setExcelExportData(exportInfoDTO.getWb(), requestParamDTO);
// 设置相关导出数据
exportInfoDTO.setOut(outputStream);
// 关流(注意不要忘记了)
ExcelUtils.closeExcelStream(exportInfoDTO, paramMap);
}
/**
* 转换为软对象(不强制使用)
*
* @param collection
*/
default void convertToSoftObject(Collection collection) {
collection.forEach(WeakReference::new);
}
}
5. 异常常量类:
/**
* @author chengxi
* @date 2020/10/28 11:31
*/
public class ExceptionConstant {
/**
* 下载异常
*/
public static final String DOWNLOAD_EXCEPTION = "downloadException";
/**
* 检查异常
*/
public static final String CHECK_EXCEPTION = "checkException";
/**
* 读取输入流异常
*/
public static final String READ_INPUT_STREAM_EXCEPTION = "readInputStreamException";
/**
* 读取Excel模板异常
*/
public static final String READ_EXPORT_TEMPLATE_EXCEPTION = "readExportTemplateException";
/**
* 写出Excel IO Exception
*/
public static final String WRITE_EXPORT_EXCEL_IO_EXCEPTION = "writeExportExcelIoException";
/**
* 空文件名称异常
*/
public static final String NUll_FILE_NAME_EXCEPTION = "NullFileNameException";
}
四: 使用示例:
在基于上述的流程之后,导出可以配置成为流程化的。之后再次导出其他只需编写三个地方。
- 在 controller 中配置各种异常方法的预信息、报表名称、方法代码(methodCode)等。
- 在 serveviceimpl 服务实现类中实现的获取模板路径配置成自己的(亦可以写成策略模式的)
- 在 serveviceimpl 服务实现类中将数据源赋值进,读取到的表格模板中即可。
1. 控制器层(controller)
@Autowired
private ReportService reportService;
@PostMapping(value = "/exportReport")
public void exportReport(HttpServletResponse response, @RequestBody ReportDTO reportDTO) throws Exception {
// XXX_REPORT_BY_TEMPLATE 请根据自己业务配置,亦可配置成常量(一个区分方法的code)
// xxx 请根据自己需要导出的报表命名
reportService.exportExcelData(response, reportDTO,
(reportDTOConsumer) -> reportDTOConsumer.setMethodCode(MethodConstantsEnum.XXX_REPORT_BY_TEMPLATE.getMethodCode()),
MapBuilder.<String,String>create(Maps.newHashMap())
.put(ExceptionConstant.READ_INPUT_STREAM_EXCEPTION, "xxx导出报表,输入流异常: ")
.put(ExceptionConstant.READ_EXPORT_TEMPLATE_EXCEPTION, "xxx导出报表,读取Excel模板异常: ")
.put(ExceptionConstant.DOWNLOAD_EXCEPTION, "该路径下未找到xxx模板报表,路径:")
.put(ExceptionConstant.WRITE_EXPORT_EXCEL_IO_EXCEPTION, "xxx导出报表,写出Excel IO异常")
.put(StringConstant.Report_Name, "xxx报表")
.build());
}
2. 服务层(service)
-
接口
import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFSheet; import javax.servlet.http.HttpServletResponse; import java.util.Map; import java.util.function.Consumer; /** * @author chengxi * @date 2021/2/1 17:06 */ public interface ReportService { /** * 导出报表数据 * * @param response * @param supplierReportDTO * @param reportDTOConsumer * @param paramMap * @throws Exception */ void exportExcelData(HttpServletResponse response, ReportDTO supplierReportDTO, Consumer<ReportDTO> reportDTOConsumer, Map<String, String> paramMap) throws Exception; /** * 设置xxx(请根据自己的业务设置名称)导出报表数据 (这里导出报表数据的其中之一的设置) * * @param sheet * @param cellStyle * @param reportDTO * @throws IllegalAccessException */ void setExcelXXXExportData(XSSFSheet sheet, XSSFCellStyle cellStyle, ReportDTO reportDTO) throws IllegalAccessException; }
-
实现类:
/** * @author chengxi * @date 2021/2/1 17:07 */ @Slf4j @Service public class ReportServiceImpl implements ReportService, ExcelService<ReportDTO> { /** * 默认调用导出规约中的默认方法 * * @param response * @param reportDTO * @param reportDTOConsumer * @param paramMap * @throws Exception */ @Override public void exportExcelData(HttpServletResponse response, ReportDTO reportDTO, Consumer<ReportDTO> reportDTOConsumer, Map<String, String> paramMap) throws Exception { reportDTOConsumer.accept(reportDTO); export(response, reportDTO, paramMap); } /** * 分发设置相关导出数据,根据方法 code区分 * * @param wb * @param reportDTO * @throws Exception */ @Override public void setExcelExportData(XSSFWorkbook wb, ReportDTO reportDTO) throws Exception { XSSFCellStyle cellStyle = ExcelUtils.queryDefaultCenterXSSFCellStyle(wb); XSSFSheet sheet = wb.getSheetAt(0); if (MethodConstantsEnum.XXX_REPORT_BY_TEMPLATE.getMethodCode().equals(reportDTO.getMethodCode())) { setExcelXXXExportData(sheet, cellStyle, reportDTO); } } /** * 设置某一导出表格数据(需根据业务变动) * * @param sheet * @param cellStyle * @param reportDTO * @throws IllegalAccessException */ @Override public void setExcelXXXExportData(XSSFSheet sheet, XSSFCellStyle cellStyle, ReportDTO reportDTO) throws IllegalAccessException { // 获取数据源 // 请配置成自己查询导出数据源的接口 List<XXXExcelDataDTO> downloadList = xxxService.queryXXXExcelData(reportDTO); if (CollectionUtil.isEmpty(downloadList)) { log.error("本次查询数据为空,查询数据为reportDTO:" + reportDTO); return; } // 这里是属性比较多,所以采用反射获取遍历了 // 虽然反射可以提取封装,但还是根据业务设置比较好(如不考虑可以封装) for (int i = 0; i < downloadList.size(); i++) { XXXExcelDataDTO downloadVO = downloadList.get(i); XSSFRow row = sheet.createRow(i + 1); Field[] declaredFields = downloadVO.getClass().getDeclaredFields(); for (int j = 0; j < declaredFields.length; j++) { Field declaredField = declaredFields[j]; declaredField.setAccessible(true); ExcelUtils.createCenterCell(row, j, cellStyle).setCellValue((String) declaredField.get(downloadVO)); } } //转换引用对象 convertToSoftObject(downloadList); } /** * 获取模板路径(需变动) * * @param reportDTO * @return */ @Override public String queryDownloadTemplatePath(ReportDTO reportDTO) { if (MethodConstantsEnum.XXX_REPORT_BY_TEMPLATE.getMethodCode().equals(reportDTO.getMethodCode())) { // 请配置成自己项目的对应模板路径(可配置在yml中,设置到配置类中) return xxxCommonData.getXXXReportTemplatePath(); } return ""; } }
五. 后语:
这个也是小熙的初次封装流程,还很粗糙,还有很大完善空间,可以根据自己的喜好配置哟。