/**
  * 初始化函数
  *
  * @param title 表格标题,传“空值”,表示无标题
  * @param headerList 表头列表
  */
 private void initialize(String title, List<String> headerList) {
   this.wb = new SXSSFWorkbook(500);
   this.sheet = wb.createSheet("Export");
   this.styles = createStyles(wb);
   // Create title
   if (StringUtils.isNotBlank(title)) {
     Row titleRow = sheet.createRow(rownum++);
     titleRow.setHeightInPoints(30);
     Cell titleCell = titleRow.createCell(0);
     titleCell.setCellStyle(styles.get("title"));
     titleCell.setCellValue(title);
     sheet.addMergedRegion(
         new CellRangeAddress(
             titleRow.getRowNum(),
             titleRow.getRowNum(),
             titleRow.getRowNum(),
             headerList.size() - 1));
   }
   // Create header
   if (headerList == null) {
     throw new RuntimeException("headerList not null!");
   }
   Row headerRow = sheet.createRow(rownum++);
   headerRow.setHeightInPoints(16);
   for (int i = 0; i < headerList.size(); i++) {
     Cell cell = headerRow.createCell(i);
     cell.setCellStyle(styles.get("header"));
     String[] ss = StringUtils.split(headerList.get(i), "**", 2);
     if (ss.length == 2) {
       cell.setCellValue(ss[0]);
       Comment comment =
           this.sheet
               .createDrawingPatriarch()
               .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
       comment.setString(new XSSFRichTextString(ss[1]));
       cell.setCellComment(comment);
     } else {
       cell.setCellValue(headerList.get(i));
     }
     sheet.autoSizeColumn(i);
   }
   for (int i = 0; i < headerList.size(); i++) {
     int colWidth = sheet.getColumnWidth(i) * 2;
     sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
   }
   log.debug("Initialize success.");
 }
Example #2
0
 public static void copySheet(Sheet sheet, Sheet newSheet) {
   int maxCol = 0;
   for (int row = 0; row <= sheet.getLastRowNum(); row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = newSheet.getRow(row);
     if (newRow == null) newRow = newSheet.createRow(row);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
     for (int col = 0; col < oldRow.getLastCellNum(); col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col);
       if (newCell == null) newCell = newRow.createCell(col);
       copyCell(oldCell, newCell, true);
     }
   }
   for (int col = 0; col <= maxCol; col++) {
     if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
   }
   for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
     CellRangeAddress cra = sheet.getMergedRegion(i);
     newSheet.addMergedRegion(cra);
   }
 }
Example #3
0
 public static void copyBlock(
     Sheet sheet,
     int startRow,
     int startCol,
     int endRow,
     int endCol,
     boolean copyStyle,
     int rowOffset,
     int colOffset,
     List<CellRangeAddress> mergedRegions) {
   for (int row = startRow; row <= endRow; row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = sheet.getRow(row + rowOffset);
     if (newRow == null) newRow = sheet.createRow(row + rowOffset);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     if (logger.isDebugEnabled()) {
       logger.debug("copy row {} to {}", row, row + rowOffset);
       logger.debug("Set row height :{}", newRow.getHeightInPoints());
     }
     for (int col = startCol; col <= endCol; col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col + colOffset);
       if (newCell == null) newCell = newRow.createCell(col + colOffset);
       copyCell(oldCell, newCell, copyStyle, rowOffset, colOffset);
     }
   }
   for (int col = startCol; col <= endCol; col++) {
     if (sheet.getColumnWidth(col) >= 0)
       sheet.setColumnWidth(col + colOffset, sheet.getColumnWidth(col));
   }
   if (mergedRegions != null) {
     for (CellRangeAddress cra : mergedRegions) {
       CellRangeAddress craNew =
           new CellRangeAddress(
               cra.getFirstRow() + rowOffset,
               cra.getLastRow() + rowOffset,
               cra.getFirstColumn() + colOffset,
               cra.getLastColumn() + colOffset);
       sheet.addMergedRegion(craNew);
     }
   }
 }
Example #4
0
  @Test
  public void autoFilterMultiTables() throws Exception {
    debug = false;
    autoFilter = true;
    InputStream inputStream = runAndRenderReport("NumberFormats.rptdesign", "xlsx");
    assertNotNull(inputStream);
    try {

      XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
      assertNotNull(workbook);

      assertEquals(1, workbook.getNumberOfSheets());
      assertEquals("Number Formats Test Report", workbook.getSheetAt(0).getSheetName());

      Sheet sheet = workbook.getSheetAt(0);
      assertEquals(22, this.firstNullRow(sheet));

      assertEquals(3035, sheet.getColumnWidth(0));
      assertEquals(3913, sheet.getColumnWidth(1));
      assertEquals(7021, sheet.getColumnWidth(2));
      assertEquals(4205, sheet.getColumnWidth(3));
      assertEquals(3474, sheet.getColumnWidth(4));
      assertEquals(2852, sheet.getColumnWidth(5));
      assertEquals(3510, sheet.getColumnWidth(6));
      assertEquals(2889, sheet.getColumnWidth(7));
      assertEquals(2048, sheet.getColumnWidth(8));

      XSSFName name = workbook.getName(XSSFName.BUILTIN_FILTER_DB);
      assertEquals(0, name.getSheetIndex());
      assertEquals("'Number Formats Test Report'!$A$1:$H$3", name.getRefersToFormula());

      assertNotNull(workbook.getSheetAt(0).getCTWorksheet().getAutoFilter());
    } finally {
      inputStream.close();
    }
  }
Example #5
0
 private void widthSetup(Sheet oldSheet, Sheet newSheet) {
   for (int i = 0; i < columnCount; i++)
     newSheet.setColumnWidth(i, oldSheet.getColumnWidth(i + INDENT));
 }