public void TestDefferedSetting()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IName n1 = wb.CreateName();
Assert.IsNull(n1.RefersToFormula);
Assert.AreEqual("", n1.NameName);
IName n2 = wb.CreateName();
Assert.IsNull(n2.RefersToFormula);
Assert.AreEqual("", n2.NameName);
n1.NameName = ("sale_1");
n1.RefersToFormula = ("10");
n2.NameName = ("sale_2");
n2.RefersToFormula = ("20");
try
{
n2.NameName = ("sale_1");
Assert.Fail("Expected exception");
}
catch (Exception e)
{
Assert.AreEqual("The workbook already contains this name: sale_1", e.Message);
}
}
public void TestAddRemove()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
Assert.AreEqual(0, wb.NumberOfNames);
IName name1 = wb.CreateName();
name1.NameName = ("name1");
Assert.AreEqual(1, wb.NumberOfNames);
IName name2 = wb.CreateName();
name2.NameName = ("name2");
Assert.AreEqual(2, wb.NumberOfNames);
IName name3 = wb.CreateName();
name3.NameName = ("name3");
Assert.AreEqual(3, wb.NumberOfNames);
wb.RemoveName("name2");
Assert.AreEqual(2, wb.NumberOfNames);
wb.RemoveName(0);
Assert.AreEqual(1, wb.NumberOfNames);
}
public void TestBug56930()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
// x1 on sheet1 defines "x=1"
wb.CreateSheet("sheet1");
IName x1 = wb.CreateName();
x1.NameName = "x";
x1.RefersToFormula = "1";
x1.SheetIndex = wb.GetSheetIndex("sheet1");
// x2 on sheet2 defines "x=2"
wb.CreateSheet("sheet2");
IName x2 = wb.CreateName();
x2.NameName = "x";
x2.RefersToFormula = "2";
x2.SheetIndex = wb.GetSheetIndex("sheet2");
IList <IName> names = wb.GetNames("x");
Assert.AreEqual(2, names.Count, "Had: " + names);
Assert.AreEqual("1", names[0].RefersToFormula);
Assert.AreEqual("2", names[1].RefersToFormula);
Assert.AreEqual("1", wb.GetName("x").RefersToFormula);
wb.RemoveName("x");
Assert.AreEqual("2", wb.GetName("x").RefersToFormula);
wb.Close();
}
public void TestMultipleNamedWrite()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
wb.CreateSheet("testSheet1");
String sheetName = wb.GetSheetName(0);
Assert.AreEqual("testSheet1", sheetName);
//Creating new Named Range
IName newNamedRange = wb.CreateName();
newNamedRange.NameName = ("RangeTest");
newNamedRange.RefersToFormula = (sheetName + "!$D$4:$E$8");
//Creating another new Named Range
IName newNamedRange2 = wb.CreateName();
newNamedRange2.NameName = ("AnotherTest");
newNamedRange2.RefersToFormula = (sheetName + "!$F$1:$G$6");
wb.GetNameAt(0);
wb = _testDataProvider.WriteOutAndReadBack(wb);
IName nm = wb.GetNameAt(wb.GetNameIndex("RangeTest"));
Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.IsTrue((wb.GetSheetName(0) + "!$D$4:$E$8").Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula);
nm = wb.GetNameAt(wb.GetNameIndex("AnotherTest"));
Assert.IsTrue("AnotherTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.IsTrue(newNamedRange2.RefersToFormula.Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula);
}
public void TestShiftWithNames()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet1 = wb.CreateSheet("Sheet1");
wb.CreateSheet("Sheet2");
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(1.1);
row.CreateCell(1).SetCellValue(2.2);
IName name1 = wb.CreateName();
name1.NameName = (/*setter*/ "name1");
name1.RefersToFormula = (/*setter*/ "Sheet1!$A$1+Sheet1!$B$1");
IName name2 = wb.CreateName();
name2.NameName = (/*setter*/ "name2");
name2.RefersToFormula = (/*setter*/ "Sheet1!$A$1");
//refers to A1 but on Sheet2. Should stay unaffected.
IName name3 = wb.CreateName();
name3.NameName = (/*setter*/ "name3");
name3.RefersToFormula = (/*setter*/ "Sheet2!$A$1");
//The scope of this one is Sheet2. Should stay unaffected.
IName name4 = wb.CreateName();
name4.NameName = (/*setter*/ "name4");
name4.RefersToFormula = (/*setter*/ "A1");
name4.SheetIndex = (/*setter*/ 1);
sheet1.ShiftRows(0, 1, 2); //shift down the top row on Sheet1.
name1 = wb.GetName("name1");
Assert.AreEqual(name1.RefersToFormula, "Sheet1!$A$3+Sheet1!$B$3");
name2 = wb.GetName("name2");
Assert.AreEqual(name2.RefersToFormula, "Sheet1!$A$3");
//name3 and name4 refer to Sheet2 and should not be affected
name3 = wb.GetName("name3");
Assert.AreEqual(name3.RefersToFormula, "Sheet2!$A$1");
name4 = wb.GetName("name4");
Assert.AreEqual(name4.RefersToFormula, "A1");
wb.Close();
}
public static void AddListFormula(IWorkbook workbook, ISheet sheet, string sheetName, string listFormula, int colIndex, int firstRow, IList <string> items, int createdCellIndex = 0)
{
ISheet sheet2 = workbook.GetSheet(sheetName);
if (sheet2 == null)
{
sheet2 = workbook.CreateSheet(sheetName);
}
int sheetIndex = workbook.GetSheetIndex(sheet2);
workbook.SetSheetHidden(sheetIndex, SheetState.Hidden);
for (int i = 0; i < items.Count; i++)
{
string cellValue = items[i];
IRow row = sheet2.GetRow(i);
if (row == null)
{
row = sheet2.CreateRow(i);
}
ICell cell = row.GetCell(createdCellIndex);
if (cell == null)
{
cell = row.CreateCell(createdCellIndex);
}
cell.SetCellValue(cellValue);
}
if (workbook.GetName(listFormula) == null)
{
IName name = workbook.CreateName();
string text2 = name.RefersToFormula = sheetName + string.Format("!${0}$1:${0}${1}", ToColumnName(createdCellIndex), items.Count);
name.NameName = listFormula;
}
}
public static void AddConstraint(this ISheet sheet, IWorkbook workbook, string name, string mula, int columnIndex, bool isCustom = false)
{
IName namedRange = workbook.CreateName();
namedRange.NameName = name;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
XSSFDataValidationConstraint dvConstraint;
// XSSFDataValidation validation;
if (!isCustom)
{
namedRange.RefersToFormula = mula;//公式
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(namedRange.NameName);
}
else
{
//自定义
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(name.Split(','));
}
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, columnIndex, columnIndex);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
public void SetRangeName(string rangeName, string formula)
{
IName range = _workbook.CreateName();
range.RefersToFormula = formula;
range.NameName = rangeName;
}
public void TestNamedCell_2()
{
// Setup for this Testcase
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper factory = wb.GetCreationHelper();
ISheet sheet = wb.CreateSheet(sname);
sheet.CreateRow(0).CreateCell(0).SetCellValue(factory.CreateRichTextString(cvalue));
// create named range for a single cell using cellreference
IName namedCell = wb.CreateName();
namedCell.NameName = (cname);
String reference = sname + "!A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cname);
IName aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
CellReference cref = new CellReference(aNamedCell.RefersToFormula);
Assert.IsNotNull(cref);
ISheet s = wb.GetSheet(cref.SheetName);
IRow r = sheet.GetRow(cref.Row);
ICell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cvalue, "Contents of cell retrieved by its named reference");
}
private static void CreateDropDownListForExcel(ISheet sheet, string[] dropDownValues, int startRow, int lastRow, int column)
{
if (sheet == null)
{
return;
}
IWorkbook workbook = sheet.Workbook;
string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
for (int i = 0, length = dropDownValues.Length; i < length; i++)
{
string name = dropDownValues[i];
IRow row = hiddenSheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.SetCellValue(name);
}
IName namedCell = workbook.CreateName();
namedCell.NameName = dropDownName;
namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Length);
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet as HSSFSheet);
IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(dropDownName);
CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
HSSFDataValidation validation = (HSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
workbook.SetSheetHidden(hiddenSheetIndex, SheetState.Hidden);
sheet.AddValidationData(validation);
}
private IWorkbook newSetSheetNameTestingWorkbook()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh1 = wb.CreateSheet("Worksheet");
ISheet sh2 = wb.CreateSheet("Testing 47100");
ISheet sh3 = wb.CreateSheet("To be Renamed");
IName name1 = wb.CreateName();
name1.NameName = (/*setter*/ "sale_1");
name1.RefersToFormula = (/*setter*/ "Worksheet!$A$1");
IName name2 = wb.CreateName();
name2.NameName = (/*setter*/ "sale_2");
name2.RefersToFormula = (/*setter*/ "'Testing 47100'!$A$1");
IName name3 = wb.CreateName();
name3.NameName = (/*setter*/ "sale_3");
name3.RefersToFormula = (/*setter*/ "'Testing 47100'!$B$1");
IName name4 = wb.CreateName();
name4.NameName = (/*setter*/ "sale_4");
name4.RefersToFormula = (/*setter*/ "'To be Renamed'!$A$3");
sh1.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "SUM('Testing 47100'!A1:C1)");
sh1.CreateRow(1).CreateCell(0).CellFormula = (/*setter*/ "SUM('Testing 47100'!A1:C1,'To be Renamed'!A1:A5)");
sh1.CreateRow(2).CreateCell(0).CellFormula = (/*setter*/ "sale_2+sale_3+'Testing 47100'!C1");
sh2.CreateRow(0).CreateCell(0).SetCellValue(1);
sh2.GetRow(0).CreateCell(1).SetCellValue(2);
sh2.GetRow(0).CreateCell(2).SetCellValue(3);
sh3.CreateRow(0).CreateCell(0).SetCellValue(1);
sh3.CreateRow(1).CreateCell(0).SetCellValue(2);
sh3.CreateRow(2).CreateCell(0).SetCellValue(3);
sh3.CreateRow(3).CreateCell(0).SetCellValue(4);
sh3.CreateRow(4).CreateCell(0).SetCellValue(5);
sh3.CreateRow(5).CreateCell(0).CellFormula = (/*setter*/ "sale_3");
sh3.CreateRow(6).CreateCell(0).CellFormula = (/*setter*/ "'Testing 47100'!C1");
return(wb);
}
static void AddNames(IWorkbook workbook, ExcelVersion version, params NamedRange[] names)
{
foreach (NamedRange namedRange in names)
{
IName name = workbook.CreateName();
name.NameName = namedRange.Name;
name.RefersToFormula = ExcelHelper.RangeToString(namedRange.Range, version);
}
}
public void TestMultiNamedRange()
{
// Create a new workbook
IWorkbook wb = _testDataProvider.CreateWorkbook();
// Create a worksheet 'sheet1' in the new workbook
wb.CreateSheet();
wb.SetSheetName(0, "sheet1");
// Create another worksheet 'sheet2' in the new workbook
wb.CreateSheet();
wb.SetSheetName(1, "sheet2");
// Create a new named range for worksheet 'sheet1'
IName namedRange1 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet1'
namedRange1.NameName = ("RangeTest1");
// Set the reference for the named range for worksheet 'sheet1'
namedRange1.RefersToFormula = ("sheet1" + "!$A$1:$L$41");
// Create a new named range for worksheet 'sheet2'
IName namedRange2 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet2'
namedRange2.NameName = ("RangeTest2");
// Set the reference for the named range for worksheet 'sheet2'
namedRange2.RefersToFormula = ("sheet2" + "!$A$1:$O$21");
// Write the workbook to a file
// Read the Excel file and verify its content
wb = _testDataProvider.WriteOutAndReadBack(wb);
IName nm1 = wb.GetNameAt(wb.GetNameIndex("RangeTest1"));
Assert.IsTrue("RangeTest1".Equals(nm1.NameName), "Name is " + nm1.NameName);
Assert.IsTrue((wb.GetSheetName(0) + "!$A$1:$L$41").Equals(nm1.RefersToFormula), "Reference is " + nm1.RefersToFormula);
IName nm2 = wb.GetNameAt(wb.GetNameIndex("RangeTest2"));
Assert.IsTrue("RangeTest2".Equals(nm2.NameName), "Name is " + nm2.NameName);
Assert.IsTrue((wb.GetSheetName(1) + "!$A$1:$O$21").Equals(nm2.RefersToFormula), "Reference is " + nm2.RefersToFormula);
}
public void TestSheetLevelFormulas()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IRow row;
ISheet sh1 = wb.CreateSheet("Sheet1");
IName nm1 = wb.CreateName();
nm1.NameName = (/*setter*/ "sales_1");
nm1.SheetIndex = (/*setter*/ 0);
nm1.RefersToFormula = (/*setter*/ "Sheet1!$A$1");
row = sh1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).CellFormula = (/*setter*/ "sales_1");
row.CreateCell(2).CellFormula = (/*setter*/ "sales_1*2");
ISheet sh2 = wb.CreateSheet("Sheet2");
IName nm2 = wb.CreateName();
nm2.NameName = (/*setter*/ "sales_1");
nm2.SheetIndex = (/*setter*/ 1);
nm2.RefersToFormula = (/*setter*/ "Sheet2!$A$1");
row = sh2.CreateRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).CellFormula = (/*setter*/ "sales_1");
row.CreateCell(2).CellFormula = (/*setter*/ "sales_1*3");
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
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);
wb.Close();
}
public void TestDeletedCell()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IName n = wb.CreateName();
n.NameName = ("MyName");
// contrived example to expose bug:
n.RefersToFormula = ("if(A1,\"#REF!\", \"\")");
if (n.IsDeleted)
{
throw new AssertionException("Identified bug in recoginising formulas referring to deleted cells");
}
}
public void Test56781()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IName name = wb.CreateName();
foreach (String valid in Arrays.AsList(
"Hello",
"number1",
"_underscore"
//"p.e.r.o.i.d.s",
//"\\Backslash",
//"Backslash\\"
))
{
name.NameName = valid;
}
try
{
name.NameName = "";
Assert.Fail("expected exception: (blank)");
}
catch (ArgumentException e)
{
Assert.AreEqual("Name cannot be blank", e.Message);
}
foreach (String invalid in Arrays.AsList(
"1number",
"Sheet1!A1",
"Exclamation!",
"Has Space",
"Colon:",
"A-Minus",
"A+Plus",
"Dollar$"))
{
try
{
name.NameName = invalid;
Assert.Fail("expected exception: " + invalid);
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("Invalid name: '" + invalid + "'"), invalid);
}
}
}
public static void CreateValidationSheet(IWorkbook export, Dictionary <string, string[]> validateData, int rowStartIndex)
{
rowStartIndex = 0;
// Dictionary<string, string> refDic = new Dictionary<string, string>();
ISheet refSheet = export.CreateSheet("Sheet2");
int rowCount = rowStartIndex;
int cellCount = validateData.Count;
foreach (KeyValuePair <string, string[]> items in validateData)
{
rowCount = Math.Max(rowCount, items.Value.Length + rowStartIndex);
}
for (int i = 0; i < rowCount; i++)
{
IRow row = refSheet.CreateRow(i);
for (int j = 0; j < cellCount; j++)
{
row.CreateCell(j);
}
}
int cellIndex = 0, rowIndex = rowStartIndex;
foreach (KeyValuePair <string, string[]> items in validateData)
{
for (int k = 0; k < items.Value.Length; k++)
{
IRow row = refSheet.GetRow(k + rowStartIndex);
row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellValue(items.Value[k]);
}
IName range = export.CreateName();
string title = IntToMoreChar(cellIndex);
//char c = (char)(cellIndex + 65);
range.RefersToFormula = string.Format("Sheet2!${0}${1}:${0}${2}", title, rowStartIndex + 1, items.Value.Length + rowStartIndex);
range.NameName = items.Key.Split('=')[0].Replace("#", "");
// range.NameName = "V" + c;// items.Key.Replace("#", "V");// "v" + items.Key.GetHashCode();// "aa" + items.Key.Length;//
// refDic.Add(items.Key, range.NameName);
////处理数据的有效性
//CellRangeAddressList regions = new CellRangeAddressList(rowStartIndex, items.Value.Length + rowStartIndex, cellIndex, cellIndex);
//DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(range.NameName);//validateData[formatter]
//HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
//workSheet.AddValidationData(dataValidate);
cellIndex++;
}
// return refDic;
}
public void TestBug54500()
{
String nameName = "AName";
String sheetName = "ASheet";
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("54500.xls");
assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3");
wb.CreateSheet(sheetName);
assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3", "ASheet");
IName n = wb.CreateName();
n.NameName = (/*setter*/ nameName);
n.SheetIndex = (/*setter*/ 3);
n.RefersToFormula = (/*setter*/ sheetName + "!A1");
assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3", "ASheet");
Assert.AreEqual("ASheet!A1", wb.GetName(nameName).RefersToFormula);
MemoryStream stream = new MemoryStream();
wb.Write(stream);
assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3", "ASheet");
Assert.AreEqual("ASheet!A1", wb.GetName(nameName).RefersToFormula);
wb.RemoveSheetAt(1);
assertSheetOrder(wb, "Sheet1", "Sheet3", "ASheet");
Assert.AreEqual("ASheet!A1", wb.GetName(nameName).RefersToFormula);
MemoryStream stream2 = new MemoryStream();
wb.Write(stream2);
assertSheetOrder(wb, "Sheet1", "Sheet3", "ASheet");
Assert.AreEqual("ASheet!A1", wb.GetName(nameName).RefersToFormula);
expectName(
new HSSFWorkbook(new MemoryStream(stream.ToArray())),
nameName, "ASheet!A1");
expectName(
new HSSFWorkbook(
new MemoryStream(stream2.ToArray())),
nameName, "ASheet!A1");
}
public void TestFunctionNames()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IName n = wb.CreateName();
Assert.IsFalse(n.IsFunctionName);
n.SetFunction(false);
Assert.IsFalse(n.IsFunctionName);
n.SetFunction(true);
Assert.IsTrue(n.IsFunctionName);
n.SetFunction(false);
Assert.IsFalse(n.IsFunctionName);
}
//define named ranges for the inputs and formulas
public static void CreateNames(IWorkbook wb)
{
IName name;
name = wb.CreateName();
name.NameName = ("Interest_Rate");
name.RefersToFormula = ("'Loan Calculator'!$E$5");
name = wb.CreateName();
name.NameName = ("Loan_Amount");
name.RefersToFormula = ("'Loan Calculator'!$E$4");
name = wb.CreateName();
name.NameName = ("Loan_Start");
name.RefersToFormula = ("'Loan Calculator'!$E$7");
name = wb.CreateName();
name.NameName = ("Loan_Years");
name.RefersToFormula = ("'Loan Calculator'!$E$6");
name = wb.CreateName();
name.NameName = ("Number_of_Payments");
name.RefersToFormula = ("'Loan Calculator'!$E$10");
name = wb.CreateName();
name.NameName = ("Monthly_Payment");
name.RefersToFormula = ("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");
name = wb.CreateName();
name.NameName = ("Total_Cost");
name.RefersToFormula = ("'Loan Calculator'!$E$12");
name = wb.CreateName();
name.NameName = ("Total_Interest");
name.RefersToFormula = ("'Loan Calculator'!$E$11");
name = wb.CreateName();
name.NameName = ("Values_Entered");
name.RefersToFormula = ("IF(ISBLANK(Loan_Start),0,IF(Loan_Amount*Interest_Rate*Loan_Years>0,1,0))");
}
public void TestScope()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
wb.CreateSheet();
wb.CreateSheet();
IName name;
name = wb.CreateName();
name.NameName = ("aaa");
name = wb.CreateName();
try
{
name.NameName = ("aaa");
Assert.Fail("Expected exception");
}
catch (Exception e)
{
Assert.AreEqual("The workbook already contains this name: aaa", e.Message);
}
name = wb.CreateName();
name.SheetIndex = (0);
name.NameName = ("aaa");
name = wb.CreateName();
name.SheetIndex = (0);
try
{
name.NameName = ("aaa");
Assert.Fail("Expected exception");
}
catch (Exception e)
{
Assert.AreEqual("The sheet already contains this name: aaa", e.Message);
}
name = wb.CreateName();
name.SheetIndex = (1);
name.NameName = ("aaa");
name = wb.CreateName();
name.SheetIndex = (1);
try
{
name.NameName = ("aaa");
Assert.Fail("Expected exception");
}
catch (Exception e)
{
Assert.AreEqual("The sheet already contains this name: aaa", e.Message);
}
Assert.AreEqual(3, wb.GetNames("aaa").Count);
}
public void TestUnicodeNamedRange()
{
IWorkbook workBook = _testDataProvider.CreateWorkbook();
workBook.CreateSheet("Test");
IName name = workBook.CreateName();
name.NameName = ("\u03B1");
name.RefersToFormula = ("Test!$D$3:$E$8");
IWorkbook workBook2 = _testDataProvider.WriteOutAndReadBack(workBook);
IName name2 = workBook2.GetNameAt(0);
Assert.AreEqual("\u03B1", name2.NameName);
Assert.AreEqual("Test!$D$3:$E$8", name2.RefersToFormula);
}
public void TestNamedCell_1()
{
// Setup for this Testcase
String sheetName = "Test Named Cell";
String cellName = "named_cell";
String cellValue = "TEST Value";
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet(sheetName);
ICreationHelper factory = wb.GetCreationHelper();
sheet.CreateRow(0).CreateCell(0).SetCellValue(factory.CreateRichTextString(cellValue));
// create named range for a single cell using areareference
IName namedCell = wb.CreateName();
namedCell.NameName = (cellName);
String reference = "'" + sheetName + "'" + "!A1:A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cellName);
IName aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
AreaReference aref = new AreaReference(aNamedCell.RefersToFormula);
Assert.IsTrue(aref.IsSingleCell, "Should be exactly 1 cell in the named cell :'" + cellName + "'");
CellReference cref = aref.FirstCell;
Assert.IsNotNull(cref);
ISheet s = wb.GetSheet(cref.SheetName);
Assert.IsNotNull(s);
IRow r = sheet.GetRow(cref.Row);
ICell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference");
}
/// <summary>
/// Sheet中引用校验引用区域
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="item"></param>
private void SheetAddDataValidation(IWorkbook workbook, ISheet sheet, ColumnProperty item)
{
if (item == null || string.IsNullOrWhiteSpace(item.ConstraintReference)) //如果没有引用区域， 则退出
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, item.ColumnIndex, item.ColumnIndex);
IDataValidation dataValidate = null;
if (excelVersion == ExcelVersion.XLSX)
{
XSSFSheet xssfSheet = sheet as XSSFSheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(item.ConstraintReference);
dataValidate = dvHelper.CreateValidation(dvConstraint, regions);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
else
{
IName range = workbook.CreateName();
range.RefersToFormula = item.ConstraintReference;
range.NameName = item.ConstraintName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(item.ConstraintName);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 生成Sheet2的下拉验证数据
/// </summary>
/// <param name="export"></param>
/// <param name="validateData"></param>
private static void CreateValidationSheet(IWorkbook export, Dictionary <string, string[]> validateData)
{
int rowStartIndex = 0;
ISheet refSheet = export.CreateSheet("Sheet2");
int rowCount = rowStartIndex;
int cellCount = validateData.Count;
foreach (KeyValuePair <string, string[]> items in validateData)
{
rowCount = Math.Max(rowCount, items.Value.Length + rowStartIndex);
}
for (int i = 0; i < rowCount; i++)
{
IRow row = refSheet.CreateRow(i);
for (int j = 0; j < cellCount; j++)
{
row.CreateCell(j);
}
}
int cellIndex = 0, rowIndex = rowStartIndex;
foreach (KeyValuePair <string, string[]> items in validateData)
{
for (int k = 0; k < items.Value.Length; k++)
{
IRow row = refSheet.GetRow(k + rowStartIndex);
row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellValue(items.Value[k]);
}
IName range = export.CreateName();
string title = ConvertIndexToChar(cellIndex);
range.RefersToFormula = string.Format("Sheet2!${0}${1}:${0}${2}", title, rowStartIndex + 1, items.Value.Length + rowStartIndex);
range.NameName = items.Key.Split('=')[0].Replace("#", "").Replace(" ", "");//不允许存在空格
cellIndex++;
}
}
/// <summary>
/// 格式化数据，并建立名称管理
/// </summary>
/// <param name="sheet">表</param>
/// <param name="model">数据源(数据库)</param>
/// <param name="firstCellName">第一列的名称</param>
/// <param name="rowNo">当前操作的行号</param>
/// <param name="workbook">工作簿</param>
/// <param name="sheetName">工作表名</param>
public void FormatData(ISheet sheet, List <string> model, string firstCellName, ref int rowNo, IWorkbook workbook, string sheetName)
{
// 按行写入类型数据
IRow row = sheet.CreateRow(rowNo);
row.CreateCell(0).SetCellValue(firstCellName);
int rowCell = 1;
foreach (var item in model)
{
row.CreateCell(rowCell).SetCellValue(item);
rowCell++;
}
// 建立名称管理
rowNo++;
IName range = workbook.CreateName();
range.NameName = firstCellName;
string colName = GetExcelColumnName(model.Count + 1);
range.RefersToFormula = string.Format("{0}!$B${1}:${2}${1}", sheetName, rowNo, colName);
range.Comment = rowNo.ToString("00");
}
public void TestUnInitialisedNameGetRefersToFormula_bug46973()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IName n = wb.CreateName();
n.NameName = ("UPSState");
String formula;
try
{
formula = n.RefersToFormula;
}
catch (ArgumentException e)
{
if (e.Message.Equals("ptgs must not be null"))
{
throw new AssertionException("Identified bug 46973");
}
throw e;
}
Assert.IsNull(formula);
Assert.IsFalse(n.IsDeleted); // according to exact defInition of isDeleted()
}
private static void AddListValidations(WorkbookFormatter wf, IWorkbook wb)
{
string cellStrValue
= "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 ";
string dataSheetName = "list_data";
// "List" Data Validation type
ISheet fSheet = wf.CreateSheet("Lists");
ISheet dataSheet = wb.CreateSheet(dataSheetName);
wf.CreateDVTypeRow("Explicit lists - list items are explicitly provided");
wf.CreateDVDescriptionRow("Disadvantage - sum of item's length should be less than 255 characters");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.LIST);
string listValsDescr = "POIFS,HSSF,HWPF,HPSF";
string[] listVals = listValsDescr.Split(",".ToCharArray());
va.AddListValidation(listVals, null, listValsDescr, false, false);
va.AddListValidation(listVals, null, listValsDescr, false, true);
va.AddListValidation(listVals, null, listValsDescr, true, false);
va.AddListValidation(listVals, null, listValsDescr, true, true);
wf.CreateDVTypeRow("Reference lists - list items are taken from others cells");
wf.CreateDVDescriptionRow("Advantage - no restriction regarding the sum of item's length");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(null, ValidationType.LIST);
string strFormula = "$A$30:$A$39";
va.AddListValidation(null, strFormula, strFormula, false, false);
strFormula = dataSheetName + "!$A$1:$A$10";
va.AddListValidation(null, strFormula, strFormula, false, false);
IName namedRange = wb.CreateName();
namedRange.NameName = (/*setter*/ "myName");
namedRange.RefersToFormula = (/*setter*/ dataSheetName + "!$A$2:$A$7");
strFormula = "myName";
va.AddListValidation(null, strFormula, strFormula, false, false);
strFormula = "offset(myName, 2, 1, 4, 2)"; // Note about last param '2':
// - Excel expects single row or single column when entered in UI, but process this OK otherwise
va.AddListValidation(null, strFormula, strFormula, false, false);
// add list data on same sheet
for (int i = 0; i < 10; i++)
{
IRow currRow = fSheet.CreateRow(i + 29);
SetCellValue(currRow.CreateCell(0), cellStrValue);
}
// add list data on another sheet
for (int i = 0; i < 10; i++)
{
IRow currRow = dataSheet.CreateRow(i + 0);
SetCellValue(currRow.CreateCell(0), "Data a" + i);
SetCellValue(currRow.CreateCell(1), "Data b" + i);
SetCellValue(currRow.CreateCell(2), "Data c" + i);
}
}
/// <summary>
/// 设置某些列的值只能输入预制的数据,显示下拉框
/// </summary>
/// <param name="sheet">要设置的sheet</param>
/// <param name="textlist">下拉框显示的内容</param>
/// <param name="firstRow">开始行</param>
/// <param name="endRow">结束行</param>
/// <param name="firstCol">开始列</param>
/// <param name="endCol">结束列</param>
/// <returns>设置好的sheet</returns>
public static ISheet SetHSSFValidation(ISheet sheet,
string[] textlist, int firstRow, int endRow, int firstCol,
int endCol)
{
IWorkbook workbook = sheet.Workbook;
if (endRow > sheet.LastRowNum)
{
endRow = sheet.LastRowNum;
}
ISheet hidden = null;
string hiddenSheetName = "hidden" + sheet.SheetName;
int hIndex = workbook.GetSheetIndex(hiddenSheetName);
if (hIndex < 0)
{
hidden = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(sheet.Workbook.NumberOfSheets - 1, SheetState.HIDDEN);
}
else
{
hidden = workbook.GetSheetAt(hIndex);
}
if (textlist == null || textlist.Length == 0)
{
textlist = new string[] { "" };
}
IRow row = null;
ICell cell = null;
for (int i = 0, length = textlist.Length; i < length; i++)
{
row = hidden.GetRow(i);
if (row == null)
{
row = hidden.CreateRow(i);
}
cell = row.GetCell(firstCol);
if (cell == null)
{
cell = row.CreateCell(firstCol);
}
cell.SetCellValue(textlist[i]);
}
// 加载下拉列表内容
string nameCellKey = hiddenSheetName + firstCol;
IName namedCell = workbook.GetName(nameCellKey);
if (namedCell == null)
{
namedCell = workbook.CreateName();
namedCell.NameName = nameCellKey;
namedCell.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", hiddenSheetName, NumberToChar(firstCol + 1), textlist.Length);
}
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(nameCellKey);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
//// 取消弹出错误框
//validation.ShowErrorBox = false;
sheet.AddValidationData(validation);
return(sheet);
}
private static void AddListValidations(WorkbookFormatter wf, IWorkbook wb)
{
String cellStrValue
= "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 ";
String dataSheetName = "list_data";
// "List" Data Validation type
ISheet fSheet = wf.CreateSheet("Lists");
ISheet dataSheet = wb.CreateSheet(dataSheetName);
wf.CreateDVTypeRow("Explicit lists - list items are explicitly provided");
wf.CreateDVDescriptionRow("Disadvantage - sum of item's length should be less than 255 characters");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.LIST);
String listValsDescr = "POIFS,HSSF,HWPF,HPSF";
String[] listVals = listValsDescr.Split(",".ToCharArray());
va.AddListValidation(listVals, null, listValsDescr, false, false);
va.AddListValidation(listVals, null, listValsDescr, false, true);
va.AddListValidation(listVals, null, listValsDescr, true, false);
va.AddListValidation(listVals, null, listValsDescr, true, true);
wf.CreateDVTypeRow("Reference lists - list items are taken from others cells");
wf.CreateDVDescriptionRow("Advantage - no restriction regarding the sum of item's length");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(null, ValidationType.LIST);
String strFormula = "$A$30:$A$39";
va.AddListValidation(null, strFormula, strFormula, false, false);
strFormula = dataSheetName + "!$A$1:$A$10";
va.AddListValidation(null, strFormula, strFormula, false, false);
IName namedRange = wb.CreateName();
namedRange.NameName = (/*setter*/"myName");
namedRange.RefersToFormula = (/*setter*/dataSheetName + "!$A$2:$A$7");
strFormula = "myName";
va.AddListValidation(null, strFormula, strFormula, false, false);
strFormula = "offset(myName, 2, 1, 4, 2)"; // Note about last param '2':
// - Excel expects single row or single column when entered in UI, but process this OK otherwise
va.AddListValidation(null, strFormula, strFormula, false, false);
// add list data on same sheet
for (int i = 0; i < 10; i++)
{
IRow currRow = fSheet.CreateRow(i + 29);
SetCellValue(currRow.CreateCell(0), cellStrValue);
}
// add list data on another sheet
for (int i = 0; i < 10; i++)
{
IRow currRow = dataSheet.CreateRow(i + 0);
SetCellValue(currRow.CreateCell(0), "Data a" + i);
SetCellValue(currRow.CreateCell(1), "Data b" + i);
SetCellValue(currRow.CreateCell(2), "Data c" + i);
}
}
//define named ranges for the inputs and formulas
public static void CreateNames(IWorkbook wb)
{
IName name;
name = wb.CreateName();
name.NameName = ("Interest_Rate");
name.RefersToFormula = ("'Loan Calculator'!$E$5");
name = wb.CreateName();
name.NameName = ("Loan_Amount");
name.RefersToFormula = ("'Loan Calculator'!$E$4");
name = wb.CreateName();
name.NameName = ("Loan_Start");
name.RefersToFormula = ("'Loan Calculator'!$E$7");
name = wb.CreateName();
name.NameName = ("Loan_Years");
name.RefersToFormula = ("'Loan Calculator'!$E$6");
name = wb.CreateName();
name.NameName = ("Number_of_Payments");
name.RefersToFormula = ("'Loan Calculator'!$E$10");
name = wb.CreateName();
name.NameName = ("Monthly_Payment");
name.RefersToFormula = ("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");
name = wb.CreateName();
name.NameName = ("Total_Cost");
name.RefersToFormula = ("'Loan Calculator'!$E$12");
name = wb.CreateName();
name.NameName = ("Total_Interest");
name.RefersToFormula = ("'Loan Calculator'!$E$11");
name = wb.CreateName();
name.NameName = ("Values_Entered");
name.RefersToFormula = ("IF(ISBLANK(Loan_Start),0,IF(Loan_Amount*Interest_Rate*Loan_Years>0,1,0))");
}
public void TestCreate()
{
// Create a new workbook
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet1 = wb.CreateSheet("Test1");
ISheet sheet2 = wb.CreateSheet("Testing Named Ranges");
IName name1 = wb.CreateName();
name1.NameName = ("testOne");
//setting a duplicate name should throw ArgumentException
IName name2 = wb.CreateName();
try
{
name2.NameName = ("testOne");
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("The workbook already contains this name: testOne", e.Message);
}
//the check for duplicates is case-insensitive
try
{
name2.NameName = ("TESTone");
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("The workbook already contains this name: TESTone", e.Message);
}
name2.NameName = ("testTwo");
String ref1 = "Test1!$A$1:$B$1";
name1.RefersToFormula = (ref1);
Assert.AreEqual(ref1, name1.RefersToFormula);
Assert.AreEqual("Test1", name1.SheetName);
String ref2 = "'Testing Named Ranges'!$A$1:$B$1";
name1.RefersToFormula = (ref2);
Assert.AreEqual("'Testing Named Ranges'!$A$1:$B$1", name1.RefersToFormula);
Assert.AreEqual("Testing Named Ranges", name1.SheetName);
Assert.AreEqual(-1, name1.SheetIndex);
name1.SheetIndex = (-1);
Assert.AreEqual(-1, name1.SheetIndex);
try
{
name1.SheetIndex = (2);
Assert.Fail("should throw ArgumentException");
}
catch (ArgumentException e)
{
Assert.AreEqual("Sheet index (2) is out of range (0..1)", e.Message);
}
name1.SheetIndex = (1);
Assert.AreEqual(1, name1.SheetIndex);
//-1 means the name applies to the entire workbook
name1.SheetIndex = (-1);
Assert.AreEqual(-1, name1.SheetIndex);
//names cannot be blank and must begin with a letter or underscore and not contain spaces
String[] invalidNames = { "", "123", "1Name", "Named Range" };
foreach (String name in invalidNames)
{
try
{
name1.NameName = (name);
Assert.Fail("should have thrown exceptiuon due to invalid name: " + name);
}
catch (ArgumentException)
{
// expected during successful Test
}
}
}
