15、HSSFWorkbook实现动态指定字段导出
一、自定义注解标记对象属性：
1、声明注解：
import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Target({ElementType.METHOD, ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) public @interface MyAnnotation { String name() default ""; }
2、注解标记：
import lombok.Data; @Data public class ResponseVO { @MyAnnotation(name = "UUID") private String uuid; @MyAnnotation(name = "姓名") private String name; @MyAnnotation(name = "状态") private String status; @MyAnnotation(name = "类型") private String type; }
二、动态指定字段导出：
1、相关依赖：
<!--POI相关依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2、通用动态字段导出工具类：
方式一：
import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.*; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.NumberFormat; import java.util.Date; import java.util.List; @Slf4j public class ExportUtil { /** * 在调用generateResponseEntity(...)前调用此方法可扩充Excel格式大小，最大扩充长度为整数长度 * * 解决：java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters * * 局限：当单元格数据大于限制任然会抛出异常，可以对单元格数据进行切割加列或加行处理 * */ public static void resetCellMaxTextLength() { SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007; if (Integer.MAX_VALUE != excel2007.getMaxTextLength()) { Field field; try { field = excel2007.getClass().getDeclaredField("_maxTextLength"); field.setAccessible(true); field.set(excel2007,Integer.MAX_VALUE); } catch (Exception e) { e.printStackTrace(); } } } /** * @Param data 数据列表 * @param title 表格的名字 * @param fieldNames 导出的字段名(前端传字段名 转成 中文描述) * @param classzz 列表相应实体类 */ public static ResponseEntity<byte[]> generateResponseEntity(List<?> data, String title, String[] fieldNames, Class classzz) { //写数据 HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); headers.setContentDispositionFormData("attachment", "attachment.xlsx"); byte[] bytes = new byte[]{}; try { log.info("开始导出数据"); bytes = getDownloadeExcel(title, fieldNames, data, classzz); } catch (Exception e) { e.printStackTrace(); } return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED); } private static byte[] getDownloadeExcel(String title, String[] heards, List<?> list, Class classzz) throws Exception { //单元格大小 Integer max = 32700; // 创建excel工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 创建excel表 XSSFSheet sheet = workbook.createSheet(title); // 创建标题行 XSSFRow titleRow = sheet.createRow(0); // 标题行, 从0行开始，写标题内容 for (int i = 0; i < heards.length; i++) { String s = heards[i]; XSSFCell cell = titleRow.createCell(i); //利用自定义注解方式获取获取本类中指定的属性对象信息 Field declaredField = classzz.getDeclaredField(s); MyAnnotation annotation = declaredField.getAnnotation(MyAnnotation.class); //列名 cell.setCellValue(annotation.name()); } // 数据行,从1行开始,共list.size行 for (int i = 1; i <= list.size(); i++) { // 创建行 XSSFRow listRow = sheet.createRow(i); // 数据列，单元格从0开始，共heards.length列 for (int j = 0; j < heards.length; j++) { // 创建数据单元格 XSSFCell listCell = listRow.createCell(j); // 通过反射的方式，将heards元素通过字符串拼接的方式，拼接出实体类相对应的get方法; String methodName = "get" + heards[j].substring(0, 1).toUpperCase() + heards[j].substring(1); try { // 通过反射拿到类对象，再获取类对象的额methodName这个方法 Method declaredMethod = classzz.getDeclaredMethod(methodName, null); // 通过invoke提交对象，执行declaredMethod这个方法 // 从List<Student>集合中取出list.get(i - 1)的methodName属性的值; Object result = declaredMethod.invoke(list.get(i - 1)); //判断返回值的类型 if (result instanceof Date) { //填写日期格式内容 XSSFDataFormat dataFormat = workbook.createDataFormat(); short format = dataFormat.getFormat("yyyy-MM-dd"); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setDataFormat(format); listCell.setCellStyle(cellStyle); } else if (result instanceof Double){ NumberFormat nf = NumberFormat.getInstance(); //保留小数位2位 nf.setMaximumFractionDigits(2); //是否保留千分位 nf.setGroupingUsed(true); listCell.setCellValue(nf.format(result)); }else if(result instanceof BigDecimal){ NumberFormat nf = NumberFormat.getInstance(); //保留小数位2位 nf.setMaximumFractionDigits(2); //是否保留千分位 nf.setGroupingUsed(true); listCell.setCellValue(nf.format(result)); // }else if (result instanceof String){ // //以下解决：java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters // //自动扩格，（适用最后一列） // if(result.toString() == null || result.toString().length() < max){ // //设置内容 // listCell.setCellValue(result == null ? "" : result.toString()); // }else { // int b = 0; // int num = result.toString().length() / max; // for (int a = 0; a < num; a++) { // XSSFCell addCell = listRow.createCell(j+a); // addCell.setCellValue(result.toString().substring(a * max, max * (a + 1))); // b++; // } // int extra = result.toString().length() % max; // if (extra > 0) { // XSSFCell addCell = listRow.createCell(j+b); // addCell.setCellValue(result.toString().substring(b * max, b * max + extra)); // } // } }else { //设置内容 listCell.setCellValue(result == null ? "" : result.toString()); } } catch (NoSuchMethodException e) { e.printStackTrace(); } } } return getNewBytes(workbook); } private static byte[] getNewBytes(Workbook wb) throws IOException { ByteArrayOutputStream baos = new ByteArrayOutputStream(); try { wb.write(baos); } catch (IOException e) { e.printStackTrace(); } finally { baos.close(); } return baos.toByteArray(); } }
方式二：
import com.alibaba.fastjson.JSONObject; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.*; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.lang.reflect.Field; import java.util.List; import java.util.Map; @Slf4j public class ExportUtil { /** * 在调用generateResponseEntity(...)前调用此方法可扩充Excel格式大小，最大扩充长度为整数长度 * * 解决：java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters * * 局限：当单元格数据大于限制任然会抛出异常，可以对单元格数据进行切割加列或加行处理 * */ public static void resetCellMaxTextLength() { SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007; if (Integer.MAX_VALUE != excel2007.getMaxTextLength()) { Field field; try { field = excel2007.getClass().getDeclaredField("_maxTextLength"); field.setAccessible(true); field.set(excel2007,Integer.MAX_VALUE); } catch (Exception e) { e.printStackTrace(); } } } /** * @Param data 数据列表 * @param title 表格的名字 * @param fieldNames 导出的字段名(前端传字段名 转成 中文描述) * @param classzz 列表相应实体类 */ public static ResponseEntity<byte[]> generateResponseEntity(List<?> data, String title, String[] fieldNames, Class classzz) { //写数据 HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); headers.setContentDispositionFormData("attachment", "attachment.xlsx"); byte[] bytes = new byte[]{}; try { bytes = getDownloadeExcel(title, fieldNames, data, classzz); } catch (Exception e) { e.printStackTrace(); } return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED); } private static byte[] getDownloadeExcel(String title, String[] heards, List<?> list, Class classzz) throws Exception { // 创建excel工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 创建excel表 XSSFSheet sheet = workbook.createSheet(title); // 创建标题行 XSSFRow titleRow = sheet.createRow(0); // 标题行写内容 for (int i = 0; i < heards.length; i++) { String s = heards[i]; XSSFCell cell = titleRow.createCell(i); //获取本类中指定的属性对象 Field declaredField = classzz.getDeclaredField(s); MyAnnotation annotation = declaredField.getAnnotation(MyAnnotation.class); cell.setCellValue(annotation.value()); } // 数据行,从1行开始,共list.size行 for (int i = 1; i <= list.size(); i++) { //转换当前数据 Map map = JSONObject.parseObject(JSONObject.toJSONString(list.get(i - 1)), Map.class); // 创建行 XSSFRow listRow = sheet.createRow(i); // 数据行单元格,从0开始，共heards.length列 for (int j = 0; j < heards.length; j++) { // 创建数据单元格 XSSFCell listCell = listRow.createCell(j); String key = heards[j]; Object result = map.get(key); //设置内容 listCell.setCellValue(result == null ? "" : result.toString()); } } return getNewBytes(workbook); } private static byte[] getNewBytes(Workbook wb) throws IOException { ByteArrayOutputStream baos = new ByteArrayOutputStream(); try { wb.write(baos); } catch (IOException e) { e.printStackTrace(); } finally { baos.close(); } return baos.toByteArray(); } }
3、应用：
