/** * Returns the index of the sheet by his name * * @param name the sheet name * @return index of the sheet (0 based) */ @Override public int getSheetIndex(String name) { return _wb.getSheetIndex(name); }
@Override public int getSheetIndex(String sheetName) { return _uBook.getSheetIndex(sheetName); }
/** * Returns the index of the given sheet * * @param sheet the sheet to look up * @return index of the sheet (0 based) */ @Override public int getSheetIndex(Sheet sheet) { return _wb.getSheetIndex(getXSSFSheet((SXSSFSheet)sheet)); }
@Override public int getSheetIndex(EvaluationSheet evalSheet) { XSSFSheet sheet = ((XSSFEvaluationSheet)evalSheet).getXSSFSheet(); return _uBook.getSheetIndex(sheet); }
@Override public int getExternalSheetIndex(String sheetName) { int sheetIndex = _uBook.getSheetIndex(sheetName); return convertToExternalSheetIndex(sheetIndex); }
if (getSheetIndex(name) == -1) { return name;
private TestConferenceSchedulingScoreRulesReader(XSSFWorkbook workbook, ConferenceSolution initialSolution) { super(workbook, ConferenceSchedulingApp.SOLVER_CONFIG); this.numberOfSheets = workbook.getNumberOfSheets(); this.currentTestSheetIndex = workbook.getSheetIndex("Talks") + 1; this.initialSolution = initialSolution; this.roomMap = initialSolution.getRoomList().stream().collect( Collectors.toMap(Room::getName, Function.identity())); this.timeslotMap = initialSolution.getTimeslotList().stream().collect( Collectors.toMap(timeslot -> Pair.of(timeslot.getStartDateTime(), timeslot.getEndDateTime()), Function.identity())); this.columnIndexToDateMap = new HashMap<>(timeslotMap.size()); this.columnIndexToStartTimeMap = new HashMap<>(timeslotMap.size()); this.columnIndexToEndTimeMap = new HashMap<>(timeslotMap.size()); }
@Override public Ptg[] getFormulaTokens(EvaluationCell evalCell) { final XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell(); final int sheetIndex = _uBook.getSheetIndex(cell.getSheet()); final int rowIndex = cell.getRowIndex(); return FormulaParser.parse(cell.getCellFormula(this), this, FormulaType.CELL, sheetIndex, rowIndex); } }
/** * Parse cell formula and re-assemble it back using the new sheet name * * @param cell the cell to update */ private void updateFormula(XSSFCell cell, String oldName, String newName) { CTCellFormula f = cell.getCTCell().getF(); if (f != null) { String formula = f.getStringValue(); if (formula != null && formula.length() > 0) { int sheetIndex = _wb.getSheetIndex(cell.getSheet()); Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, FormulaType.CELL, sheetIndex, cell.getRowIndex()); for (Ptg ptg : ptgs) { updatePtg(ptg, oldName, newName); } String updatedFormula = FormulaRenderer.toFormulaString(_fpwb, ptgs); if (!formula.equals(updatedFormula)) { f.setStringValue(updatedFormula); } } } }
/** * 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 idx = getSheetIndex(sheetname); sheets.add(pos, sheets.remove(idx)); // Reorder CTSheets CTSheets ct = workbook.getSheets(); XmlObject cts = ct.getSheetArray(idx).copy(); workbook.getSheets().removeSheet(idx); CTSheet newcts = ct.insertNewSheet(pos); newcts.set(cts); //notify sheets //noinspection deprecation CTSheet[] sheetArray = ct.getSheetArray(); for(int i=0; i < sheetArray.length; i++) { sheets.get(i).sheet = sheetArray[i]; } updateNamedRangesAfterSheetReorder(idx, pos); updateActiveSheetAfterSheetReorder(idx, pos); }
/*package*/ static void updateConditionalFormatting(Sheet sheet, FormulaShifter formulaShifter) { XSSFSheet xsheet = (XSSFSheet) sheet; XSSFWorkbook wb = xsheet.getWorkbook(); int sheetIndex = wb.getSheetIndex(sheet); final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting
protected void adjustCellReferencesInsideFormula(Cell cell, Sheet destSheet, int deltaX, int deltaY){ XSSFWorkbook hostWorkbook = (XSSFWorkbook) destSheet.getWorkbook(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(hostWorkbook); Ptg[] ptgs = FormulaParser.parse(cell.getCellFormula(), fpb, FormulaType.CELL, 0); int destSheetIndex = hostWorkbook.getSheetIndex(destSheet); if(adjustInBothDirections(ptgs, destSheetIndex, deltaX, deltaY)) cell.setCellFormula(FormulaRenderer.toFormulaString(fpb, ptgs)); } }
/** * 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); }
private CellRangeAddress getRepeatingRowsOrColums(boolean rows) { int sheetIndex = getWorkbook().getSheetIndex(this); XSSFName name = getWorkbook().getBuiltInName( XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
/** * Shifts columns between startColumn and endColumn n number of columns. * If you use a negative number, it will shift columns left. * Code ensures that columns don't wrap around * * @param startColumn the column to start shifting * @param endColumn the column to end shifting * @param n length of the shifting step */ @Override public void shiftColumns(int startColumn, int endColumn, final int n) { XSSFVMLDrawing vml = getVMLDrawing(false); shiftCommentsForColumns(vml, startColumn, endColumn, n); FormulaShifter formulaShifter = FormulaShifter.createForColumnShift(this.getWorkbook().getSheetIndex(this), this.getSheetName(), startColumn, endColumn, n, SpreadsheetVersion.EXCEL2007); XSSFColumnShifter columnShifter = new XSSFColumnShifter(this); columnShifter.shiftColumns(startColumn, endColumn, n); columnShifter.shiftMergedRegions(startColumn, startColumn, n); columnShifter.updateFormulas(formulaShifter); columnShifter.updateConditionalFormatting(formulaShifter); columnShifter.updateHyperlinks(formulaShifter); columnShifter.updateNamedRanges(formulaShifter); rebuildRows(); }
int sheetIndex = getWorkbook().getSheetIndex(this);
XSSFVMLDrawing vml = getVMLDrawing(false); int sheetIndex = getWorkbook().getSheetIndex(this); String sheetName = getWorkbook().getSheetName(sheetIndex); FormulaShifter formulaShifter = FormulaShifter.createForRowShift(
final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet); final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex); final int srcRowNum = srcRow.getRowNum();
@Override public XSSFAutoFilter setAutoFilter(CellRangeAddress range) { CTAutoFilter af = worksheet.getAutoFilter(); if(af == null) { af = worksheet.addNewAutoFilter(); } CellRangeAddress norm = new CellRangeAddress(range.getFirstRow(), range.getLastRow(), range.getFirstColumn(), range.getLastColumn()); String ref = norm.formatAsString(); af.setRef(ref); XSSFWorkbook wb = getWorkbook(); int sheetIndex = getWorkbook().getSheetIndex(this); XSSFName name = wb.getBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex); if (name == null) { name = wb.createBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex); } name.getCTName().setHidden(true); CellReference r1 = new CellReference(getSheetName(), range.getFirstRow(), range.getFirstColumn(), true, true); CellReference r2 = new CellReference(null, range.getLastRow(), range.getLastColumn(), true, true); String fmla = r1.formatAsString() + ":" + r2.formatAsString(); name.setRefersToFormula(fmla); return new XSSFAutoFilter(this); }
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()), getRowIndex());