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