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 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 TestBug58085RemoveSheetWithNames() { HSSFWorkbook wb1 = new HSSFWorkbook(); ISheet sheet1 = wb1.CreateSheet("sheet1"); ISheet sheet2 = wb1.CreateSheet("sheet2"); ISheet sheet3 = wb1.CreateSheet("sheet3"); sheet1.CreateRow(0).CreateCell((short)0).SetCellValue("val1"); sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("val2"); sheet3.CreateRow(0).CreateCell((short)0).SetCellValue("val3"); IName namedCell1 = wb1.CreateName(); namedCell1.NameName = (/*setter*/ "name1"); String reference1 = "sheet1!$A$1"; namedCell1.RefersToFormula = (/*setter*/ reference1); IName namedCell2 = wb1.CreateName(); namedCell2.NameName = (/*setter*/ "name2"); String reference2 = "sheet2!$A$1"; namedCell2.RefersToFormula = (/*setter*/ reference2); IName namedCell3 = wb1.CreateName(); namedCell3.NameName = (/*setter*/ "name3"); String reference3 = "sheet3!$A$1"; namedCell3.RefersToFormula = (/*setter*/ reference3); HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1); wb1.Close(); IName nameCell = wb2.GetName("name1"); Assert.AreEqual("sheet1!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name2"); Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name3"); Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula); wb2.RemoveSheetAt(wb2.GetSheetIndex("sheet1")); nameCell = wb2.GetName("name1"); Assert.AreEqual("#REF!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name2"); Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name3"); Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula); wb2.Close(); }
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 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 TestNamedRange() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls"); //Creating new Named Range IName 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 IName namedRange1 = wb.GetNameAt(0); //Getting it sheet name sheetName = namedRange1.SheetName; // sanity check SanityChecker c = new SanityChecker(); c.CheckHSSFWorkbook(wb); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); IName 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 static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook, CellRangeAddressList addressList, string formulaName) { var hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss"); ISheet CourseSheet = workbook.CreateSheet(hiddenSheetName); workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true); //CourseSheet.CreateRow(0).CreateCell(0).SetCellValue(""); IRow row = null; ICell cell = null; for (int i = 0; i < datas.Length; i++) { row = CourseSheet.CreateRow(i); cell = row.CreateCell(0); cell.SetCellValue(datas[i]); } IName range = workbook.CreateName(); range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length); range.NameName = formulaName; DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(formulaName); HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint); sheet.AddValidationData(dataValidate); }
private void setSheet2(HSSFWorkbook workBook, ISheet sheet) { //创建表 ISheet sheet2 = workBook.CreateSheet("岗位数据"); //隐藏 workBook.SetSheetHidden(1, true); //取数据 using (var db = DbFactory.Open()) { var builder = db.From <Model.Post.Post>().Where(w => w.PostType == ZZTXEnums.行政村防汛防台工作组.ToString()); var rlist = db.Select <PostViewModel>(builder); for (int iRowIndex = 0; iRowIndex < rlist.Count; iRowIndex++) { sheet2.CreateRow(iRowIndex).CreateCell(0).SetCellValue(rlist[iRowIndex].PostName); } } //设计表名称 IName range = workBook.CreateName(); range.RefersToFormula = "岗位数据!$A:$A"; range.NameName = "PostDataName"; //定义下拉框范围 CellRangeAddressList regions = new CellRangeAddressList(3, 65535, 1, 1); //设置数据引用 DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("PostDataName"); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); }
/// <summary> /// 设置下拉选项 /// </summary> /// <param name="workBook"></param> /// <param name="sheet"></param> /// <param name="cellName"></param> /// <param name="cellNo"></param> /// <param name="list"></param> public void setSheet2(HSSFWorkbook workBook, ISheet sheet, string cellName, int cellNo, List <string> list) { //创建表 ISheet sheet2 = workBook.CreateSheet(cellName); //隐藏 workBook.SetSheetHidden(1, true); //取数据 for (int i = 0; i < list.Count; i++) { sheet2.CreateRow(i).CreateCell(0).SetCellValue(list[i]); } //设计表名称 IName range = workBook.CreateName(); range.RefersToFormula = cellName + "!$A:$A"; range.NameName = cellName; //定义下拉框范围 CellRangeAddressList regions = new CellRangeAddressList(2, 65535, cellNo, cellNo); //设置数据引用 DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(cellName); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); }
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 static void test1() //{ // HSSFWorkbook hssfworkbook = new HSSFWorkbook(); // HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet; // CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0); // DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" }); // HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); // sheet1.AddValidationData(dataValidate); // MemoryStream ms = new MemoryStream(); // hssfworkbook.Write(ms); // string workbookFile = @"D:\\wulei22.xls"; // hssfworkbook = null; // FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write); // byte[] data = ms.ToArray(); // fs.Write(data, 0, data.Length); // fs.Flush(); // fs.Close(); //} public static void setdownlist() { //创建工作簿 HSSFWorkbook ssfworkbook = new HSSFWorkbook(); //创建工作表(页) HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet; //创建一行 HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0); //设置表头 headerRow.CreateCell(0).SetCellValue("ID"); //设置表头的宽度 sheet1.SetColumnWidth(0, 15 * 256); #region 添加显示下拉列表 HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet; ssfworkbook.SetSheetHidden(1, true); //隐藏 sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据 sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB"); sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC"); HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称 // range.Reference = "ShtDictionary!$A$1:$A$3";//格式 range.NameName = "dicRange"; #endregion headerRow.CreateCell(1).SetCellValue("Selected"); sheet1.SetColumnWidth(1, 15 * 256); //将下拉列表添加 CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 1, 1); DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange"); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet1.AddValidationData(dataValidate); headerRow.CreateCell(2).SetCellValue("VALUE"); sheet1.SetColumnWidth(2, 15 * 256); //写入数据 //创建数据行 HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1); //填充数据 dataRow.CreateCell(0).SetCellValue("1"); //id dataRow.CreateCell(1).SetCellValue(""); //选择框 dataRow.CreateCell(2).SetCellValue("值"); //选择框 System.IO.MemoryStream ms = new System.IO.MemoryStream(); ssfworkbook.Write(ms); string filename = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls"; object Response = null; string workbookFile = @"D:\\wulei.xls"; FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + "")); //Response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); }
//Crea una tabla a partir de los parametros indicados public void CrearTabla(List <string> Columns, int Rows, int X, int Y, string headerText, string tableName, string hoja) { ISheet Sheet1 = hssfworkbook.GetSheet(hoja) != null?hssfworkbook.GetSheet(hoja) : hssfworkbook.CreateSheet(hoja); ICell Title = Sheet1.GetRow(Y) != null ? (Sheet1.GetRow(Y).GetCell(X) != null ? Sheet1.GetRow(Y).GetCell(X) : Sheet1.GetRow(Y).CreateCell(X)) : Sheet1.CreateRow(Y).CreateCell(X); Title.CellStyle = EstTitulos; Title.SetCellValue(headerText); Sheet1.AddMergedRegion(new CellRangeAddress(Y, Y + 1, X, X + Columns.Count - 1)); /* CAMPOS */ IRow RowColumns = Sheet1.GetRow(Y + 2) != null?Sheet1.GetRow(Y + 2) : Sheet1.CreateRow(Y + 2); for (int c = 0; c < Columns.Count; c++) { ICell CellColumn = RowColumns.GetCell(X + c) != null?RowColumns.GetCell(X + c) : RowColumns.CreateCell(X + c); CellColumn.CellStyle = EstSubtitulos; CellColumn.SetCellValue(Columns[c]); Sheet1.AutoSizeColumn(CellColumn.ColumnIndex); } for (int i = 0; i < Rows; i++) { IRow CurrentRow = Sheet1.GetRow(Y + i + 3) != null?Sheet1.GetRow(Y + i + 3) : Sheet1.CreateRow(Y + i + 3); for (int j = 0; j < Columns.Count; j++) { ICell currentCell = CurrentRow.GetCell(X + j) != null?CurrentRow.GetCell(X + j) : CurrentRow.CreateCell(X + j); currentCell.CellStyle = EstCeldas; } } IName name = hssfworkbook.CreateName(); if (hssfworkbook.GetName(tableName) == null) { name.NameName = tableName; } else { throw new Exception("No se pueden crear dos espacios de nombre iguales."); } name.RefersToFormula = "'" + hoja + "'" + "!$" + getChar(X + 1) + "$" + (X + 3) + ":$" + getChar(Y + Columns.Count) + "$" + (X + 3 + Rows); }
public void TestNamesInFormulas() { TestCases.CultureShim.SetCurrentCulture("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 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); }
private void CreateEmptyExcelFile(string filepath) { HSSFWorkbook wkbook = new HSSFWorkbook(); wkbook.CreateName(); using (FileStream filesave = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { wkbook.Write(filesave); } }
public void TestFormulasWithUnderscore() { HSSFWorkbook wb = new HSSFWorkbook(); 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); }
/// <summary> /// 引用另一个工作表的形式 得到下拉 /// </summary> /// <param name="book"></param> /// <param name="columnIndex"></param> /// <param name="values"></param> /// <param name="sheetName"></param> /// <returns></returns> public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "") { if (values == null) { return; } if (string.IsNullOrEmpty(sheetName)) { sheetName = "_constraintSheet_"; } //创建下拉数据到新表中 ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);; var firstRow = sheet.GetRow(0); var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells; var rowIndex = 0; var lastValue = string.Empty; foreach (var value in values) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(value); rowIndex++; lastValue = value; } //如果无可选值的话,则增加一个空选项,防止用户填写内容 if (values.Count() == 0) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(" "); rowIndex++; } //给该列所有单元格加上下拉选择 IName range = book.CreateName(); range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}", (Char)('A' + conColumnIndex), rowIndex.ToString(), sheetName); string rangeName = "dicRange" + columnIndex; range.NameName = rangeName; var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); var constraint = DVConstraint.CreateFormulaListConstraint(rangeName); book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN); //创建验证 HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint); //关联验证 HSSFSheet v = book.GetSheetAt(0) as HSSFSheet; v.AddValidationData(valid); }
private void WriteReportWithData() { var reportdata = _data.ReportData.ToList().AsParallel(); InitializeWorkbook(); var sheet = _hssfworkbook.CreateSheet("request"); _hssfworkbook.CreateSheet("hidden"); var style = _hssfworkbook.CreateCellStyle(); style.FillForegroundColor = HSSFColor.DarkBlue.Index; style.FillPattern = FillPattern.SolidForeground; var font = _hssfworkbook.CreateFont(); font.Color = HSSFColor.White.Index; style.SetFont(font); var rows = reportdata.Count() + 1; for (var j = 0; j < rows; j++) { var row = sheet.CreateRow(j); for (var i = 0; i < _data.Columns.Count; i++) { var header = _data.Columns.ElementAt(i); if (j == 0) { var cell = row.CreateCell(i); cell.SetCellValue(header); cell.CellStyle = style; } else { var dictionary = reportdata.ElementAt(j - 1); row.CreateCell(i).SetCellValue(dictionary[header]); } } sheet.AutoSizeColumn(j); } sheet.SetColumnHidden(0, true); var namedcell = _hssfworkbook.CreateName(); namedcell.NameName = "hidden"; var constraint = DVConstraint.CreateFormulaListConstraint("hidden"); var addressList = new CellRangeAddressList(1, reportdata.Count(), _data.Columns.Count, _data.Columns.Count); var validation = new HSSFDataValidation(addressList, constraint); _hssfworkbook.SetSheetHidden(1, true); }
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 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); Assert.AreEqual(CellType.Numeric, value.CellType); Assert.AreEqual(5.33, value.NumberValue, 0.0); } catch (Exception e) { if (e.Message.Equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) { Assert.Fail("Identified bug 47048a"); } throw e; } finally { wb.Close(); } }
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 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 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); }
/// <summary> /// 设置有效性 /// </summary> /// <param name="colName">列名</param> /// <param name="startRowIndex">开始行索引 0起</param> /// <param name="validSheetName">有效性 目标sheet</param> /// <param name="validateColName">有效性列名</param> /// <param name="validateRowStart">有效性开始行 0起</param> /// <param name="validateRowEnd">有效性结束行 0起</param> public void SetValidation(string colName, int startRowIndex, string validSheetName, string validateColName, int validateRowStart, int validateRowEnd) { var columnIndex = ColumnNameToIndex(colName); //设置数据有效性作用域 var regions = new CellRangeAddressList(startRowIndex, 65535, columnIndex, columnIndex); //设置名称管理器管理数据源范围 var range = workbook.CreateName(); // 验证页, 验证列名 验证开始行 range.RefersToFormula = validSheetName + "!$" + validateColName + "$" + (validateRowStart + 1) + //验证结束列 //验证结束行 ":$" + validateColName + "$" + (validateRowStart + validateRowEnd); range.NameName = "dicRange" + columnIndex; //根据名称生成下拉框内容 DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange" + columnIndex); //绑定下拉框和作用区域 var dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); }
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 static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values) { var sheetName = "_constraintSheet_"; ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName); var firstRow = sheet.GetRow(0); var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells; var rowIndex = 0; var lastValue = ""; foreach (var value in values) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(value); rowIndex++; lastValue = value; } //如果只有一个可选值,则增加一个相同的选项 if (values.Count() == 1) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(lastValue); rowIndex++; } IName range = book.CreateName(); range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}", (Char)('A' + conColumnIndex), rowIndex.ToString(), sheetName); string rangeName = "dicRange" + columnIndex; range.NameName = rangeName; var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); var constraint = DVConstraint.CreateFormulaListConstraint(rangeName); book.SetSheetHidden(book.GetSheetIndex(sheet), true); return(new HSSFDataValidation(cellRegions, constraint)); }
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 static void test1() { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet; sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB"); sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC"); HSSFName range = hssfworkbook.CreateName() as HSSFName; range.RefersToFormula = "ShtDictionary!$A1:$A3"; range.NameName = "dicRange"; HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet; CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0); DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange"); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet1.AddValidationData(dataValidate); MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); string workbookFile = @"D:\\wulei1.xls"; hssfworkbook = null; FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); }
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 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); }