poi导出excel，表头数据动态拼装

/*

• 第一步：拼装表头和数据
  /
  // 放多个sheet的集合
  List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
  //字典项
  String typeCode = Tool.getTypeCodeByCategory(belongsCategory);
  List<DataDictVO> dataDicts = dataDictService.getDataDictListByTypeCode(typeCode);
  List<Map<String,String>> purchaseMethodData = (List<Map<String,String>>) getDataInfoBusiness.getDataSourceList2(params, “TradeGetInfo”, “category”, belongsCategory, statisticalTime);
  //动态表头
  List<List<CellInfo>> headerTwo = new LinkedList<List<CellInfo>>();
  List<CellInfo> firstTwo = new LinkedList<CellInfo>();
  firstTwo.add(new CellInfo(“来源系统”,2,1));
  if(!CollectionUtils.isEmpty(dataDicts)){
  for(DataDictVO dict:dataDicts){
  firstTwo.add(new CellInfo(dict.getName(),1,1));
  }
  }
  headerTwo.add(firstTwo);
  List<CellInfo> second = new LinkedList<CellInfo>();
  int colNum = 1;
  second.add(new CellInfo(“来源系统”));
  if(!CollectionUtils.isEmpty(dataDicts)){
  for(int i=0;i<dataDicts.size();i++){
  second.add(new CellInfo(“入库”));
  colNum += 1;
  }
  }
  headerTwo.add(second);
  //表数据
  List<List<CellInfo>> dataTwo = new LinkedList<List<CellInfo>>(); for(Map<String,String> typeData:purchaseMethodData){
  List<CellInfo> row = new LinkedList<CellInfo>();
  row.add(new CellInfo(typeData.get(“DATA_SOURCE_CODE_NAME”)));
  for(DataDictVO dict:dataDicts){
  String code = dict.getCode() + “_ACCESS_NUM”;
  row.add(new CellInfo(typeData.get(code)));
  }
  dataTwo.add(row);
  } Map<String, Object> mapTwo = new HashMap<String, Object>();
  mapTwo.put(“sheetName”, “按类别统计”);
  mapTwo.put(“title”, “按类别统计”);
  mapTwo.put(“unitInfo”, “”);
  mapTwo.put(“headerLength”, colNum);
  mapTwo.put(“header”, headerTwo);
  mapTwo.put(“data”, dataTwo);
  datas.add(mapTwo);
  /
  
• 第二步：调用工具类
  */
  /**
  
  • 导出成Excel表格
    
  • @param sheetName sheet名称 该类多Sheet
    
  • @param title 表格的title。eg:政府采购
    
  • @param unitInfo 表格单位信息。eg:金额单位:万元
    
  • @param headerLength 表头的最大列数（按未合并前的单元格算）
    
  • @param header 表头信息（见CellInfo）如果跨行则被合并的行不再需要说明跨行信息 即：跨行信息放在第一行出现该信息的CellInfo里。后面的每行只需要输入相同的内容即可。
    
  • @param data 同表头
    
  • @param out 输出流 将生成的Excel数据流传输到该输出流
    
  • @throws IOException
    */
    @SuppressWarnings(“unchecked”)
    public static HSSFWorkbook exportExcel(List<Map<String,Object>> datas) throws IOException{
    //创建工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    //表头的样式
    HSSFCellStyle titlestyle = workbook.createCellStyle();// 创建样式对象
    titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
    titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
    titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    // titlestyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    // titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    //字体
    HSSFFont titleFont = workbook.createFont(); // 创建字体对象
    titleFont.setFontHeightInPoints((short) 11); // 设置字体大小
    titleFont.setFontName(“微软雅黑”); // 设置为黑体字
    // titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
    titlestyle.setFont(titleFont);
    //指定当单元格内容显示不下时自动换行
    titlestyle.setWrapText(true);
    //表数据的样式
    HSSFCellStyle style = workbook.createCellStyle();// 创建样式对象
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    //字体
    HSSFFont font = workbook.createFont(); // 创建字体对象
    font.setFontHeightInPoints((short) 11); // 设置字体大小
    font.setFontName(“微软雅黑”); // 设置为黑体字
    style.setFont(font);
    //指定当单元格内容显示不下时自动换行
    style.setWrapText(true);
    for(Map<String,Object> map:datas){
    String sheetName = map.get(“sheetName”).toString();
    String title = map.get(“title”).toString();
    String unitInfo = map.get(“unitInfo”).toString();
    int headerLength = (int)map.get(“headerLength”);
    List<List<CellInfo>> header = (List<List<CellInfo>>)map.get(“header”);
    List<List<CellInfo>> data = (List<List<CellInfo>>)map.get(“data”);
    //创建Sheet
    HSSFSheet sheet = workbook.createSheet(sheetName);
    //设置表格默认列宽度为20个字节
    sheet.setDefaultColumnWidth(20);
    //设置第一行 （单位信息，定制）
    int rowNum = 0;
    if(null!=unitInfo){
    sheet.addMergedRegion(new CellRangeAddress(rowNum,1,0,headerLength-1));
    HSSFRow rowUnit = sheet.createRow(rowNum);
    HSSFCell cellUnit = rowUnit.createCell(0);
    HSSFCellStyle unitStyle = workbook.createCellStyle();// 创建样式对象
    unitStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
    unitStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    unitStyle.setFont(font);
    cellUnit.setCellStyle(unitStyle);
    cellUnit.setCellValue(unitInfo);
    rowNum =1;
    }
    //标题
    if(null!=title){
    rowNum++;
    HSSFRow rowHeader = sheet.createRow(rowNum);
    for(int i=0;i<headerLength;i++){
    HSSFCell cellHeader = rowHeader.createCell(i);
    cellHeader.setCellStyle(titlestyle);
    cellHeader.setCellValue(title);
    }
    rowNum++;
    HSSFRow title2 = sheet.createRow(rowNum);
    for(int i=0;i<headerLength;i++){
    HSSFCell cellHeader = title2.createCell(i);
    cellHeader.setCellStyle(titlestyle);
    cellHeader.setCellValue(title);
    }
    sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum,0,headerLength-1));
    } //表头
    for (int i = 0; i < header.size(); i++) {
    //行
    rowNum ++;
    HSSFRow row = sheet.createRow(rowNum);
    List<CellInfo> cols = header.get(i);
    //创建列
    int colNum = 0;
    for(int j=0;j<cols.size();j++){
    HSSFCell cell = row.createCell(colNum);
    cell.setCellStyle(titlestyle);
    cell.setCellValue(cols.get(j).getContent());
    int firstRow = rowNum;
    int lastRow =rowNum;
    int firstCol = colNum;
    int lastCol = colNum;
    boolean merge = false;
    if(cols.get(j).getRowSpan()>1){
    lastRow += cols.get(j).getRowSpan()-1;
    merge = true;
    }
    //如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题）
    if(cols.get(j).getColSpan()>1){
    for(int k=0;k<cols.get(j).getColSpan()-1;k++){
    colNum++;
    HSSFCell tmpCell = row.createCell(colNum);
    tmpCell.setCellStyle(titlestyle);
    tmpCell.setCellValue(cols.get(j).getContent());
    }
    lastCol = colNum;
    merge = true;
    }
    colNum++;
    if(merge){
    sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
    }
    }
    }
    //表格数据
    for (int i = 0; i < data.size(); i++) {
    //行
    rowNum ++;
    HSSFRow row = sheet.createRow(rowNum);
    List<CellInfo> cols = data.get(i);
    //创建列
    int colNum = 0;
    for(int j=0;j<cols.size();j++){
    HSSFCell cell = row.createCell(colNum);
    cell.setCellStyle(style);
    cell.setCellValue(cols.get(j).getContent());
    int firstRow = rowNum;
    int lastRow =rowNum;
    int firstCol = colNum;
    int lastCol = colNum;
    boolean merge = false;
    if(cols.get(j).getRowSpan()>1){
    lastRow += cols.get(j).getRowSpan()-1;
    merge = true;
    }
    //如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题）
    if(cols.get(j).getColSpan()>1){
    for(int k=0;k<cols.get(j).getColSpan()-1;k++){
    colNum++;
    HSSFCell tmpCell = row.createCell(colNum);
    tmpCell.setCellStyle(style);
    tmpCell.setCellValue(cols.get(j).getContent());
    }
    lastCol = colNum;
    merge = true;
    }
    colNum++;
    if(merge){
    sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
    }
    }
    }
    rowNum++; //页底样式
    HSSFCellStyle footerStyle = workbook.createCellStyle();// 创建样式对象
    footerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
    footerStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    footerStyle.setFont(font); int n = headerLength/4;
    int m = headerLength%4;
    int start = 0;
    int end = n;
    HSSFRow footer = sheet.createRow(rowNum);
    HSSFCell unitFooter = footer.createCell(0);
    unitFooter.setCellStyle(footerStyle);
    unitFooter.setCellValue(“填报单位：”); if(m>0){
    end += 1;
    }
    sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end;
    HSSFCell timeFooter = footer.createCell(start);
    timeFooter.setCellStyle(footerStyle);
    timeFooter.setCellValue(“填报时间：”);
    if(m>1){
    end += n+1;
    }else{
    end += n;
    }
    sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end;
    HSSFCell personFooter = footer.createCell(start);
    personFooter.setCellStyle(footerStyle);
    personFooter.setCellValue(“填报人：”);
    if(m>2){
    end += n+1;
    }else{
    end += n;
    }
    sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end;
    HSSFCell telFooter = footer.createCell(start);
    telFooter.setCellStyle(footerStyle);
    telFooter.setCellValue(“联系电话：”);
    sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,headerLength-1));
    }
    return workbook;
    }
    /**
    
  • 解决 导出excel 文件名 为中文时乱码的问题
    
  • @param fileName 文件名
    
  • @return
    */
    public static String toUtf8String(String fileName) {
    try {
    return new String(fileName.getBytes(“GBK”), “ISO8859-1”);
    } catch (UnsupportedEncodingException e) {
    e.printStackTrace();
    }
    return fileName;
    }