/**
* Watch list serve for formula changes. Basically all the rows appeared in the formula in the
* current sheet will be watched. Note if the cell reference is from other sheet or workbooks, it
* will be ignored.
*
* @param wbWrapper XSSFEvaluationWorkbook used for formula parse.
* @param sheet current sheet.
* @return List row number for monitoring.
*/
private List<Integer> buildFormWatchList(
final XSSFEvaluationWorkbook wbWrapper, final Sheet sheet) {
List<Integer> watchList = new ArrayList<Integer>();
ConfigRange cRange = this.getConfigRange();
List<ConfigCommand> commandList = cRange.getCommandList();
if (commandList.size() <= 0) {
// if no command then no dynamic changes. then no need formula shifts.
return watchList;
}
int lastStaticRow = commandList.get(0).getTopRow() - 1;
if (lastStaticRow < 0) {
lastStaticRow = this.getTopRow();
}
Workbook wb = sheet.getWorkbook();
for (int i = this.getTopRow(); i <= this.getLastRow(); i++) {
Row row = sheet.getRow(i);
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String formula = cell.getCellFormula();
Ptg[] ptgs =
FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, wb.getSheetIndex(sheet));
for (int k = 0; k < ptgs.length; k++) {
Object ptg = ptgs[k];
// For area formula, only first row is watched.
// Reason is the lastRow must shift same rows with
// firstRow.
// Otherwise it's difficult to calculate.
// In case some situation cannot fit, then should make
// change to the formula.
int areaInt = ShiftFormula.getFirstSupportedRowNumFromPtg(ptg);
if (areaInt >= 0) {
addToWatchList(sheet, areaInt, lastStaticRow, watchList);
}
}
// when insert row, the formula may changed. so here is the workaround.
// change formula to user formula to preserve the row changes.
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(
ConfigurationHelper.USER_FORMULA_PREFIX
+ formula
+ ConfigurationHelper.USER_FORMULA_SUFFIX);
}
}
}
return watchList;
}
/**
* テンプレートワークブックのシートを、帳票出力単位に変換する。
*
* @param workbook テンプレートワークブック
* @param reportBook 帳票ワークブック情報
* @return 削除が必要なテンプレートシートインデックス
*/
private Set<Integer> expandTemplate(Workbook workbook, ReportBook reportBook) {
Set<Integer> delTemplateIndexs = new TreeSet<Integer>(Collections.reverseOrder());
Set<Integer> useTemplateIndexs = new HashSet<Integer>();
// 出力シート単位にコピーする
for (ReportSheet reportSheet : reportBook.getReportSheets()) {
if (reportSheet != null) {
if (reportSheet.getSheetName().equals(reportSheet.getTemplateName())) {
// テンプレート名＝出力シート名
int lastSheetIndex = workbook.getNumberOfSheets() - 1;
workbook.setSheetOrder(reportSheet.getSheetName(), lastSheetIndex);
useTemplateIndexs.add(lastSheetIndex);
} else {
int tempIdx = workbook.getSheetIndex(reportSheet.getTemplateName());
Sheet sheet = workbook.cloneSheet(tempIdx);
workbook.setSheetName(workbook.getSheetIndex(sheet), reportSheet.getSheetName());
delTemplateIndexs.add(tempIdx);
}
}
}
// 出力対象外シートを削除インデックスに追加
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
if (!isOutputSheet(sheet, reportBook)) {
delTemplateIndexs.add(workbook.getSheetIndex(sheet));
}
}
delTemplateIndexs.removeAll(useTemplateIndexs);
return delTemplateIndexs;
}
/**
* @param wb
* @param reviewerSheetName
*/
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
sheet.setColumnWidth(i, COL_WIDTH * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
}
}
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < HEADER_TITLES.length; i++) {
sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
cell.setCellValue(HEADER_TITLES[i]);
}
}
/** Clone the sheet at the passed index and replace values as needed */
public Sheet addSheet(
Workbook wb,
SheetToAdd sheetToAdd,
Set<String> usedSheetNames,
ReportData reportData,
ReportDesign design,
Map<String, String> repeatSections) {
String prefix = getExpressionPrefix(design);
String suffix = getExpressionSuffix(design);
Sheet sheet = sheetToAdd.getSheet();
sheet.setForceFormulaRecalculation(true);
int sheetIndex = wb.getSheetIndex(sheet);
// Configure the sheet name, replacing any values as needed, and ensuring it is unique for the
// workbook
String sheetName =
EvaluationUtil.evaluateExpression(
sheetToAdd.getOriginalSheetName(), sheetToAdd.getReplacementData(), prefix, suffix)
.toString();
sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames);
wb.setSheetName(sheetIndex, sheetName);
usedSheetNames.add(sheetName);
log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex);
// Iterate across all of the rows in the sheet, and configure all those that need to be
// added/cloned
List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>();
int totalRows = sheet.getPhysicalNumberOfRows();
int rowsFound = 0;
for (int rowNum = 0;
rowsFound < totalRows && rowNum < 50000;
rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases
Row currentRow = sheet.getRow(rowNum);
log.debug("Handling row: " + ExcelUtil.formatRow(currentRow));
if (currentRow != null) {
rowsFound++;
}
// If we find that the row that we are on is a repeating row, then add the appropriate number
// of rows to clone
String repeatingRowProperty =
getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum, repeatSections);
if (repeatingRowProperty != null) {
String[] dataSetSpanSplit = repeatingRowProperty.split(",");
String dataSetName = dataSetSpanSplit[0];
DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData());
int numRowsToRepeat = 1;
if (dataSetSpanSplit.length == 2) {
numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
}
log.debug(
"Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat);
int repeatNum = 0;
for (DataSetRow dataSetRow : dataSet) {
repeatNum++;
for (int i = 0; i < numRowsToRepeat; i++) {
Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i));
if (repeatNum == 1 && row != null && row != currentRow) {
rowsFound++;
}
Map<String, Object> newReplacements =
getReplacementData(
sheetToAdd.getReplacementData(),
reportData,
design,
dataSetName,
dataSetRow,
repeatNum);
rowsToAdd.add(new RowToAdd(row, newReplacements));
log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow);
}
}
if (numRowsToRepeat > 1) {
rowNum += numRowsToRepeat - 1;
}
} else {
rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData()));
log.debug("Adding row: " + ExcelUtil.formatRow(currentRow));
}
}
// Now, go through all of the collected rows, and add them back in
for (int i = 0; i < rowsToAdd.size(); i++) {
RowToAdd rowToAdd = rowsToAdd.get(i);
if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) {
Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections);
log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow));
}
}
return sheet;
}