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