private static void OutPurchaseSum(IXLWorksheet sheet, ref int currentRow, int rowDataBegin) { var cell = sheet.Cell(currentRow++, 5); cell.Value = sheet.Evaluate($"=SUM($E{rowDataBegin}:$E{currentRow - 2})"); cell.Style.Fill.BackgroundColor = XLColor.FromColor(Color.LightPink); }
private static void OutGood(IXLWorksheet sheet, PurchaseItem item, int row) { int column = 1; sheet.Cell(row, column++).Value = item.Type.ToString(); sheet.Cell(row, column++).Value = item.Name; sheet.Cell(row, column++).Value = item.Price; sheet.Cell(row, column++).Value = item.Amount; sheet.Cell(row, column++).Value = sheet.Evaluate($"=$C{row}*$D{row}"); }
public void Workdays_MultipleHolidaysGiven() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Date") .CellBelow().SetValue(new DateTime(2008, 10, 1)) .CellBelow().SetValue(151) .CellBelow().SetValue(new DateTime(2008, 11, 26)) .CellBelow().SetValue(new DateTime(2008, 12, 4)) .CellBelow().SetValue(new DateTime(2009, 1, 21)); Object actual = ws.Evaluate("Workday(A2,A3,A4:A6)"); Assert.AreEqual(new DateTime(2009, 5, 5), actual); }
public void SeriesSum() { object actual = XLWorkbook.EvaluateExpr("SERIESSUM(2,3,4,5)"); Assert.AreEqual(40.0, actual); var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.Cell("A2").FormulaA1 = "PI()/4"; ws.Cell("A3").Value = 1; ws.Cell("A4").FormulaA1 = "-1/FACT(2)"; ws.Cell("A5").FormulaA1 = "1/FACT(4)"; ws.Cell("A6").FormulaA1 = "-1/FACT(6)"; actual = ws.Evaluate("SERIESSUM(A2,0,2,A3:A6)"); Assert.IsTrue(Math.Abs(0.70710321482284566 - (double)actual) < XLHelper.Epsilon); }
public Double EvaluateFormula(string formula) { var sum = ExcelSayfa.Evaluate(formula); return(Convert.ToDouble(sum)); }