Hi. We have aspose license for Aspose Cells product for C#.
We have a problem. We have a excel template with various worksheets with a lot of formulas. We execute with C# api next code :
var path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
var workBookTemplate = new Workbook(Path.Combine(path, templatePath, templateName));
var tableOptions = new ImportTableOptions
{
IsFieldNameShown = false,
InsertRows = false
};
foreach (var inputLine in inputData.DataSets)
{
var sheetInput = workBookTemplate.Worksheets[inputLine.SheetName];
if (sheetInput == null)
continue;
sheetInput.Cells.ImportCustomObjects(inputLine.Data, 1, 0, tableOptions);
}
workBookTemplate.CalculateFormula();
After the execution of CalculateFormula we inspect the result of cells that will be calculated but not are calculated. We notice that the formulas are calculated when we open the excel generated but we don´t want this, we want all formulas will be calculated before user open excel file.
¿Could you help me please?. Is a very urgent problem for us. I cant attach the template because I dont see any option to do this. If it would be necessary, please tell me.
Thank you very much
@davidfalck,
Thanks for the sample code segment and details.
Do you need to get calculated results (against the formulas in the cells) at runtime? Well, you need to get/read the cells’ values (again) after you call Workbook.CalculatedFormula() method. See the document with simple example for your reference:
In short, you will always need to get those formula cell to check their (updated) values or export to other formats, e.g PDF or image.
Let us know if we understand you correctly or you need something else, please elaborate.
I have to read/get the cells values affected to the formulas?. There are a lot of formulas, 200 perhaps. I tested export to pdf and not works, appears all the cells without calculated formulas executed. I dont have necessity of read values of formulas after calculation, is not possible other possibility?. Thank you very much
@davidfalck,
I am not entirely sure about your issue. Which file format you are saving your file after calculating formulas, PDF? Could you provide your template (input) Excel file and output file and some screenshots to highlight the issue, we will check it soon. Moreover, since you are importing from some data source/datatable, so kindly create a simple console demo application (runnable), zip the project and post us, we will check it soon. Also kindly remove any inter-dependencies to external database/data source and use dynamic dataset/datatable in code, so we could execute your application seamlessly to evaluate your issue.
I save in excel format, aand tested pdf alsobut not works. How I can attach file here?. I can attach the template with formulas but I can´t do in this chat, I cant see the option to do this.
@davidfalck,
When you click on Reply you may upload your attachment using Upload button in the window, see the screenshot for your reference.
sc_shot1.png (13.0 KB)
I attach the input template and the output template. The error in the formula cells is that shown #NAInput.zip (302.0 KB)
Output.zip (1.4 MB)
ME?
I think I found the problem. The excel FILTER formula is not supported in aspose cells?. All the cells that contains this formula show #NAME after this was calculated. In excel documentation says that this error is because of an error in a formula. Can you confirm if FILTER formula is not supported?
@davidfalck,
Thanks for the template file and details.
After an initial test, I am able to reproduce the issue as you mentioned by using the simplest lines of code with your template file. It looks like FILTER function is not calculated although Aspose.Cells does support the formula (Supported Excel Functions|Documentation):
e.g
Sample code:
Workbook wb = new Workbook("E:\\test2\\ReportTemplateSpain1.xlsx");
wb.CalculateFormula();
wb.Save("E:\\test2\\out1.pdf");
I have logged a ticket with an id “CELLSNET-47267” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
How much time do you think FILTER function will be integrated?. I hope your reply as soon as possible, we need FILTER function for our purposes. Thank you very much
@davidfalck,
Please spare us little time (3-5 days or so) to analyse your issue in details first. We will try to figure your issue soon.
Once we have any new information available, we will update you.
@davidfalck,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
Thank you very much, Please tell me when the version be available to download.
@davidfalck,
You may expect the fixed version within the next 2-3 days or so.
@davidfalck,
Please try our latest version/fix: Aspose.Cells for .NET v20.3.6 (attached)
Your issue should be fixed in it.
Let us know your feedback.
Aspose.Cells20.3.6 For .Net2_AuthenticodeSigned.Zip (5.3 MB)
Aspose.Cells20.3.6 For .Net4.0.Zip (5.3 MB)
Hi. We work with net core. This version of dll are not compatible. Do you test with net core framework?. I try to add twice dll files but visual studio dont recognize the Aspose.Cells namespace. Could you help me please? Thank you very much
@davidfalck,
Yes, we provided the fix for common .NET framework versions. Could you try it in common .NET framework version in a separate project if it works fine. For .NET core compiled fix, we will soon provide you the supported version.
It works in .NET Framework. Please, I need in NET Core version. Please, tell me when this version will be available. Thank you very much
@davidfalck,
Sure, we are working on this request and will share the required version as soon as it is ready to share.
@davidfalck,
Please try our latest version/fix: Aspose.Cells for .NET v20.3.9 (attached)
Your issue should be fixed in it.
Let us know your feedback.
Aspose.Cells20.3.9 For .NetStandard20.Zip (5.3 MB)