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); } } } } }
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; }
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); } } } }