头的数据格式设置为 List<List<String>> 并且具体的数据格式也设置为List<List<String>>具体的数据和表头一一对应
public <T> void exportTerminalnew(ExportHandlerDS handler, List<String> excelHeaders, String staffCode, FastFileStorageClient fastFileClient, String fdfsUrl, IReportService reportExportDetailService, Long id) { ...... //根据前端选择的字段生成表头 List<List<String>> head = head(excelHeaders); WriteSheet writeSheet = EasyExcel.writerSheet().head(head). //设置样式 registerWriteHandler(setExcelStyle()). //自定义列宽 registerWriteHandler(new Custemhandler()). //registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()). //固定表头 registerWriteHandler(new CustomSheetWriteHandler()). //动态合并单元格 registerWriteHandler(new ExcelCellMergeStrategy()). build(); //设置列宽？ // writeSheet.setColumnWidthMap(); ExcelWriter excelWriter = EasyExcel.write(baos).excelType(ExcelTypeEnum.XLSX).build(); WriteTable build = EasyExcel.writerTable(0).needHead(false).build(); ..... //根据传入的字段筛选导出哪些数据 List<List<String>> body = getBody(list, excelHeaders); excelWriter.write(body,writeSheet,build); excelWriter.finish(); StorePath storePath=null; if(!selImage){ final ByteArrayInputStream byteIs = new ByteArrayInputStream(baos.toByteArray()); //生成的文件上传到fast DFS系统上 storePath = fastFileClient.uploadFile(byteIs, byteIs.available(), ExcelTypeEnum.XLSX.getValue().substring(1), null); }else { StringBuilder fileNewPath=new StringBuilder(); final ByteArrayInputStream byteIs = new ByteArrayInputStream(getFile(baos,fileNewPath,list,staffCode)); //生成的文件上传到fast DFS系统上 storePath = fastFileClient.uploadFile(byteIs, byteIs.available(), "zip", null); // 删除文件夹及文件 path.add(fileNewPath.toString()); } String fullPath = storePath.getFullPath(); filePath = fdfsUrl + "/" + fullPath; // 1: 执行成功 -1: 执行失败 if (StringUtils.isNotBlank(fullPath)) { reportExportDetailDTO.setStatus(1); reportExportDetailDTO.setFilePath(filePath); } else { reportExportDetailDTO.setStatus(-1); } } catch (Exception e) { reportExportDetailDTO.setStatus(-1); log.error("导出Excel报表文件出错",e); } finally { if(CollectionUtils.isNotEmpty(path)){ //删除文件 delFile(path); log.error("要删除的目录地址："+path); } reportExportDetailDTO.setId(id); reportExportDetailService.updateExport(reportExportDetailDTO); } }
** * 设置excel样式 * @return */ private HorizontalCellStyleStrategy setExcelStyle(){ // 表头策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); WriteFont writeFont=new WriteFont(); writeFont.setFontName("宋体"); writeFont.setFontHeightInPoints((short) 11); //加粗 writeFont.setBold(true); headWriteCellStyle.setWriteFont(writeFont); //内容样式策略 WriteCellStyle contextWriteeCellStyle=new WriteCellStyle(); //设置水平居中 contextWriteeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,contextWriteeCellStyle); return horizontalCellStyleStrategy; }
/**
easyexcel提供的自动列宽不能满足需求，只能自己根据业务要求重写
* @description: EasyExcel设置列宽 * @author: qiuqingxin * @create: 2020-08-07 15:49 **/ public class Custemhandler extends AbstractColumnWidthStyleStrategy { private static final int MAX_COLUMN_WIDTH = 50; public Custemhandler() { } @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { int columnWidth=20; if (isHead ) { if (cell.getStringCellValue().equals("照片类型")){ columnWidth=20; } if (cell.getStringCellValue().equals("当前位置")){ columnWidth=50; } if (cell.getStringCellValue().equals("备注")){ columnWidth=30; } ...... writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } }
/** * @description: easyExcel拦截器 * @author: qiuqingxin * @create: 2020-06-16 11:29 **/ public class CustomSheetWriteHandler implements SheetWriteHandler { @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { //固定表头 writeSheetHolder.getSheet().createFreezePane(0,1,0,1); } } /** * @description: 自定义合并单元格 * @author: qiuqingxin * @create: 2020-08-07 16:38 **/ public class ExcelCellMergeStrategy implements CellWriteHandler { private int fileColumnIndex = -1; private int imageColumnIndex = -1; private String regEx="[^0-9]"; @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } /** 每在excel中写入一个单元格数据都会调用次方法 **/ @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { if (isHead) { if ("附件".equals(cell.getStringCellValue())) { fileColumnIndex = cell.getColumnIndex(); return; } if ("现场照片".equals(cell.getStringCellValue())) { imageColumnIndex = cell.getColumnIndex(); return; } } else { if (imageColumnIndex != -1) { //找到附件所在的列 if (cell.getColumnIndex() == imageColumnIndex + 1) { // Sheet sheet = writeSheetHolder.getSheet(); Row row = cell.getRow(); Cell cell1 = row.getCell(imageColumnIndex); Cell cell2 = row.getCell(imageColumnIndex + 1); String imageUrl = cell2.getStringCellValue(); String imageName = cell1.getStringCellValue(); if (!isZero(imageName)) { cell1.setCellFormula( "HYPERLINK("" + imageUrl + "","" + imageName + "")"); }else { cell1.setCellValue(""); } row.removeCell(cell2); } } if (fileColumnIndex != -1) { if (null == cell.getRow().getCell(fileColumnIndex)) { //找到附件所在的列 if (cell.getColumnIndex() == fileColumnIndex + 2) { // Sheet sheet = writeSheetHolder.getSheet(); Row row = cell.getRow(); Cell cell3 = row.createCell(fileColumnIndex); Cell cell1 = row.getCell(fileColumnIndex + 1); Cell cell2 = row.getCell(fileColumnIndex + 2); String fileUrl = cell2.getStringCellValue(); cell3.setCellFormula("HYPERLINK("" + fileUrl + "","" + cell1.getStringCellValue() + "")"); row.removeCell(cell1); row.removeCell(cell2); } } else { if (cell.getColumnIndex() == fileColumnIndex + 1) { // Sheet sheet = writeSheetHolder.getSheet(); Row row = cell.getRow(); Cell cell1 = row.getCell(fileColumnIndex ); Cell cell2 = row.getCell(fileColumnIndex + 1); String fileUrl = cell2.getStringCellValue(); cell1.setCellFormula("HYPERLINK("" + fileUrl + "","" + cell1.getStringCellValue() + "")"); row.removeCell(cell2); } } } } } private boolean isZero(String value){ Pattern p = Pattern.compile(regEx); Matcher m = p.matcher(value); String trim = m.replaceAll("").trim(); return trim.equals("0")?true:false; } } private List<List<String>> getBody(List<DsTerminalUploadExcel> list3,List<String> excelHeaders )throws Exception{ Map<Integer,String> map=new HashMap<>(); List<List<String>> lists=new ArrayList<>(); Integer index=1; for (String excelHeader : excelHeaders) { map.put(index++,excelHeader); } int size = map.size(); for (DsTerminalUploadExcel dsTerminalUploadExcel : list3) { List<String> data=new ArrayList<>(); for (int i =0;i<size;i++){ if (map.get(i+1).equals("image")){ String value2=dsTerminalUploadExcel.getImagePaths()!=null?dsTerminalUploadExcel.getImagePaths().size()+"张)":"0张)"; value2="点击查看图片("+value2; data.add(value2); value2=null==dsTerminalUploadExcel.getStaffName()? "/"+dsTerminalUploadExcel.getReportDate().substring(0,10)+"_"+dsTerminalUploadExcel.getId(): "/"+dsTerminalUploadExcel.getStaffName()+"_"+dsTerminalUploadExcel.getReportDate().substring(0,10)+"_"+dsTerminalUploadExcel.getId(); value2="images/"+value2; data.add(value2); }else { String value=(String)getValue(dsTerminalUploadExcel,map.get(i+1)); data.add(value); if (map.get(i+1).equals("fileName")){ String value2=(String)getValue(dsTerminalUploadExcel,"filePath"); data.add(value2); } } } lists.add(data); } return lists; } //反射调用get方法 public static Object getValue(Object dto,String name) throws Exception { String s = name.substring(0, 1).toUpperCase(); String substring = name.substring(1); Method method = dto.getClass().getMethod("get" + s + substring); return method.invoke(dto); } //将数据生成字节数组 private byte[] getFile(ByteArrayOutputStream baos,StringBuilder fileNewPath,List<DsTerminalUploadExcel> list3, String staffCode) throws Exception { // String path = "D:\sss\"+staffCode+"_"+(new SimpleDateFormat("yyMMddHHmmss").format(new Date()))+"\"; String path = "/data/file/"+staffCode+"_"+(new SimpleDateFormat("yyMMddHHmmss").format(new Date()))+"/"; log.error("获取的路径:"+path); String fileName=""+System.currentTimeMillis(); String filePath=path+fileName; fileNewPath.append(path); File file = new File(filePath); if (!file.exists()){ file.mkdirs(); } FileOutputStream fileOutputStream=new FileOutputStream(filePath+"/"+System.currentTimeMillis()+ExcelTypeEnum.XLSX.getValue()); fileOutputStream.write(baos.toByteArray()); fileOutputStream.close(); genImages(list3,filePath); log.error("filePath"+filePath);
return setZip(filePath,path,fileName); } private void genImages(List<DsTerminalUploadExcel> list3,String filePath) throws Exception { File file = new File(filePath + "/images"); if (!file.exists()){ file.mkdirs(); } for (DsTerminalUploadExcel dsTerminalUploadExcel : list3) { List<String> imagePaths = dsTerminalUploadExcel.getImagePaths(); if (null==imagePaths||imagePaths.size()==0){ continue; } String imagepath=null==dsTerminalUploadExcel.getStaffName()? "/"+dsTerminalUploadExcel.getReportDate().substring(0,10)+"_"+dsTerminalUploadExcel.getId(): "/"+dsTerminalUploadExcel.getStaffName()+"_"+dsTerminalUploadExcel.getReportDate().substring(0,10)+"_"+dsTerminalUploadExcel.getId(); imagepath=file.getPath()+imagepath; File file1 = new File(imagepath); if (!file1.exists()) file1.mkdirs(); for (String imagePath : imagePaths) { String ext = imagePath.substring(imagePath.lastIndexOf("/") + 1); File file2 = new File(file1.getPath() + "/" + ext); FileOutputStream fileOutputStream = new FileOutputStream(file2); BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(fileOutputStream); //发送HTTP请求并指定返回的数据为byte数组 RestTemplate restTemplate = new RestTemplate(); byte[] forObject = restTemplate.getForObject(imagePath, byte[].class); bufferedOutputStream.write(forObject); bufferedOutputStream.flush(); fileOutputStream.close(); bufferedOutputStream.close(); } } }
private byte[] setZip(String allPath, String path, String fileName) throws Exception {
String newzipPath = path + fileName + ".zip";
File file = new File(newzipPath);
FileOutputStream fileOutputStream = new FileOutputStream(file);
//压缩工具类
byte[] aByte = ZipUtils.getByte(allPath, fileOutputStream, true, file);
fileOutputStream.close();
return aByte;
}