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);
}
}
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);
}
}
}
}
}
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);
}
}
}
private void setLayout(Sheet worksheet) {
Row rowHeader = worksheet.createRow(0);
rowHeader.setHeight((short) 500);
worksheet.setColumnWidth(OFFICE_NAME_COL, 4000);
worksheet.setColumnWidth(CLIENT_NAME_COL, 5000);
worksheet.setColumnWidth(SAVINGS_ACCOUNT_NO_COL, 3000);
worksheet.setColumnWidth(PRODUCT_COL, 4000);
worksheet.setColumnWidth(OPENING_BALANCE_COL, 4000);
worksheet.setColumnWidth(TRANSACTION_TYPE_COL, 3300);
worksheet.setColumnWidth(AMOUNT_COL, 4000);
worksheet.setColumnWidth(TRANSACTION_DATE_COL, 3000);
worksheet.setColumnWidth(PAYMENT_TYPE_COL, 3000);
worksheet.setColumnWidth(ACCOUNT_NO_COL, 3000);
worksheet.setColumnWidth(CHECK_NO_COL, 3000);
worksheet.setColumnWidth(RECEIPT_NO_COL, 3000);
worksheet.setColumnWidth(ROUTING_CODE_COL, 3000);
worksheet.setColumnWidth(BANK_NO_COL, 3000);
worksheet.setColumnWidth(LOOKUP_CLIENT_NAME_COL, 5000);
worksheet.setColumnWidth(LOOKUP_ACCOUNT_NO_COL, 3000);
worksheet.setColumnWidth(LOOKUP_PRODUCT_COL, 3000);
worksheet.setColumnWidth(LOOKUP_OPENING_BALANCE_COL, 3700);
worksheet.setColumnWidth(LOOKUP_SAVINGS_ACTIVATION_DATE_COL, 3500);
writeString(OFFICE_NAME_COL, rowHeader, "Office Name*");
writeString(CLIENT_NAME_COL, rowHeader, "Client Name*");
writeString(SAVINGS_ACCOUNT_NO_COL, rowHeader, "Account No.*");
writeString(PRODUCT_COL, rowHeader, "Product Name");
writeString(OPENING_BALANCE_COL, rowHeader, "Opening Balance");
writeString(TRANSACTION_TYPE_COL, rowHeader, "Transaction Type*");
writeString(AMOUNT_COL, rowHeader, "Amount*");
writeString(TRANSACTION_DATE_COL, rowHeader, "Date*");
writeString(PAYMENT_TYPE_COL, rowHeader, "Type*");
writeString(ACCOUNT_NO_COL, rowHeader, "Account No");
writeString(CHECK_NO_COL, rowHeader, "Check No");
writeString(RECEIPT_NO_COL, rowHeader, "Receipt No");
writeString(ROUTING_CODE_COL, rowHeader, "Routing Code");
writeString(BANK_NO_COL, rowHeader, "Bank No");
writeString(LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client");
writeString(LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Account");
writeString(LOOKUP_PRODUCT_COL, rowHeader, "Lookup Product");
writeString(LOOKUP_OPENING_BALANCE_COL, rowHeader, "Lookup Opening Balance");
writeString(LOOKUP_SAVINGS_ACTIVATION_DATE_COL, rowHeader, "Lookup Savings Activation Date");
}
/** Adds in a Row to the given Sheet */
public Row addRow(
Workbook wb,
SheetToAdd sheetToAdd,
RowToAdd rowToAdd,
int rowIndex,
ReportData reportData,
ReportDesign design,
Map<String, String> repeatSections) {
// Create a new row and copy over style attributes from the row to add
Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
Row rowToClone = rowToAdd.getRowToClone();
try {
CellStyle rowStyle = rowToClone.getRowStyle();
if (rowStyle != null) {
newRow.setRowStyle(rowStyle);
}
} catch (Exception e) {
// No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the
// rowStyle. Mysteries of POI
}
newRow.setHeight(rowToClone.getHeight());
// Iterate across all of the cells in the row, and configure all those that need to be
// added/cloned
List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();
int totalCells = rowToClone.getPhysicalNumberOfCells();
int cellsFound = 0;
for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
Cell currentCell = rowToClone.getCell(cellNum);
log.debug("Handling cell: " + currentCell);
if (currentCell != null) {
cellsFound++;
}
// If we find that the cell that we are on is a repeating cell, then add the appropriate
// number of cells to clone
String repeatingColumnProperty =
getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections);
if (repeatingColumnProperty != null) {
String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
String dataSetName = dataSetSpanSplit[0];
DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
int numCellsToRepeat = 1;
if (dataSetSpanSplit.length == 2) {
numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
}
log.debug(
"Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
int repeatNum = 0;
for (DataSetRow dataSetRow : dataSet) {
repeatNum++;
for (int i = 0; i < numCellsToRepeat; i++) {
Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
if (repeatNum == 1 && cell != null && cell != currentCell) {
cellsFound++;
}
Map<String, Object> newReplacements =
getReplacementData(
rowToAdd.getReplacementData(),
reportData,
design,
dataSetName,
dataSetRow,
repeatNum);
cellsToAdd.add(new CellToAdd(cell, newReplacements));
log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
}
}
cellNum += numCellsToRepeat;
} else {
cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
log.debug("Adding " + currentCell);
}
}
// Now, go through all of the collected cells, and add them back in
ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb);
String prefix = getExpressionPrefix(design);
String suffix = getExpressionSuffix(design);
for (int i = 0; i < cellsToAdd.size(); i++) {
CellToAdd cellToAdd = cellsToAdd.get(i);
Cell newCell = newRow.createCell(i);
Cell cellToClone = cellToAdd.getCellToClone();
if (cellToClone != null) {
String contents = ExcelUtil.getCellContentsAsString(cellToClone);
newCell.setCellStyle(cellToClone.getCellStyle());
try {
newCell.setCellFormula(cellToClone.getCellFormula());
} catch (Exception e) {
// Do nothing here. I don't know why POI throw exceptions here when the cell is not a
// formula, but this suppresses them...
}
int numFormattings =
sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings();
for (int n = 0; n < numFormattings; n++) {
ConditionalFormatting f =
sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n);
for (CellRangeAddress add : f.getFormattingRanges()) {
if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
&& add.getLastRow() == rowToClone.getRowNum()) {
if (add.getFirstColumn() == cellToClone.getColumnIndex()
&& add.getLastColumn() == cellToClone.getColumnIndex()) {
ConditionalFormattingRule[] rules =
new ConditionalFormattingRule[f.getNumberOfRules()];
for (int j = 0; j < f.getNumberOfRules(); j++) {
rules[j] = f.getRule(j);
}
CellRangeAddress[] cellRange = new CellRangeAddress[1];
cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
sheetToAdd
.getSheet()
.getSheetConditionalFormatting()
.addConditionalFormatting(cellRange, rules);
}
}
}
}
if (ObjectUtil.notNull(contents)) {
Object newContents =
EvaluationUtil.evaluateExpression(
contents, cellToAdd.getReplacementData(), prefix, suffix);
ExcelUtil.setCellContents(styleHelper, newCell, newContents);
}
}
}
return newRow;
}