POI导出设置复杂表头

POI导出设置复杂表头

先展示一下表头结构

工具类

  /**
   * 生成表格（用于生成复杂表头）
   * 
   * @param sheetName sheet名称
   * @param wb 表对象
   * @param cellListMap 表头数据 {key=cellRowNum-1}
   * @param cellRowNum 表头总占用行数
   * @param exportData 行数据
   * @param rowMapper 单元格名称
   * @return
   * @throws Exception
   */
  @SuppressWarnings({ "rawtypes", "unchecked" })
  public static SXSSFWorkbook createCSVUtil(String sheetName, SXSSFWorkbook wb, Map<String,List<CellModel>> cellListMap,
          Integer cellRowNum, List<LinkedHashMap> exportData)throws Exception {
      //设置表格名称
      SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);
      sheet.autoSizeColumn(1, true);
      XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
      //设置单元格内容水平对齐
      cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
      //设置单元格内容垂直对齐
      cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
      //设置自动换行
      cellStyle.setWrapText(true);
      for(int t = 0; t < cellRowNum; t++) {
      	SXSSFRow row = (SXSSFRow) sheet.createRow(t);
      	List<CellModel> cellNameList = cellListMap.get(String.valueOf(t));
          for (CellModel cellModel : cellNameList) {
              sheet.addMergedRegion(new CellRangeAddress(cellModel.getStartRow(),
                      cellModel.getEndRow(), cellModel.getStartColumn(), cellModel.getEndColumn()));
          } 
          for (int i = 0; i < cellNameList.size(); i++) {
              CellModel cellModel = cellNameList.get(i);
              // 遍历插入表头
              SXSSFCell cell = (SXSSFCell) row.createCell(cellModel.getStartColumn());
              cell.setCellValue(cellModel.getCellName());
              cell.setCellStyle(cellStyle);
          }
      }
      for (LinkedHashMap hashMap : exportData) {
          SXSSFRow rowValue = (SXSSFRow) sheet.createRow(cellRowNum);
          Iterator<Map.Entry> iteratorRow = hashMap.entrySet().iterator();
          while (iteratorRow.hasNext()) {
              Map.Entry entryRow = iteratorRow.next();
              Integer key = Integer.valueOf(entryRow.getKey().toString());
              String value = "";
              if (entryRow.getValue() != null) {
                  value = entryRow.getValue().toString();
              } else {
                  value = "";
              }
              SXSSFCell cellValue = (SXSSFCell) rowValue.createCell(key - 1);
              cellValue.setCellValue(value);
              cellValue.setCellStyle(cellStyle);
          }
          cellRowNum++;
      }
      return wb;
  }

辅助工具类

/**
 * 用于设置复杂表头
 * @author asus
 *
 */
public class CellModel {
	private String cellName;
    private Integer startRow;//起始行
    private Integer startColumn;//起始列
    private Integer endRow;//结束行
    private Integer endColumn;//结束列

    public String getCellName() {
        return cellName;
    }

    public void setCellName(String cellName) {
        this.cellName = cellName;
    }

    public Integer getStartRow() {
        return startRow;
    }

    public void setStartRow(Integer startRow) {
        this.startRow = startRow;
    }

    public Integer getStartColumn() {
        return startColumn;
    }

    public void setStartColumn(Integer startColumn) {
        this.startColumn = startColumn;
    }

    public Integer getEndRow() {
        return endRow;
    }

    public void setEndRow(Integer endRow) {
        this.endRow = endRow;
    }

    public Integer getEndColumn() {
        return endColumn;
    }

    public void setEndColumn(Integer endColumn) {
        this.endColumn = endColumn;
    }
}

表头数据信息及表格展示数据

