1、项目导出用Excel,制杖产品要求把导出参数全部去掉,导致导出时会导出全表,表数据千万级
2、引依赖
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.8</version>
</dependency>
3、创建导出XXXExportVO 本代码采用反射读取ExcelProperty注解获取表头以及表字段属性
@Data
public class XXXExportVO implements Serializable {
@ExcelProperty(value = "序号")
private Integer rowNum;
//.
//.
// .后面还有多个字段,字段的顺序就是导出列表头的顺序
}
4、创建工具类ExportCsvUtil
public class ExportCsvUtil {
//日志类
private static XXXXSLogger logger;
/**
*参数详解:response响应对象,fileName生成的文件名,entity 导出类,dataList查询出来的数据
*/
public static void writeCSV2(HttpServletResponse response, String fileName, Class<?> entity, List<?> dataList) throws NoSuchFieldException {
//字段属性-->rowNum
List<String> columns = Lists.newArrayList();
//表头-->序号
List<String> titleName = Lists.newArrayList();
Field[] declaredFields = entity.getDeclaredFields();
for (Field item : declaredFields) {
String name = item.getName();
columns.add(name);
Annotation[] annotatedType = item.getAnnotatedType().getAnnotations();
System.out.println(annotatedType);
ExcelProperty annotation = item.getAnnotation(ExcelProperty.class);
String[] value = annotation.value();
titleName.add(value[0]);
}
//转换成数组
String[] strings = titleName.stream().toArray(String[]::new);
String[] strings1 = columns.stream().toArray(String[]::new);
OutputStream out = null;
try {
//设置响应头、编码、文件名
out = response.getOutputStream();
response.setContentType("application/CSV;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
OutputStreamWriter osw = new OutputStreamWriter(out, "UTF-8");
//追加BOM标识
osw.write(new String(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF}));
//写入标题
CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(strings);
CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);
//组装行数据
for (int index = 0; index < dataList.size(); index++) {
Object obj = dataList.get(index);
Class clazz = obj.getClass();
String str[] = new String[strings1.length];
for (int columnIndex = 0; columnIndex < strings1.length; columnIndex++) {
String title = strings1[columnIndex].trim();
if (!"".equals(title)) {
// 获取返回类型
String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1);
String methodName = "get" + UTitle;
Method method = clazz.getDeclaredMethod(methodName);
String returnType = method.getReturnType().getName();
Object object = method.invoke(obj);
//获取到数据
String data = method.invoke(obj) == null ? "" : object.toString();
//判断时间格式数据不为空才格式化,要不报错了
if (data!="" && Date.class.getName().equals(returnType)) {
str[columnIndex] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object);
} else {
//组装数据
str[columnIndex] = data;
}
}
}
csvPrinter.printRecord(str);
}
csvPrinter.flush();
csvPrinter.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.flush();
out.close();
} catch (IOException e) {
logger.error("导出Csv异常");
}
}
}
}
}
5、查询出的类要与导出类一致
/**
*list为List<XXXExportVO> 并且 XXXExportVO对象不能存在没有@ExcelProperty注解的属性字段, 会报错,我就是懒得再加验证就直接写个导出VO了
*/
ExportCsvUtil.writeCSV2(response,fileName,XXXExportVO.class,list);
6、其他业务代码自己干吧
发现几十万数据跑不过来了,随即切换到另一种写法了
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>5.6</version>
</dependency>
public static void writeCSV1(HttpServletResponse response, String fileName, Class<?> entity, List<?> dataList) throws NoSuchFieldException {
List<String> columns = Lists.newArrayList();
List<String> titleName = Lists.newArrayList();
Field[] declaredFields = entity.getDeclaredFields();
for (Field item : declaredFields) {
String name = item.getName();
columns.add(name);
ExcelProperty annotation = item.getAnnotation(ExcelProperty.class);
String[] value = annotation.value();
titleName.add(value[0]);
}
String[] strings1 = columns.stream().toArray(String[]::new);
try {
response.setContentType("application/CSV;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
PrintWriter osw = response.getWriter();
osw.write(new String(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF}));
ColumnPositionMappingStrategy<T> mapper = new ColumnPositionMappingStrategy<>();
mapper.setType((Class<? extends T>) entity);
mapper.setColumnMapping(strings1);
CSVWriter csvWriter = new CSVWriter(response.getWriter());
String[] hearders = titleName.stream().toArray(String[]::new);
csvWriter.writeNext(hearders);
StatefulBeanToCsv beanToCsv = new StatefulBeanToCsvBuilder(osw).withMappingStrategy(mapper).build();
beanToCsv.write(dataList);
csvWriter.close();
osw.close();
} catch (Exception e) {
e.printStackTrace();
}
}