@Override public int getSheetIndex(String sheetName) { return _uBook.getSheetIndex(sheetName); }
@Override public int getSheetIndex(EvaluationSheet evalSheet) { HSSFSheet sheet = ((HSSFEvaluationSheet)evalSheet).getHSSFSheet(); return _uBook.getSheetIndex(sheet); } @Override
@Override public int getExternalSheetIndex(String sheetName) { int sheetIndex = _uBook.getSheetIndex(sheetName); return _iBook.checkExternSheet(sheetIndex); } @Override
private static List<Integer> getIndexesByName(String[] params, HSSFWorkbook workbook) { List<Integer> list = new ArrayList<>(); int pos = getAttributeIndex(SHEET_NAME_PARAM, params); if (-1 != pos) { if (pos >= params.length) { throw new IllegalArgumentException("sheet name param value was not specified"); } String sheetName = params[pos + 1]; int sheetPos = workbook.getSheetIndex(sheetName); if (-1 == sheetPos){ throw new IllegalArgumentException("specified sheet name has not been found in xls file"); } list.add(sheetPos); } return list; }
/** * Returns the name of this sheet * * @return the name of this sheet */ @SuppressWarnings("resource") @Override public String getSheetName() { HSSFWorkbook wb = getWorkbook(); int idx = wb.getSheetIndex(this); return wb.getSheetName(idx); }
private NameRecord getBuiltinNameRecord(byte builtinCode) { int sheetIndex = _workbook.getSheetIndex(this); int recIndex = _workbook.findExistingBuiltinNameRecordIdx(sheetIndex, builtinCode); if (recIndex == -1) { return null; } return _workbook.getNameRecord(recIndex); }
@SuppressWarnings("resource") private Ptg[] createListFormula(HSSFSheet sheet) { if (_explicitListValues == null) { HSSFWorkbook wb = sheet.getWorkbook(); // formula is parsed with slightly different RVA rules: (root node type must be 'reference') return HSSFFormulaParser.parse(_formula1, wb, FormulaType.DATAVALIDATION_LIST, wb.getSheetIndex(sheet)); // To do: Excel places restrictions on the available operations within a list formula. // Some things like union and intersection are not allowed. } // explicit list was provided StringBuilder sb = new StringBuilder(_explicitListValues.length * 16); for (int i = 0; i < _explicitListValues.length; i++) { if (i > 0) { sb.append('\0'); // list delimiter is the nul char } sb.append(_explicitListValues[i]); } return new Ptg[] { new StringPtg(sb.toString()), }; }
/** * @return The parsed token array representing the formula or value specified. * Empty array if both formula and value are <code>null</code> */ @SuppressWarnings("resource") private static Ptg[] convertDoubleFormula(String formula, Double value, HSSFSheet sheet) { if (formula == null) { if (value == null) { return Ptg.EMPTY_PTG_ARRAY; } return new Ptg[] { new NumberPtg(value.doubleValue()), }; } if (value != null) { throw new IllegalStateException("Both formula and value cannot be present"); } HSSFWorkbook wb = sheet.getWorkbook(); return HSSFFormulaParser.parse(formula, wb, FormulaType.CELL, wb.getSheetIndex(sheet)); }
/** * TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea * this call will produce the wrong results if the formula contains any cell references * One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int) * Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects. * * @param formula The formula to parse, excluding the leading equals sign. * @param sheet The sheet that the formula is on. * @return <code>null</code> if <tt>formula</tt> was null. */ public static Ptg[] parseFormula(String formula, HSSFSheet sheet) { if(formula == null) { return null; } int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); return HSSFFormulaParser.parse(formula, sheet.getWorkbook(), FormulaType.CELL, sheetIndex); }
private int getSheetExtIx(SheetIdentifier sheetIden) { int extIx; if (sheetIden == null) { extIx = -1; } else { String workbookName = sheetIden.getBookName(); String firstSheetName = sheetIden.getSheetIdentifier().getName(); String lastSheetName = firstSheetName; if (sheetIden instanceof SheetRangeIdentifier) { lastSheetName = ((SheetRangeIdentifier)sheetIden).getLastSheetIdentifier().getName(); } if (workbookName == null) { int firstSheetIndex = _uBook.getSheetIndex(firstSheetName); int lastSheetIndex = _uBook.getSheetIndex(lastSheetName); extIx = _iBook.checkExternSheet(firstSheetIndex, lastSheetIndex); } else { extIx = _iBook.getExternalSheetIndex(workbookName, firstSheetName, lastSheetName); } } return extIx; }
/** * sets the order of appearance for a given sheet. * * @param sheetname the name of the sheet to reorder * @param pos the position that we want to insert the sheet into (0 based) */ @Override public void setSheetOrder(String sheetname, int pos ) { int oldSheetIndex = getSheetIndex(sheetname); _sheets.add(pos,_sheets.remove(oldSheetIndex)); workbook.setSheetOrder(sheetname, pos); FormulaShifter shifter = FormulaShifter.createForSheetShift(oldSheetIndex, pos); for (HSSFSheet sheet : _sheets) { sheet.getSheet().updateFormulasAfterCellShift(shifter, /* not used */ -1 ); } workbook.updateNamesAfterCellShift(shifter); updateNamedRangesAfterSheetReorder(oldSheetIndex, pos); updateActiveSheetAfterSheetReorder(oldSheetIndex, pos); }
@Override public CellRange<HSSFCell> setArrayFormula(String formula, CellRangeAddress range) { // make sure the formula parses OK first int sheetIndex = _workbook.getSheetIndex(this); Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, sheetIndex); CellRange<HSSFCell> cells = getCellRange(range); for (HSSFCell c : cells) { c.setCellArrayFormula(range); } HSSFCell mainArrayFormulaCell = cells.getTopLeftCell(); FormulaRecordAggregate agg = (FormulaRecordAggregate) mainArrayFormulaCell.getCellValueRecord(); agg.setArrayFormula(range, ptgs); return cells; }
private void setRepeatingRowsAndColumns( CellRangeAddress rowDef, CellRangeAddress colDef) { int sheetIndex = _workbook.getSheetIndex(this); int maxRowIndex = SpreadsheetVersion.EXCEL97.getLastRowIndex(); int maxColIndex = SpreadsheetVersion.EXCEL97.getLastColumnIndex();
/** * Shifts columns in range [startColumn, endColumn] for n places to the right. * For n < 0, it will shift columns left. * Additionally adjusts formulas. * Probably should also process other features (hyperlinks, comments...) in the way analog to shiftRows method * @param startRow the row to start shifting * @param endRow the row to end shifting * @param n the number of rows to shift */ @Beta @Override public void shiftColumns(int startColumn, int endColumn, int n){ HSSFColumnShifter columnShifter = new HSSFColumnShifter(this); columnShifter.shiftColumns(startColumn, endColumn, n); int sheetIndex = _workbook.getSheetIndex(this); short externSheetIndex = _book.checkExternSheet(sheetIndex); String sheetName = _workbook.getSheetName(sheetIndex); FormulaShifter formulaShifter = FormulaShifter.createForColumnShift( externSheetIndex, sheetName, startColumn, endColumn, n, SpreadsheetVersion.EXCEL97); updateFormulasForShift(formulaShifter); // add logic for hyperlinks etc, like in shiftRows() }
private void updateFormulasForShift(FormulaShifter formulaShifter) { int sheetIndex = _workbook.getSheetIndex(this); short externSheetIndex = _book.checkExternSheet(sheetIndex); // update formulas on this sheet that point to rows which have been moved _sheet.updateFormulasAfterCellShift(formulaShifter, externSheetIndex); // update formulas on other sheets that point to rows that have been moved on this sheet int nSheets = _workbook.getNumberOfSheets(); for (int i = 0; i < nSheets; i++) { InternalSheet otherSheet = _workbook.getSheetAt(i).getSheet(); if (otherSheet == this._sheet) { continue; } short otherExtSheetIx = _book.checkExternSheet(i); otherSheet.updateFormulasAfterCellShift(formulaShifter, otherExtSheetIx); } _workbook.getWorkbook().updateNamesAfterCellShift(formulaShifter); }
public void setCellFormula(String formula) { if(isPartOfArrayFormulaGroup()){ notifyArrayFormulaChanging(); } int row=_record.getRow(); short col=_record.getColumn(); short styleIndex=_record.getXFIndex(); if (formula==null) { notifyFormulaChanging(); setCellType(CellType.BLANK, false, row, col, styleIndex); return; } int sheetIndex = _book.getSheetIndex(_sheet); Ptg[] ptgs = HSSFFormulaParser.parse(formula, _book, FormulaType.CELL, sheetIndex); setCellType(CellType.FORMULA, false, row, col, styleIndex); FormulaRecordAggregate agg = (FormulaRecordAggregate) _record; FormulaRecord frec = agg.getFormulaRecord(); frec.setOptions((short) 2); frec.setValue(0); //only set to default if there is no extended format index already set if (agg.getXFIndex() == (short)0) { agg.setXFIndex((short) 0x0f); } agg.setParsedExpression(ptgs); } /**
@Override public HSSFAutoFilter setAutoFilter(CellRangeAddress range) { InternalWorkbook workbook = _workbook.getWorkbook(); int sheetIndex = _workbook.getSheetIndex(this);
int sheetIndex = _workbook.getSheetIndex(this); short externSheetIndex = _book.checkExternSheet(sheetIndex); String sheetName = _workbook.getSheetName(sheetIndex);
/** * Returns the name of this sheet * * @return the name of this sheet */ @SuppressWarnings("resource") @Override public String getSheetName() { HSSFWorkbook wb = getWorkbook(); int idx = wb.getSheetIndex(this); return wb.getSheetName(idx); }
/** * Returns the name of this sheet * * @return the name of this sheet */ public String getSheetName() { HSSFWorkbook wb = getWorkbook(); int idx = wb.getSheetIndex(this); return wb.getSheetName(idx); }