public void TestStdev() { IWorkbook wb = new HSSFWorkbook(); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(0).CreateCell(0); a1.SetCellValue(1); ICell a2 = sh.CreateRow(1).CreateCell(0); a2.SetCellValue(3); ICell a3 = sh.CreateRow(2).CreateCell(0); a3.CellFormula = ("SUBTOTAL(7,A1:A2)"); ICell a4 = sh.CreateRow(3).CreateCell(0); a4.SetCellValue(1); ICell a5 = sh.CreateRow(4).CreateCell(0); a5.SetCellValue(7); ICell a6 = sh.CreateRow(5).CreateCell(0); a6.CellFormula = ("SUBTOTAL(7,A1:A5)*2 + 2"); ICell a7 = sh.CreateRow(6).CreateCell(0); a7.CellFormula = ("SUBTOTAL(7,A1:A6)"); fe.EvaluateAll(); Assert.AreEqual(1.41421, a3.NumericCellValue, 0.0001); Assert.AreEqual(7.65685, a6.NumericCellValue, 0.0001); Assert.AreEqual(2.82842, a7.NumericCellValue, 0.0001); }
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet) { var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2); sheet.AddMergedRegion(merge); var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var caminho = $"{diretorioAtual}/Recursos/logoVLI.png"; byte[] data = ArquivosUtil.RetornarArquivo(caminho); int pictureIndex = workbook.AddPicture(data, PictureType.JPEG); ICreationHelper helper = workbook.GetCreationHelper(); IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = helper.CreateClientAnchor(); anchor.Col1 = 1; anchor.Row1 = 1; IPicture picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/ return(sheet); }
public static void insertExcelData(string folderPath, string fileName, string[] args) { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCreationHelper createHelper = (HSSFCreationHelper)wb.GetCreationHelper(); using (FileStream file = new FileStream(folderPath + "\\" + fileName, FileMode.Open, FileAccess.Read)) { wb = new HSSFWorkbook(file); } ISheet sheet = wb.GetSheet("219Option"); IRow row = sheet.CreateRow(sheet.LastRowNum + 1); row.CreateCell(0).SetCellValue(createHelper.CreateRichTextString(args[1])); row.CreateCell(1).SetCellValue(createHelper.CreateRichTextString(args[2])); row.CreateCell(2).SetCellValue(createHelper.CreateRichTextString(args[3])); using (FileStream file = new FileStream(folderPath + "\\" + fileName, FileMode.Create, FileAccess.Write)) { wb.Write(file); file.Close(); } } catch (Exception ex) { Console.WriteLine("Occur error insertExcelData function : " + ex.ToString()); } }
public void TestFunctionsFromTestSpreadsheet() { HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("SubtotalsNested.xls"); ISheet sheet = workbook.GetSheetAt(0); IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); Assert.AreEqual(10.0, sheet.GetRow(1).GetCell(1).NumericCellValue, "B2"); Assert.AreEqual(20.0, sheet.GetRow(2).GetCell(1).NumericCellValue, "B3"); //Test simple subtotal over one area ICell cellA3 = sheet.GetRow(3).GetCell(1); ConfirmExpectedResult(evaluator, "B4", cellA3, 30.0); //Test existence of the second area Assert.IsNotNull(sheet.GetRow(1).GetCell(2), "C2 must not be null"); Assert.AreEqual(7.0, sheet.GetRow(1).GetCell(2).NumericCellValue, "C2"); ICell cellC1 = sheet.GetRow(1).GetCell(3); ICell cellC2 = sheet.GetRow(2).GetCell(3); ICell cellC3 = sheet.GetRow(3).GetCell(3); //Test Functions SUM, COUNT and COUNTA calculation of SUBTOTAL //a) areas A and B are used //b) first 2 subtotals don't consider the value of nested subtotal in A3 ConfirmExpectedResult(evaluator, "SUBTOTAL(SUM;B2:B8;C2:C8)", cellC1, 37.0); ConfirmExpectedResult(evaluator, "SUBTOTAL(COUNT;B2:B8;C2:C8)", cellC2, 3.0); ConfirmExpectedResult(evaluator, "SUBTOTAL(COUNTA;B2:B8;C2:C8)", cellC3, 5.0); }
private HSSFWorkbook OpenXLSFile(string fileName) { retry: try { string newFileName = Path.GetFullPath(Path.Combine(Directory.GetCurrentDirectory(), filePath + fileName)); FileStream file = new FileStream(newFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); HSSFWorkbook workbook = new HSSFWorkbook(file); file.Close(); //WORKAROUND: Replace values for unsupported formulas @"Utils" worksheet with the exact formula stored as string TempUtils(workbook.GetSheet("Utils")); workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); return(workbook); } catch (IOException) { MessageBox.Show(new Form() { TopMost = true }, "Please close the test case file: " + fileName + " and click on OK.", "File Access Conflict", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); goto retry; } }
public static void WriteDataToExcel(string path, IEnumerable <dynamic> list) { using (FileStream file = new FileStream(path, FileMode.Create, FileAccess.ReadWrite)) { var wb = new HSSFWorkbook(); var sheet = wb.CreateSheet("Sheet1"); var ch = wb.GetCreationHelper(); int table = 1; int seat = 1; int i = 0; foreach (var item in list) { var row = sheet.CreateRow(i); var cell = row.CreateCell(0); cell.SetCellValue(table); cell = row.CreateCell(1); cell.SetCellValue(seat); if (seat == 10) { seat = 1; table++; } else { seat++; } cell = row.CreateCell(2); cell.SetCellValue(item.lanid); cell = row.CreateCell(3); cell.SetCellValue(item.name); i++; } wb.Write(file); } }
public void TestAvg() { IWorkbook wb = new HSSFWorkbook(); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(1).CreateCell(1); a1.SetCellValue(1); ICell a2 = sh.CreateRow(2).CreateCell(1); a2.SetCellValue(3); ICell a3 = sh.CreateRow(3).CreateCell(1); a3.CellFormula = ("SUBTOTAL(1,B2:B3)"); ICell a4 = sh.CreateRow(4).CreateCell(1); a4.SetCellValue(1); ICell a5 = sh.CreateRow(5).CreateCell(1); a5.SetCellValue(7); ICell a6 = sh.CreateRow(6).CreateCell(1); a6.CellFormula = ("SUBTOTAL(1,B2:B6)*2 + 2"); ICell a7 = sh.CreateRow(7).CreateCell(1); a7.CellFormula = ("SUBTOTAL(1,B2:B7)"); fe.EvaluateAll(); Assert.AreEqual(2.0, a3.NumericCellValue); Assert.AreEqual(8.0, a6.NumericCellValue); Assert.AreEqual(3.0, a7.NumericCellValue); }
public void TestEvaluate() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet(); IRow row1 = sh.CreateRow(0); // Create cells row1.CreateCell(0, CellType.String); // Create references CellReference a1Ref = new CellReference("A1"); // Set values ICell cellA1 = sh.GetRow(a1Ref.Row).GetCell(a1Ref.Col); ICell cell1 = row1.CreateCell(1); cell1.CellFormula = "IFS(A1=\"A\", \"Value for A\", A1=\"B\",\"Value for B\")"; IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); cellA1.SetCellValue("A"); Assert.AreEqual(CellType.String, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric"); Assert.AreEqual("Value for A", evaluator.Evaluate(cell1).StringValue, "IFS should return 'Value for B'"); cellA1.SetCellValue("B"); evaluator.ClearAllCachedResultValues(); Assert.AreEqual(CellType.String, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric"); Assert.AreEqual("Value for B", evaluator.Evaluate(cell1).StringValue, "IFS should return 'Value for B'"); }
public void TestResultOutsideRange() { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); HSSFWorkbook wb = new HSSFWorkbook(); ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0); cell.CellFormula = "D2:D5"; // IF(TRUE,D2:D5,D2) or OFFSET(D2:D5,0,0) would work too IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); CellValue cv; try { cv = fe.Evaluate(cell); } catch (ArgumentException e) { if ("Specified row index (0) is outside the allowed range (1..4)".Equals(e.Message)) { throw new AssertionException("Identified bug in result dereferencing"); } throw; } Assert.AreEqual(CellType.Error, cv.CellType); Assert.AreEqual(ErrorConstants.ERROR_VALUE, cv.ErrorValue); // verify circular refs are still detected properly fe.ClearAllCachedResultValues(); cell.CellFormula = "OFFSET(A1,0,0)"; cv = fe.Evaluate(cell); Assert.AreEqual(CellType.Error, cv.CellType); Assert.AreEqual(ErrorEval.CIRCULAR_REF_ERROR.ErrorCode, cv.ErrorValue); }
public void TestCount() { IWorkbook wb = new HSSFWorkbook(); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(1).CreateCell(1); a1.SetCellValue(1); ICell a2 = sh.CreateRow(2).CreateCell(1); a2.SetCellValue(3); ICell a3 = sh.CreateRow(3).CreateCell(1); a3.CellFormula = ("SUBTOTAL(2,B2:B3)"); ICell a4 = sh.CreateRow(4).CreateCell(1); a4.SetCellValue("POI"); // A4 is string and not counted ICell a5 = sh.CreateRow(5).CreateCell(1); // A5 is blank and not counted ICell a6 = sh.CreateRow(6).CreateCell(1); a6.CellFormula = ("SUBTOTAL(2,B2:B6)*2 + 2"); ICell a7 = sh.CreateRow(7).CreateCell(1); a7.CellFormula = ("SUBTOTAL(2,B2:B7)"); fe.EvaluateAll(); Assert.AreEqual(2.0, a3.NumericCellValue); Assert.AreEqual(6.0, a6.NumericCellValue); Assert.AreEqual(2.0, a7.NumericCellValue); }
/// <summary> /// 导出xls结尾的Excel数据,每个实体必须含有ExcelColNameAttr /// </summary> /// <param name="dataList">集合类型数据,分多个工作簿</param> /// <param name="outputStream">输出流</param> /// <param name="groupName">组名称</param> public static void ListToExcelXls <T>(List <T>[] dataListArrays, Stream outputStream, string groupName = null) { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); ICellStyle dateCellStyle = hssfWorkbook.CreateCellStyle(); ICreationHelper creationHelper = hssfWorkbook.GetCreationHelper(); dateCellStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat("yyyy-MM-dd hh:mm:ss"); foreach (List <T> dataList in dataListArrays) { if (dataList == null || dataList.Count == 0) { throw new Exception("数据列表为空,未能导出数据!"); } List <ExcelColInfo> excelColInfoList = ColFilter(typeof(T), groupName); if (excelColInfoList.Count == 0) { throw new Exception("字段列表为空,不能导出数据!"); } HSSFSheet hssfSheet = (HSSFSheet)hssfWorkbook.CreateSheet(GetSheetName(typeof(T), groupName)); HSSFRow hssfRow = (HSSFRow)hssfSheet.CreateRow(0); ExcelCol excelCol; int[] colWidths = new int[excelColInfoList.Count]; int width; foreach (ExcelColInfo excelColInfo in excelColInfoList) { excelCol = excelColInfo.ExcelCol; width = SetCellValue(hssfRow.CreateCell(excelCol.ColIndex), excelCol.ColName); if (width > colWidths[excelCol.ColIndex]) { colWidths[excelCol.ColIndex] = width; } } T temp; for (int i = 0, len = dataList.Count; i < len; i++) { temp = dataList[i]; hssfRow = (HSSFRow)hssfSheet.CreateRow(i + 1); foreach (ExcelColInfo excelColInfo in excelColInfoList) { excelCol = excelColInfo.ExcelCol; width = SetCellValue( (HSSFCell)hssfRow.CreateCell(excelCol.ColIndex), excelColInfo.PropertyInfo.GetValue(temp), dateCellStyle ); if (width > colWidths[excelCol.ColIndex]) { colWidths[excelCol.ColIndex] = width; } } } UpdateColWidth(hssfSheet, colWidths); } hssfWorkbook.Write(outputStream); }
public void SetUp() { wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRandBetween.xls"); Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sheet = wb.CreateSheet("RandBetweenSheet"); IRow row = sheet.CreateRow(0); bottomValueCell = row.CreateCell(0); topValueCell = row.CreateCell(1); formulaCell = row.CreateCell(2, CellType.Formula); }
public void TestCallFunction_invalidArgs() { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("test"); IRow row1 = sheet.CreateRow(0); ICell cellA1 = row1.CreateCell(0, CellType.Formula); cellA1.CellFormula = (/*setter*/ "COUNTIFS()"); IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); CellValue Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(15, Evaluate.ErrorValue); cellA1.CellFormula = (/*setter*/ "COUNTIFS(A1:C1)"); Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(15, Evaluate.ErrorValue); cellA1.SetCellFormula("COUNTIFS(A1:C1,2,2)"); Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(15, Evaluate.ErrorValue); }
public void TestEvaluate() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet(); IRow row1 = sh.CreateRow(0); IRow row2 = sh.CreateRow(1); // Create cells row1.CreateCell(0, CellType.Numeric); row1.CreateCell(1, CellType.Numeric); row1.CreateCell(2, CellType.Numeric); row2.CreateCell(0, CellType.Numeric); row2.CreateCell(1, CellType.Numeric); // Create references CellReference a1 = new CellReference("A1"); CellReference a2 = new CellReference("A2"); CellReference b1 = new CellReference("B1"); CellReference b2 = new CellReference("B2"); CellReference c1 = new CellReference("C1"); // Set values sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(210); sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(55); sh.GetRow(b1.Row).GetCell(b1.Col).SetCellValue(35); sh.GetRow(b2.Row).GetCell(b2.Col).SetCellValue(0); sh.GetRow(c1.Row).GetCell(c1.Col).SetCellFormula("A1/B2"); ICell cell1 = sh.CreateRow(3).CreateCell(0); cell1.SetCellFormula("IFERROR(A1/B1,\"Error in calculation\")"); ICell cell2 = sh.CreateRow(3).CreateCell(0); cell2.SetCellFormula("IFERROR(A2/B2,\"Error in calculation\")"); ICell cell3 = sh.CreateRow(3).CreateCell(0); cell3.SetCellFormula("IFERROR(C1,\"error\")"); double accuracy = 1E-9; IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Assert.AreEqual(CellType.Numeric, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric"); Assert.AreEqual(6.0, evaluator.Evaluate(cell1).NumberValue, accuracy, "Divides 210 by 35 and returns 6.0"); Assert.AreEqual(CellType.String, evaluator.Evaluate(cell2).CellType, "Checks that the cell is numeric"); Assert.AreEqual("Error in calculation", evaluator.Evaluate(cell2).StringValue, "Rounds -10 to a nearest multiple of -3 (-9)"); Assert.AreEqual(CellType.String, evaluator.Evaluate(cell3).CellType, "Check that C1 returns string"); Assert.AreEqual("error", evaluator.Evaluate(cell3).StringValue, "Check that C1 returns string \"error\""); }
}//解压结束 /// <summary> /// 初始化导出表格样式 /// </summary> /// LZ Add 2016-08-04 /// <param name="book">Excel文件</param> /// <param name="param"></param> /// <param name="paramComment">批注字典 注意:字典的key要与表头key一致</param> /// <returns></returns> public static ISheet CreateSheet(HSSFWorkbook book, Dictionary <string, int> param, Dictionary <string, string> paramComment, string sheetName = "Sheet1") { //添加一个sheet ISheet sheet1 = book.CreateSheet(sheetName); IRow row = sheet1.CreateRow(0); //初始化样式 ICellStyle mStyle = book.CreateCellStyle(); mStyle.Alignment = HorizontalAlignment.Center; mStyle.VerticalAlignment = VerticalAlignment.Center; IFont mfont = book.CreateFont(); mfont.FontHeight = 10 * 20; mStyle.SetFont(mfont); HSSFPatriarch patr = sheet1.CreateDrawingPatriarch() as HSSFPatriarch; NPOI.SS.UserModel.ICreationHelper facktory = book.GetCreationHelper(); HSSFComment comment = null; NPOI.SS.UserModel.IClientAnchor anchor = facktory.CreateClientAnchor(); int i = 0; foreach (var item in param) { //设置列宽 sheet1.SetColumnWidth(i, item.Value * 256); sheet1.SetDefaultColumnStyle(i, mStyle); row.CreateCell(i).SetCellValue(item.Key.ToString()); if (paramComment.ContainsKey(item.Key.ToString())) { //设置批注 anchor = facktory.CreateClientAnchor(); anchor.Col1 = row.GetCell(i).ColumnIndex; anchor.Col2 = row.GetCell(i).ColumnIndex + 1; anchor.Row1 = row.RowNum; anchor.Row2 = row.RowNum + 3; comment = patr.CreateCellComment(anchor) as HSSFComment; comment.String = new HSSFRichTextString(paramComment[item.Key.ToString()].ToString()); comment.Author = ("CySoft"); row.GetCell(i).CellComment = (comment); } i++; } i = 0; sheet1.GetRow(0).Height = 28 * 20; return(sheet1); }
public void TestOffsetWithEmpty23Arguments() { IWorkbook workbook = new HSSFWorkbook(); ICell cell = workbook.CreateSheet().CreateRow(0).CreateCell(0); cell.SetCellFormula("OFFSET(B1,,)"); string value = "EXPECTED_VALUE"; ICell valueCell = cell.Row.CreateCell(1); valueCell.SetCellValue(value); workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); Assert.AreEqual(CellType.String, cell.CachedFormulaResultType); Assert.AreEqual(value, cell.StringCellValue); }
public void Test49658() { // test if inserted EscherMetafileBlip will be read again IWorkbook wb = new HSSFWorkbook(); byte[] pictureDataEmf = POIDataSamples.GetDocumentInstance().ReadFile("vector_image.emf"); int indexEmf = wb.AddPicture(pictureDataEmf, PictureType.EMF); byte[] pictureDataPng = POIDataSamples.GetSpreadSheetInstance().ReadFile("logoKarmokar4.png"); int indexPng = wb.AddPicture(pictureDataPng, PictureType.PNG); byte[] pictureDataWmf = POIDataSamples.GetSlideShowInstance().ReadFile("santa.wmf"); int indexWmf = wb.AddPicture(pictureDataWmf, PictureType.WMF); ISheet sheet = wb.CreateSheet(); HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; ICreationHelper ch = wb.GetCreationHelper(); IClientAnchor anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 2); anchor.Col2 = (/*setter*/ 5); anchor.Row1 = (/*setter*/ 1); anchor.Row2 = (/*setter*/ 6); patriarch.CreatePicture(anchor, indexEmf); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 2); anchor.Col2 = (/*setter*/ 5); anchor.Row1 = (/*setter*/ 10); anchor.Row2 = (/*setter*/ 16); patriarch.CreatePicture(anchor, indexPng); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 6); anchor.Col2 = (/*setter*/ 9); anchor.Row1 = (/*setter*/ 1); anchor.Row2 = (/*setter*/ 6); patriarch.CreatePicture(anchor, indexWmf); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); byte[] pictureDataOut = (wb.GetAllPictures()[0] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(pictureDataEmf, pictureDataOut)); byte[] wmfNoHeader = new byte[pictureDataWmf.Length - 22]; Array.Copy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.Length - 22); pictureDataOut = (wb.GetAllPictures()[2] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(wmfNoHeader, pictureDataOut)); }
public void TestCountifBug51498() { int REF_COL = 4; int EVAL_COL = 3; HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("51498.xls"); IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); ISheet sheet = workbook.GetSheetAt(0); // numeric criteria for (int i = 0; i < 8; i++) { CellValue expected = evaluator.Evaluate(sheet.GetRow(i).GetCell(REF_COL)); CellValue actual = evaluator.Evaluate(sheet.GetRow(i).GetCell(EVAL_COL)); Assert.AreEqual(expected.FormatAsString(), actual.FormatAsString()); } // boolean criteria for (int i = 0; i < 8; i++) { HSSFCell cellFmla = (HSSFCell)sheet.GetRow(i).GetCell(8); HSSFCell cellRef = (HSSFCell)sheet.GetRow(i).GetCell(9); double expectedValue = cellRef.NumericCellValue; double actualValue = evaluator.Evaluate(cellFmla).NumberValue; Assert.AreEqual(expectedValue, actualValue, 0.0001, "Problem with a formula at " + new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] "); } // string criteria for (int i = 1; i < 9; i++) { ICell cellFmla = sheet.GetRow(i).GetCell(13); ICell cellRef = sheet.GetRow(i).GetCell(14); double expectedValue = cellRef.NumericCellValue; double actualValue = evaluator.Evaluate(cellFmla).NumberValue; Assert.AreEqual(expectedValue, actualValue, 0.0001, "Problem with a formula at " + new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] "); } }
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 TestMissingWorkbookMissing() { IFormulaEvaluator evaluator = mainWorkbook.GetCreationHelper().CreateFormulaEvaluator(); HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0); HSSFRow lARow = (HSSFRow)lSheet.GetRow(0); HSSFCell lA1Cell = (HSSFCell)lARow.GetCell(0); Assert.AreEqual(CellType.Formula, lA1Cell.CellType); try { evaluator.EvaluateFormulaCell(lA1Cell); Assert.Fail("Missing external workbook reference exception expected!"); } catch (RuntimeException re) { Assert.IsTrue(re.Message.IndexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1, "Unexpected exception: " + re); } }
public void TestPlainValueCache() { HSSFWorkbook wb = new HSSFWorkbook(); int numberOfSheets = 4098; // Bug 51448 reported that Evaluation Cache got messed up After 256 sheets IRow row; ICell cell; //create summary sheet ISheet summary = wb.CreateSheet("summary"); wb.SetActiveSheet(wb.GetSheetIndex(summary)); //formula referring all sheets Created below row = summary.CreateRow(0); ICell summaryCell = row.CreateCell(0); summaryCell.CellFormula = "SUM(A2:A" + (numberOfSheets + 2) + ")"; //create sheets with cells having (different) numbers // and add a row to summary for (int i = 1; i < numberOfSheets; i++) { ISheet sheet = wb.CreateSheet("new" + i); row = sheet.CreateRow(0); cell = row.CreateCell(0); cell.SetCellValue(i); row = summary.CreateRow(i); cell = row.CreateCell(0); cell.CellFormula = "new" + i + "!A1"; } //calculate IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Evaluator.EvaluateFormulaCell(summaryCell); Assert.AreEqual(8394753.0, summaryCell.NumericCellValue); }
//private static DataTable InitReimbursementSlipCoverDetail() //{ // return dtVal; //} public static byte[] CreateVoucher(DataTable dt, string path, ref string msg) { msg = ""; byte[] data = null; using (FileStream fs = new FileStream(path, FileMode.Open)) { HSSFWorkbook wbHssf = new HSSFWorkbook(fs); ISheet sheet = wbHssf.GetSheetAt(0); sheet.ForceFormulaRecalculation = true;// int index = 1; foreach (DataRow r in dt.Rows) { IRow row = sheet.CreateRow(index); foreach (DataColumn c in dt.Columns) { if (string.IsNullOrEmpty(r[c.ColumnName].ToString())) { continue; } int cIndex = Convert.ToInt32(c.ColumnName) - 1; ICell cell = row.CreateCell(cIndex); cell.SetCellValue(r[c.ColumnName].ToString()); } index++; } //填完数据后,统一更新公式计算 wbHssf.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); using (MemoryStream ms = new MemoryStream()) { wbHssf.Write(ms); ms.Flush(); ms.Position = 0; data = ms.GetBuffer(); } wbHssf = null; sheet = null; } return(data); }
public void Test50209() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(1).CreateCell(1); a1.SetCellValue(1); ICell a2 = sh.CreateRow(2).CreateCell(1); a2.CellFormula = ("SUBTOTAL(9,B2)"); ICell a3 = sh.CreateRow(3).CreateCell(1); a3.CellFormula = ("SUBTOTAL(9,B2:B3)"); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); fe.EvaluateAll(); Assert.AreEqual(1.0, a2.NumericCellValue); Assert.AreEqual(1.0, a3.NumericCellValue); }
public void TestCallFunction() { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("test"); IRow row1 = sheet.CreateRow(0); ICell cellA1 = row1.CreateCell(0, CellType.Formula); ICell cellB1 = row1.CreateCell(1, CellType.Numeric); ICell cellC1 = row1.CreateCell(2, CellType.Numeric); ICell cellD1 = row1.CreateCell(3, CellType.Numeric); ICell cellE1 = row1.CreateCell(4, CellType.Numeric); cellB1.SetCellValue(1); cellC1.SetCellValue(1); cellD1.SetCellValue(2); cellE1.SetCellValue(4); cellA1.SetCellFormula("COUNTIFS(B1:C1,1, D1:E1,2)"); IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); CellValue Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(1.0d, Evaluate.NumberValue); }
private static void TestPlainValueCache(HSSFWorkbook wb, int numberOfSheets) { IRow row; ICell cell; //create summary sheet ISheet summary = wb.CreateSheet("summary"); wb.SetActiveSheet(wb.GetSheetIndex(summary)); //formula referring all sheets Created below row = summary.CreateRow(0); ICell summaryCell = row.CreateCell(0); summaryCell.CellFormula = ("SUM(A2:A" + (numberOfSheets + 2) + ")"); //create sheets with cells having (different) numbers // and add a row to summary for (int i = 1; i < numberOfSheets; i++) { ISheet sheet = wb.CreateSheet("new" + i); row = sheet.CreateRow(0); cell = row.CreateCell(0); cell.SetCellValue(i); row = summary.CreateRow(i); cell = row.CreateCell(0); cell.CellFormula = ("new" + i + "!A1"); } //calculate IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Evaluator.EvaluateFormulaCell(summaryCell); }
public void TestRefToBlankCellInArrayFormula() { IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cellA1 = row.CreateCell(0); ICell cellB1 = row.CreateCell(1); ICell cellC1 = row.CreateCell(2); IRow row2 = sheet.CreateRow(1); ICell cellA2 = row2.CreateCell(0); ICell cellB2 = row2.CreateCell(1); ICell cellC2 = row2.CreateCell(2); IRow row3 = sheet.CreateRow(2); ICell cellA3 = row3.CreateCell(0); ICell cellB3 = row3.CreateCell(1); ICell cellC3 = row3.CreateCell(2); cellA1.SetCellValue("1"); // cell B1 intentionally left blank cellC1.SetCellValue("3"); cellA2.SetCellFormula("A1"); cellB2.SetCellFormula("B1"); cellC2.SetCellFormula("C1"); sheet.SetArrayFormula("A1:C1", CellRangeAddress.ValueOf("A3:C3")); wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); Assert.AreEqual(cellA2.StringCellValue, "1"); Assert.AreEqual(cellB2.NumericCellValue, 0, 0.00001); Assert.AreEqual(cellC2.StringCellValue, "3"); Assert.AreEqual(cellA3.StringCellValue, "1"); Assert.AreEqual(cellB3.NumericCellValue, 0, 0.00001); Assert.AreEqual(cellC3.StringCellValue, "3"); }
public void TestEvaluate() { TestCases.CultureShim.SetCurrentCulture("en-US"); HSSFWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet(); ICell cell1 = sh.CreateRow(0).CreateCell(0); cell1.CellFormula = ("MROUND(10, 3)"); ICell cell2 = sh.CreateRow(0).CreateCell(0); cell2.CellFormula = ("MROUND(-10, -3)"); ICell cell3 = sh.CreateRow(0).CreateCell(0); cell3.CellFormula = ("MROUND(1.3, 0.2)"); ICell cell4 = sh.CreateRow(0).CreateCell(0); cell4.CellFormula = ("MROUND(5, -2)"); ICell cell5 = sh.CreateRow(0).CreateCell(0); cell5.CellFormula = ("MROUND(5, 0)"); double accuracy = 1E-9; IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)"); Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)"); Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)"); Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)"); Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0"); }
public static void createExcelFile(string folderPath, string fileName) { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("219Option"); HSSFRow row = (HSSFRow)sheet.CreateRow(0); HSSFCreationHelper createHelper = (HSSFCreationHelper)wb.GetCreationHelper(); row.CreateCell(0).SetCellValue(createHelper.CreateRichTextString("Option Name")); row.CreateCell(1).SetCellValue(createHelper.CreateRichTextString("Input Value")); row.CreateCell(2).SetCellValue(createHelper.CreateRichTextString("Output Value")); using (FileStream file = new FileStream(folderPath + "\\" + fileName, FileMode.Create, FileAccess.Write)) { wb.Write(file); file.Close(); } } catch (Exception ex) { Console.WriteLine("Occur error createExcelFile function : " + ex.ToString()); } }
public void TestMissingWorkbookMissingOverride() { mainWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(MAIN_WORKBOOK_FILENAME); HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0); HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0); HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0); HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0); Assert.AreEqual(CellType.Formula, lA1Cell.CellType); Assert.AreEqual(CellType.Formula, lB1Cell.CellType); Assert.AreEqual(CellType.Formula, lC1Cell.CellType); HSSFFormulaEvaluator evaluator = (HSSFFormulaEvaluator)mainWorkbook.GetCreationHelper().CreateFormulaEvaluator(); evaluator.IgnoreMissingWorkbooks = (true); Assert.AreEqual(CellType.Numeric, evaluator.EvaluateFormulaCell(lA1Cell)); Assert.AreEqual(CellType.String, evaluator.EvaluateFormulaCell(lB1Cell)); Assert.AreEqual(CellType.Boolean, evaluator.EvaluateFormulaCell(lC1Cell)); Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d); Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue); Assert.AreEqual(true, lC1Cell.BooleanCellValue); }
public void TestAddPictures() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet("Pictures"); IDrawing dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(0, ((HSSFPatriarch)dr).Children.Count); IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor(); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); Assert.AreEqual(1, idx1); IPicture p1 = dr.CreatePicture(anchor, idx1); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)p1).PictureData.Data)); // register another one byte[] data2 = new byte[] { 4, 5, 6 }; int idx2 = wb.AddPicture(data2, PictureType.JPEG); Assert.AreEqual(2, idx2); IPicture p2 = dr.CreatePicture(anchor, idx2); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)p2).PictureData.Data)); // confirm that HSSFPatriarch.Children returns two picture shapes Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // Write, read back and verify that our pictures are there wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst2 = wb.GetAllPictures(); Assert.AreEqual(2, lst2.Count); Assert.IsTrue(Arrays.Equals(data1, (lst2[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst2[(1)] as HSSFPictureData).Data)); // confirm that the pictures are in the Sheet's Drawing sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // add a third picture byte[] data3 = new byte[] { 7, 8, 9 }; // picture index must increment across Write-read int idx3 = wb.AddPicture(data3, PictureType.JPEG); Assert.AreEqual(3, idx3); IPicture p3 = dr.CreatePicture(anchor, idx3); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)p3).PictureData.Data)); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); // write and read again wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst3 = wb.GetAllPictures(); // all three should be there Assert.AreEqual(3, lst3.Count); Assert.IsTrue(Arrays.Equals(data1, (lst3[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst3[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst3[(2)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); // forth picture byte[] data4 = new byte[] { 10, 11, 12 }; int idx4 = wb.AddPicture(data4, PictureType.JPEG); Assert.AreEqual(4, idx4); dr.CreatePicture(anchor, idx4); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst4 = wb.GetAllPictures(); Assert.AreEqual(4, lst4.Count); Assert.IsTrue(Arrays.Equals(data1, (lst4[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst4[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst4[(2)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data4, (lst4[(3)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); }