复制带表头的excel sheet页(poi的HSSFWorkbook)
HSSFWorkbook导出excel时会有65535行的限制，当我们数据量大的时候可以分多个sheet页的方案来解决，
但是每个sheet页都需要读取下载模版文件的表头，而poi库对一个excel之间的多sheet页都读取模版excel文件不支持，
本文的思路是先生成多个excel文件到内存，然后再将这些excel复制到一个文件的多个sheet页中。
一 . 首先查出来数据总数，按6万条一个excel进行生成。
//生成excel @GetMapping("/generate") public void generate(String fileName){ List<Map<String,Object>> dataList=new ArrayList(); //测试数据 for (int i=0;i<200000;i++){ Map m1 = new HashMap(); m1.put("Zara", "8bbb"); m1.put("Mahnaz", "31cc"); m1.put("Ayan", "12"); m1.put("Daisy", "14111aa"); dataList.add(m1); } //单个sheet处理65536条最多 final int sheetNum= new Double(Math.ceil((float)dataList.size()/60000)).intValue();
HSSFWorkbook sumWorkBook =new HSSFWorkbook(); for (int i=0;i<sheetNum;i++){ HSSFWorkbook workbook1=exportExcelByPostFix(dataList.subList(60000*i, (i!=(sheetNum-1)?(60000*(i+1)):dataList.size()))); HSSFSheet sheet1=workbook1.getSheetAt(0); HSSFSheet newsheet =sumWorkBook.createSheet("sheet"+(i+1)); ExcelUtil.copySheet(sumWorkBook,sheet1,newsheet); } //生成文件 exportFile(sumWorkBook,fileName); return Result.succ("copy"); }
二 . 将每6万条数据生成一个excel到内存中，即excel的第一个sheet页
public HSSFWorkbook exportExcelByPostFix(List<Map<String,Object>> dataList){ //获取模版表头 Resource resourceData=new DefaultResourceLoader().getResource("classpath:doctemp/temp.xls"); InputStream inputStream=null; HSSFWorkbook wb=null; try { //获取输入流 inputStream= resourceData.getInputStream(); //创建带表头的工作表 wb=new HSSFWorkbook(inputStream); //设置单元格基础样式，加边框，加基础黑框 CellStyle hssfCellStyle=wb.createCellStyle(); hssfCellStyle.setBorderBottom(BorderStyle.THIN); hssfCellStyle.setBorderLeft(BorderStyle.THIN); hssfCellStyle.setBorderRight(BorderStyle.THIN); hssfCellStyle.setBorderTop(BorderStyle.THIN); //字居中 hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); //获取第一个sheet页 HSSFSheet sheet=wb.getSheetAt(0); //前五行表头 int count=5; ExcelUtil.exportForData(dataList,sheet,hssfCellStyle,count); } catch (IOException e){ } finally { } return wb; }
三.生成excel工具类，以及复制sheet页的方法
public class ExcelUtil { /** * sheet复制 */ public static void copySheet(HSSFWorkbook wb, HSSFSheet fromSheet, HSSFSheet toSheet){ //合并区域处理 mergeSheetAllRegion(fromSheet, toSheet); // 列宽 int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum(); for (int i = 0; i <= length; i++) { toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i)); } int flag=0; //设置单元格基础样式，加边框，加基础黑框 CellStyle hssfCellStyle=wb.createCellStyle(); hssfCellStyle.setBorderBottom(BorderStyle.THIN); hssfCellStyle.setBorderLeft(BorderStyle.THIN); hssfCellStyle.setBorderRight(BorderStyle.THIN); hssfCellStyle.setBorderTop(BorderStyle.THIN); //字居中 hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) { HSSFRow oldRow = (HSSFRow) rowIt.next(); HSSFRow newRow = toSheet.createRow(oldRow.getRowNum()); flag++; copyRow(wb, oldRow, newRow,flag,hssfCellStyle); } } /** * 行复制 */ private static void copyRow(HSSFWorkbook wb, HSSFRow oldRow, HSSFRow toRow,int flag, CellStyle hssfCellStyle) { toRow.setHeight(oldRow.getHeight()); for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) { HSSFCell tmpCell = (HSSFCell) cellIt.next(); HSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex()); copyCell(wb, tmpCell, newCell,flag,hssfCellStyle); } } /** * 合并单元格处理 */ private static void mergeSheetAllRegion(HSSFSheet fromSheet, HSSFSheet toSheet) { int sheetMergeCount = fromSheet.getNumMergedRegions(); CellRangeAddress cellR = null; for (int i = 0; i < sheetMergeCount; i++) { cellR = fromSheet.getMergedRegion(i); toSheet.addMergedRegion(cellR); } } /** * 复制单元格 */ private static void copyCell(HSSFWorkbook wb, HSSFCell fromCell, HSSFCell toCell, int flag, CellStyle hssfCellStyle) { //excel表头有5行 if(flag<6){ HSSFCellStyle newstyle = wb.createCellStyle(); // 复制单元格样式 newstyle.cloneStyleFrom(fromCell.getCellStyle()); // 样式 toCell.setCellStyle(newstyle); } else { //其它的单元格用标准格式 //单元格格式过多会抛出异常，不支持创建太多 toCell.setCellStyle(hssfCellStyle); } if (fromCell.getCellComment() != null) { toCell.setCellComment(fromCell.getCellComment()); } // 不同数据类型处理 CellType fromCellType = fromCell.getCellType(); toCell.setCellType(fromCellType); if (fromCellType == CellType.NUMERIC) { if (DateUtil.isCellDateFormatted(fromCell)) { toCell.setCellValue(fromCell.getDateCellValue()); } else { toCell.setCellValue(fromCell.getNumericCellValue()); } } else if (fromCellType == CellType.STRING) { toCell.setCellValue(fromCell.getRichStringCellValue()); } else if (fromCellType == CellType.BLANK) { // nothing21 } else if (fromCellType == CellType.BOOLEAN) { toCell.setCellValue(fromCell.getBooleanCellValue()); } else if (fromCellType == CellType.ERROR) { toCell.setCellErrorValue(fromCell.getErrorCellValue()); } else if (fromCellType == CellType.FORMULA) { toCell.setCellFormula(fromCell.getCellFormula()); } else { // nothing29 } } /** * 创建单元格 */ public static void createCell(Row rows,int column,String value,CellStyle hssfCellStyle){ Cell cell=rows.createCell(column); cell.setCellValue(value); cell.setCellStyle(hssfCellStyle); } /** * 根据数据组装cell数据 * count=5表示从第6行开始生成单元格，前面5行是表头 */ public static void exportForData(List<Map<String,Object>> list, HSSFSheet hssfSheet, CellStyle hssfCellStyle,int count){ if(list!=null&& list.size()!=0){ for (int i=0;i<list.size();i++){ //创建行 Row rows=hssfSheet.createRow(count); Map<String,Object> map= list.get(i); //是否总计数据 int j=0; Set<String> sets= map.keySet(); if(sets==null||sets.size()<1){ continue; } for (String key:sets){ createCell(rows,j,String.valueOf(map.get(key)),hssfCellStyle); //在for循环中。单元格纬度下调用该函数，会极大降低效率 //所以在最后一行数据时，才调用这个函数 if(i==list.size()-1){ hssfSheet.autoSizeColumn(j);//按内容撑开单元格宽度 } j++; } count++; } } } }
四,将生成的每个excel的sheet1合并到一个总的excel中，代码在第一步已经列出。然后生成文文件
private void exportFile(HSSFWorkbook workbook,String fileName){ String c="/workspace/java/"; File file=new File(c); if(!file.exists()){ file.mkdir(); } String exportFilePath=file +"/"+fileName+".xls"; FileOutputStream outputStream=null; try { outputStream =new FileOutputStream(exportFilePath); workbook.write(outputStream); outputStream.flush(); } catch (Exception e){ } finally { } }
五，结束。需要注意的是window开发环境和部署的linux环境的差别
