Do you have an expected date on the addition of the new functions to the formula engine? I have attached a s/s that is commonly used in excel for loan amortizations. The functions I use in this s/s are Sum, Round, VLookup,IF,CountIF,IRR, PMT, Month,Day,Date and Year.
Our ideal scenario is to open this s/s in code on the asp.net server, input the cells, calculate the sheet, and then grab some of the calculated fields without ever showing the s/s to the end user (but saving a copy).
However this doesn't work, I'm assuming we are getting a null reference due to the limited support of the functions when we try this with the evaluation version. We are just testing a simple snippet of code. It errors on the Calculate Formula. Without that line is opens and saves fine.
Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim ss As New Excel()
ss.Open(Server.MapPath("apr.xls"))
Dim LoanCell As Cell = ss.Worksheets(0).Cells("C3")
LoanCell.PutValue(20000)
ss.CalculateFormula()
Response.Write(ss.Worksheets(0).Cells("C3").Value)
ss.Save(Server.MapPath("apr.xls"), FileFormatType.Default)
End Sub
Currently in your requested functions, only Sum, Round and IF are supported. I will implement them in the future release.
I think VLookup, IRR and PMT are more complex than others.
@RobinMarks,
Aspose.Excel is not available now and is discarded. A new product Aspose.Cells is introduced that supports all the latest features in different versions of MS Excel and contains all the features of its predecessor. All the functions are now supported by Aspose.Cells including VLookup, CountIF, IRR, PMT, Month, Day, Date and Year. Here is an example that demonstrates working with VLookup.
// Create new workbook
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Add data for VLOOKUP
worksheet.Cells["A1"].PutValue("Apple");
worksheet.Cells["A2"].PutValue("Orange");
worksheet.Cells["A3"].PutValue("Banana");
// Access cell A5 and A6
Cell cellA5 = worksheet.Cells["A5"];
Cell cellA6 = worksheet.Cells["A6"];
// Assign IFNA formula to A5 and A6
cellA5.Formula = "=IFNA(VLOOKUP(\"Pear\",$A$1:$A$3,1,0),\"Not found\")";
cellA6.Formula = "=IFNA(VLOOKUP(\"Orange\",$A$1:$A$3,1,0),\"Not found\")";
// Caclulate the formula of workbook
workbook.CalculateFormula();
// Print the values of A5 and A6
Console.WriteLine(cellA5.StringValue);
Console.WriteLine(cellA6.StringValue);
workbook.Save("OutputVLookup.xlsx");
For a complete list of supported formulas, refer to the link iven below:
Supported Formula Functions
Formulas
Working with Caclulation Engine
The latest trial version of this product can be downloaded here:
Aspose.Cells for .NET (Latest Version)
Here is a comprehensive runnable solution that can be used to test the product features.