@Override
 public Workbook writeWorkbook(
     Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) {
   if (workbook == null) workbook = new XSSFWorkbook();
   int exportRecordNum = tableDataList.size(); // 导出的总记录数
   int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数
   int sheetNum =
       exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum)
           + 1; // 工作表的页数
   List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>();
   if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet";
   for (int i = 0; i < sheetNum; i++) {
     // 在Excel工作簿中建一工作表
     String sTitle = sheetTitle + (i + 1);
     XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle);
     sheet.setSelected(true); // 设置工作薄为选中
     sheet.setAutobreaks(true);
     sheet.setPrintGridlines(true);
     sheetList.add(sheet);
   }
   /** ***********************输出表头********************************* */
   if (headerNum > 0) {
     for (XSSFSheet sheet : sheetList) {
       XSSFRow headRow = sheet.createRow(0);
       headRow.setHeightInPoints(20);
       for (int i = 0; i < headerNum; i++) {
         List<String> headerRowDataList = tableDataList.get(i);
         for (int j = 0; j < headerRowDataList.size(); j++) {
           XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式
           createCell(headRow, j, headerRowDataList.get(j), cellStyle);
         }
       }
       // 固定表头
       sheet.createFreezePane(0, 1);
     }
   }
   /** *********************输出表体内容************************* */
   // 设置列样式
   XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle();
   columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
   columnStyle.setWrapText(true);
   if (tableDataList.size() > (tableStartRowNum + 1)) {
     for (int i = tableStartRowNum; i < exportRecordNum; i++) {
       List<String> rowDataList = tableDataList.get(i);
       XSSFRow row = null;
       int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数
       XSSFSheet sheet = sheetList.get(currentSheet);
       int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet;
       row = sheet.createRow(rowIndex);
       for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) {
         createCell(row, colIndex, rowDataList.get(colIndex));
       }
     }
   }
   // 调整列的宽度(取第一列为基准)
   for (XSSFSheet sheet : sheetList) {
     for (int i = 0; i < tableDataList.get(0).size(); i++) {
       sheet.autoSizeColumn((short) i);
       sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000));
     }
   }
   return workbook;
 }
Example #2
0
  public static boolean writeArray2XLSXFile(
      String FileName, String[] titleNames, List<String[]> arry) throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet("Data");
    int columnNum = titleNames.length;
    int rowNum = arry.size();

    // for (int i = 0; i < columnNum; i++) {
    //  sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
    // }
    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom headFont to the text in the comment
    XSSFFont headFont = wb.createFont();
    headFont.setFontName("Courier New");
    headFont.setFontHeightInPoints((short) 10);
    headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    headFont.setColor(HSSFColor.BLACK.index);

    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
    headStyle.setLocked(true);
    headStyle.setFont(headFont);
    headStyle.setBorderTop((short) 2);
    headStyle.setBorderBottom((short) 1);

    XSSFCellStyle contentStyle = wb.createCellStyle();
    // apply custom headFont to the text in the comment
    XSSFFont contentFont = wb.createFont();
    contentFont.setFontName("Courier New");
    contentFont.setFontHeightInPoints((short) 9);
    // headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    contentFont.setColor(HSSFColor.BLACK.index);

    contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
    contentStyle.setFont(contentFont);

    // create titile row
    XSSFRow row = sheet1.createRow(0);
    int heandLine = 0;
    XSSFCell cell = null;

    if (titleNames != null) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(titleNames[i]);
        cell.setCellStyle(headStyle);
      }
      heandLine++;
    }

    for (int i = 0; i < rowNum; i++) {
      row = sheet1.createRow((i + heandLine));
      String[] line = (String[]) arry.get(i);
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (line[j] != null) {
          if (Util.isNumeric(line[j])) {
            // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(line[j]));
          } else {
            cell.setCellValue(line[j]);
          }
        } else {
          cell.setCellValue(".");
        }
      }
    }

    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(FileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }