@Override public void exportFile(String catalogIds,HttpServletResponse response) { String fileName = "导出文件信息列表.xls"; try { this.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); ExportExcelUtil eeu = new ExportExcelUtil(); HSSFWorkbook workbook = new HSSFWorkbook(); List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(); // 截取;之前的字符串 String fileIds = catalogIds.substring(0, catalogIds.indexOf(";")); if(!fileIds.equals("") && fileIds != null) {// 判断是否选择了文件选择的话进行打包压缩下载否则不执行 String[] fileIdsArray = fileIds.split(","); for(int j = 0; j < fileIdsArray.length; j++) { List<Map<String, Object>> map=catalogMapper.exportFiles(fileIdsArray[j]); resultList.addAll(map); } } // 截取;之后的字符串 String muluIds = catalogIds.substring(fileIds.length() + 1, catalogIds.length()); if(!muluIds.equals("") && muluIds != null) { String [] muluIdsArray = muluIds.split(","); for(int i = 0; i < muluIdsArray.length; i++) { List<Map<String, Object>> map=catalogMapper.exportFolders(muluIdsArray[i]); resultList.addAll(map); } } if (Objects.nonNull(resultList)&&resultList.size() > 0) { String a="文件名,文件路径,大小,上传人,上传时间"; String b="catalogName,catalogPath,fileSize,uploadUser,uploadTime"; String[] headers =a.split(","); String[] columns = b.split(","); String[][] content = new String[resultList.size()][headers.length]; for (int i = 0; i < resultList.size(); i++) { content[i] = new String[headers.length]; for (int j = 0; j <columns.length;j++ ) { content[i][j] = Objects.isNull(resultList.get(i).get(columns[j]))?null:resultList.get(i).get(columns[j]).toString(); } } eeu.exportExcel(workbook, 0,"文件信息", headers, content, os); } workbook.write(os); os.flush(); os.close(); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } //发送响应流方法 public void setResponseHeader(HttpServletResponse response, String fileName) { try { response.setContentType("application/octet-stream"); String file_name = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Access-Control-Expose-Headers", "Content-disposition"); response.setHeader("Content-disposition", "attachment;filename=" + file_name); } catch (Exception ex) { ex.printStackTrace(); } }
import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.List; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.springframework.core.io.ClassPathResource; /** * ExportExcelUtil<br> * * @author lijianjun <br> * @time 2021/12/6 16:23 <br> */ public class ExportExcelUtil { /** * * @param title 标题 * @param headers 表头 * @param values 表中元素 * @return */ public static HSSFWorkbook getHSSFWorkbook(String title, String headers[], String[][] values){ //创建一个HSSFWorkbook,对应一个Excel文件 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); //在workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet hssfSheet = hssfWorkbook.createSheet(title); //创建标题合并行 hssfSheet.addMergedRegion(new CellRangeAddress(0,(short)0,0,(short)headers.length - 1)); //设置标题样式 HSSFCellStyle style = hssfWorkbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); //设置居中样式 style.setVerticalAlignment(VerticalAlignment.CENTER); //设置标题字体 Font titleFont = hssfWorkbook.createFont(); titleFont.setFontHeightInPoints((short) 28); style.setFont(titleFont); //设置值表头样式 设置表头居中 HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle(); hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); //设置居中样式 hssfCellStyle.setBorderBottom(BorderStyle.THIN); hssfCellStyle.setBorderLeft(BorderStyle.THIN); hssfCellStyle.setBorderRight(BorderStyle.THIN); hssfCellStyle.setBorderTop(BorderStyle.THIN); Font titleFont0 = hssfWorkbook.createFont(); titleFont0.setFontHeightInPoints((short) 20); hssfCellStyle.setFont(titleFont0); //设置表内容样式 //创建单元格,并设置值表头 设置表头居中 HSSFCellStyle style1 = hssfWorkbook.createCellStyle(); style1.setAlignment(HorizontalAlignment.RIGHT); style1.setBorderBottom(BorderStyle.THIN); style1.setBorderLeft(BorderStyle.THIN); style1.setBorderRight(BorderStyle.THIN); style1.setBorderTop(BorderStyle.THIN); Font titleFont1 = hssfWorkbook.createFont(); titleFont1.setFontHeightInPoints((short) 16); style1.setFont(titleFont1); style1.setWrapText(true); //产生标题行 HSSFRow hssfRow = hssfSheet.createRow(0); HSSFCell cell = hssfRow.createCell(0); cell.setCellValue(title); cell.setCellStyle(style); //产生表头 HSSFRow row1 = hssfSheet.createRow(1); for (int i = 0; i < headers.length; i++) { HSSFCell hssfCell = row1.createCell(i); hssfCell.setCellValue(headers[i]); hssfCell.setCellStyle(hssfCellStyle); } //创建内容 for (int i = 0; i <values.length; i++){ row1 = hssfSheet.createRow(i +2); for (int j = 0; j < values[i].length; j++){ //将内容按顺序赋给对应列对象 HSSFCell hssfCell = row1.createCell(j); hssfCell.setCellValue(values[i][j]); hssfCell.setCellStyle(style1); hssfSheet.setColumnWidth(i,values[i][j].getBytes().length*256+4000>15000?15000:values[i][j].getBytes().length*256+4000); } row1.setHeightInPoints(20); } return hssfWorkbook; } /** * @Title: exportExcel * @Description: 导出Excel的方法 * @param workbook * @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet) * @param sheetTitle (sheet的名称) * @param headers (表格的标题) * @param values (表格的数据) * @param out (输出流) * @throws Exception */ public Boolean exportExcel(HSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers, String[][] values, OutputStream out) { Boolean res = false ; try{ // 生成一个表格 HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(sheetNum, sheetTitle); // 设置表格默认列宽度为20个字节 sheet.setDefaultColumnWidth((short) 20); // 生成一个样式 CellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(BorderStyle.THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); HSSFCellStyle style1 = workbook.createCellStyle(); style1.setAlignment(HorizontalAlignment.RIGHT); style1.setBorderBottom(BorderStyle.THIN); style1.setBorderLeft(BorderStyle.THIN); style1.setBorderRight(BorderStyle.THIN); style1.setBorderTop(BorderStyle.THIN); Font titleFont1 = workbook.createFont(); titleFont1.setFontHeightInPoints((short) 14); style1.setFont(titleFont1); style1.setWrapText(true); // 生成一个字体 Font headerFont = workbook.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 18); headerFont.setBold(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); // 把字体应用到当前的样式 style.setFont(headerFont); // 指定当单元格内容显示不下时自动换行 style.setWrapText(true); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell((short) i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text.toString()); } /*// 遍历集合数据,产生数据行 if (result != null) { int index = 1; for (List<String> m : result) { row = sheet.createRow(index); int cellIndex = 0; for (String str : m) { HSSFCell cell = row.createCell((short) cellIndex); cell.setCellValue(str.toString()); cellIndex++; } index++; } }*/ //创建内容 for (int i = 0; i <values.length; i++){ row = sheet.createRow(i +1); for (int j = 0; j < values[i].length; j++){ //将内容按顺序赋给对应列对象 HSSFCell hssfCell = row.createCell(j); hssfCell.setCellValue(values[i][j]); hssfCell.setCellStyle(style1); //sheet.setColumnWidth(i, Objects.isNull(values[i][j])?50:values[i][j].getBytes().length*256+4000>15000?15000:values[i][j].getBytes().length*256+4000); //sheet.setColumnWidth(i,15000); } row.setHeightInPoints(20); } res = true; }catch (Exception e){ e.printStackTrace(); } return res; } public static SXSSFWorkbook createTemplate(String sheetName, int titleColumnNumber, String titleName, List<String> columnNameList) { SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet sheet = workbook.createSheet(sheetName); sheet.setDefaultColumnWidth(18); CellStyle style = workbook.createCellStyle(); if (titleColumnNumber > 1) { sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleColumnNumber)); } SXSSFRow rowTitle = sheet.createRow(0); style.setAlignment(HorizontalAlignment.LEFT); SXSSFCell titleCell = rowTitle.createCell(0); titleCell.setCellValue(titleName); CellStyle titleStyle = getCellStyle(workbook); titleCell.setCellStyle(titleStyle); Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short)20); titleFont.setBold(true); titleStyle.setFont(titleFont); if (CollectionUtils.isNotEmpty(columnNameList)) { CellStyle columnTitleStyle = getCellStyle(workbook); Font columnTitleFont = workbook.createFont(); columnTitleFont.setFontHeightInPoints((short)12); columnTitleFont.setBold(true); columnTitleStyle.setFont(columnTitleFont); SXSSFRow row = sheet.createRow(1); for(int i = 0; i < columnNameList.size(); ++i) { SXSSFCell columnCell = row.createCell(i); columnCell.setCellValue((String)columnNameList.get(i)); columnCell.setCellStyle(columnTitleStyle); } } return workbook; } private static CellStyle getCellStyle(SXSSFWorkbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); return cellStyle; } public static Workbook getWorkbook(String excelName) { Workbook workbook = null; ClassPathResource classPathResource = new ClassPathResource("excel/" + excelName); try { InputStream io = classPathResource.getInputStream(); workbook = WorkbookFactory.create(io); } catch (IOException var4) { var4.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } return workbook; } }