public void TestEvaluate() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls"); HSSFWorkbook wb2 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls"); CellReference cellRef = new CellReference(wb.GetName("QUANT").RefersToFormula); ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); cell.SetCellValue(NEW_QUANT); cell = wb2.GetSheet("CostSheet").GetRow(1).GetCell(1); cell.SetCellValue(NEW_PART_COST); HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb); HSSFFormulaEvaluator EvaluatorCost = new HSSFFormulaEvaluator(wb2); String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" }; HSSFFormulaEvaluator[] Evaluators = { Evaluator, EvaluatorCost, }; HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators); cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula); ICell uccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); cellRef = new CellReference(wb.GetName("COST").RefersToFormula); ICell ccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula); ICell tccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); Evaluator.EvaluateFormulaCell(uccell); Evaluator.EvaluateFormulaCell(ccell); Evaluator.EvaluateFormulaCell(tccell); Assert.AreEqual(NEW_PART_COST, uccell.NumericCellValue); Assert.AreEqual(NEW_PART_COST * NEW_QUANT, ccell.NumericCellValue); Assert.AreEqual(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.NumericCellValue); }
public void TestReadCalcSheet() { try { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls"); Assert.AreEqual("Sheet1!$A$2", wb.GetName("QUANT").RefersToFormula); Assert.AreEqual("Sheet1!$B$2", wb.GetName("PART").RefersToFormula); Assert.AreEqual("x123", wb.GetSheet("Sheet1").GetRow(1).GetCell(1).StringCellValue); Assert.AreEqual("Sheet1!$C$2", wb.GetName("UNITCOST").RefersToFormula); CellReference cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula); ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula); Assert.AreEqual("Sheet1!$D$2", wb.GetName("COST").RefersToFormula); cellRef = new CellReference(wb.GetName("COST").RefersToFormula); cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); Assert.AreEqual("UNITCOST*Quant", cell.CellFormula); Assert.AreEqual("Sheet1!$E$2", wb.GetName("TOTALCOST").RefersToFormula); cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula); cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula); } catch (Exception e) { Assert.Fail(e.Message); } }
public void TestReadReferencedSheet() { try { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls"); Assert.AreEqual("CostSheet!$A$2:$B$3", wb.GetName("COSTS").RefersToFormula); Assert.AreEqual("x123", wb.GetSheet("CostSheet").GetRow(1).GetCell(0).StringCellValue); Assert.AreEqual(PART_COST, wb.GetSheet("CostSheet").GetRow(1).GetCell(1).NumericCellValue); Assert.AreEqual("MarkupSheet!$B$1", wb.GetName("Markup_Cost").RefersToFormula); Assert.AreEqual(MARKUP_COST_1, wb.GetSheet("MarkupSheet").GetRow(0).GetCell(1).NumericCellValue); } catch (Exception e) { Assert.Fail(e.Message); } }
public CellReference[] GetCells(string name) { var iName = _workBook.GetName(name); var area = new AreaReference(iName.RefersToFormula); return(area.GetAllReferencedCells()); }
public void TestNames() { HSSFWorkbook wb = new HSSFWorkbook(); try { wb.GetNameAt(0); Assert.Fail("Fails without any defined names"); } catch (ArgumentException e) { //Assert.IsTrue(e.Message.Contains("no defined names"), e.Message); } HSSFName name = (HSSFName)wb.CreateName(); Assert.IsNotNull(name); Assert.IsNull(wb.GetName("somename")); name.NameName = ("myname"); Assert.IsNotNull(wb.GetName("myname")); Assert.AreEqual(0, wb.GetNameIndex(name)); Assert.AreEqual(0, wb.GetNameIndex("myname")); try { wb.GetNameAt(5); Assert.Fail("Fails without any defined names"); } catch (ArgumentException e) { //Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message); } try { wb.GetNameAt(-3); Assert.Fail("Fails without any defined names"); } catch (ArgumentException e) { //Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message); } }
public void TestBug58085RemoveSheetWithNames() { HSSFWorkbook wb1 = new HSSFWorkbook(); ISheet sheet1 = wb1.CreateSheet("sheet1"); ISheet sheet2 = wb1.CreateSheet("sheet2"); ISheet sheet3 = wb1.CreateSheet("sheet3"); sheet1.CreateRow(0).CreateCell((short)0).SetCellValue("val1"); sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("val2"); sheet3.CreateRow(0).CreateCell((short)0).SetCellValue("val3"); IName namedCell1 = wb1.CreateName(); namedCell1.NameName = (/*setter*/ "name1"); String reference1 = "sheet1!$A$1"; namedCell1.RefersToFormula = (/*setter*/ reference1); IName namedCell2 = wb1.CreateName(); namedCell2.NameName = (/*setter*/ "name2"); String reference2 = "sheet2!$A$1"; namedCell2.RefersToFormula = (/*setter*/ reference2); IName namedCell3 = wb1.CreateName(); namedCell3.NameName = (/*setter*/ "name3"); String reference3 = "sheet3!$A$1"; namedCell3.RefersToFormula = (/*setter*/ reference3); HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1); wb1.Close(); IName nameCell = wb2.GetName("name1"); Assert.AreEqual("sheet1!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name2"); Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name3"); Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula); wb2.RemoveSheetAt(wb2.GetSheetIndex("sheet1")); nameCell = wb2.GetName("name1"); Assert.AreEqual("#REF!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name2"); Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name3"); Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula); wb2.Close(); }
//Crea una tabla a partir de los parametros indicados public void CrearTabla(List <string> Columns, int Rows, int X, int Y, string headerText, string tableName, string hoja) { ISheet Sheet1 = hssfworkbook.GetSheet(hoja) != null?hssfworkbook.GetSheet(hoja) : hssfworkbook.CreateSheet(hoja); ICell Title = Sheet1.GetRow(Y) != null ? (Sheet1.GetRow(Y).GetCell(X) != null ? Sheet1.GetRow(Y).GetCell(X) : Sheet1.GetRow(Y).CreateCell(X)) : Sheet1.CreateRow(Y).CreateCell(X); Title.CellStyle = EstTitulos; Title.SetCellValue(headerText); Sheet1.AddMergedRegion(new CellRangeAddress(Y, Y + 1, X, X + Columns.Count - 1)); /* CAMPOS */ IRow RowColumns = Sheet1.GetRow(Y + 2) != null?Sheet1.GetRow(Y + 2) : Sheet1.CreateRow(Y + 2); for (int c = 0; c < Columns.Count; c++) { ICell CellColumn = RowColumns.GetCell(X + c) != null?RowColumns.GetCell(X + c) : RowColumns.CreateCell(X + c); CellColumn.CellStyle = EstSubtitulos; CellColumn.SetCellValue(Columns[c]); Sheet1.AutoSizeColumn(CellColumn.ColumnIndex); } for (int i = 0; i < Rows; i++) { IRow CurrentRow = Sheet1.GetRow(Y + i + 3) != null?Sheet1.GetRow(Y + i + 3) : Sheet1.CreateRow(Y + i + 3); for (int j = 0; j < Columns.Count; j++) { ICell currentCell = CurrentRow.GetCell(X + j) != null?CurrentRow.GetCell(X + j) : CurrentRow.CreateCell(X + j); currentCell.CellStyle = EstCeldas; } } IName name = hssfworkbook.CreateName(); if (hssfworkbook.GetName(tableName) == null) { name.NameName = tableName; } else { throw new Exception("No se pueden crear dos espacios de nombre iguales."); } name.RefersToFormula = "'" + hoja + "'" + "!$" + getChar(X + 1) + "$" + (X + 3) + ":$" + getChar(Y + Columns.Count) + "$" + (X + 3 + Rows); }
public void TestSetSheetOrderHSSF() { IWorkbook wb = new HSSFWorkbook(); ISheet s1 = wb.CreateSheet("first sheet"); ISheet s2 = wb.CreateSheet("other sheet"); IName name1 = wb.CreateName(); name1.NameName = (/*setter*/ "name1"); name1.RefersToFormula = (/*setter*/ "'first sheet'!D1"); IName name2 = wb.CreateName(); name2.NameName = (/*setter*/ "name2"); name2.RefersToFormula = (/*setter*/ "'other sheet'!C1"); IRow s1r1 = s1.CreateRow(2); ICell c1 = s1r1.CreateCell(3); c1.SetCellValue(30); ICell c2 = s1r1.CreateCell(2); c2.CellFormula = (/*setter*/ "SUM('other sheet'!C1,'first sheet'!C1)"); IRow s2r1 = s2.CreateRow(0); ICell c3 = s2r1.CreateCell(1); c3.CellFormula = (/*setter*/ "'first sheet'!D3"); ICell c4 = s2r1.CreateCell(2); c4.CellFormula = (/*setter*/ "'other sheet'!D3"); // conditional formatting ISheetConditionalFormatting sheetCF = s1.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1"); IConditionalFormattingRule[] cfRules = { rule1 }; CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5 }; sheetCF.AddConditionalFormatting(regions, cfRules); wb.SetSheetOrder("other sheet", 0); // names Assert.AreEqual("'first sheet'!D1", wb.GetName("name1").RefersToFormula); Assert.AreEqual("'other sheet'!C1", wb.GetName("name2").RefersToFormula); // cells Assert.AreEqual("SUM('other sheet'!C1,'first sheet'!C1)", c2.CellFormula); Assert.AreEqual("'first sheet'!D3", c3.CellFormula); Assert.AreEqual("'other sheet'!D3", c4.CellFormula); // conditional formatting IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.AreEqual("'first sheet'!D1", cf.GetRule(0).Formula1); Assert.AreEqual("'other sheet'!D1", cf.GetRule(0).Formula2); }
private void expectName(HSSFWorkbook wb, String name, String expect) { Assert.AreEqual(expect, wb.GetName(name).RefersToFormula); }