protected void importMergedRegions(Sheet poiSheet, SSheet sheet) {
// merged cells
// reference RangeImpl.getMergeAreas()
int nMerged = poiSheet.getNumMergedRegions();
final SheetImpl sheetImpl = (SheetImpl) sheet;
for (int i = nMerged - 1; i >= 0; --i) {
final CellRangeAddress mergedRegion = poiSheet.getMergedRegion(i);
// ZSS-1114: any new merged region that overlapped with previous merged region is thrown away
final CellRegion r =
new CellRegion(
mergedRegion.getFirstRow(),
mergedRegion.getFirstColumn(),
mergedRegion.getLastRow(),
mergedRegion.getLastColumn());
final CellRegion overlapped = sheetImpl.checkMergedRegion(r);
if (overlapped != null) {
_logger.warning(
"Drop the region "
+ r
+ " which is overlapped with existing merged area "
+ overlapped
+ ".");
continue;
}
sheetImpl.addDirectlyMergedRegion(r);
}
}
/**
* The purpose of this method is to validate the cell state prior to modification
*
* @see #notifyArrayFormulaChanging()
*/
void notifyArrayFormulaChanging(String msg) {
if (isPartOfArrayFormulaGroup()) {
CellRangeAddress cra = getArrayFormulaRange();
if (cra.getNumberOfCells() > 1) {
throw new IllegalStateException(msg);
}
// un-register the single-cell array formula from the parent XSSFSheet
getRow().getSheet().removeArrayFormula(this);
}
}
/**
* POI AutoFilter.getFilterColumn(i) sometimes returns null. A POI FilterColumn object only exists
* when we have set a criteria on that column. For example, if we enable auto filter on 2 columns,
* but we only set criteria on 2nd column. Thus, the size of filter column is 1. There is only one
* FilterColumn object and its column id is 1. Only getFilterColumn(1) will return a FilterColumn,
* other get null.
*
* @param poiSheet source POI sheet
* @param sheet destination sheet
*/
protected void importAutoFilter(Sheet poiSheet, SSheet sheet) {
AutoFilter poiAutoFilter = poiSheet.getAutoFilter();
if (poiAutoFilter != null) {
CellRangeAddress filteringRange = poiAutoFilter.getRangeAddress();
SAutoFilter autoFilter =
sheet.createAutoFilter(new CellRegion(filteringRange.formatAsString()));
int numberOfColumn = filteringRange.getLastColumn() - filteringRange.getFirstColumn() + 1;
importAutoFilterColumns(poiAutoFilter, autoFilter, numberOfColumn); // ZSS-1019
}
}
protected CellValue getCellValueAt(int index) {
if (index < 0 || index >= numOfCells) {
throw new IndexOutOfBoundsException(
"Index must be between 0 and " + (numOfCells - 1) + " (inclusive), given: " + index);
}
int firstRow = cellRangeAddress.getFirstRow();
int firstCol = cellRangeAddress.getFirstColumn();
int lastCol = cellRangeAddress.getLastColumn();
int width = lastCol - firstCol + 1;
int rowIndex = firstRow + index / width;
int cellIndex = firstCol + index % width;
Row row = sheet.getRow(rowIndex);
return (row == null) ? null : evaluator.evaluate(row.getCell(cellIndex));
}
protected CellRangeTextSource(Sheet sheet, CellRangeAddress cellRangeAddress) {
this.sheet = sheet;
// Make copy since CellRangeAddress is mutable.
this.cellRangeAddress = cellRangeAddress.copy();
this.numOfCells = this.cellRangeAddress.getNumberOfCells();
this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
}
public CellRangeAddress getArrayFormulaRange() {
XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
if (cell == null) {
throw new IllegalStateException("Cell " + _cell.getR() + " is not part of an array formula.");
}
String formulaRef = cell._cell.getF().getRef();
return CellRangeAddress.valueOf(formulaRef);
}
/**
* Creates a non shared formula from the shared formula counterpart
*
* @param si Shared Group Index
* @return non shared formula created for the given shared formula and this cell
*/
private String convertSharedFormula(int si) {
XSSFSheet sheet = getSheet();
CTCellFormula f = sheet.getSharedFormula(si);
if (f == null)
throw new IllegalStateException(
"Master cell of a shared formula with sid=" + si + " was not found");
String sharedFormula = f.getStringValue();
// Range of cells which the shared formula applies to
String sharedFormulaRange = f.getRef();
CellRangeAddress ref = CellRangeAddress.valueOf(sharedFormulaRange);
int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(sheet.getWorkbook());
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex);
Ptg[] fmla =
sf.convertSharedFormulas(
ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
return FormulaRenderer.toFormulaString(fpb, fmla);
}
/* package */ void setCellArrayFormula(String formula, CellRangeAddress range) {
setFormula(formula, FormulaType.ARRAY);
CTCellFormula cellFormula = _cell.getF();
cellFormula.setT(STCellFormulaType.ARRAY);
cellFormula.setRef(range.formatAsString());
}
/*
* import sheet scope content from POI Sheet.
*/
protected SSheet importSheet(Sheet poiSheet, int poiSheetIndex) {
SSheet sheet = book.createSheet(poiSheet.getSheetName());
sheet.setDefaultRowHeight(UnitUtil.twipToPx(poiSheet.getDefaultRowHeight()));
// ZSS-952
importSheetDefaultColumnWidth(poiSheet, sheet);
// reference FreezeInfoLoaderImpl.getRowFreeze()
sheet.getViewInfo().setNumOfRowFreeze(BookHelper.getRowFreeze(poiSheet));
sheet.getViewInfo().setNumOfColumnFreeze(BookHelper.getColumnFreeze(poiSheet));
sheet
.getViewInfo()
.setDisplayGridlines(
poiSheet
.isDisplayGridlines()); // Note isDisplayGridlines() and isPrintGridlines() are
// different
sheet.getViewInfo().setColumnBreaks(poiSheet.getColumnBreaks());
sheet.getViewInfo().setRowBreaks(poiSheet.getRowBreaks());
SPrintSetup sps = sheet.getPrintSetup();
SHeader header = sheet.getViewInfo().getHeader();
if (header != null) {
header.setCenterText(poiSheet.getHeader().getCenter());
header.setLeftText(poiSheet.getHeader().getLeft());
header.setRightText(poiSheet.getHeader().getRight());
sps.setHeader(header);
}
SFooter footer = sheet.getViewInfo().getFooter();
if (footer != null) {
footer.setCenterText(poiSheet.getFooter().getCenter());
footer.setLeftText(poiSheet.getFooter().getLeft());
footer.setRightText(poiSheet.getFooter().getRight());
sps.setFooter(footer);
}
if (poiSheet.isDiffOddEven()) {
Header poiEvenHeader = poiSheet.getEvenHeader();
if (poiEvenHeader != null) {
SHeader evenHeader = new HeaderFooterImpl();
evenHeader.setCenterText(poiEvenHeader.getCenter());
evenHeader.setLeftText(poiEvenHeader.getLeft());
evenHeader.setRightText(poiEvenHeader.getRight());
sps.setEvenHeader(evenHeader);
}
Footer poiEvenFooter = poiSheet.getEvenFooter();
if (poiEvenFooter != null) {
SFooter evenFooter = new HeaderFooterImpl();
evenFooter.setCenterText(poiEvenFooter.getCenter());
evenFooter.setLeftText(poiEvenFooter.getLeft());
evenFooter.setRightText(poiEvenFooter.getRight());
sps.setEvenFooter(evenFooter);
}
}
if (poiSheet.isDiffFirst()) {
Header poiFirstHeader = poiSheet.getFirstHeader();
if (poiFirstHeader != null) {
SHeader firstHeader = new HeaderFooterImpl();
firstHeader.setCenterText(poiFirstHeader.getCenter());
firstHeader.setLeftText(poiFirstHeader.getLeft());
firstHeader.setRightText(poiFirstHeader.getRight());
sps.setFirstHeader(firstHeader);
}
Footer poiFirstFooter = poiSheet.getFirstFooter();
if (poiFirstFooter != null) {
SFooter firstFooter = new HeaderFooterImpl();
firstFooter.setCenterText(poiFirstFooter.getCenter());
firstFooter.setLeftText(poiFirstFooter.getLeft());
firstFooter.setRightText(poiFirstFooter.getRight());
sps.setFirstFooter(firstFooter);
}
}
PrintSetup poips = poiSheet.getPrintSetup();
sps.setBottomMargin(poiSheet.getMargin(Sheet.BottomMargin));
sps.setTopMargin(poiSheet.getMargin(Sheet.TopMargin));
sps.setLeftMargin(poiSheet.getMargin(Sheet.LeftMargin));
sps.setRightMargin(poiSheet.getMargin(Sheet.RightMargin));
sps.setHeaderMargin(poiSheet.getMargin(Sheet.HeaderMargin));
sps.setFooterMargin(poiSheet.getMargin(Sheet.FooterMargin));
sps.setAlignWithMargins(poiSheet.isAlignMargins());
sps.setErrorPrintMode(poips.getErrorsMode());
sps.setFitHeight(poips.getFitHeight());
sps.setFitWidth(poips.getFitWidth());
sps.setHCenter(poiSheet.getHorizontallyCenter());
sps.setLandscape(poips.getLandscape());
sps.setLeftToRight(poips.getLeftToRight());
sps.setPageStart(poips.getUsePage() ? poips.getPageStart() : 0);
sps.setPaperSize(PoiEnumConversion.toPaperSize(poips.getPaperSize()));
sps.setCommentsMode(poips.getCommentsMode());
sps.setPrintGridlines(poiSheet.isPrintGridlines());
sps.setPrintHeadings(poiSheet.isPrintHeadings());
sps.setScale(poips.getScale());
sps.setScaleWithDoc(poiSheet.isScaleWithDoc());
sps.setDifferentOddEvenPage(poiSheet.isDiffOddEven());
sps.setDifferentFirstPage(poiSheet.isDiffFirst());
sps.setVCenter(poiSheet.getVerticallyCenter());
Workbook poiBook = poiSheet.getWorkbook();
String area = poiBook.getPrintArea(poiSheetIndex);
if (area != null) {
sps.setPrintArea(area);
}
CellRangeAddress rowrng = poiSheet.getRepeatingRows();
if (rowrng != null) {
sps.setRepeatingRowsTitle(rowrng.getFirstRow(), rowrng.getLastRow());
}
CellRangeAddress colrng = poiSheet.getRepeatingColumns();
if (colrng != null) {
sps.setRepeatingColumnsTitle(colrng.getFirstColumn(), colrng.getLastColumn());
}
sheet.setPassword(poiSheet.getProtect() ? "" : null);
// import hashed password directly
importPassword(poiSheet, sheet);
// ZSS-832
// import sheet visible
if (poiBook.isSheetHidden(poiSheetIndex)) {
sheet.setSheetVisible(SheetVisible.HIDDEN);
} else if (poiBook.isSheetVeryHidden(poiSheetIndex)) {
sheet.setSheetVisible(SheetVisible.VERY_HIDDEN);
} else {
sheet.setSheetVisible(SheetVisible.VISIBLE);
}
// ZSS-1130
// import conditionalFormatting
importConditionalFormatting(sheet, poiSheet);
return sheet;
}
public String getFormulaString() {
return cellRangeAddress.formatAsString(sheet.getSheetName(), true);
}