private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
      throws Exception {
    Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
    Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
    nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
    nCell.setCellStyle(tCell.getCellStyle());

    tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
    nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
    nCell.setCellValue(
        tCell
            .getStringCellValue()
            .replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
            .replaceAll(
                PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
            .replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
    nCell.setCellStyle(tCell.getCellStyle());

    Row tempRow = tempSheet.getRow(4);
    Row newRow = newSheet.createRow(4);

    for (int c = 0; c < tempRow.getLastCellNum(); c++) {
      tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
      nCell = newRow.createCell(c, tCell.getCellType());
      nCell.setCellValue(tCell.getStringCellValue());
      nCell.setCellStyle(tCell.getCellStyle());
    }

    for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
      CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
      newSheet.addMergedRegion(mergedRegion);
    }
  }
Exemplo n.º 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);
   }
 }
Exemplo n.º 3
0
  private void initActiveSheet() {
    if (dataFile == null) return;

    if (sheetIndex > -1) {
      dataSheet = dataFile.getSheetAt(sheetIndex);
      if (dataSheet == null) {
        throw new IndexOutOfBoundsException(
            "Sheet with index "
                + sheetIndex
                + " does not exist in file: "
                + inputFile.getFullPath());
      }
    } else if (sheetName != null) {
      dataSheet = dataFile.getSheet(sheetName);
      if (dataSheet == null) {
        throw new IllegalArgumentException(
            "Sheet with name " + sheetName + " does not exist in file: " + inputFile.getFullPath());
      }
    } else {
      int index = dataFile.getActiveSheetIndex();
      dataSheet = dataFile.getSheetAt(index);
    }
    headerColumns = null;
    int numMergedRegions = dataSheet.getNumMergedRegions();
    mergedRegions = new ArrayList<>(numMergedRegions);
    for (int i = 0; i < numMergedRegions; i++) {
      mergedRegions.add(dataSheet.getMergedRegion(i));
    }
  }
Exemplo n.º 4
0
  //  traversal cell
  public void traversalCell(String filePath) {
    try {
      Workbook workBook = null;
      try {
        workBook = new XSSFWorkbook(filePath); // 支持2007
      } catch (Exception ex) {
        workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
      }

      // 获得Excel中工作表个数
      System.out.println("工作表个数 :" + workBook.getNumberOfSheets());

      // 循环每个工作表
      for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        // 创建工作表
        Sheet sheet = workBook.getSheetAt(i);

        int rows = sheet.getPhysicalNumberOfRows(); // 获得行数

        System.out.println(
            "工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());

        if (rows > 0) {
          sheet.getMargin(Sheet.TopMargin);
          for (int r = 0; r < rows; r++) { // 行循环
            Row row = sheet.getRow(r);
            if (row != null) {

              int cells = row.getLastCellNum(); // 获得列数
              for (short c = 0; c < cells; c++) { // 列循环
                Cell cell = row.getCell(c);

                if (cell != null) {
                  String value = getCellData(cell);
                  System.out.println("第" + r + "行 " + "第" + c + "列:" + value);
                }
              }
            }
          }
        }

        // 查询合并的单元格
        for (i = 0; i < sheet.getNumMergedRegions(); i++) {
          System.out.println("第" + i + "个合并单元格");
          CellRangeAddress region = sheet.getMergedRegion(i);
          int row = region.getLastRow() - region.getFirstRow() + 1;
          int col = region.getLastColumn() - region.getFirstColumn() + 1;
          System.out.println("起始行:" + region.getFirstRow());
          System.out.println("起始列:" + region.getFirstColumn());
          System.out.println("所占行:" + row);
          System.out.println("所占列:" + col);
        }
      }

    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
Exemplo n.º 5
0
  /**
   * Compute width of a single cell
   *
   * @param cell the cell whose width is to be calculated
   * @param defaultCharWidth the width of a single character
   * @param formatter formatter used to prepare the text to be measured
   * @param useMergedCells whether to use merged cells
   * @return the width in pixels
   */
  public static double getCellWidth(
      Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {

    Sheet sheet = cell.getSheet();
    Workbook wb = sheet.getWorkbook();
    Row row = cell.getRow();
    int column = cell.getColumnIndex();

    int colspan = 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
      CellRangeAddress region = sheet.getMergedRegion(i);
      if (containsCell(region, row.getRowNum(), column)) {
        if (!useMergedCells) {
          // If we're not using merged cells, skip this one and move on to the next.
          return -1;
        }
        cell = row.getCell(region.getFirstColumn());
        colspan = 1 + region.getLastColumn() - region.getFirstColumn();
      }
    }

    CellStyle style = cell.getCellStyle();
    int cellType = cell.getCellType();

    // for formula cells we compute the cell width for the cached formula result
    if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType();

    Font font = wb.getFontAt(style.getFontIndex());

    AttributedString str;
    TextLayout layout;

    double width = -1;
    if (cellType == Cell.CELL_TYPE_STRING) {
      RichTextString rt = cell.getRichStringCellValue();
      String[] lines = rt.getString().split("\\n");
      for (int i = 0; i < lines.length; i++) {
        String txt = lines[i] + defaultChar;

        str = new AttributedString(txt);
        copyAttributes(font, str, 0, txt.length());

        if (rt.numFormattingRuns() > 0) {
          // TODO: support rich text fragments
        }

        layout = new TextLayout(str.getIterator(), fontRenderContext);
        if (style.getRotation() != 0) {
          /*
           * Transform the text using a scale so that it's height is increased by a multiple of the leading,
           * and then rotate the text before computing the bounds. The scale results in some whitespace around
           * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
           * is added by the standard Excel autosize.
           */
          AffineTransform trans = new AffineTransform();
          trans.concatenate(
              AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
          trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
          width =
              Math.max(
                  width,
                  ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        } else {
          width =
              Math.max(
                  width,
                  ((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        }
      }
    } else {
      String sval = null;
      if (cellType == Cell.CELL_TYPE_NUMERIC) {
        // Try to get it formatted to look the same as excel
        try {
          sval = formatter.formatCellValue(cell, dummyEvaluator);
        } catch (Exception e) {
          sval = String.valueOf(cell.getNumericCellValue());
        }
      } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
        sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase();
      }
      if (sval != null) {
        String txt = sval + defaultChar;
        str = new AttributedString(txt);
        copyAttributes(font, str, 0, txt.length());

        layout = new TextLayout(str.getIterator(), fontRenderContext);
        if (style.getRotation() != 0) {
          /*
           * Transform the text using a scale so that it's height is increased by a multiple of the leading,
           * and then rotate the text before computing the bounds. The scale results in some whitespace around
           * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
           * is added by the standard Excel autosize.
           */
          AffineTransform trans = new AffineTransform();
          trans.concatenate(
              AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
          trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
          width =
              Math.max(
                  width,
                  ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        } else {
          width =
              Math.max(
                  width,
                  ((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        }
      }
    }
    return width;
  }