コード例 #1
0
  private void addSumBottom() {
    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      Sheet sheet = book.getSheetAt(i);

      Row row = sheet.createRow(sheet.getLastRowNum() + 1);
      row.setHeight((short) (ROW_HEIGHT + 100));

      for (int j = 0; j < 1000000; j++) {
        if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
            && StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
          break;
        }
        Cell cell = row.createCell(j);
        cell.setCellStyle(Style.get(book).SUM);
        if (j == 0) {
          cell.setCellValue("合计");
        } else {
          cell.setCellValue(0);
        }

        if (j >= 7) {
          cell.setCellType(Cell.CELL_TYPE_FORMULA);
          cell.setCellFormula(
              String.format(
                  "SUM(%s%s:%s%s)",
                  CellUtils.convertToABC(j + 1),
                  5,
                  CellUtils.convertToABC(j + 1),
                  sheet.getLastRowNum()));
        }
      }
      sheet.addMergedRegion(
          new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
    }

    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      Sheet sheet = book.getSheetAt(i);
      for (int j = 4; j <= sheet.getLastRowNum(); j++) {
        Row row = sheet.getRow(j);
        for (int k = 0; k <= row.getLastCellNum(); k++) {
          Cell cell = row.getCell(k);
          if (cell == null) {
            continue;
          }

          if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
            cell.setCellStyle(Style.get(book).SUM);
          }
        }
      }
    }
  }
コード例 #2
0
  private String buildSumFormula(int rowIndex, List<Integer> colIndexes) {
    List<String> colRows = new ArrayList<String>();
    for (int col : colIndexes) {
      colRows.add(CellUtils.convertToABC(col + 1) + (rowIndex + 1));
    }

    String formula = "SUM(" + StringUtils.join(colRows, ",") + ")";

    return formula;
  }
コード例 #3
0
  private void rebuildFormula(Sheet sheet) {
    ProductType type = ProductType.get(sheet.getSheetName());
    SizeInfo sizeInfo = SizeInfo.getByType(type);

    // size -> list<columnIndex>
    Map<String, List<Integer>> columns = new HashMap<String, List<Integer>>();

    for (int colIndex = 6 + sizeInfo.getSizeNumber() + 1;
        colIndex < sheet.getRow(2).getLastCellNum();
        colIndex++) {
      Row row = sheet.getRow(2);
      String value = row.getCell(colIndex).getStringCellValue().trim();
      if ("数量".equals(value) || StringUtils.isBlank(value)) {
        continue;
      }

      if (!columns.containsKey(value)) {
        columns.put(value, new ArrayList<Integer>());
      }

      columns.get(value).add(colIndex);
    }

    //        for(int rowIndex = 3; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
    //            Row row = sheet.getRow(rowIndex);
    //
    //            for(int colIndex = 7; colIndex <= 7 + sizeRange - 1; colIndex++) {
    //                String size = sheet.getRow(2).getCell(colIndex).getStringCellValue().trim();
    //
    //                List<Integer> cols = columns.get(size);
    //                row.getCell(colIndex).setCellType(Cell.CELL_TYPE_FORMULA);
    //                row.getCell(colIndex).setCellFormula(this.buildSumFormula(rowIndex, cols));
    //            }
    //        }

    for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
      Row row = sheet.getRow(rowIndex);
      if (row == null) {
        continue;
      }

      for (int colIndex = 7; colIndex <= row.getLastCellNum(); colIndex++) {
        if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(colIndex)))) {
          Cell cell = row.getCell(colIndex);

          cell.setCellType(Cell.CELL_TYPE_FORMULA);
          String formula =
              String.format(
                  "SUM(%s%s:%s%s)",
                  CellUtils.convertToABC(colIndex - sizeInfo.getSizeNumber() + 1),
                  rowIndex + 1,
                  CellUtils.convertToABC(colIndex - 1 + 1),
                  rowIndex + 1);
          cell.setCellFormula(formula);
        }
      }
    }

    for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
      Row row = sheet.getRow(rowIndex);
      if ("合计".equals(CellUtils.getStringValue(row.getCell(0)))) {
        for (int colIndex = 7; colIndex <= sheet.getLastRowNum(); colIndex++) {
          Cell cell = row.getCell(colIndex);
          if (cell == null) {
            continue;
          }
          cell.setCellType(Cell.CELL_TYPE_FORMULA);
          String formula =
              String.format(
                  "SUM(%s%s:%s%s)",
                  CellUtils.convertToABC(colIndex + 1),
                  5,
                  CellUtils.convertToABC(colIndex + 1),
                  rowIndex);
          cell.setCellFormula(formula);
        }
      }
    }
  }