/** * Opens the sheet we wrote out by BinomialOperator and makes sure the formulas * all Match what we expect (x operator y) */ private static void BinomialVerify(String operator1, HSSFWorkbook wb) { NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0); IRow r = null; ICell c = null; //get our minimum values r = s.GetRow(0); c = r.GetCell(1); Assert.IsTrue(("1" + operator1 + "1").Equals(c.CellFormula), "minval Formula is as expected 1" + operator1 + "1 != " + c.CellFormula); for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2)) { r = s.GetRow(x); for (int y = 1; y < 256 && y > 0; y++) { c = r.GetCell(y); Assert.IsTrue(("" + x + operator1 + y).Equals(c.CellFormula), "loop Formula is as expected " + x + operator1 + y + "!=" + c.CellFormula ); } } //Test our maximum values r = s.GetRow(0); c = r.GetCell(0); Assert.IsTrue( ("" + short.MaxValue + operator1 + short.MaxValue).Equals(c.CellFormula), "maxval Formula is as expected" ); }
public void TestWithTwoHyperlinks() { HSSFWorkbook wb = OpenSample("WithTwoHyperLinks.xls"); NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); ICell cell1 = sheet.GetRow(4).GetCell(0); IHyperlink link1 = cell1.Hyperlink; Assert.IsNotNull(link1); Assert.AreEqual("Foo", link1.Label); Assert.AreEqual("http://poi.apache.org/", link1.Address); Assert.AreEqual(4, link1.FirstRow); Assert.AreEqual(0, link1.FirstColumn); ICell cell2 = sheet.GetRow(8).GetCell(1); IHyperlink link2 = cell2.Hyperlink; Assert.IsNotNull(link2); Assert.AreEqual("Bar", link2.Label); Assert.AreEqual("http://poi.apache.org/hssf/", link2.Address); Assert.AreEqual(8, link2.FirstRow); Assert.AreEqual(1, link2.FirstColumn); wb.Close(); }
public void TestOnARealFile() { HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("DateFormats.xls"); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); InternalWorkbook wb = workbook.Workbook; Assert.IsNotNull(wb); IRow row; ICell cell; NPOI.SS.UserModel.ICellStyle style; double aug_10_2007 = 39304.0; // Should have dates in 2nd column // All of them are the 10th of August // 2 US dates, 3 UK dates row = sheet.GetRow(0); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.AreEqual("d-mmm-yy", style.GetDataFormatString()); Assert.IsTrue(DateUtil.IsInternalDateFormat(style.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(1); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(2); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsTrue(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(3); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(4); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); }
public void Test44410() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls"); NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb); // =index(C:C,2,1) -> 2 IRow rowIDX = sheet.GetRow(3); // =sum(C:C) -> 6 IRow rowSUM = sheet.GetRow(4); // =sum(C:D) -> 66 IRow rowSUM2D = sheet.GetRow(5); // Test the sum ICell cellSUM = rowSUM.GetCell(0); FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord; Ptg[] ops = frec.FormulaRecord.ParsedExpression; Assert.AreEqual(2, ops.Length); Assert.AreEqual(typeof(AreaPtg), ops[0].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType()); // Actually stored as C1 to C65536 // (last row is -1 === 65535) AreaPtg ptg = (AreaPtg)ops[0]; Assert.AreEqual(2, ptg.FirstColumn); Assert.AreEqual(2, ptg.LastColumn); Assert.AreEqual(0, ptg.FirstRow); Assert.AreEqual(65535, ptg.LastRow); Assert.AreEqual("C:C", ptg.ToFormulaString()); // Will show as C:C, but won't know how many // rows it covers as we don't have the sheet // to hand when turning the Ptgs into a string Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula); // But the evaluator knows the sheet, so it // can do it properly Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0); // Test the index // Again, the formula string will be right but // lacking row count, Evaluated will be right ICell cellIDX = rowIDX.GetCell(0); Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula); Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0); // Across two colums ICell cellSUM2D = rowSUM2D.GetCell(0); Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula); Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0); wb.Close(); }
public DataTable RenderFromExcel(NPOI.SS.UserModel.ISheet sheet) { DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; int rowCount = sheet.LastRowNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j); } } } table.Rows.Add(dataRow); } return(table); }
//导入 public DataTable ImportExcel(string filePath) { HSSFWorkbook hssfworkbook; //初始化信息 #region try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); { DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0); //第一行为标题行 int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1 //handling header. for (int i = headerRow.FirstCellNum; i < cellCount; i++) { //if (headerRow.getCell(0) != null) //{ // row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // stuUser.setPhone(row.getCell(0).getStringCellValue()); //} DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = GetCellValue(row.GetCell(j)); } } } table.Rows.Add(dataRow); } return(table); } }
/// <summary> /// 导入Excel /// </summary> /// <param name="importPath">导入文件路径</param> /// <param name="headerIndex">表头所在行索引 (兼容第一行为大标题的情况)</param> public static DataSet ImportExcel(string importPath, int headerIndex = 0) { DataSet ds = new DataSet(); DataTable dt = null; FileStream fs = new FileStream(importPath, FileMode.Open, FileAccess.Read); IWorkbook book; if (Path.GetExtension(importPath) == "xls") { book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); } else { book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); } int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(headerIndex); //从第0行开始取 列头 if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new DataTable(sheet.SheetName); for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = headerIndex + 1; i <= sheet.LastRowNum; i++) //从第1行开始取数据 { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { var cell = sheet.GetRow(i).GetCell(j); cell.SetCellType(CellType.String); newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } ds.Tables.Add(dt); } return(ds); }
private void button1_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); DataTable dt = null; OpenFileDialog sflg = new OpenFileDialog(); sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new DataTable(sheet.SheetName); for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = 1; i <= sheet.LastRowNum; i++) { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } ds.Tables.Add(dt); } }
static void CreateSheet(List <Record> rs, NPOI.SS.UserModel.ISheet sheet) { var OutRs = new List <OutPutRecord>(); var 仓库统计H型钢 = string.Empty; var 仓库统计低合金H型钢 = string.Empty; foreach (var 仓库Group in rs.GroupBy(x => x.仓库)) { var 仓库记录列表 = 仓库Group.ToList(); foreach (var 规格Group in 仓库记录列表.GroupBy(x => x.规格 + "-" + x.产地)) { var r = new OutPutRecord(); var SegInfo = 规格Group.Key.Split("-".ToCharArray()); r.规格 = SegInfo[0]; r.产地 = 仓库Group.Key + "-" + SegInfo[1] + (SegInfo.Length == 3 ? ("-" + SegInfo[2]) : string.Empty); r.数量 = 规格Group.Sum(x => x.可供数); Console.WriteLine(r.产地 + ":" + r.规格); OutRs.Add(r); } 仓库统计H型钢 += 仓库Group.Key + ":" + 仓库Group.Sum(x => x.品名 == "H型钢" ? x.可供数 : 0) + "支,"; 仓库统计低合金H型钢 += 仓库Group.Key + ":" + 仓库Group.Sum(x => x.品名 == "低合金H型钢" ? x.可供数 : 0) + "支,"; } Console.WriteLine("Start Fill"); //填写数量 SeekAndFill(sheet, OutRs, 0); SeekAndFill(sheet, OutRs, 10); if (sheet.SheetName == "H型钢") { var rfirst = sheet.FirstRowNum; var rlast = sheet.LastRowNum; for (int i = rfirst; i < rlast; i++) { var row = sheet.GetRow(i); if (row.GetCell(1) != null && row.GetCell(1).CellType == CellType.String && row.GetCell(1).StringCellValue == "仓库") { row.GetCell(2).SetCellValue(仓库统计H型钢.TrimEnd(",".ToCharArray())); break; } } } else { SeekAndFill(sheet, OutRs, 20); var rfirst = sheet.FirstRowNum; var rlast = sheet.LastRowNum; for (int i = rfirst; i < rlast; i++) { var row = sheet.GetRow(i); if (row.GetCell(21) != null && row.GetCell(21).CellType == CellType.String && row.GetCell(21).StringCellValue == "仓库") { row.GetCell(22).SetCellValue(仓库统计低合金H型钢.TrimEnd(",".ToCharArray())); break; } } } }
public void TestStyleNames() { HSSFWorkbook wb = OpenSample("WithExtendedStyles.xls"); NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0); ICell c1 = s.GetRow(0).GetCell(0); ICell c2 = s.GetRow(1).GetCell(0); ICell c3 = s.GetRow(2).GetCell(0); HSSFCellStyle cs1 = (HSSFCellStyle)c1.CellStyle; HSSFCellStyle cs2 = (HSSFCellStyle)c2.CellStyle; HSSFCellStyle cs3 = (HSSFCellStyle)c3.CellStyle; Assert.IsNotNull(cs1); Assert.IsNotNull(cs2); Assert.IsNotNull(cs3); // Check we got the styles we'd expect Assert.AreEqual(10, cs1.GetFont(wb).FontHeightInPoints); Assert.AreEqual(9, cs2.GetFont(wb).FontHeightInPoints); Assert.AreEqual(12, cs3.GetFont(wb).FontHeightInPoints); Assert.AreEqual(15, cs1.Index); Assert.AreEqual(23, cs2.Index); Assert.AreEqual(24, cs3.Index); Assert.IsNull(cs1.ParentStyle); Assert.IsNotNull(cs2.ParentStyle); Assert.IsNotNull(cs3.ParentStyle); Assert.AreEqual(21, cs2.ParentStyle.Index); Assert.AreEqual(22, cs3.ParentStyle.Index); // Now Check we can get style records for // the parent ones Assert.IsNull(wb.Workbook.GetStyleRecord(15)); Assert.IsNull(wb.Workbook.GetStyleRecord(23)); Assert.IsNull(wb.Workbook.GetStyleRecord(24)); Assert.IsNotNull(wb.Workbook.GetStyleRecord(21)); Assert.IsNotNull(wb.Workbook.GetStyleRecord(22)); // Now Check the style names Assert.AreEqual(null, cs1.UserStyleName); Assert.AreEqual(null, cs2.UserStyleName); Assert.AreEqual(null, cs3.UserStyleName); Assert.AreEqual("style1", cs2.ParentStyle.UserStyleName); Assert.AreEqual("style2", cs3.ParentStyle.UserStyleName); // now apply a named style to a new cell ICell c4 = s.GetRow(0).CreateCell(1); c4.CellStyle = (cs2); Assert.AreEqual("style1", ((HSSFCellStyle)c4.CellStyle).ParentStyle.UserStyleName); }
/// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable Import(string strFileName) { DataTable dt = new DataTable(); // HSSFWorkbook hssfworkbook; IWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { string fileExt = Path.GetExtension(strFileName); if (fileExt == ".xls") { hssfworkbook = new HSSFWorkbook(file); } else if (fileExt == ".xlsx") { hssfworkbook = new XSSFWorkbook(file); } else { return(new DataTable()); } } NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dataRow); } return(dt); }
public void TestReadEmployeeSimple() { HSSFWorkbook workbook = OpenSample("Employee.xls"); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); Assert.AreEqual(EMPLOYEE_INFORMATION, sheet.GetRow(1).GetCell(1).RichStringCellValue.String); Assert.AreEqual(LAST_NAME_KEY, sheet.GetRow(3).GetCell(2).RichStringCellValue.String); Assert.AreEqual(FIRST_NAME_KEY, sheet.GetRow(4).GetCell(2).RichStringCellValue.String); Assert.AreEqual(SSN_KEY, sheet.GetRow(5).GetCell(2).RichStringCellValue.String); }
public void Test44297() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44297.xls"); IRow row; ICell cell; NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb); row = sheet.GetRow(0); cell = row.GetCell(0); Assert.AreEqual("31+46", cell.CellFormula); Assert.AreEqual(77, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(1); cell = row.GetCell(0); Assert.AreEqual("30+53", cell.CellFormula); Assert.AreEqual(83, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(2); cell = row.GetCell(0); Assert.AreEqual("SUM(A1:A2)", cell.CellFormula); Assert.AreEqual(160, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(4); cell = row.GetCell(0); Assert.AreEqual("32767+32768", cell.CellFormula); Assert.AreEqual(65535, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(7); cell = row.GetCell(0); Assert.AreEqual("32744+42333", cell.CellFormula); Assert.AreEqual(75077, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(8); cell = row.GetCell(0); Assert.AreEqual("327680/32768", cell.CellFormula); Assert.AreEqual(10, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(9); cell = row.GetCell(0); Assert.AreEqual("32767+32769", cell.CellFormula); Assert.AreEqual(65536, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(10); cell = row.GetCell(0); Assert.AreEqual("35000+36000", cell.CellFormula); Assert.AreEqual(71000, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(11); cell = row.GetCell(0); Assert.AreEqual("-1000000-3000000", cell.CellFormula); Assert.AreEqual(-4000000, eva.Evaluate(cell).NumberValue, 0); wb.Close(); }
/// <summary> /// 删除指定工作表名的所有行 /// </summary> /// <param name="sheetName">工作表名称</param> private void sheetClear(string sheetName) { xlSheet = xlBook.GetSheet(sheetName); NPOI.SS.UserModel.IRow row = null; for (int i = 0; i < xlSheet.LastRowNum + 1; i++) { if (xlSheet.GetRow(i) != null) { row = xlSheet.GetRow(i); xlSheet.RemoveRow(row); } } }
/// <summary> /// 合并单元格 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="sheet">工作表</param> /// <param name="columnIndex">列索引</param> /// <param name="options">导出选项配置</param> private void MergeCells <T>(NPOI.SS.UserModel.ISheet sheet, int columnIndex, IExportOptions <T> options) where T : class, new() { string currentCellValue; var startRowIndex = options.DataRowStartIndex; NPOI.SS.Util.CellRangeAddress mergeRangeAddress; var startRow = sheet.GetRow(startRowIndex); if (startRow == null) { return; } var startCell = startRow.GetCell(columnIndex); if (startCell == null) { return; } string startCellValue = startCell.StringCellValue; if (string.IsNullOrWhiteSpace(startCellValue)) { return; } for (var rowIndex = options.DataRowStartIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++) { var cell = sheet.GetRow(rowIndex)?.GetCell(columnIndex); currentCellValue = cell == null ? string.Empty : cell.StringCellValue; if (currentCellValue.Trim() != startCellValue.Trim()) { mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex - 1, columnIndex, columnIndex); if (mergeRangeAddress.NumberOfCells > 1) { sheet.AddMergedRegion(mergeRangeAddress); startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center; } startRowIndex = rowIndex; startCellValue = currentCellValue; } if (rowIndex == sheet.PhysicalNumberOfRows - 1 && startRowIndex != rowIndex) { mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex, columnIndex, columnIndex); sheet.AddMergedRegion(mergeRangeAddress); startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center; } } }
public void TestModifySimple() { HSSFWorkbook workbook = OpenSample("Simple.xls"); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); ICell cell = sheet.GetRow(0).GetCell(0); cell.SetCellValue(new HSSFRichTextString(REPLACED)); workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); sheet = workbook.GetSheetAt(0); cell = sheet.GetRow(0).GetCell(0); Assert.AreEqual(REPLACED, cell.RichStringCellValue.String); }
/// <summary> /// 设置单元格内容 /// </summary> /// <param name="row">单元格行标(从0开始,下同)</param> /// <param name="col">单元格列标</param> /// <param name="o">写入内容</param> public void SetValue(int r, int c, object o) { if (o != null) { if (r <= osheet.LastRowNum) { IRow row = osheet.GetRow(r); if (row == null) { row = osheet.CreateRow(r); row.HeightInPoints = 14; } if (c <= row.LastCellNum) { ICell cell = row.GetCell(c); cell.SetCellValue(o.ToString()); } else { for (int j = row.LastCellNum; j < c; j++) { row.CreateCell(j + 1); ICell cell22 = row.GetCell(j + 1); ICellStyle style = obook.CreateCellStyle(); cell22.CellStyle = style; } ICell cell = row.GetCell(c); cell.SetCellValue(o.ToString()); } } else { for (int i = osheet.LastRowNum; i < r; i++) { IRow row22 = osheet.CreateRow(i + 1); row22.HeightInPoints = 14; } IRow row = osheet.GetRow(r); for (int j = row.LastCellNum; j < c; j++) { row.CreateCell(j + 1);; ICell cell22 = row.GetCell(j + 1); ICellStyle style = obook.CreateCellStyle(); cell22.CellStyle = style; } ICell cell = row.GetCell(c); cell.SetCellValue(o.ToString()); } } }
public void TestBOFandEOFRecords() { HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("SimpleChart.xls"); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); IRow firstRow = sheet.GetRow(0); ICell firstCell = firstRow.GetCell(0); //System.out.println("first assertion for date"); Assert.AreEqual(new DateTime(2000, 1, 1, 10, 51, 2), DateUtil.GetJavaDate(firstCell.NumericCellValue, false)); IRow row = sheet.CreateRow(15); ICell cell = row.CreateCell(1); cell.SetCellValue(22); InternalSheet newSheet = ((HSSFSheet)workbook.GetSheetAt(0)).Sheet; IList <RecordBase> records = newSheet.Records; //System.out.println("BOF Assertion"); Assert.IsTrue(records[0] is BOFRecord); //System.out.println("EOF Assertion"); Assert.IsTrue(records[records.Count - 1] is EOFRecord); workbook.Close(); }
public void TestAbsRefs() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); IRow r; ICell c; r = s.CreateRow(0); c = r.CreateCell(0); c.CellFormula = ("A3+A2"); c = r.CreateCell(1); c.CellFormula = ("$A3+$A2"); c = r.CreateCell(2); c.CellFormula = ("A$3+A$2"); c = r.CreateCell(3); c.CellFormula = ("$A$3+$A$2"); c = r.CreateCell(4); c.CellFormula = ("SUM($A$3,$A$2)"); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); s = wb.GetSheetAt(0); r = s.GetRow(0); c = r.GetCell(0); Assert.IsTrue(("A3+A2").Equals(c.CellFormula), "A3+A2"); c = r.GetCell(1); Assert.IsTrue(("$A3+$A2").Equals(c.CellFormula), "$A3+$A2"); c = r.GetCell(2); Assert.IsTrue(("A$3+A$2").Equals(c.CellFormula), "A$3+A$2"); c = r.GetCell(3); Assert.IsTrue(("$A$3+$A$2").Equals(c.CellFormula), "$A$3+$A$2"); c = r.GetCell(4); Assert.IsTrue(("SUM($A$3,$A$2)").Equals(c.CellFormula), "SUM($A$3,$A$2)"); }
/** * Writes a function then Tests to see if its correct * */ public void RefAreaArrayFunctionTest(String function) { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); IRow r = null; ICell c = null; r = s.CreateRow(0); c = r.CreateCell(0); c.CellFormula = (function + "(A2:A4,B2:B4)"); c = r.CreateCell(1); c.CellFormula = (function + "($A$2:$A4,B$2:B4)"); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); s = wb.GetSheetAt(0); r = s.GetRow(0); c = r.GetCell(0); Assert.IsTrue( (function + "(A2:A4,B2:B4)").Equals(c.CellFormula), "function =" + function + "(A2:A4,B2:B4)" ); c = r.GetCell(1); Assert.IsTrue((function + "($A$2:$A4,B$2:B4)").Equals(c.CellFormula), "function =" + function + "($A$2:$A4,B$2:B4)" ); }
/** * All multi-binomial operator Tests use this to Create a worksheet with a * huge set of x operator y formulas. Next we call BinomialVerify and verify * that they are all how we expect. */ private static void BinomialOperator(String operator1) { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); IRow r = null; ICell c = null; //get our minimum values r = s.CreateRow(0); c = r.CreateCell(1); c.CellFormula = (1 + operator1 + 1); for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2)) { r = s.CreateRow(x); for (int y = 1; y < 256 && y > 0; y++) { c = r.CreateCell(y); c.CellFormula = ("" + x + operator1 + y); } } //make sure we do the maximum value of the Int operator if (s.LastRowNum < short.MaxValue) { r = s.GetRow(0); c = r.CreateCell(0); c.CellFormula = ("" + short.MaxValue + operator1 + short.MaxValue); } wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); BinomialVerify(operator1, wb); }
public void TestSheetFunctions() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet("A"); IRow r = null; ICell c = null; r = s.CreateRow(0); c = r.CreateCell(0); c.SetCellValue(1); c = r.CreateCell(1); c.SetCellValue(2); s = wb.CreateSheet("B"); r = s.CreateRow(0); c = r.CreateCell(0); c.CellFormula = ("AVERAGE(A!A1:B1)"); c = r.CreateCell(1); c.CellFormula = ("A!A1+A!B1"); c = r.CreateCell(2); c.CellFormula = ("A!$A$1+A!$B1"); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); s = wb.GetSheet("B"); r = s.GetRow(0); c = r.GetCell(0); Assert.IsTrue(("AVERAGE(A!A1:B1)").Equals(c.CellFormula), "expected: AVERAGE(A!A1:B1) got: " + c.CellFormula); c = r.GetCell(1); Assert.IsTrue(("A!A1+A!B1").Equals(c.CellFormula), "expected: A!A1+A!B1 got: " + c.CellFormula); }
public void TestManyRows() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); IRow row; ICell cell; int i, j; for (i = 0, j = 32771; j > 0; i++, j--) { row = sheet.CreateRow(i); cell = row.CreateCell(0); cell.SetCellValue(i); } sanityChecker.CheckHSSFWorkbook(workbook); Assert.AreEqual(32770, sheet.LastRowNum, "LAST ROW == 32770"); cell = sheet.GetRow(32770).GetCell(0); double lastVal = cell.NumericCellValue; HSSFWorkbook wb = HSSFTestDataSamples.WriteOutAndReadBack(workbook); NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0); row = s.GetRow(32770); cell = row.GetCell(0); Assert.AreEqual(lastVal, cell.NumericCellValue, 0, "Value from last row == 32770"); Assert.AreEqual(32770, s.LastRowNum, "LAST ROW == 32770"); }
public void TestSquareMacro() { HSSFWorkbook w = OpenSample("SquareMacro.xls"); NPOI.SS.UserModel.ISheet s0 = w.GetSheetAt(0); IRow[] r = { s0.GetRow(0), s0.GetRow(1) }; ICell a1 = r[0].GetCell(0); Assert.AreEqual("square(1)", a1.CellFormula); Assert.AreEqual(1d, a1.NumericCellValue, 1e-9); ICell a2 = r[1].GetCell(0); Assert.AreEqual("square(2)", a2.CellFormula); Assert.AreEqual(4d, a2.NumericCellValue, 1e-9); ICell b1 = r[0].GetCell(1); Assert.AreEqual("IF(TRUE,square(1))", b1.CellFormula); Assert.AreEqual(1d, b1.NumericCellValue, 1e-9); ICell b2 = r[1].GetCell(1); Assert.AreEqual("IF(TRUE,square(2))", b2.CellFormula); Assert.AreEqual(4d, b2.NumericCellValue, 1e-9); ICell c1 = r[0].GetCell(2); Assert.AreEqual("square(square(1))", c1.CellFormula); Assert.AreEqual(1d, c1.NumericCellValue, 1e-9); ICell c2 = r[1].GetCell(2); Assert.AreEqual("square(square(2))", c2.CellFormula); Assert.AreEqual(16d, c2.NumericCellValue, 1e-9); ICell d1 = r[0].GetCell(3); Assert.AreEqual("square(one())", d1.CellFormula); Assert.AreEqual(1d, d1.NumericCellValue, 1e-9); ICell d2 = r[1].GetCell(3); Assert.AreEqual("square(two())", d2.CellFormula); Assert.AreEqual(4d, d2.NumericCellValue, 1e-9); }
private static DateTime ReadCell(HSSFWorkbook workbook, int rowIdx, int colIdx) { NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); IRow row = sheet.GetRow(rowIdx); ICell cell = row.GetCell(colIdx); return(cell.DateCellValue); }
private static void ConfirmResolveCellRef(HSSFWorkbook wb, CellReference cref) { NPOI.SS.UserModel.ISheet s = wb.GetSheet(cref.SheetName); IRow r = s.GetRow(cref.Row); ICell c = r.GetCell((int)cref.Col); Assert.IsNotNull(c); }
private static void ConfirmCell(NPOI.SS.UserModel.ISheet sheet, int rowIx, int colIx, double expectedValue, String expectedFormula) { ICell cell = sheet.GetRow(rowIx).GetCell(colIx); Assert.AreEqual(expectedValue, cell.NumericCellValue, 0.0); Assert.AreEqual(expectedFormula, cell.CellFormula); }
public void TestShiftRows() { // Read initial file in HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls"); NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0); // Shift the second row down 1 and Write to temp file s.ShiftRows(1, 1, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); // Read from temp file and Check the number of cells in each // row (in original file each row was unique) s = wb.GetSheetAt(0); Assert.AreEqual(1, s.GetRow(0).PhysicalNumberOfCells); ConfirmEmptyRow(s, 1); Assert.AreEqual(2, s.GetRow(2).PhysicalNumberOfCells); Assert.AreEqual(4, s.GetRow(3).PhysicalNumberOfCells); Assert.AreEqual(5, s.GetRow(4).PhysicalNumberOfCells); // Shift rows 1-3 down 3 in the current one. This Tests when // 1 row is blank. Write to a another temp file s.ShiftRows(0, 2, 3); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); // Read and ensure things are where they should be s = wb.GetSheetAt(0); ConfirmEmptyRow(s, 0); ConfirmEmptyRow(s, 1); ConfirmEmptyRow(s, 2); Assert.AreEqual(1, s.GetRow(3).PhysicalNumberOfCells); ConfirmEmptyRow(s, 4); Assert.AreEqual(2, s.GetRow(5).PhysicalNumberOfCells); // Read the first file again wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls"); s = wb.GetSheetAt(0); // Shift rows 3 and 4 up and Write to temp file s.ShiftRows(2, 3, -2); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); s = wb.GetSheetAt(0); Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 3); Assert.AreEqual(s.GetRow(1).PhysicalNumberOfCells, 4); ConfirmEmptyRow(s, 2); ConfirmEmptyRow(s, 3); Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5); }
private static DataTable ReadFromFile(string filePath) { IWorkbook hssfworkbook; #region 初始化信息 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = WorkbookFactory.Create(file) as IWorkbook; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); rows.MoveNext(); IRow row = rows.Current as IRow; for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //将第一列作为列表头 DataColumn dtc = new DataColumn(row.GetCell(j).ToString(), typeof(string)); dt.Columns.Add(dtc); } while (rows.MoveNext()) { try { row = (IRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); Console.WriteLine(cell == null ? "" : cell + "\t\t"); Console.WriteLine(); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } catch (Exception ex) { continue; // throw; } } return(dt); }
public void TestShiftWithFormulas() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ForShifting.xls"); NPOI.SS.UserModel.ISheet sheet = wb.GetSheet("Sheet1"); Assert.AreEqual(20, sheet.LastRowNum); ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)"); ConfirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)"); ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)"); ConfirmCell(sheet, 6, 1, 271, "200+B1"); ConfirmCell(sheet, 7, 1, 272, "200+B2"); ConfirmCell(sheet, 8, 1, 273, "200+B3"); ConfirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced // ----------- // Row index 1 -> 11 (row "2" -> row "12") sheet.ShiftRows(1, 1, 10); // Now Check what sheet looks like after move // no changes on row "1" ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)"); // row "2" is now empty Assert.AreEqual(0, sheet.GetRow(1).PhysicalNumberOfCells); // Row "2" moved to row "12", and the formula has been updated. // note however that the cached formula result (2) has not been updated. (POI differs from Excel here) ConfirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)"); // no changes on row "3" ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)"); ConfirmCell(sheet, 14, 0, 0.0, "#REF!"); // Formulas on rows that weren't shifted: ConfirmCell(sheet, 6, 1, 271, "200+B1"); ConfirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved ConfirmCell(sheet, 8, 1, 273, "200+B3"); // Check formulas on other sheets NPOI.SS.UserModel.ISheet sheet2 = wb.GetSheet("Sheet2"); ConfirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1"); ConfirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12"); ConfirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3"); ConfirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!"); // Note - named ranges formulas have not been updated }