//向指定的Excel中写入数据
		OutputStream out = null;
		//设置最大数据行数
		SXSSFWorkbook wb = new SXSSFWorkbook(5000);
		try {
            String tabName = "档案与资料利用情况统计表（查档目的）";
            Map<String,List<CellModel>> map = new HashMap<String,List<CellModel>>();
            // 设置数据
            List<CellModel> firstRow = new ArrayList<CellModel>();
            CellModel cellModel1 = new CellModel();
            //总占用3行
            Integer cellRow = 3;
            cellModel1.setCellName("查档目的");
            cellModel1.setStartRow(0);
            cellModel1.setEndRow(2);
            cellModel1.setStartColumn(0);
            cellModel1.setEndColumn(0);

            CellModel cellModel2 = new CellModel();
            cellModel2.setCellName("利用人次");
            cellModel2.setStartRow(0);
            cellModel2.setEndRow(2);
            cellModel2.setStartColumn(1);
            cellModel2.setEndColumn(1);

            CellModel cellModel3 = new CellModel();
            cellModel3.setCellName("提供档案（卷次）");
            cellModel3.setStartRow(0);
            cellModel3.setEndRow(2);
            cellModel3.setStartColumn(2);
            cellModel3.setEndColumn(2);

            CellModel cellModel4 = new CellModel();
            cellModel4.setCellName("提供档案（件）");
            cellModel4.setStartRow(0);
            cellModel4.setEndRow(2);
            cellModel4.setStartColumn(3);
            cellModel4.setEndColumn(3);

            CellModel cellModel5 = new CellModel();
            cellModel5.setCellName("提供资料（册）");
            cellModel5.setStartRow(0);
            cellModel5.setEndRow(2);
            cellModel5.setStartColumn(4);
            cellModel5.setEndColumn(4);
            
            CellModel cellModel6 = new CellModel();
            cellModel6.setCellName("复制（页）");
            cellModel6.setStartRow(0);
            cellModel6.setEndRow(0);
            cellModel6.setStartColumn(5);
            cellModel6.setEndColumn(9);
            
            firstRow.add(cellModel1);
            firstRow.add(cellModel2);
            firstRow.add(cellModel3);
            firstRow.add(cellModel4);
            firstRow.add(cellModel5);
            firstRow.add(cellModel6);
            map.put("0", firstRow);

            List<CellModel> secondRow = new ArrayList<CellModel>();
            CellModel cellModel7 = new CellModel();
            cellModel7.setCellName("合计");
            cellModel7.setStartRow(1);
            cellModel7.setEndRow(2);
            cellModel7.setStartColumn(5);
            cellModel7.setEndColumn(5);

            CellModel cellModel8 = new CellModel();
            cellModel8.setCellName("其中");
            cellModel8.setStartRow(1);
            cellModel8.setEndRow(1);
            cellModel8.setStartColumn(6);
            cellModel8.setEndColumn(9);
            
            secondRow.add(cellModel7);
            secondRow.add(cellModel8);
            map.put("1", secondRow);
            
            List<CellModel> thirdRow = new ArrayList<CellModel>();
            CellModel cellModel9 = new CellModel();
            cellModel9.setCellName("打印");
            cellModel9.setStartRow(2);
            cellModel9.setEndRow(2);
            cellModel9.setStartColumn(6);
            cellModel9.setEndColumn(6);
            
            CellModel cellModel10 = new CellModel();
            cellModel10.setCellName("复印");
            cellModel10.setStartRow(2);
            cellModel10.setEndRow(2);
            cellModel10.setStartColumn(7);
            cellModel10.setEndColumn(7);
            
            CellModel cellModel11 = new CellModel();
            cellModel11.setCellName("摘抄");
            cellModel11.setStartRow(2);
            cellModel11.setEndRow(2);
            cellModel11.setStartColumn(8);
            cellModel11.setEndColumn(8);
            
            CellModel cellModel12 = new CellModel();
            cellModel12.setCellName("拍摄");
            cellModel12.setStartRow(2);
            cellModel12.setEndRow(2);
            cellModel12.setStartColumn(9);
            cellModel12.setEndColumn(9);
            
            thirdRow.add(cellModel9);
            thirdRow.add(cellModel10);
            thirdRow.add(cellModel11);
            thirdRow.add(cellModel12);
            map.put("2", thirdRow);

            List<LinkedHashMap> exportData = new ArrayList<LinkedHashMap>();
            for (int i = 0; i < list.size(); i++) {
                WorkStatistics wk = list.get(i);
                LinkedHashMap<String, String> rowPut = new LinkedHashMap<String, String>();
                rowPut.put("1", wk.get());
                rowPut.put("2", wk.get());
                rowPut.put("3", wk.get());
                rowPut.put("4", wk.get());
                rowPut.put("5", wk.get());
                rowPut.put("6", wk.get());
                rowPut.put("7", wk.get());
                rowPut.put("8", wk.get());
                rowPut.put("9", wk.get());
                rowPut.put("10", wk.get());

                exportData.add(rowPut);
            }
            // 用于写入文件
            wb = ExcelUtils.createCSVUtil("查档目的",wb, map, cellRow, exportData);
            Date date = new Date();
			String sd = sdf.format(date);
            // 设置文件名，加上格式化的时间
            String excelName =
                    tabName + sd + ".xlsx";
            String s_attachment = "attachment; filename=" + excelName;
            // 设置字符编码的格式
            s_attachment = new String(s_attachment.getBytes("gb2312"), "ISO8859-1");

            getResponse().setHeader("Content-disposition", s_attachment); // 设定输出文件头
            getResponse().setContentType("application/vnd.ms-excel");// 定义输出类型
            getResponse().setContentType("text/plain;charset=UTF-8");
            out = getResponse().getOutputStream();
            wb.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {}
            }
            if (wb != null) {
                wb.dispose();
            }
        }