public void TestNamedCell_2() { // setup for this Testcase String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sname); sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cvalue)); // Create named range for a single cell using cellreference NPOI.SS.UserModel.Name namedCell = wb.CreateName(); namedCell.NameName = (cname); String reference = sname + "!A1"; namedCell.RefersToFormula = (reference); // retrieve the newly Created named range int namedCellIdx = wb.GetNameIndex(cname); NPOI.SS.UserModel.Name 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); NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName); Row r = sheet.GetRow(cref.Row); Cell c = r.GetCell(cref.Col); String contents = c.RichStringCellValue.String; Assert.AreEqual(contents, cvalue, "Contents of cell retrieved by its named reference"); }
public void TestDiscontinousReference() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls"); HSSFWorkbook wb = new HSSFWorkbook(is1); NPOI.HSSF.Model.Workbook workbook = wb.Workbook; HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual(1, wb.NumberOfNames); String sheetName = "Tabelle1"; String rawRefA = "$C$10:$C$14"; String rawRefB = "$C$16:$C$18"; String refA = sheetName + "!" + rawRefA; String refB = sheetName + "!" + rawRefB; String ref1 = refA + "," + refB; // Check the low level record NameRecord nr = workbook.GetNameRecord(0); Assert.IsNotNull(nr); Assert.AreEqual("test", nr.NameText); Ptg[] def = nr.NameDefinition; Assert.AreEqual(4, def.Length); MemFuncPtg ptgA = (MemFuncPtg)def[0]; Area3DPtg ptgB = (Area3DPtg)def[1]; Area3DPtg ptgC = (Area3DPtg)def[2]; UnionPtg ptgD = (UnionPtg)def[3]; Assert.AreEqual("", ptgA.ToFormulaString()); Assert.AreEqual(refA, ptgB.ToFormulaString(eb)); Assert.AreEqual(refB, ptgC.ToFormulaString(eb)); Assert.AreEqual(",", ptgD.ToFormulaString()); Assert.AreEqual(ref1, NPOI.HSSF.Model.HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // Check the high level definition int idx = wb.GetNameIndex("test"); Assert.AreEqual(0, idx); NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(idx); // Should have 2 references Assert.AreEqual(ref1, aNamedCell.RefersToFormula); // Check the parsing of the reference into cells Assert.IsFalse(AreaReference.IsContiguous(aNamedCell.RefersToFormula)); AreaReference[] arefs = AreaReference.GenerateContiguous(aNamedCell.RefersToFormula); Assert.AreEqual(2, arefs.Length); Assert.AreEqual(refA, arefs[0].FormatAsString()); Assert.AreEqual(refB, arefs[1].FormatAsString()); for (int i = 0; i < arefs.Length; i++) { AreaReference ar = arefs[i]; ConfirmResolveCellRef(wb, ar.FirstCell); ConfirmResolveCellRef(wb, ar.LastCell); } }
public void TestDeletedReference() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("24207.xls"); Assert.AreEqual(2, wb.NumberOfNames); NPOI.SS.UserModel.Name name1 = wb.GetNameAt(0); Assert.AreEqual("a", name1.NameName); Assert.AreEqual("Sheet1!$A$1", name1.RefersToFormula); AreaReference ref1 = new AreaReference(name1.RefersToFormula); //Assert.IsTrue(true, "Successfully constructed first reference"); NPOI.SS.UserModel.Name name2 = wb.GetNameAt(1); Assert.AreEqual("b", name2.NameName); Assert.AreEqual("Sheet1!#REF!", name2.RefersToFormula); Assert.IsTrue(name2.IsDeleted); try { AreaReference ref2 = new AreaReference(name2.RefersToFormula); Assert.Fail("attempt to supply an invalid reference to AreaReference constructor results in exception"); } catch (IndexOutOfRangeException e) { // TODO - use a different exception for this condition // expected during successful Test } }
public void TestMultipleNamedWrite() { HSSFWorkbook wb = new HSSFWorkbook(); wb.CreateSheet("TestSheet1"); String sheetName = wb.GetSheetName(0); Assert.AreEqual("TestSheet1", sheetName); //Creating new Named Range NPOI.SS.UserModel.Name newNamedRange = wb.CreateName(); newNamedRange.NameName = ("RangeTest"); newNamedRange.RefersToFormula = (sheetName + "!$D$4:$E$8"); //Creating another new Named Range NPOI.SS.UserModel.Name newNamedRange2 = wb.CreateName(); newNamedRange2.NameName = ("AnotherTest"); newNamedRange2.RefersToFormula = (sheetName + "!$F$1:$G$6"); NPOI.SS.UserModel.Name namedRange1 = wb.GetNameAt(0); String referece = namedRange1.RefersToFormula; wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name 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 TestNamedRange1() { HSSFWorkbook wb = OpenSample("Simple.xls"); //Creating new Named Range NPOI.SS.UserModel.Name newNamedRange = wb.CreateName(); //Getting Sheet Name for the reference String sheetName = wb.GetSheetName(0); //Setting its name newNamedRange.NameName = ("RangeTest"); //Setting its reference newNamedRange.RefersToFormula = (sheetName + "!$D$4:$E$8"); //Getting NAmed Range NPOI.SS.UserModel.Name namedRange1 = wb.GetNameAt(0); //Getting it sheet name sheetName = namedRange1.SheetName; //Getting its reference String referece = namedRange1.RefersToFormula; // sanity Check SanityChecker c = new SanityChecker(); c.CheckHSSFWorkbook(wb); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name nm = wb.GetNameAt(wb.GetNameIndex("RangeTest")); Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName); Assert.AreEqual(wb.GetSheetName(0) + "!$D$4:$E$8", nm.RefersToFormula); }
public void TestNamedReadModify() { HSSFWorkbook wb = OpenSample("namedinput.xls"); NPOI.SS.UserModel.Name name = wb.GetNameAt(0); String sheetName = wb.GetSheetName(0); Assert.AreEqual(sheetName + "!$A$1:$D$10", name.RefersToFormula); name = wb.GetNameAt(1); String newReference = sheetName + "!$A$1:$C$36"; name.RefersToFormula = (newReference); Assert.AreEqual(newReference, name.RefersToFormula); }
public void TestUnicodeNamedRange() { HSSFWorkbook workBook = new HSSFWorkbook(); workBook.CreateSheet("Test"); NPOI.SS.UserModel.Name name = workBook.CreateName(); name.NameName = ("\u03B1"); name.RefersToFormula = ("Test!$D$3:$E$8"); HSSFWorkbook workBook2 = HSSFTestDataSamples.WriteOutAndReadBack(workBook); NPOI.SS.UserModel.Name name2 = workBook2.GetNameAt(0); Assert.AreEqual("\u03B1", name2.NameName); Assert.AreEqual("Test!$D$3:$E$8", name2.RefersToFormula); }
public void TestMultiNamedRange() { // Create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // 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' NPOI.SS.UserModel.Name 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' NPOI.SS.UserModel.Name 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 = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name 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); NPOI.SS.UserModel.Name 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 TestNamedRead() { HSSFWorkbook wb = OpenSample("namedinput.xls"); //Get index of the namedrange with the name = "NamedRangeName" , which was defined in input.xls as A1:D10 int NamedRangeIndex = wb.GetNameIndex("NamedRangeName"); //Getting NAmed Range NPOI.SS.UserModel.Name namedRange1 = wb.GetNameAt(NamedRangeIndex); String sheetName = wb.GetSheetName(0); //Getting its reference String reference = namedRange1.RefersToFormula; Assert.AreEqual(sheetName + "!$A$1:$D$10", reference); NPOI.SS.UserModel.Name namedRange2 = wb.GetNameAt(1); Assert.AreEqual(sheetName + "!$D$17:$G$27", namedRange2.RefersToFormula); Assert.AreEqual(namedRange2.NameName, "SecondNamedRange"); }
public void TestNamedCell_1() { // setup for this Testcase String sheetName = "Test Named Cell"; String cellName = "A name for a named cell"; String cellValue = "TEST Value"; HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sheetName); sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cellValue)); // Create named range for a single cell using areareference NPOI.SS.UserModel.Name 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); NPOI.SS.UserModel.Name 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); NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName); Assert.IsNotNull(s); Row r = sheet.GetRow(cref.Row); Cell c = r.GetCell(cref.Col); String contents = c.RichStringCellValue.String; Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference"); }
public void TestInvoke() { HSSFWorkbook wb; NPOI.SS.UserModel.Sheet sheet ; Cell cell; if (false) { // TODO - this code won't work until we can create user-defined functions directly with POI wb = new HSSFWorkbook(); sheet = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); NPOI.SS.UserModel.Name hssfName = wb.CreateName(); hssfName.NameName = ("myFunc"); } else { // This sample spreadsheet already has a VB function called 'myFunc' wb = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls"); sheet = wb.GetSheetAt(0); Row row = sheet.CreateRow(0); cell = row.CreateCell(1); } cell.CellFormula = ("myFunc()"); String actualFormula = cell.CellFormula; Assert.AreEqual("myFunc()", actualFormula); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); NPOI.SS.UserModel.CellValue evalResult = fe.Evaluate(cell); // Check the return value from ExternalFunction.evaluate() // TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better Assert.AreEqual(NPOI.SS.UserModel.CellType.ERROR, evalResult.CellType); Assert.AreEqual(ErrorEval.FUNCTION_NOT_IMPLEMENTED.ErrorCode, evalResult.ErrorValue); }
public void TestWithNamedRange() { HSSFWorkbook workbook = new HSSFWorkbook(); FormulaParser fp; Ptg[] ptgs; NPOI.SS.UserModel.Sheet 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); NPOI.SS.UserModel.Name name = workbook.CreateName(); name.NameName = ("testName"); name.RefersToFormula = ("A1:A2"); ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook); Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length); Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); // Now make it a single cell name.RefersToFormula = ("C3"); ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook); Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length); Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); // And make it non-contiguous name.RefersToFormula = ("A1:A2,C3"); ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook); Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length); Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); }
public override void OutPut(DataTable dt) { string fileExt = DateTime.Now.ToString("yyyyMMddHHmmss"); string fileOutPut = base.m_OutputFilePath.Insert(m_OutputFilePath.LastIndexOf("."), fileExt); string saveFileName = fileOutPut; //SaveFileDialog saveDialog = new SaveFileDialog(); //saveDialog.DefaultExt = "xls"; //saveDialog.Filter = "Excel文件|*.xls"; //saveDialog.FileName = fileName; //saveDialog.ShowDialog(); //saveFileName = saveDialog.FileName; HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); Sheet sheet = workbook.CreateSheet("Sheet1"); NPOI.SS.UserModel.Row dataTableName = sheet.CreateRow(0); NPOI.SS.UserModel.Cell cellTableName = dataTableName.CreateCell(0); cellTableName.SetCellValue(dt.TableName); //sheet.SetActiveCellRange(0, 2, 0, dt.Columns.Count); int rangeID = sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 0, dt.Columns.Count)); NPOI.SS.UserModel.Row dataRowColumnName = sheet.CreateRow(3); for (int j = 0; j < dt.Columns.Count; j++) { NPOI.SS.UserModel.Cell cellName = dataRowColumnName.CreateCell(j); cellName.SetCellValue(dt.Columns[j].ColumnName); NPOI.SS.UserModel.Name sheetName = workbook.CreateName(); sheetName.NameName = dt.Columns[j].ColumnName; sheetName.SheetIndex = 0; } int rowCount = dt.Rows.Count; int colCount = dt.Columns.Count; for (int i = 0; i < rowCount; i++) { NPOI.SS.UserModel.Row dataRow = sheet.CreateRow(4 + i); for (int j = 0; j < colCount; j++) { NPOI.SS.UserModel.Cell cell = dataRow.CreateCell(j); if (dt.Rows[i][j].GetType().Equals(typeof(DateTime))) { cell.SetCellValue(((DateTime)(dt.Rows[i][j])).ToString("yyyy-MM-dd HH:mm:ss")); } else { cell.SetCellValue(dt.Rows[i][j].ToString()); //项目序号 } } } workbook.Write(ms); FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); if (MessageBox.Show("Excel导出成功:" + fileOutPut + "\r\n是否要打开?", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { System.Diagnostics.Process.Start(fileOutPut); } }
public void TestRepeatingRowsAndColumsNames() { // First Test that setting RR&C for same sheet more than once only Creates a // single Print_Titles built-in record HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("FirstSheet"); // set repeating rows and columns twice for the first sheet for (int i = 0; i < 2; i++) { wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1); sheet.CreateFreezePane(0, 3); } Assert.AreEqual(1, wb.NumberOfNames); NPOI.SS.UserModel.Name nr1 = wb.GetNameAt(0); Assert.AreEqual("Print_Titles", nr1.NameName); if (false) { // TODO - full column references not rendering properly, absolute markers not present either Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula); } else { Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula); } // Save and re-Open HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.AreEqual(1, nwb.NumberOfNames); nr1 = nwb.GetNameAt(0); Assert.AreEqual("Print_Titles", nr1.NameName); Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula); // Check that setting RR&C on a second sheet causes a new Print_Titles built-in // name to be Created sheet = nwb.CreateSheet("SecondSheet"); nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0); Assert.AreEqual(2, nwb.NumberOfNames); NPOI.SS.UserModel.Name nr2 = nwb.GetNameAt(1); Assert.AreEqual("Print_Titles", nr2.NameName); Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula); if (false) { // In case you fancy Checking in excel, to ensure it // won't complain about the file now try { string tmppath = NPOI.Util.TempFile.GetTempFilePath("POI-45126-", ".xls"); FileStream fout = new FileStream(tmppath, FileMode.OpenOrCreate); nwb.Write(fout); fout.Close(); Console.WriteLine("Check out " + Path.GetFullPath(tmppath)); } catch (IOException) { throw; } } }
private static void AddListValidations(WorkbookFormatter wf, HSSFWorkbook 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 NPOI.SS.UserModel.Sheet fSheet = wf.CreateSheet("Lists"); NPOI.SS.UserModel.Sheet 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, DVConstraint.ValidationType.LIST); String listValsDescr = "POIFS,HSSF,HWPF,HPSF"; String[] listVals = listValsDescr.Split(new char[] { ',' }); 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, DVConstraint.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); NPOI.SS.UserModel.Name namedRange = wb.CreateName(); namedRange.NameName = ("myName"); namedRange.RefersToFormula = (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++) { Row currRow = fSheet.CreateRow(i + 29); SetCellValue(currRow.CreateCell(0), cellStrValue); } // Add list data on another sheet for (int i = 0; i < 10; i++) { Row 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); } }