1.普通单个Excel导出
1.Excel表中的字段与要导出的数据一致
public class **OrderDTO** {
@ExcelProperty(value = {"Excel表名", "Excel表字段"})
@ColumnWidth(20)列宽
private String orderSn;
}
2.Excel工具类编写
public class ExcelUtils {
/**
* 导出excel核心方法
*
* @param
*/
public static void export(HttpServletResponse response, Class head, List data, String sheetName) throws IOException {
//给定导出实体类
EasyExcel.write(response.getOutputStream(), head)
//给定工作表名称
.sheet(sheetName)
//给定导出数据
.doWrite(data);
}
/**
* 设置请求头、文件名
*
* @param fileName excel文件名
*/
public static void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
//编码设置成UTF-8,excel文件格式为.xlsx
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
// 这里URLEncoder.encode可以防止中文乱码 和easyexcel本身没有关系
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
}
}
3.实现类编写ServiceImpl
@Override
public void getEcsResourceExcel(HttpServletResponse response) throws IOException {
List<EcsPreviewModel> ecsPreviewList = ecsService.getEcsPreviewList();
String fileName = URLEncoder.encode("ECS", "UTF-8");
ExcelUtils.setResponse(response, fileName);
ExcelUtils.export(response, EcsPreviewModel.class, ecsPreviewList, "ECS");
}
Excel含多个sheel表的导出
3.实现类编写ServiceImpl
@Override
public void getBatchExcel(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
List<RPreviewModel> rPreviewList = rService.getRPreviewList();
List<SPreviewModel> sPreviewList = sService.getSPreviewList();
List<OPreviewModel> oPreviewList = oService.getOPreviewList();
List<EPreviewModel> ePreviewList = eService.getEPreviewList();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
try {
String fileName = URLEncoder.encode("资源预览表", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//新建ExcelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
//获取sheet0对象
WriteSheet eSheet = EasyExcel.writerSheet(0, "sheel名称").head(EPreviewModel.class).build();
//向sheet0写入数据 传入空list这样只导出表头
excelWriter.write(ePreviewList,eSheet);
WriteSheet rSheet = EasyExcel.writerSheet(1, "sheel名称").head(RPreviewModel.class).build();
excelWriter.write(rPreviewList,rSheet);
WriteSheet sSheet = EasyExcel.writerSheet(2, "sheel名称").head(SPreviewModel.class).build();
excelWriter.write(sPreviewList,sSheet);
WriteSheet oSheet = EasyExcel.writerSheet(3, "sheel名称").head(OPreviewModel.class).build();
excelWriter.write(oPreviewList,oSheet);
//关闭流
excelWriter.finish();
} catch (IOException e) {
log.error("导出异常{}", e.getMessage());
}
}
3.导出多个Excel并压缩
3.导出为Excel表并进行压缩(ServiceImpl)
@Override
@Transactional(rollbackFor = Exception.class)
public void export(List<String> list, HttpServletRequest request, HttpServletResponse response) {
String dir = String.valueOf(System.currentTimeMillis());
//1.生成excel文件
createExcel(list, dir);
//2.压缩文件
try {
ZipUtil.zip(filePath + File.separator + tempDir);
} catch (IOException e) {
log.info("压缩文件失败!", e);
throw new RuntimeException("压缩文件失败!");
}
//3.下载文件
File file = new File(filePath + File.separator + tempDir + ".zip");
try {
response = DownLoadHelper.filenameEncode(tempDir + File.separator + ".zip", request, response);
FileUtils.copyFile(file , response.getOutputStream());
} catch (IOException e) {
log.info("下载文件失败!", e);
throw new RuntimeException("下载文件失败!");
} finally {
try {
FileUtils.forceDelete(new File(filePath + File.separator + tempDir));
FileUtils.forceDelete(file );
} catch (IOException e) {
log.info("删除文件失败!");
}
}
}
2.创建excel文件
private void createExcel(List<String> list, String tempDir) {
**//所有要导出的商品信息**
List<OrderDTO> procurementProductDTOS = procurementOrderMapper.productByList(list);
//订单去重
data1 = data1.stream().distinct().collect(Collectors.toList());
//合计行
InvoiceProductDTO productDTO = new InvoiceProductDTO();
productDTO.setIdx("合计");
.....
productDTO.setEstimateAmount(totalAmount.toString());
data2.add(productDTO);
Sheet sheet = new Sheet(n + 1, 0);
sheet.setSheetName(carNameList.get(n));
Table table1 = new Table(1);
table1.setClazz(InvoiceOrderDTO.class);
writer.write(data1, sheet, table1);
Table table2 = new Table(2);
table2.setClazz(InvoiceProductDTO.class);
writer.write(data2, sheet, table2);
}
writer.finish();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
});
}
时间转化将毫秒值转化为天时分秒
final long milliseconds = 5478965412358l;
final long day = TimeUnit.MILLISECONDS.toDays(milliseconds);
final long hours = TimeUnit.MILLISECONDS.toHours(milliseconds)
- TimeUnit.DAYS.toHours(TimeUnit.MILLISECONDS.toDays(milliseconds));
final long minutes = TimeUnit.MILLISECONDS.toMinutes(milliseconds)
- TimeUnit.HOURS.toMinutes(TimeUnit.MILLISECONDS.toHours(milliseconds));
final long seconds = TimeUnit.MILLISECONDS.toSeconds(milliseconds)
- TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(milliseconds));
final long ms = TimeUnit.MILLISECONDS.toMillis(milliseconds)
- TimeUnit.SECONDS.toMillis(TimeUnit.MILLISECONDS.toSeconds(milliseconds));
System.out.println("milliseconds :-" + milliseconds);
System.out.println(String.format("%d Days %d Hours %d Minutes %d Seconds %d Milliseconds", day, hours, minutes, seconds, ms));