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);
     }
 }
示例#4
0
        public CellReference[] GetCells(string name)
        {
            var iName = _workBook.GetName(name);
            var area  = new AreaReference(iName.RefersToFormula);

            return(area.GetAllReferencedCells());
        }
示例#5
0
        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);
            }
        }
示例#6
0
        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();
        }
示例#7
0
    //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);
    }
示例#8
0
        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);
        }
示例#9
0
 private void expectName(HSSFWorkbook wb, String name, String expect)
 {
     Assert.AreEqual(expect, wb.GetName(name).RefersToFormula);
 }