/** * @return array of <tt>CellRangeAddress</tt>s. Never <code>null</code> */ @Override public CellRangeAddress[] getFormattingRanges() { ArrayList<CellRangeAddress> lst = new ArrayList<>(); for (Object stRef : _cf.getSqref()) { String[] regions = stRef.toString().split(" "); for (final String region : regions) { lst.add(CellRangeAddress.valueOf(region)); } } return lst.toArray(new CellRangeAddress[lst.size()]); }
/** * Returns the list of merged regions. If you want multiple regions, this is * faster than calling {@link #getMergedRegion(int)} each time. * * @return the list of merged regions */ @Override public List<CellRangeAddress> getMergedRegions() { List<CellRangeAddress> addresses = new ArrayList<>(); CTMergeCells ctMergeCells = worksheet.getMergeCells(); if(ctMergeCells == null) { return addresses; } for(CTMergeCell ctMergeCell : ctMergeCells.getMergeCellArray()) { String ref = ctMergeCell.getRef(); addresses.add(CellRangeAddress.valueOf(ref)); } return addresses; }
/** * Returns the errors currently being ignored and the ranges * where they are ignored. * * @return Map of error type to the range(s) where they are ignored. */ public Map<IgnoredErrorType, Set<CellRangeAddress>> getIgnoredErrors() { Map<IgnoredErrorType, Set<CellRangeAddress>> result = new LinkedHashMap<>(); if (worksheet.isSetIgnoredErrors()) { for (CTIgnoredError err : worksheet.getIgnoredErrors().getIgnoredErrorList()) { for (IgnoredErrorType errType : XSSFIgnoredErrorHelper.getErrorTypes(err)) { if (!result.containsKey(errType)) { result.put(errType, new LinkedHashSet<>()); } for (Object ref : err.getSqref()) { result.get(errType).add(CellRangeAddress.valueOf(ref.toString())); } } } } return result; }
/** * Returns the merged region at the specified index. If you want multiple * regions, it is faster to call {@link #getMergedRegions()} than to call * this each time. * * @return the merged region at the specified index */ @Override public CellRangeAddress getMergedRegion(int index) { CTMergeCells ctMergeCells = worksheet.getMergeCells(); if(ctMergeCells == null) { throw new IllegalStateException("This worksheet does not contain merged regions"); } CTMergeCell ctMergeCell = ctMergeCells.getMergeCellArray(index); String ref = ctMergeCell.getRef(); return CellRangeAddress.valueOf(ref); }
@Override public CellRangeAddress getArrayFormulaRange() { XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); if (cell == null) { throw new IllegalStateException("Cell " + getReference() + " is not part of an array formula."); } String formulaRef = cell._cell.getF().getRef(); return CellRangeAddress.valueOf(formulaRef); }
String[] regions = stRef.toString().split(" "); for (String region : regions) { cellRanges.add(CellRangeAddress.valueOf(region));
/*package*/ static void updateHyperlinks(Sheet sheet, FormulaShifter formulaShifter) { int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList(); for (Hyperlink hyperlink : hyperlinkList) { XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink; String cellRef = xhyperlink.getCellRef(); CellRangeAddress cra = CellRangeAddress.valueOf(cellRef); CellRangeAddress shiftedRange = BaseRowColShifter.shiftRange(formulaShifter, cra, sheetIndex); if (shiftedRange != null && shiftedRange != cra) { // shiftedRange should not be null. If shiftedRange is null, that means // that a hyperlink wasn't deleted at the beginning of shiftRows when // identifying rows that should be removed because they will be overwritten xhyperlink.setCellReference(shiftedRange.formatAsString()); } } }
void onReadCell(XSSFCell cell){ //collect cells holding shared formulas CTCell ct = cell.getCTCell(); CTCellFormula f = ct.getF(); if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) { // save a detached copy to avoid XmlValueDisconnectedException, // this may happen when the master cell of a shared formula is changed CTCellFormula sf = (CTCellFormula)f.copy(); CellRangeAddress sfRef = CellRangeAddress.valueOf(sf.getRef()); CellReference cellRef = new CellReference(cell); // If the shared formula range precedes the master cell then the preceding part is discarded, e.g. // if the cell is E60 and the shared formula range is C60:M85 then the effective range is E60:M85 // see more details in https://issues.apache.org/bugzilla/show_bug.cgi?id=51710 if(cellRef.getCol() > sfRef.getFirstColumn() || cellRef.getRow() > sfRef.getFirstRow()){ String effectiveRef = new CellRangeAddress( Math.max(cellRef.getRow(), sfRef.getFirstRow()), sfRef.getLastRow(), Math.max(cellRef.getCol(), sfRef.getFirstColumn()), sfRef.getLastColumn()).formatAsString(); sf.setRef(effectiveRef); } sharedFormulas.put((int)f.getSi(), sf); } if (f != null && f.getT() == STCellFormulaType.ARRAY && f.getRef() != null) { arrayFormulas.add(CellRangeAddress.valueOf(f.getRef())); } }
/** * 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, BaseXSSFEvaluationWorkbook fpb){ 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); SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007); Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex, getRowIndex()); Ptg[] fmla = sf.convertSharedFormulas(ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn()); return FormulaRenderer.toFormulaString(fpb, fmla); }
int maxColIndex = SpreadsheetVersion.EXCEL2007.getLastColumnIndex(); for (String part : parts) { CellRangeAddress range = CellRangeAddress.valueOf(part); if ((range.getFirstColumn() == 0 && range.getLastColumn() == maxColIndex)
/** * when a cell with a 'master' shared formula is removed, the next cell in the range becomes the master * @param cell The cell that is removed * @param evalWb BaseXSSFEvaluationWorkbook in use, if one exists */ protected void onDeleteFormula(XSSFCell cell, BaseXSSFEvaluationWorkbook evalWb){ CTCellFormula f = cell.getCTCell().getF(); if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) { CellRangeAddress ref = CellRangeAddress.valueOf(f.getRef()); if(ref.getNumberOfCells() > 1){ DONE: for(int i = cell.getRowIndex(); i <= ref.getLastRow(); i++){ XSSFRow row = getRow(i); if(row != null) for(int j = cell.getColumnIndex(); j <= ref.getLastColumn(); j++){ XSSFCell nextCell = row.getCell(j); if(nextCell != null && nextCell != cell && nextCell.getCellType() == CellType.FORMULA){ CTCellFormula nextF = nextCell.getCTCell().getF(); nextF.setStringValue(nextCell.getCellFormula(evalWb)); CellRangeAddress nextRef = new CellRangeAddress( nextCell.getRowIndex(), ref.getLastRow(), nextCell.getColumnIndex(), ref.getLastColumn()); nextF.setRef(nextRef.formatAsString()); sharedFormulas.put((int)nextF.getSi(), nextF); break DONE; } } } } } }
/** * @return array of <tt>CellRangeAddress</tt>s. Never <code>null</code> */ public CellRangeAddress[] getFormattingRanges(){ ArrayList<CellRangeAddress> lst = new ArrayList<CellRangeAddress>(); for (Object stRef : _cf.getSqref()) { String[] regions = stRef.toString().split(" "); for (int i = 0; i < regions.length; i++) { lst.add(CellRangeAddress.valueOf(regions[i])); } } return lst.toArray(new CellRangeAddress[lst.size()]); }
/** * @return array of <tt>CellRangeAddress</tt>s. Never <code>null</code> */ @Override public CellRangeAddress[] getFormattingRanges() { ArrayList<CellRangeAddress> lst = new ArrayList<>(); for (Object stRef : _cf.getSqref()) { String[] regions = stRef.toString().split(" "); for (final String region : regions) { lst.add(CellRangeAddress.valueOf(region)); } } return lst.toArray(new CellRangeAddress[lst.size()]); }
/** * @return the merged region at the specified index * @throws IllegalStateException if this worksheet does not contain merged regions */ public CellRangeAddress getMergedRegion(int index) { CTMergeCells ctMergeCells = worksheet.getMergeCells(); if(ctMergeCells == null) throw new IllegalStateException("This worksheet does not contain merged regions"); CTMergeCell ctMergeCell = ctMergeCells.getMergeCellArray(index); String ref = ctMergeCell.getRef(); return CellRangeAddress.valueOf(ref); }
/** * Returns the merged region at the specified index. If you want multiple * regions, it is faster to call {@link #getMergedRegions()} than to call * this each time. * * @return the merged region at the specified index */ @Override public CellRangeAddress getMergedRegion(int index) { CTMergeCells ctMergeCells = worksheet.getMergeCells(); if(ctMergeCells == null) { throw new IllegalStateException("This worksheet does not contain merged regions"); } CTMergeCell ctMergeCell = ctMergeCells.getMergeCellArray(index); String ref = ctMergeCell.getRef(); return CellRangeAddress.valueOf(ref); }
@Override public CellRangeAddress getArrayFormulaRange() { XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); if (cell == null) { throw new IllegalStateException("Cell " + getReference() + " is not part of an array formula."); } String formulaRef = cell._cell.getF().getRef(); return CellRangeAddress.valueOf(formulaRef); }
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); }
/** * Use Excel conditional formatting to shade alternating rows on the worksheet */ static void shadeAlt(Sheet sheet) { SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows"); sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)"); }
/*package*/ static void updateHyperlinks(Sheet sheet, FormulaShifter formulaShifter) { int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList(); for (Hyperlink hyperlink : hyperlinkList) { XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink; String cellRef = xhyperlink.getCellRef(); CellRangeAddress cra = CellRangeAddress.valueOf(cellRef); CellRangeAddress shiftedRange = BaseRowColShifter.shiftRange(formulaShifter, cra, sheetIndex); if (shiftedRange != null && shiftedRange != cra) { // shiftedRange should not be null. If shiftedRange is null, that means // that a hyperlink wasn't deleted at the beginning of shiftRows when // identifying rows that should be removed because they will be overwritten xhyperlink.setCellReference(shiftedRange.formatAsString()); } } }
private String convertSharedFormula(SharedFormulaDefinition formulaDefinition) { CellRangeAddress ref = CellRangeAddress.valueOf(formulaDefinition.getRef()); SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007); Ptg[] parsedTokens = FormulaParser.parse(formulaDefinition.getValue(), formulaParsingWorkbook, FormulaType.CELL, sheetIndex, current.getRow()); Ptg[] convertedTokens = sf.convertSharedFormulas(parsedTokens, current.getRow() - ref.getFirstRow(), current.getColumn() - ref.getFirstColumn()); // Formulas with links to other workbooks aren't supported return FormulaRenderer.toFormulaString(null, convertedTokens); }