public void Bug47737() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("47737.xlsx"); Assert.AreEqual(2, wb.NumberOfNames); Assert.IsNotNull(wb.GetCalculationChain()); XSSFName nm0 = (XSSFName)wb.GetNameAt(0); Assert.IsTrue(nm0.GetCTName().IsSetLocalSheetId()); Assert.AreEqual(0u, nm0.GetCTName().localSheetId); XSSFName nm1 = (XSSFName)wb.GetNameAt(1); Assert.IsTrue(nm1.GetCTName().IsSetLocalSheetId()); Assert.AreEqual(1u, nm1.GetCTName().localSheetId); wb.RemoveSheetAt(0); Assert.AreEqual(1, wb.NumberOfNames); XSSFName nm2 = (XSSFName)wb.GetNameAt(0); Assert.IsTrue(nm2.GetCTName().IsSetLocalSheetId()); Assert.AreEqual(0u, nm2.GetCTName().localSheetId); //calculation chain is Removed as well Assert.IsNull(wb.GetCalculationChain()); }
public IEvaluationName GetName(String name, int sheetIndex) { for (int i = 0; i < _uBook.NumberOfNames; i++) { IName nm = _uBook.GetNameAt(i); String nameText = nm.NameName; int nameSheetindex = nm.SheetIndex; if (name.Equals(nameText, StringComparison.CurrentCultureIgnoreCase) && (nameSheetindex == -1 || nameSheetindex == sheetIndex)) { return(new Name(_uBook.GetNameAt(i), i, this)); } } return(sheetIndex == -1 ? null : GetName(name, -1)); }
/** * Update sheet name in all formulas and named ranges. * Called from {@link XSSFWorkbook#setSheetName(int, String)} * <p/> * <p> * The idea is to parse every formula and render it back to string * with the updated sheet name. This is done by parsing into Ptgs, * looking for ones with sheet references in them, and changing those * </p> * * @param sheetIndex the 0-based index of the sheet being changed * @param oldName the old sheet name * @param newName the new sheet name */ public void UpdateSheetName(int sheetIndex, string oldName, string newName) { // update named ranges for (int i = 0; i < _wb.NumberOfNames; i++) { IName nm = _wb.GetNameAt(i); if (nm.SheetIndex == -1 || nm.SheetIndex == sheetIndex) { UpdateName(nm, oldName, newName); } } // update formulas foreach (ISheet sh in _wb) { foreach (IRow row in sh) { foreach (ICell cell in row) { if (cell.CellType == CellType.Formula) { UpdateFormula((XSSFCell)cell, oldName, newName); } } } } }
/** * Update sheet name in all formulas and named ranges. * Called from {@link XSSFWorkbook#SetSheetName(int, String)} * <p/> * <p> * The idea is to parse every formula and render it back to string * with the updated sheet name. The IFormulaParsingWorkbook passed to the formula Parser * is constructed from the old workbook (sheet name is not yet updated) and * the FormulaRenderingWorkbook passed to FormulaRenderer#toFormulaString is a custom implementation that * returns the new sheet name. * </p> * * @param sheetIndex the 0-based index of the sheet being Changed * @param name the new sheet name */ public void UpdateSheetName(int sheetIndex, String name) { /** * An instance of FormulaRenderingWorkbook that returns */ IFormulaRenderingWorkbook frwb = new XSSFFormulaRenderingWorkbook(_fpwb, sheetIndex, name); // update named ranges for (int i = 0; i < _wb.NumberOfNames; i++) { IName nm = _wb.GetNameAt(i); if (nm.SheetIndex == -1 || nm.SheetIndex == sheetIndex) { UpdateName(nm, frwb); } } // update formulas foreach (ISheet sh in _wb) { foreach (IRow row in sh) { foreach (ICell cell in row) { if (cell.CellType == CellType.Formula) { UpdateFormula((XSSFCell)cell, frwb); } } } } }
public void TestRemoveSheet() { // Test removing a sheet maintains the named ranges correctly XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet("Sheet1"); wb.CreateSheet("Sheet2"); XSSFName sheet1Name = wb.CreateName() as XSSFName; sheet1Name.NameName = "name1"; sheet1Name.SheetIndex = 0; sheet1Name.RefersToFormula = "Sheet1!$A$1"; XSSFName sheet2Name = wb.CreateName() as XSSFName; sheet2Name.NameName = "name1"; sheet2Name.SheetIndex = 1; sheet2Name.RefersToFormula = "Sheet2!$A$1"; Assert.IsTrue(wb.GetAllNames().Contains(sheet1Name)); Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name)); Assert.AreEqual(2, wb.GetNames("name1").Count); Assert.AreEqual(sheet1Name, wb.GetNames("name1")[0]); Assert.AreEqual(sheet2Name, wb.GetNames("name1")[1]); // Remove sheet1, we should only have sheet2Name now wb.RemoveSheetAt(0); Assert.IsFalse(wb.GetAllNames().Contains(sheet1Name)); Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name)); Assert.AreEqual(1, wb.GetNames("name1").Count); Assert.AreEqual(sheet2Name, wb.GetNames("name1")[0]); // Check by index as well for sanity Assert.AreEqual(1, wb.NumberOfNames); Assert.AreEqual(0, wb.GetNameIndex("name1")); Assert.AreEqual(sheet2Name, wb.GetNameAt(0)); wb.Close(); }
private List <IName> GetNamesFromWorkbook(XSSFWorkbook workbook) { List <IName> names = new List <IName>(); int numberOfNames = workbook.NumberOfNames; for (int i = 0; i < numberOfNames; i++) { names.Add(workbook.GetNameAt(i)); } return(names); }
public IName GetNameAt(int nameIndex) { return(XssfWorkbook.GetNameAt(nameIndex)); }
public void TestRepeatingRowsAndColums() { // First Test that Setting RR&C for same sheet more than once only Creates a // single Print_Titles built-in record XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = (XSSFSheet)wb.CreateSheet("First Sheet"); sheet1.RepeatingRows = (null); sheet1.RepeatingColumns = (null); // Set repeating rows and columns twice for the first sheet for (int i = 0; i < 2; i++) { sheet1.RepeatingRows = (CellRangeAddress.ValueOf("1:4")); sheet1.RepeatingColumns = (CellRangeAddress.ValueOf("A:A")); //sheet.CreateFreezePane(0, 3); } Assert.AreEqual(1, wb.NumberOfNames); IName nr1 = wb.GetNameAt(0); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName); Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula); //remove the columns part sheet1.RepeatingColumns = (null); Assert.AreEqual("'First Sheet'!$1:$4", nr1.RefersToFormula); //revert sheet1.RepeatingColumns = (CellRangeAddress.ValueOf("A:A")); //remove the rows part sheet1.RepeatingRows = (null); Assert.AreEqual("'First Sheet'!$A:$A", nr1.RefersToFormula); //revert sheet1.RepeatingRows = (CellRangeAddress.ValueOf("1:4")); // Save and re-open IWorkbook nwb = XSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.AreEqual(1, nwb.NumberOfNames); nr1 = nwb.GetNameAt(0); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName); Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula); // check that Setting RR&C on a second sheet causes a new Print_Titles built-in // name to be Created XSSFSheet sheet2 = (XSSFSheet)nwb.CreateSheet("SecondSheet"); sheet2.RepeatingRows = (CellRangeAddress.ValueOf("1:1")); sheet2.RepeatingColumns = (CellRangeAddress.ValueOf("B:C")); Assert.AreEqual(2, nwb.NumberOfNames); IName nr2 = nwb.GetNameAt(1); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr2.NameName); Assert.AreEqual("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.RefersToFormula); sheet2.RepeatingRows = (null); sheet2.RepeatingColumns = (null); }