数据导出的基本思路
通过查询数据库中的数据,生成excel文件
@PostMapping("/exportExcelAll")
public AjaxResult exportExcelAll(@RequestBody QueryBodys queryBodys,HttpServletResponse response)throws Exception{
List<String> ids = zhUserService.getSonDepartment(queryBodys.getParentDepartment());
List<CxdckExport> exportAllData = zhCxdckService.getExportAllData(queryBodys, ids);
ExcelUtils excelUtils = new ExcelUtils();
SXSSFWorkbook hssfWorkbook = new SXSSFWorkbook(60000);
hssfWorkbook = excelUtils.exportExcel2(exportAllData, CxdckExport.class);
// 文件默认下载到桌面
String path = File.separator+"车险代查勘"+DateUtil.format(new Date(),"yyyy年MM月dd日HH时mm分ss秒")+".xls";
File f = new File(path);
if(f.exists()){
f.mkdirs();
}
OutputStream stream = new FileOutputStream(f);
hssfWorkbook.write(stream);
hssfWorkbook.close();
stream.close();
System.out.println("导出文件已经结束");
return AjaxResult.success(path);
}
导出方法
public <T> HSSFWorkbook exportExcel1(List<T> dataList, Class<T> classType,String sheetName) throws Exception {
Field[] declaredFields = classType.getDeclaredFields();
List<String> headers = new LinkedList<>();
List<String> variables = new LinkedList<>();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建sheet
HSSFSheet hssfSheet = hssfWorkbook.createSheet();
HSSFRow row = hssfSheet.createRow(0);
hssfWorkbook.setSheetName(0, sheetName);
int headerIndex = 0;
//创建导入实体的字段名称
for (Field field : declaredFields) {
if (field.isAnnotationPresent(ExcelHeader.class)) {
ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
headers.add(annotation.name());
row.createCell(headerIndex).setCellValue(annotation.name());
variables.add(field.getName());
headerIndex += 1;
}
}
//循环传入值
for (int i = 0; i < dataList.size(); i++) {
HSSFRow rowData = hssfSheet.createRow(i + 1);
T t = dataList.get(i);
Class<?> aClass = t.getClass();
for (int j = 0; j < variables.size(); j++) {
Field declaredField = aClass.getDeclaredField(variables.get(j));
declaredField.setAccessible(true);
String key = declaredField.getName();
Object o = declaredField.get(t);
if ("null".equals(String.valueOf(o))) {
rowData.createCell(j).setCellValue("");
} else {
rowData.createCell(j).setCellValue(String.valueOf(o));
}
}
}
}
hssfWorkbook.close();
return hssfWorkbook;
注解反射的使用
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelHeader {
String name() default "";
int columnIndex() default 0;
}
//导入数据的实体
@Data
public class CxdckExport {
/**
* id
*/
private Long cxdckId;
/**
* 委托方名称
*/
@ExcelHeader(name = "委托方")
private String cName;
/**
* 催收信息id
*/
private Long csInfoId;
/**
* 案件是否委托确认(0:是;1:否)
*/
private String caseClien;
@ExcelHeader(name = "案件是否委托确认")
private String caseClienName;
/**
* 案件是否可查询(0:是;1:否)
*/
private String caseCanQuery;
@ExcelHeader(name="案件是否可查询")
private String caseCanQueryName;
/**
* 不可查询原因
*/
@ExcelHeader(name="不可查询原因")
private String queryReson;
/**
* 结案情况
*/
@ExcelHeader(name="结案情况")
private String caseInfo;
/**
* 公估费录入情况
*/
@ExcelHeader(name="公估费录入情况")
private String ggInfo;
/**
* 公估发票接受情况
*/
@ExcelHeader(name="公估发票接受情况")
private String ggfpInfo;
/**
* 费用支付情况
*/
@ExcelHeader(name="费用支付情况")
private String costInfo;
/**
* 是否有跟踪信息信息
*/
private String caseTrack;
@ExcelHeader(name = "是否有跟踪信息")
private String caseTrackName;
/**
* 日常表id
*/
private Long dailyId;
/**
* 反馈内容
*/
@ExcelHeader(name="反馈内容")
private String feedback;
/**
* 委托单位名称
*/
@ExcelHeader(name = "委托单位名称")
private String clientDepartment;
/**
* 委托单位联系人姓名
*/
@ExcelHeader(name = "委托单位联系人姓名")
private String clientContactName;
/**
* 委托单位联系人电话
*/
@ExcelHeader(name = "委托单位联系人电话")
private String clientContactPhone;
/**
* 委托人姓名
*/
@ExcelHeader(name = "委托人姓名")
private String clientName;
/**
* 委托人身份证号码
*/
@ExcelHeader(name = "委托人身份证号")
private String clientIdCard;
/**
* 委托人电话号码
*/
@ExcelHeader(name = "委托人电话号码")
private String clientPhone;
/**
* 委托渠道名称
*/
@ExcelHeader(name = "委托渠道")
private String clientChannelName;
/**
* 业务来源部门名称
*/
@ExcelHeader(name = "业务来源部门")
private String businessSourceName;
/**
* 所属部门或事业部名称
*/
@ExcelHeader(name = "所属部门或事业部")
private String fromDepartmentName;
/**
* 业务处理部门名称
*/
@ExcelHeader(name = "业务处理部门")
private String businessProcessingDepartmentName;
/**
* 业务性质名称
*/
@ExcelHeader(name = "业务性质")
private String businessNatureName;
/**
* 案件编号
*/
@ExcelHeader(name = "案件编号")
private String caseNo;
/**
* 委托时间
*/
@ExcelHeader(name = "委托时间")
private String clientTime;
/**
* 报案号
*/
@ExcelHeader(name = "报案号")
private String toCaseNo;
/**
* 车牌号
*/
@ExcelHeader(name = "车牌号")
private String carNo;
/**
* 票据号
*/
@ExcelHeader(name = "票据号")
private String ticketNo;
/**
* 开票时间
*/
@ExcelHeader(name = "开票时间")
private String ticketTime;
/**
* 开票人名称
*/
@ExcelHeader(name = "开票人")
private String ticketPeopleName;
/**
* 开票金额
*/
@ExcelHeader(name = "开票金额")
private BigDecimal ticketAmount;
/**
* 开票单位
*/
@ExcelHeader(name = "开票单位")
private String ticketDepartment;
/**
* 开票状态名称
*/
@ExcelHeader(name = "开票状态")
private String ticketStatusName;
/**
* 到账金额
*/
@ExcelHeader(name = "到账金额")
private BigDecimal amountReceived;
/**
* 到账日期
*/
@ExcelHeader(name = "到账日期")
private String receivedDate;
/**
* 核销时间
*/
@ExcelHeader(name = "核销时间")
private String writeOffTime;
/**
* 催收日常序号
*/
@ExcelHeader(name="催收日常序号")
private String infoId;
/**
* 跟踪时间
*/
@ExcelHeader(name="跟踪时间")
private String trackTime;
/**
* 跟踪人
*/
@ExcelHeader(name="跟踪人")
private String track;
/**
* 催收日常反馈
*/
@ExcelHeader(name="催收日常反馈")
private String dailyFeedback;
/**
* 催收日常备注
*/
@ExcelHeader(name="催收日常备注")
private String note;
}
bug的解决
1.关于excel的单sheet最大限制65535行
通过循环多sheet解决
public <T> HSSFWorkbook exportExcel1(List<T> dataList, Class<T> classType,HSSFWorkbook hssfWorkbook) throws Exception {
Field[] declaredFields = classType.getDeclaredFields();
List<String> headers = new LinkedList<>();
List<String> variables = new LinkedList<>();
//HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
if(dataList.size()/50000 > 0){
for(int m = 1;m <= dataList.size()/50000+1;m++){
HSSFSheet hssfSheet = hssfWorkbook.createSheet();
HSSFRow row = hssfSheet.createRow(0);
// hssfWorkbook.setSheetName(0, sheetName);
int headerIndex = 0;
for (Field field : declaredFields) {
if (field.isAnnotationPresent(ExcelHeader.class)) {
ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
headers.add(annotation.name());
row.createCell(headerIndex).setCellValue(annotation.name());
variables.add(field.getName());
headerIndex += 1;
}
}
for (int i = 50000*(m-1); i < 50000*m && i<dataList.size(); i++) {
HSSFRow rowData;
if(m != 1){;
rowData = hssfSheet.createRow(i%(50000*(m-1)) + 1);}
else {
rowData = hssfSheet.createRow(i+1);
}
T t = dataList.get(i);
Class<?> aClass = t.getClass();
for (int j = 0; j < variables.size(); j++) {
Field declaredField = aClass.getDeclaredField(variables.get(j));
declaredField.setAccessible(true);
String key = declaredField.getName();
Object o = declaredField.get(t);
if ("null".equals(String.valueOf(o))) {
rowData.createCell(j).setCellValue("");
} else {
rowData.createCell(j).setCellValue(String.valueOf(o));
}
}
}
}
}else {
HSSFSheet hssfSheet = hssfWorkbook.createSheet();
HSSFRow row = hssfSheet.createRow(0);
// hssfWorkbook.setSheetName(0, sheetName);
int headerIndex = 0;
for (Field field : declaredFields) {
if (field.isAnnotationPresent(ExcelHeader.class)) {
ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
headers.add(annotation.name());
row.createCell(headerIndex).setCellValue(annotation.name());
variables.add(field.getName());
headerIndex += 1;
}
}
for (int i = 0; i < dataList.size(); i++) {
HSSFRow rowData = hssfSheet.createRow(i + 1);
T t = dataList.get(i);
Class<?> aClass = t.getClass();
for (int j = 0; j < variables.size(); j++) {
Field declaredField = aClass.getDeclaredField(variables.get(j));
declaredField.setAccessible(true);
String key = declaredField.getName();
Object o = declaredField.get(t);
if ("null".equals(String.valueOf(o))) {
rowData.createCell(j).setCellValue("");
} else {
rowData.createCell(j).setCellValue(String.valueOf(o));
}
}
}
}
hssfWorkbook.close();
return hssfWorkbook;
}
2.关于导入数据过多致使堆内存溢出
解决的办法是将HSSFWorkbook替换成SXSSFWorkbook,
//SXSSFWorkbook可以设置内存的最大阈值,内存中的对象数量最大值,超过这个值会生成一个临时文件存放到硬盘中,这样就不会报堆内存溢出异常
SXSSFWorkbook sx = new SXSSFWorkbook(60000);
修改代码如下
public <T> SXSSFWorkbook exportExcel2(List<T> dataList, Class<T> classType) throws Exception {
Field[] declaredFields = classType.getDeclaredFields();
List<String> headers = new LinkedList<>();
List<String> variables = new LinkedList<>();
SXSSFWorkbook hssfWorkbook = new SXSSFWorkbook();
if(dataList.size()/50000 > 0){
for(int m = 1;m <= dataList.size()/50000+1;m++){
SXSSFSheet hssfSheet = hssfWorkbook.createSheet();
SXSSFRow row = hssfSheet.createRow(0);
//hssfWorkbook.setSheetName(0, sheetName);
int headerIndex = 0;
for (Field field : declaredFields) {
if (field.isAnnotationPresent(ExcelHeader.class)) {
ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
headers.add(annotation.name());
row.createCell(headerIndex).setCellValue(annotation.name());
variables.add(field.getName());
headerIndex += 1;
}
}
for (int i = 50000*(m-1); i < 50000*m && i<dataList.size(); i++) {
SXSSFRow rowData;
if(m != 1){;
rowData = hssfSheet.createRow(i%(50000*(m-1)) + 1);}
else {
rowData = hssfSheet.createRow(i+1);
}
T t = dataList.get(i);
Class<?> aClass = t.getClass();
for (int j = 0; j < variables.size(); j++) {
Field declaredField = aClass.getDeclaredField(variables.get(j));
declaredField.setAccessible(true);
String key = declaredField.getName();
Object o = declaredField.get(t);
if ("null".equals(String.valueOf(o))) {
rowData.createCell(j).setCellValue("");
} else {
rowData.createCell(j).setCellValue(String.valueOf(o));
}
}
}
// hssfWorkbook.close();
}
}else {
SXSSFSheet hssfSheet = hssfWorkbook.createSheet();
SXSSFRow row = hssfSheet.createRow(0);
//hssfWorkbook.setSheetName(0, sheetName);
int headerIndex = 0;
for (Field field : declaredFields) {
if (field.isAnnotationPresent(ExcelHeader.class)) {
ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
headers.add(annotation.name());
row.createCell(headerIndex).setCellValue(annotation.name());
variables.add(field.getName());
headerIndex += 1;
}
}
for (int i = 0; i < dataList.size(); i++) {
SXSSFRow rowData = hssfSheet.createRow(i + 1);
T t = dataList.get(i);
Class<?> aClass = t.getClass();
for (int j = 0; j < variables.size(); j++) {
Field declaredField = aClass.getDeclaredField(variables.get(j));
declaredField.setAccessible(true);
String key = declaredField.getName();
Object o = declaredField.get(t);
if ("null".equals(String.valueOf(o))) {
rowData.createCell(j).setCellValue("");
} else {
rowData.createCell(j).setCellValue(String.valueOf(o));
}
}
}
// hssfWorkbook.close();
}
return hssfWorkbook;
}
至此问题解决,有问题求指出