写在前面的话:
业务Service获取要导出的相关数据,封入List,调用相关工具类,转为导出所需相关VO,实现Excel的下载。
备注:1、由于本方法为通用方法,故不支持涉及到行or列合并的情况。
2、本文展示示例,当前仅满足chrome下载文件名不乱码。若使用其他浏览器或postman访问,可能出现文件名乱码的情况。
目录
2、处理headerTitle、contentTitle、content样式的代码
4、处理文件注入的代码(getTypeDicByFileByte 方法见 五-3)
一、结果示例
1、代码调用示例(Controller)
@ApiOperation("导出") public ResponseEntity<byte[]> queryAll4Export(QueryUserListReqVO reqVO) { // 业务处理 List<Export4UserListRespVO> list = cgUserService.queryAll4Export(reqVO); // 调用工具类 DownloadAttachmentRespVO respVO = ExcelUtils.export2Excel(list, Export4UserListRespVO.class, "人员信息", "人员信息", "人员信息"); // 封装返回参数,因可通用,故下列代码可任意复制 HttpHeaders header = new HttpHeaders(); header.setContentType(MediaType.APPLICATION_OCTET_STREAM); header.setContentDispositionFormData("attachment", respVO.getAttName()); return new ResponseEntity<byte[]>(respVO.getData(), header, HttpStatus.OK); }
2、结果示例
二、实现原理
1、数据实现原理
1)、所有的数据列,遍历每一行,通过反射取值value。针对日期和图片相关字段,则对value判断类型,定制化处理(对应上图中第3列开始)。
2)、所有的数据标题,通过自定义注解赋值,在业务代码,接受数据的VO中,通过注解,指定该字段输出到excel的标题文字(对应上图中第2列)。如图:
3)、文件的名称、sheet页的名称、和headerTitle(上图中第一列),则通过传参的方式进行赋值。
2、图片实现原理
1)、通过行rowIndex、列columnIndex,获取图片所在单元格四个角的坐标。
2)、通过HssfWordbook.addPicture注入图片。其中涉及到参数format,需通过文件流获取fomat类型值,此部分见(五、不是很重要的辅助工具代码)
三、pom依赖引入
<commons.lang3.version>3.8.1</commons.lang3.version> <poi.version>3.17</poi.version> <commons.io.version>2.6</commons.io.version> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>${commons.lang3.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>${commons.io.version}</version> </dependency>
四、工具源码
特别说明:
1、为保证透彻理解代码及注释,此处统一叫法:一-2中的截图,行号为1的,称为headerTitle;行号为2的,称为contentTitle;行号为3以后的,均为content。
2、工具代码中的Constants4Api.xxx不再单独列出。
1、工具核心代码
/** * 导出excel * 1、初始化参数,设置文件名称, * 2、初始化参数,设置 sheet 页名称,并创建 sheet 页。 * 3、初始化参数,设置 headerTitle 名称,根据 headerTitle 入参情况,设置 contentTitle 和 content 的起始列。 * 4、调用工具,获取 contentTitle 的值,并写入至单元格(详细注释见相关工具类)。 * 5、调用工具,遍历数据源list,并写入至单元格(详细注释见相关工具类)。 * * @param objList 目标对象list * @param clazz obj.class * @param fileName 要导出的文件名, 不填则默认为 "自定义.xls" * @param sheetName 要导出的文件的工作簿名称, 不填则默认为"sheet1" * @param <T> 泛型 * @return 文件数据 */ public static <T> DownloadAttachmentRespVO export2Excel(List<T> objList, Class<T> clazz, String headerTitle, String fileName, String sheetName) { // 设置 标题行、内容行,默认无headerTitle头 int contentTitleRow = 0; int contentRow = 1; // 1. 设置文件名称 String exportFileName = StringUtils.isEmpty(fileName) ? "自定义" : fileName + ".xls"; try { exportFileName = URLEncoder.encode(exportFileName, "UTF-8"); exportFileName = new String(exportFileName.getBytes(), StandardCharsets.ISO_8859_1); } catch (UnsupportedEncodingException e) { log.error("格式化文件名称失败!", e); throw new RuntimeException(Constants4Api.EXCEL_NAME_ERROR); } // 2. 创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(StringUtils.isEmpty(sheetName) ? "sheet1" : sheetName); if (!StringUtils.isEmpty(headerTitle)) { // 3. 设置headerTitle【2020-03-31新增功能】: headerTitle contentTitleRow = 1; contentRow = 2; // 3.1 设置标题样式 HSSFCellStyle headerTitleStyle = handleHeaderTitleStyle4Export(workbook); // 3.2 设置标题内容 HSSFRow headerTitleRow = sheet.createRow(0); headerTitleRow.setHeight((short) (24 * 20)); HSSFCell titleCell = headerTitleRow.createCell(0); titleCell.setCellValue(new HSSFRichTextString(headerTitle)); titleCell.setCellStyle(headerTitleStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, clazz.getDeclaredFields().length - 1)); } // 4. 设置表头: rowTitle // 4.1 设置表头样式 HSSFCellStyle contentTitleStyle = handleContentTitleStyle4Export(workbook); // 4.2 获取表头 List<String> titleList = analysisAnnotation(clazz); // 4.3 设置表头内容 HSSFRow row = sheet.createRow(contentTitleRow); row.setHeight((short) (18 * 20)); int columnIndex = 0; for (String title : titleList) { HSSFCell cell = row.createCell(columnIndex ++); cell.setCellValue(new HSSFRichTextString(title)); cell.setCellStyle(contentTitleStyle); } // 5. 设置输出内容 // 5.1 设置内容样式【2020-07-20新增功能】: content HSSFCellStyle contentStyle = handleContentStyle4Export(workbook); // 5.2 设置输出的值 try { for (T obj : objList) { columnIndex = 0; HSSFRow valueRow = sheet.createRow(contentRow ++); valueRow.setHeight((short) (16 * 20)); Class<?> tempClazz = obj.getClass(); Field[] fieldArr = tempClazz.getDeclaredFields(); for (Field field : fieldArr) { field.setAccessible(true); Object value = field.get(obj); if (Objects.isNull(value)) { valueRow.createCell(columnIndex ++).setCellValue(""); } else { if (value instanceof Date) { // 处理日期格式 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm"); value = sdf.format(value); } else if (value instanceof byte[]){ // 处理文件注入 handlePicInject(workbook, sheet, valueRow, contentRow, columnIndex, (byte[])value); valueRow.createCell(columnIndex ++).setCellValue(""); continue; } // 自动列宽 -- 极其影响性能 sheet.autoSizeColumn(columnIndex, true); HSSFCell cell = valueRow.createCell(columnIndex++); cell.setCellValue(String.valueOf(value)); cell.setCellStyle(contentStyle); cell.setCellType(CellType.STRING); } } } } catch (IllegalAccessException e) { log.error("导出excel:设置输出值失败!", e); throw new RuntimeException(Constants4Api.EXCEL_EXPORT_ERROR); } // 6. 转为字节流 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); try { workbook.write(outputStream); } catch (IOException e) { log.error("转为字节流过程中出现异常!"); throw new RuntimeException(Constants4Api.EXCEL_EXPORT_ERROR); } return new DownloadAttachmentRespVO(exportFileName, outputStream.toByteArray()); }
2、处理headerTitle、contentTitle、content样式的代码
private static HSSFCellStyle handleHeaderTitleStyle4Export (HSSFWorkbook workbook) { HSSFCellStyle headerTitleStyle = workbook.createCellStyle(); HSSFFont headerTitleFont = workbook.createFont(); headerTitleFont.setFontName("宋体"); headerTitleFont.setBold(true); headerTitleFont.setFontHeightInPoints((short) 16); headerTitleStyle.setFont(headerTitleFont); headerTitleStyle.setAlignment(HorizontalAlignment.CENTER); headerTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER); return headerTitleStyle; }
private static HSSFCellStyle handleContentTitleStyle4Export (HSSFWorkbook workbook) { HSSFCellStyle contentTitleStyle = workbook.createCellStyle(); HSSFFont contentTitleFont = workbook.createFont(); contentTitleFont.setFontName("宋体"); contentTitleFont.setBold(true); contentTitleFont.setFontHeightInPoints((short) 13); contentTitleStyle.setFont(contentTitleFont); contentTitleStyle.setAlignment(HorizontalAlignment.CENTER); contentTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER); return contentTitleStyle; }
private static HSSFCellStyle handleContentStyle4Export (HSSFWorkbook workbook) { HSSFCellStyle contentStyle = workbook.createCellStyle(); HSSFFont contentFont = workbook.createFont(); contentFont.setFontName("宋体"); contentFont.setFontHeightInPoints((short) 11); HSSFDataFormat dataFormat = workbook.createDataFormat(); contentStyle.setFont(contentFont); contentStyle.setDataFormat(dataFormat.getFormat("@")); contentStyle.setAlignment(HorizontalAlignment.CENTER); contentStyle.setVerticalAlignment(VerticalAlignment.CENTER); return contentStyle; }
3、解析contentTitle自定义注解的代码
private static <T> List<String> analysisAnnotation(Class<T> clazz) { List<String> titleList = new ArrayList<>(); Field[] fieldArr = clazz.getDeclaredFields(); for (Field field : fieldArr) { String title = "未定义"; try { FieldName annotation = field.getAnnotation(FieldName.class); title = annotation.value(); } catch (Exception e) { log.error("获取自定义注解失败!当前字段名为:" + field.getName()); } titleList.add(title); } return titleList; }
4、处理文件注入的代码(getTypeDicByFileByte 方法见 五-3)
private static void handlePicInject (HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow rowData, int rowNum, int columnNum, byte[] fileData) { if (!Objects.isNull(fileData)) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) columnNum, rowNum - 1, (short) (columnNum + 1), rowNum); sheet.setColumnWidth(columnNum, 60 * 256); rowData.setHeight((short) (40 * 20)); int record = workbook.addPicture(fileData, getTypeDicByFileByte(fileData)); patriarch.createPicture(anchor, record); } }
5、导出列的列名设置,自定义注解类
/**************************************************** * * 自定义注解 -- 导出excel字段备注 * * * @author Francis * @date 2020/01/17 11:10 * @version 1.0 **************************************************/ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface FieldName { String value() default "未知"; }
6、方法出参vo
/**************************************************** * * 文件下载 -- 出参VO * * * @author Francis * @since 2020/2/21 16:49 * @version 1.0 **************************************************/ @Data @AllArgsConstructor @NoArgsConstructor @ApiModel("文件下载 -- 出参vo") public class DownloadAttachmentRespVO implements Serializable { private static final long serialVersionUID = 1L; @ApiModelProperty("文件名称") public String attName; @ApiModelProperty("文件数据:如果是多个文件,则为压缩包") public byte[] data; }
7、示例中的入参vo
/**************************************************** * * 导出excel: 人员信息 -- 出参vo * * * @author Francis * @since 2021/4/26 13:07 * @version 1.0 **************************************************/ @Data @ApiModel("导出excel: 人员信息 -- 出参vo") public class Export4UserListRespVO { @FieldName(value = "人员姓名") private String userName; @FieldName(value = "单位名称") private String companyName; @FieldName(value = "部门名称") private String deptName; @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd") @FieldName(value = "出生日期") private Date birthday; @FieldName(value = "延迟时间范围(分钟)") private Integer attendanceTimeScope; @FieldName(value = "月度评分") private Float grade; @FieldName(value = "月薪(元)") private Double monthlyPay; @FieldName(value = "年薪(万元)") private BigDecimal annualPay; @FieldName(value = "是否删除") private Boolean isDelete; @FieldName(value = "排序号") private Short orderNo; @FieldName(value = "备注") private String remark; @FieldName(value = "头像") private byte[] headerImg; }
五、不是很重要的辅助工具代码
1、根据http文件访问路径,下载文件
/** * 根据 url 下载 文件 * * @param url url * @param folderPath 存放路径(文件夹:如: D:/download/) * @return file */ public static File downloadFileByUrl (String url, String folderPath) { OutputStream outputStream = null; // 1. 设置请求头 List<MediaType> acceptList = new ArrayList<>(); acceptList.add(MediaType.APPLICATION_OCTET_STREAM); HttpHeaders headers = new HttpHeaders(); headers.setAccept(acceptList); // 2. 发送请求 RestTemplate client = new RestTemplate(); ResponseEntity<byte[]> response = client.exchange(url, HttpMethod.GET, new HttpEntity<byte[]>(headers), byte[].class); // 3. 处理结果,获取文件输入流 byte[] result = response.getBody(); // 4. 获取文件名称 String[] tempArr = url.split("/"); String fileName = tempArr[tempArr.length - 1]; // 5. 保存文件 File fileFolder = new File(folderPath); if (!fileFolder.isDirectory()) { fileFolder.mkdirs(); } File file = new File(folderPath + fileName); try { outputStream = new BufferedOutputStream(new FileOutputStream(file)); outputStream.write(result); outputStream.close(); } catch (IOException e) { e.printStackTrace(); } finally { try { if (outputStream != null) { outputStream.close(); } } catch (IOException e) { e.printStackTrace(); } } return file; }
2、根据http图片的访问路径,获取文件流
/** * 根据 文件的映射路径(即:url路径) 获取 文件流 * * @param url 文件的url路径:如:http://xxxx/aaa.jpg * @return 文件流 */ public static byte[] getFileByUrl (String url) { // 1. 下载文件 String tempPath = System.getProperty("user.dir") + "/tempDownload/"; File file = downloadFileByUrl(url, tempPath); // 2. 获取文件流 byte[] fileData = new byte[0]; try { fileData = FileUtils.readFileToByteArray(file); } catch (IOException e) { log.error("获取文件流过程中出现异常!"); throw new RuntimeException("获取文件流过程中出现异常!"); } finally { // 3. 删除文件 file.delete(); } return fileData; }
3、根据文件流,获取excel注入图片的format类型
private static Integer getTypeDicByFileByte (byte[] fileData) { StringBuilder sb = new StringBuilder(); if (Objects.isNull(fileData)) { return HSSFWorkbook.PICTURE_TYPE_JPEG; } IntStream.range(0, fileData.length).map(i -> fileData[i] & 0xFF).mapToObj(Integer::toHexString).forEach(hv -> { if (hv.length() < 2) { sb.append(0); } sb.append(hv); }); String fileSuffix = TypeDictUtils.checkType(sb.toString().toUpperCase().substring(0, 6)); Integer typeDic = null; switch (fileSuffix) { case "jpg" : typeDic = HSSFWorkbook.PICTURE_TYPE_JPEG; break; case "png" : typeDic = HSSFWorkbook.PICTURE_TYPE_PNG; break; default: typeDic = HSSFWorkbook.PICTURE_TYPE_JPEG; break; } return typeDic; }
/**************************************************** * * 校验文件后缀工具 * * * @author Francis * @date 2020/4/26 17:34 * @version 1.0 **************************************************/ public class TypeDictUtils { /** * 常用文件的文件头如下:(以前六位为准) * JPEG (jpg),文件头:FFD8FF * PNG (png),文件头:89504E47 * GIF (gif),文件头:47494638 * TIFF (tif),文件头:49492A00 * Windows Bitmap (bmp),文件头:424D * CAD (dwg),文件头:41433130 * Adobe Photoshop (psd),文件头:38425053 * Rich Text Format (rtf),文件头:7B5C727466 * XML (xml),文件头:3C3F786D6C * HTML (html),文件头:68746D6C3E * Email [thorough only] (eml),文件头:44656C69766572792D646174653A * Outlook Express (dbx),文件头:CFAD12FEC5FD746F * Outlook (pst),文件头:2142444E * MS Word/Excel (xls.or.doc),文件头:D0CF11E0 * MS Access (mdb),文件头:5374616E64617264204A * WordPerfect (wpd),文件头:FF575043 * Postscript (eps.or.ps),文件头:252150532D41646F6265 * Adobe Acrobat (pdf),文件头:255044462D312E * Quicken (qdf),文件头:AC9EBD8F * Windows Password (pwl),文件头:E3828596 * ZIP Archive (zip),文件头:504B0304 * RAR Archive (rar),文件头:52617221 * Wave (wav),文件头:57415645 * AVI (avi),文件头:41564920 * Real Audio (ram),文件头:2E7261FD * Real Media (rm),文件头:2E524D46 * MPEG (mpg),文件头:000001BA * MPEG (mpg),文件头:000001B3 * Quicktime (mov),文件头:6D6F6F76 * Windows Media (asf),文件头:3026B2758E66CF11 * MIDI (mid),文件头:4D546864 * * @param headerStr 文件头前6位 * @return 文件类型 */ public static String checkType (String headerStr) { String fileSuffix = null; switch (headerStr.substring(0, 6)) { case "FFD8FF": fileSuffix = "jpg"; break; case "89504E": fileSuffix = "png"; break; default: break; } return fileSuffix; } }
六、下章预告