@Override public Ptg[] getFormulaTokens(EvaluationCell evalCell) { HSSFCell cell = ((HSSFEvaluationCell)evalCell).getHSSFCell(); // re-parsing the formula text also works, but is a waste of time // return HSSFFormulaParser.parse(cell.getCellFormula(), _uBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); // It is useful within the tests to make sure that all formulas POI can evaluate can also be parsed. // see HSSFFileHandler.handleFile instead FormulaRecordAggregate fra = (FormulaRecordAggregate) cell.getCellValueRecord(); return fra.getFormulaTokens(); }
protected void adjustCellReferencesInsideFormula(Cell cell, Sheet destSheet, int deltaX, int deltaY) { FormulaRecordAggregate fra = (FormulaRecordAggregate)((HSSFCell)cell).getCellValueRecord(); int destSheetIndex = destSheet.getWorkbook().getSheetIndex(destSheet); Ptg[] ptgs = fra.getFormulaTokens(); if(adjustInBothDirections(ptgs, destSheetIndex, deltaX, deltaY)) fra.setParsedExpression(ptgs); } }
@Override public CellRange<HSSFCell> removeArrayFormula(Cell cell) { if (cell.getSheet() != this) { throw new IllegalArgumentException("Specified cell does not belong to this sheet."); } CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord(); if (!(rec instanceof FormulaRecordAggregate)) { String ref = new CellReference(cell).formatAsString(); throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula."); } FormulaRecordAggregate fra = (FormulaRecordAggregate) rec; CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex()); CellRange<HSSFCell> result = getCellRange(range); // clear all cells in the range for (Cell c : result) { c.setCellType(CellType.BLANK); } return result; }
@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; }
/** * Use this to create new cells within the row and return it. * <p> * The cell that is returned will be of the requested type. * The type can be changed either through calling setCellValue * or setCellType, but there is a small overhead to doing this, * so it is best to create the required type up front. * * @param columnIndex - the column number this cell represents * * @return HSSFCell a high level representation of the created cell. * @throws IllegalArgumentException if columnIndex < 0 or greater than 255, * the maximum number of columns supported by the Excel binary format (.xls) */ @Override public HSSFCell createCell(int columnIndex, CellType type) { short shortCellNum = (short)columnIndex; if(columnIndex > 0x7FFF) { shortCellNum = (short)(0xffff - columnIndex); } HSSFCell cell = new HSSFCell(book, sheet, getRowNum(), shortCellNum, type); addCell(cell); sheet.getSheet().addValueRecord(getRowNum(), cell.getCellValueRecord()); return cell; }
private void removeCell(HSSFCell cell, boolean alsoRemoveRecords) { int column=cell.getColumnIndex(); if(column < 0) { throw new RuntimeException("Negative cell indexes not allowed"); } if(column >= cells.length || cell != cells[column]) { throw new RuntimeException("Specified cell is not from this row"); } if(cell.isPartOfArrayFormulaGroup()){ cell.notifyArrayFormulaChanging(); } cells[column]=null; if(alsoRemoveRecords) { CellValueRecordInterface cval = cell.getCellValueRecord(); sheet.getSheet().removeValueRecord(getRowNum(), cval); } if (cell.getColumnIndex()+1 == row.getLastCol()) { row.setLastCol(calculateNewLastCellPlusOne(row.getLastCol())); } if (cell.getColumnIndex() == row.getFirstCol()) { row.setFirstCol(calculateNewFirstCell(row.getFirstCol())); } }
HSSFHyperlink link = cell.getHyperlink(); row.removeCell(cell); CellValueRecordInterface cellRecord = cell.getCellValueRecord(); cellRecord.setRow(rowNum + n); row2Replace.createCellFromRecord(cellRecord);
for (Cell cellI : row) { HSSFCell cell = (HSSFCell) cellI; short oldXf = cell.getCellValueRecord().getXFIndex(); for (Row row : s) { for (Cell cell : row) { short oldXf = ((HSSFCell) cell).getCellValueRecord().getXFIndex();
@Override public Ptg[] getFormulaTokens(EvaluationCell evalCell) { HSSFCell cell = ((HSSFEvaluationCell)evalCell).getHSSFCell(); // re-parsing the formula text also works, but is a waste of time // return HSSFFormulaParser.parse(cell.getCellFormula(), _uBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); // It is useful within the tests to make sure that all formulas POI can evaluate can also be parsed. // see HSSFFileHandler.handleFile instead FormulaRecordAggregate fra = (FormulaRecordAggregate) cell.getCellValueRecord(); return fra.getFormulaTokens(); }
public Ptg[] getFormulaTokens(EvaluationCell evalCell) { HSSFCell cell = ((HSSFEvaluationCell)evalCell).getHSSFCell(); if (false) { // re-parsing the formula text also works, but is a waste of time // It is useful from time to time to run all unit tests with this code // to make sure that all formulas POI can evaluate can also be parsed. try { return HSSFFormulaParser.parse(cell.getCellFormula(), _uBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); } catch (FormulaParseException e) { // Note - as of Bugzilla 48036 (svn r828244, r828247) POI is capable of evaluating // IntesectionPtg. However it is still not capable of parsing it. // So FormulaEvalTestData.xls now contains a few formulas that produce errors here. logger.log( POILogger.ERROR, e.getMessage()); } } FormulaRecordAggregate fra = (FormulaRecordAggregate) cell.getCellValueRecord(); return fra.getFormulaTokens(); } public UDFFinder getUDFFinder(){
public Ptg[] getFormulaTokens(EvaluationCell evalCell) { HSSFCell cell = ((HSSFEvaluationCell)evalCell).getHSSFCell(); if (false) { // re-parsing the formula text also works, but is a waste of time // It is useful from time to time to run all unit tests with this code // to make sure that all formulas POI can evaluate can also be parsed. try { return HSSFFormulaParser.parse(cell.getCellFormula(), _uBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); } catch (FormulaParseException e) { // Note - as of Bugzilla 48036 (svn r828244, r828247) POI is capable of evaluating // IntesectionPtg. However it is still not capable of parsing it. // So FormulaEvalTestData.xls now contains a few formulas that produce errors here. System.err.println(e.getMessage()); } } FormulaRecordAggregate fra = (FormulaRecordAggregate) cell.getCellValueRecord(); return fra.getFormulaTokens(); } public UDFFinder getUDFFinder(){
protected void adjustCellReferencesInsideFormula(Cell cell, Sheet destSheet, int deltaX, int deltaY) { FormulaRecordAggregate fra = (FormulaRecordAggregate)((HSSFCell)cell).getCellValueRecord(); int destSheetIndex = destSheet.getWorkbook().getSheetIndex(destSheet); Ptg[] ptgs = fra.getFormulaTokens(); if(adjustInBothDirections(ptgs, destSheetIndex, deltaX, deltaY)) fra.setParsedExpression(ptgs); } }
public CellRange<HSSFCell> removeArrayFormula(Cell cell) { if (cell.getSheet() != this) { throw new IllegalArgumentException("Specified cell does not belong to this sheet."); } CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord(); if (!(rec instanceof FormulaRecordAggregate)) { String ref = new CellReference(cell).formatAsString(); throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula."); } FormulaRecordAggregate fra = (FormulaRecordAggregate) rec; CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex()); CellRange<HSSFCell> result = getCellRange(range); // clear all cells in the range for (Cell c : result) { c.setCellType(Cell.CELL_TYPE_BLANK); } return result; }
public CellRange<HSSFCell> removeArrayFormula(Cell cell) { if (cell.getSheet() != this) { throw new IllegalArgumentException("Specified cell does not belong to this sheet."); } CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord(); if (!(rec instanceof FormulaRecordAggregate)) { String ref = new CellReference(cell).formatAsString(); throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula."); } FormulaRecordAggregate fra = (FormulaRecordAggregate) rec; CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex()); CellRange<HSSFCell> result = getCellRange(range); // clear all cells in the range for (Cell c : result) { c.setCellType(Cell.CELL_TYPE_BLANK); } return result; }
@Override public CellRange<HSSFCell> removeArrayFormula(Cell cell) { if (cell.getSheet() != this) { throw new IllegalArgumentException("Specified cell does not belong to this sheet."); } CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord(); if (!(rec instanceof FormulaRecordAggregate)) { String ref = new CellReference(cell).formatAsString(); throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula."); } FormulaRecordAggregate fra = (FormulaRecordAggregate) rec; CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex()); CellRange<HSSFCell> result = getCellRange(range); // clear all cells in the range for (Cell c : result) { c.setCellType(CellType.BLANK); } return result; }
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; }
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; }
@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; }
/** * Use this to create new cells within the row and return it. * <p> * The cell that is returned is a CELL_TYPE_BLANK. The type can be changed * either through calling setCellValue or setCellType. * * @param columnIndex - the column number this cell represents * * @return HSSFCell a high level representation of the created cell. * @throws IllegalArgumentException if columnIndex < 0 or greater than 255, * the maximum number of columns supported by the Excel binary format (.xls) */ public HSSFCell createCell(int columnIndex, int type) { short shortCellNum = (short)columnIndex; if(columnIndex > 0x7FFF) { shortCellNum = (short)(0xffff - columnIndex); } HSSFCell cell = new HSSFCell(book, sheet, getRowNum(), shortCellNum, type); addCell(cell); sheet.getSheet().addValueRecord(getRowNum(), cell.getCellValueRecord()); return cell; }
private void removeCell(HSSFCell cell, boolean alsoRemoveRecords) { int column=cell.getColumnIndex(); if(column < 0) { throw new RuntimeException("Negative cell indexes not allowed"); } if(column >= cells.length || cell != cells[column]) { throw new RuntimeException("Specified cell is not from this row"); } if(cell.isPartOfArrayFormulaGroup()){ cell.notifyArrayFormulaChanging(); } cells[column]=null; if(alsoRemoveRecords) { CellValueRecordInterface cval = cell.getCellValueRecord(); sheet.getSheet().removeValueRecord(getRowNum(), cval); } if (cell.getColumnIndex()+1 == row.getLastCol()) { row.setLastCol(calculateNewLastCellPlusOne(row.getLastCol())); } if (cell.getColumnIndex() == row.getFirstCol()) { row.setFirstCol(calculateNewFirstCell(row.getFirstCol())); } }