第一步引用包,去百度一个吧,我就不放了,下面都是对应的逻辑。
第二步创建实体类,默认是导出全部的实体字段数据,
@ApiModel("导出响应实体")
@Data
public class ExportBillListResponse implements Serializable {
@ExcelProperty(value = "姓名")
private String username;
@ExcelProperty(value = "手机号")
private String mobile;
@ExcelProperty(value = "金额")
private Double totalAmount;
}
第三步创建requst请求类
@ApiModel("导出请求")
@Data
public class TenantBillListRequest{
@ApiModelProperty(value = "导出所需字段名",required = true)
private String fieldColumn;
}
第四步Controller请求以及service(service层我就不放全部代码了,放了对应的请求方法),implservice代码
@ApiOperation(value = "导出账单")
@PostMapping("/exportBill")
public void exportBill(@RequestBody TenantBillListRequest request){
tBillService.exportBill(request);
}
//导出账单
Result<String> exportBill(TenantBillListRequest request);
//导出账单
@Override
public Result<String> exportBill(TenantBillListRequest request){
if(request.getFieldColumn().length() <=0 || request.getFieldColumn().isEmpty() || request.getFieldColumn() == null){
throw new IllegalArgumentException("至少导出一个字段");
}
String[] stringList = request.getFieldColumn().split(",");
// 获取前端选择的导出指定属性
Set<String> column = Arrays.stream(stringList).collect(Collectors.toSet());
String str = Joiner.on(",").join(stringList);
str.replace("\"","");
//查询数据
List<ExportBillListResponse> res = tBillDao.excelLists(str,request.getContractType(),request.getState(),request.getBillType(),request.getText(),request.getStartTime(), request.getEndTime());
try {
String filename = "ceshi";
EasyExcelUtil.writeExcel(httpServletresponse, list, filename, ExportBillListResponse.class,column);
return Result.success(200,"导出成功");
} catch (Exception e) {
throw new IllegalArgumentException("数据导出失败");
}
}
第五步 重要的导出类 EasyExcelUtil (这里面可以限制一个表格数量,如果数据量大可分多个sheet导出,自己研究吧)
public class EasyExcelUtil {
private static final Logger log = LoggerFactory.getLogger(EasyExcelUtil.class);
private static final int MAXROWS = 100000;
/**
* 获取默认表头内容的样式
* @return
*/
private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){
/** 表头样式 **/
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景色(浅灰色)
// 可以参考:https://www.cnblogs.com/vofill/p/11230387.html
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 字体大小
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
/** 内容样式 **/
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 内容字体样式(名称、大小)
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置内容垂直居中对齐
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置内容水平居中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 头样式与内容样式合并
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* @author weiguorong
* @createDate 2020-11-16
* @param response
* @param data 查询结果
* @param fileName 导出文件名称
* @param clazz 映射实体class类
* @param <T> 查询结果类型
* @throws Exception
*/
public static<T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class<T> clazz,Set<String> includeColumnFiledNames) throws Exception {
long exportStartTime = System.currentTimeMillis();
log.info("报表导出Size: "+data.size()+"条。");
List<List<T>> lists = SplitList.splitList(data,MAXROWS); // 分割的集合
RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
HttpServletRequest request = (HttpServletRequest) requestAttributes.resolveReference(RequestAttributes.REFERENCE_REQUEST);
String method = request.getMethod();
OutputStream out = null;
//区分请求,导出设置的头不一样
if(method.equals("GET")){
out = getOutputStream(fileName, response);
}
out = exportExcelWithBlobStream(fileName, response);
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out,clazz).includeColumnFiledNames(includeColumnFiledNames).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
ExcelWriter excelWriter = excelWriterBuilder.build();
ExcelWriterSheetBuilder excelWriterSheetBuilder;
WriteSheet writeSheet;
for (int i =1;i<=lists.size();i++){
excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
excelWriterSheetBuilder.sheetNo(i);
excelWriterSheetBuilder.sheetName("sheet"+i);
writeSheet = excelWriterSheetBuilder.build();
excelWriter.write(lists.get(i-1),writeSheet);
}
out.flush();
excelWriter.finish();
out.close();
System.out.println("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
// response.setContentType("application/vnd.ms-excel"); // .xls
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // .xlsx
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
return response.getOutputStream();
}
/**
* blob方式 POST请求返回
*/
private static OutputStream exportExcelWithBlobStream(String excelName,HttpServletResponse response) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
String fileName = URLEncoder.encode(excelName, "UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")+".xlsx");
return response.getOutputStream();
} catch (IOException e) {
log.error(">>> 导出数据异常:{}", e.getMessage());
throw new IllegalArgumentException("导出数据异常");
}
}
}
导出结果案例1
导出结果案例2
因为考虑前端传字段是无序的,所以要对应上数据。
没了,还看啥呢,复制粘贴试试啊