private static HSSFWorkbook CreateWBA()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("Sheet1");
ISheet sheet2 = wb.CreateSheet("Sheet2");
ISheet sheet3 = wb.CreateSheet("John's sales");
CreateDataRow(sheet1, 0, 11, 12, 13, 14);
CreateDataRow(sheet1, 1, 21, 22, 23, 24);
CreateDataRow(sheet1, 2, 31, 32, 33, 34);
CreateDataRow(sheet2, 0, 50, 55, 60, 65);
CreateDataRow(sheet2, 1, 51, 56, 61, 66);
CreateDataRow(sheet2, 2, 52, 57, 62, 67);
CreateDataRow(sheet3, 0, 30, 31, 32);
CreateDataRow(sheet3, 1, 33, 34, 35);
IName name1 = wb.CreateName();
name1.NameName = ("sales1");
name1.RefersToFormula = ("Sheet1!A1:D1");
IName name2 = wb.CreateName();
name2.NameName = ("sales2");
name2.RefersToFormula = ("Sheet2!B1:C3");
IRow row = sheet1.CreateRow(3);
row.CreateCell(0).SetCellValue("sales1"); //A4
row.CreateCell(1).SetCellValue("sales2"); //B4
return wb;
}
public void TestFormulaRelAbs_bug46174()
{
// perhaps this Testcase belongs on TestHSSFName
IWorkbook wb = new HSSFWorkbook();
IName name = wb.CreateName();
wb.CreateSheet("Sheet1");
name.NameName = (/*setter*/"test");
name.RefersToFormula = (/*setter*/"Sheet1!$B$3");
if (name.RefersToFormula.Equals("Sheet1!B3"))
{
throw new AssertionException("Identified bug 46174");
}
Assert.AreEqual("Sheet1!$B$3", name.RefersToFormula);
}
public void TestWithNamedRange()
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet s = workbook.CreateSheet("Foo");
s.CreateRow(0).CreateCell((short)0).SetCellValue(1.1);
s.CreateRow(1).CreateCell((short)0).SetCellValue(2.3);
s.CreateRow(2).CreateCell((short)2).SetCellValue(3.1);
IName name = workbook.CreateName();
name.NameName = ("testName");
name.RefersToFormula = ("A1:A2");
ConfirmParseFormula(workbook);
// Now make it a single cell
name.RefersToFormula = ("C3");
ConfirmParseFormula(workbook);
// And make it non-contiguous
name.RefersToFormula = ("A1:A2,C3");
ConfirmParseFormula(workbook);
}
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);
}
public void TestNamesWithUnderscore()
{
HSSFWorkbook wb = new HSSFWorkbook(); //or new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("NamesWithUnderscore");
IName nm;
nm = wb.CreateName();
nm.NameName = ("DA6_LEO_WBS_Number");
nm.RefersToFormula = ("33");
nm = wb.CreateName();
nm.NameName = ("DA6_LEO_WBS_Name");
nm.RefersToFormula = ("33");
nm = wb.CreateName();
nm.NameName = ("A1_");
nm.RefersToFormula = ("22");
nm = wb.CreateName();
nm.NameName = ("_A1");
nm.RefersToFormula = ("11");
nm = wb.CreateName();
nm.NameName = ("A_1");
nm.RefersToFormula = ("44");
nm = wb.CreateName();
nm.NameName = ("A_1_");
nm.RefersToFormula = ("44");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("DA6_LEO_WBS_Number*2");
Assert.AreEqual("DA6_LEO_WBS_Number*2", cell.CellFormula);
cell.CellFormula = ("(A1_*_A1+A_1)/A_1_");
Assert.AreEqual("(A1_*_A1+A_1)/A_1_", cell.CellFormula);
cell.CellFormula = ("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))");
Assert.AreEqual("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))", cell.CellFormula);
wb.Close();
}
public void TestZeroRowRefs()
{
String badCellRef = "B0"; // bad because zero is not a valid row number
String leadingZeroCellRef = "B000001"; // this should get parsed as "B1"
HSSFWorkbook wb = new HSSFWorkbook();
try
{
HSSFFormulaParser.Parse(badCellRef, wb);
throw new AssertionException("Identified bug 47312b - Shouldn't be able to parse cell ref '"
+ badCellRef + "'.");
}
catch (FormulaParseException e)
{
// expected during successful Test
ConfirmParseException(e, "Specified named range '"
+ badCellRef + "' does not exist in the current workbook.");
}
Ptg[] ptgs;
try
{
ptgs = HSSFFormulaParser.Parse(leadingZeroCellRef, wb);
Assert.AreEqual("B1", ((RefPtg)ptgs[0]).ToFormulaString());
}
catch (FormulaParseException e)
{
ConfirmParseException(e, "Specified named range '"
+ leadingZeroCellRef + "' does not exist in the current workbook.");
// close but no cigar
throw new AssertionException("Identified bug 47312c - '"
+ leadingZeroCellRef + "' should parse as 'B1'.");
}
// create a defined name called 'B0' and try again
IName n = wb.CreateName();
n.NameName = ("B0");
n.RefersToFormula = ("1+1");
ptgs = HSSFFormulaParser.Parse("B0", wb);
ConfirmTokenClasses(ptgs, typeof(NamePtg));
}
public void TestParseComplexName()
{
// Mock up a spreadsheet to match the critical details of the sample
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("Sheet1");
IName definedName = wb.CreateName();
definedName.NameName = ("foo");
definedName.RefersToFormula = ("Sheet1!B2");
// Set the complex flag - POI doesn't usually manipulate this flag
NameRecord nameRec = TestHSSFName.GetNameRecord(definedName);
nameRec.OptionFlag = ((short)0x10); // 0x10 -> complex
Ptg[] result;
try
{
result = HSSFFormulaParser.Parse("1+foo", wb);
}
catch (FormulaParseException e)
{
if (e.Message.Equals("Specified name 'foo' is not a range as expected."))
{
throw new AssertionException("Identified bug 47078c");
}
throw e;
}
ConfirmTokenClasses(result, typeof(IntPtg), typeof(NamePtg), typeof(AddPtg));
}
public void TestBackSlashInNames()
{
HSSFWorkbook wb = new HSSFWorkbook();
IName name = wb.CreateName();
name.NameName = ("POI\\2009");
name.RefersToFormula = ("Sheet1!$A$1");
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell_C1 = row.CreateCell(2);
cell_C1.CellFormula = ("POI\\2009");
Assert.AreEqual("POI\\2009", cell_C1.CellFormula);
ICell cell_D1 = row.CreateCell(2);
cell_D1.CellFormula = ("NOT(POI\\2009=\"3.5-final\")");
Assert.AreEqual("NOT(POI\\2009=\"3.5-final\")", cell_D1.CellFormula);
wb.Close();
}
public void TestNamedRangeThatLooksLikeCell()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IName name = wb.CreateName();
name.RefersToFormula = ("Sheet1!B1");
name.NameName = ("pfy1");
Ptg[] ptgs;
try
{
ptgs = HSSFFormulaParser.Parse("count(pfy1)", wb);
}
catch (ArgumentException e)
{
if (e.Message.Equals("Specified colIx (1012) is out of range"))
{
throw new AssertionException("Identified bug 45354");
}
throw e;
}
ConfirmTokenClasses(ptgs, typeof(NamePtg), typeof(FuncVarPtg));
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula = ("count(pfy1)");
Assert.AreEqual("COUNT(pfy1)", cell.CellFormula);
try
{
cell.CellFormula = ("count(pf1)");
throw new AssertionException("Expected formula parse execption");
}
catch (FormulaParseException e)
{
ConfirmParseException(e,
"Specified named range 'pf1' does not exist in the current workbook.");
}
cell.CellFormula = ("count(fp1)"); // plain cell ref, col is in range
}
public void TestNamesInFormulas()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IName name1 = wb.CreateName();
name1.NameName = "aConstant";
name1.RefersToFormula = "3.14";
IName name2 = wb.CreateName();
name2.NameName = "aFormula";
name2.RefersToFormula = "SUM(Sheet1!$A$1:$A$3)";
IName name3 = wb.CreateName();
name3.NameName = "aSet";
name3.RefersToFormula = "Sheet1!$A$2:$A$4";
IRow row0 = sheet.CreateRow(0);
IRow row1 = sheet.CreateRow(1);
IRow row2 = sheet.CreateRow(2);
IRow row3 = sheet.CreateRow(3);
row0.CreateCell(0).SetCellValue(2);
row1.CreateCell(0).SetCellValue(5);
row2.CreateCell(0).SetCellValue(3);
row3.CreateCell(0).SetCellValue(7);
row0.CreateCell(2).SetCellFormula("aConstant");
row1.CreateCell(2).SetCellFormula("aFormula");
row2.CreateCell(2).SetCellFormula("SUM(aSet)");
row3.CreateCell(2).SetCellFormula("aConstant+aFormula+SUM(aSet)");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(3.14, fe.Evaluate(row0.GetCell(2)).NumberValue);
Assert.AreEqual(10.0, fe.Evaluate(row1.GetCell(2)).NumberValue);
Assert.AreEqual(15.0, fe.Evaluate(row2.GetCell(2)).NumberValue);
Assert.AreEqual(28.14, fe.Evaluate(row3.GetCell(2)).NumberValue);
}
public ActionResult Template(int? tagId)
{
var basicInfoSheetName = ProUploadService.BASIC_SHEET;
var moreInfoSheetName = ProUploadService.MORE_SHEET;
var supportSheetName = "不要修改";
var headerLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"商品名称",new {dataformat=0,width=20}},
{"描述",new {dataformat=0,width=50}},
{"吊牌价",new {dataformat=2,width=8}},
{"现价",new {dataformat=2,width=8}},
{"品牌名",new {dataformat=0,width=20}},
{ "分类名",new {dataformat=0,width=20}},
{"门店名",new {dataformat=0,width=20}},
{ "促销活动编码",new {dataformat=0,width=20}},
{ "专题编码（多个以,分割)",new {dataformat=0,width=20}},
{"可销售",new {dataformat=2,width=5}},
{"商品货号",new {dataformat=0,width=10}}
};
var workbook = new HSSFWorkbook();
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.BorderBottom = BorderStyle.THIN;
headerLabelCellStyle.BorderLeft = BorderStyle.THIN;
headerLabelCellStyle.BorderRight = BorderStyle.THIN;
headerLabelCellStyle.BorderTop = BorderStyle.THIN;
headerLabelCellStyle.WrapText = true;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
//set support sheet
var supportSheet = workbook.CreateSheet(supportSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true);
Func<int, dynamic, int> supportFill = (rowIndex, data) =>
{
var brandRow = supportSheet.CreateRow(rowIndex++);
var brandCodeCol = brandRow.CreateCell(1);
brandCodeCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Id);
var brandNameCol = brandRow.CreateCell(2);
brandNameCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Name);
return rowIndex;
};
int brandRowIndex = 0;
foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
brandRowIndex = supportFill(brandRowIndex, brand);
}
int tagRowIndex = brandRowIndex;
var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name });
//if (tagId.HasValue)
// tagLinq = tagLinq.Where(t => t.Id == tagId.Value);
foreach (var tag in tagLinq.OrderBy(t => t.Name))
{
tagRowIndex = supportFill(tagRowIndex, tag);
}
int storeRowIndex = tagRowIndex;
foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
storeRowIndex = supportFill(storeRowIndex, store);
}
//set basic sheet
var sheet1 = workbook.CreateSheet(basicInfoSheetName);
//workbook.SetSheetOrder(basicInfoSheetName, 0);
var rowFirst = sheet1.CreateRow(0);
Action<int, string, dynamic> cellSetting = (cellindex, desc, option) =>
{
var cell = rowFirst.CreateCell(cellindex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(desc);
cell.CellStyle = headerLabelCellStyle;
sheet1.SetColumnWidth(cellindex, option.width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)option.dataformat;
sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle);
};
int index = 0;
foreach (var key in headerLabels.Keys)
{
cellSetting(index++, key, headerLabels[key]);
}
//set constraint
DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex));
CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5);
HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint);
branddataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(branddataValidation);
DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex));
CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6);
HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint);
tagdataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(tagdataValidation);
DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex));
CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7);
HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint);
storedataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(storedataValidation);
DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO });
CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10);
HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint);
is4saledataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(is4saledataValidation);
//set sheet2
//create property value sheet
var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted)
.Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i })
.OrderBy(b => b.P.CategoryId)
.ThenBy(b => b.P.PropertyDesc);
int propertyRowIndex = storeRowIndex;
foreach (var tag in tagLinq)
{
var propertyFromIndex = propertyRowIndex;
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc}))
{
propertyRowIndex = supportFill(propertyRowIndex, property);
}
if (propertyRowIndex > propertyFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex);
rName.NameName = tag.Name;
}
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id))
{
int valueFromIndex = propertyRowIndex;
foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc }))
{
propertyRowIndex = supportFill(propertyRowIndex, pvalue);
}
if (propertyRowIndex > valueFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex);
rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc);
}
}
}
var moreheaderLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"属性名",new {dataformat=0,width=20}},
{"属性值",new {dataformat=0,width=10}}
};
var sheet2 = workbook.CreateSheet(moreInfoSheetName);
var morerowFirst = sheet2.CreateRow(0);
int moreCellIndex = 0;
foreach (var key in moreheaderLabels.Keys)
{
var cell = morerowFirst.CreateCell(moreCellIndex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(key);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat;
sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle);
moreCellIndex++;
}
for (int i = 3; i <= 11; i++)
{
var cell = morerowFirst.CreateCell(i);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(string.Empty);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(i, 10 * 255);
}
//set merge cell for last cell
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11));
//set constraint
DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000));
CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0);
HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint);
codedataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(codedataValidation);
for (int i = 1; i < 1000; i++)
{
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName),
new CellRangeAddress(i,i,12,12));
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName),
new CellRangeAddress(i, i, 13, 13));
sheet2.SetColumnHidden(12, true);
sheet2.SetColumnHidden(13, true);
DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex));
CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1);
HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint);
pdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pdataValidation);
//set constraint
DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1));
CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11);
HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint);
pvdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pvdataValidation);
}
workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1));
var ms = new MemoryStream();
workbook.Write(ms);
ms.Position = 0;
var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls";
return File(ms, "application/vnd.ms-excel", downloadName);
}
public void TestFormulaGeneral()
{
// perhaps this Testcase belongs on TestHSSFName
IWorkbook wb = new HSSFWorkbook();
IName name = wb.CreateName();
wb.CreateSheet("Sheet1");
name.NameName = (/*setter*/"test");
name.RefersToFormula = (/*setter*/"Sheet1!A1+Sheet1!A2");
Assert.AreEqual("Sheet1!A1+Sheet1!A2", name.RefersToFormula);
name.RefersToFormula = (/*setter*/"5*6");
Assert.AreEqual("5*6", name.RefersToFormula);
}
public void TestFormulasWithUnderscore()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
IName nm1 = wb.CreateName();
nm1.NameName = ("_score1");
nm1.RefersToFormula = ("A1");
IName nm2 = wb.CreateName();
nm2.NameName = ("_score2");
nm2.RefersToFormula = ("A2");
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(2);
cell.CellFormula = ("_score1*SUM(_score1+_score2)");
Assert.AreEqual("_score1*SUM(_score1+_score2)", cell.CellFormula);
}
finally
{
wb.Close();
}
}
public void TestSheetLevelFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
IRow row;
ISheet sh1 = wb.CreateSheet("Sheet1");
IName nm1 = wb.CreateName();
nm1.NameName = ("sales_1");
nm1.SheetIndex = (0);
nm1.RefersToFormula = ("Sheet1!$A$1");
row = sh1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*2");
ISheet sh2 = wb.CreateSheet("Sheet2");
IName nm2 = wb.CreateName();
nm2.NameName = ("sales_1");
nm2.SheetIndex = (1);
nm2.RefersToFormula = ("Sheet2!$A$1");
row = sh2.CreateRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*3");
//check that NamePtg refers to the correct NameRecord
Ptg[] ptgs1 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 0);
NamePtg nPtg1 = (NamePtg)ptgs1[0];
Assert.AreSame(nm1, wb.GetNameAt(nPtg1.Index));
Ptg[] ptgs2 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 1);
NamePtg nPtg2 = (NamePtg)ptgs2[0];
Assert.AreSame(nm2, wb.GetNameAt(nPtg2.Index));
//check that the formula evaluator returns the correct result
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(3.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(6.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0);
Assert.AreEqual(5.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(15.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.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);
}
}
public void TestDefinedNameWithComplexFlag_bug47048()
{
// Mock up a spreadsheet to match the critical details of the sample
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Input");
IName definedName = wb.CreateName();
definedName.NameName = ("Is_Multicar_Vehicle");
definedName.RefersToFormula = ("Input!$B$17:$G$17");
// Set up some data and the formula
IRow row17 = sheet.CreateRow(16);
row17.CreateCell(0).SetCellValue(25.0);
row17.CreateCell(1).SetCellValue(1.33);
row17.CreateCell(2).SetCellValue(4.0);
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
cellA1.CellFormula = ("SUM(Is_Multicar_Vehicle)");
// Set the complex flag - POI doesn't usually manipulate this flag
NameRecord nameRec = TestHSSFName.GetNameRecord(definedName);
nameRec.OptionFlag = (short)0x10; // 0x10 -> complex
HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
CellValue value;
try
{
value = hsf.Evaluate(cellA1);
}
catch (Exception e)
{
if (e.Message.Equals("Don't now how to evalate name 'Is_Multicar_Vehicle'"))
{
throw new AssertionException("Identified bug 47048a");
}
throw e;
}
Assert.AreEqual(CellType.Numeric, value.CellType);
Assert.AreEqual(5.33, value.NumberValue, 0.0);
}
public void TestNamesInFormulas()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IName name1 = wb.CreateName();
name1.NameName = ("aConstant");
name1.RefersToFormula = ("3.14");
IName name2 = wb.CreateName();
name2.NameName = ("aFormula");
name2.RefersToFormula = ("SUM(Sheet1!$A$1:$A$3)");
IName name3 = wb.CreateName();
name3.NameName = ("aSet");
name3.RefersToFormula = ("Sheet1!$A$2:$A$4");
IRow row0 = sheet.CreateRow(0);
IRow row1 = sheet.CreateRow(1);
IRow row2 = sheet.CreateRow(2);
IRow row3 = sheet.CreateRow(3);
row0.CreateCell(0).SetCellValue(2);
row1.CreateCell(0).SetCellValue(5);
row2.CreateCell(0).SetCellValue(3);
row3.CreateCell(0).SetCellValue(7);
row0.CreateCell(2).SetCellFormula("aConstant");
row1.CreateCell(2).SetCellFormula("aFormula");
row2.CreateCell(2).SetCellFormula("SUM(aSet)");
row3.CreateCell(2).SetCellFormula("aConstant+aFormula+SUM(aSet)");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(3.14, fe.Evaluate(row0.GetCell(2)).NumberValue);
Assert.AreEqual(10.0, fe.Evaluate(row1.GetCell(2)).NumberValue);
Assert.AreEqual(15.0, fe.Evaluate(row2.GetCell(2)).NumberValue);
Assert.AreEqual(28.14, fe.Evaluate(row3.GetCell(2)).NumberValue);
}