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);
}
