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 "";
}
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;
}
<!--POI相关依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
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();
}
}
