Formulas Not Refreshing - .xlsm file - Aspose version 5.3.3

Aspose.Cells Product Family
1

Hi!

We just upgraded to Aspose version 5.3.3 from 4.9.1. Most of our reports use .xlsm templates. The templates contain Aspose tags, which are populated from stored procedures. The data is coming through fine. However, if we have a formula that links to one of the data cells, then it is not refreshing. If I use Application.CalculateFull (CTRL+ATL+F9), then the formulas refresh. However, they will not refresh automatically when the workbook is launched. By the way, we do have formulas set in Excel to Automatically update. Manual is not checked.

I have attached a sample output report with the issue. Note: The EventLog worksheet was populated from Aspose. The Test worksheet contains the formulas that are not refreshing.

Here is some sample code from one of our reports:

xls.SetDataSource("EventLog", dtEventLog.DefaultView)
xls.SetDataSource("Detail", dtDetail.DefaultView)
xls.Workbook.SaveOptions.SaveFormat = Aspose.Cells.SaveFormat.Xlsm
xls.Workbook.Settings.ReCalculateOnOpen = True
xls.Workbook.Settings.CalcMode = Aspose.Cells.CalcModeType.Automatic
xls.Process(True)

Dim saveOptions As New Aspose.Cells.OoxmlSaveOptions

saveOptions.SaveFormat = Aspose.Cells.SaveFormat.Xlsm

xls.Workbook.Save(HttpContext.Current.Response, "AffordablePortfolioSummary.xlsm", Aspose.Cells.ContentDisposition.Attachment, saveOptions)

Response.End()

We are having to add a macro to all of our .xlsm reports to work around this issue. However, we would prefer to have this handled in our Aspose code, if possible. This issue did not occur prior to the upgrade.

Thanks,

Melanie Gruschow

2

Hi,

I have tested your file with the latest Aspose.Cells for .NET v5.3.3.0 and I saw no problem. Formulas are refreshing fine.

Please see the output file generated by the following code.

C#


string filePath = @“F:\Downloads\Test-+Aspose+Issue.xlsm”;


Workbook workbook = new Workbook(filePath);


workbook.Save(filePath + “.out.xlsm”, SaveFormat.Xlsm);

3

Hi! Thanks for your quick response.

Actually, I had attached the output report to my prior post instead of the original template. The output report did not contain any Aspose tags. The problem occurs when data is imported from our database into the Aspose tags within the template. I have attached the template to this post. The data comes in fine on the EventLog worksheet, but the formulas on the Test worksheet do not refresh when the report is opened.

If you do get this to work, would you please provide us the code in VB.Net? Also, we need to launch the report using the HTTP Response object. You can see our current code in my post above.

Thanks,

Melanie Gruschow

4

Hi Malanie,


Thanks for your file [Aspose test template.xlsm], but it’s worksheets are protected. I am unable to load data in them to test the formula refresh problem.
Your previous file [Test-Aspose Issue.xlsm] which already has data does not exhibit any issue with refreshing of formula using latest version of Aspose.Cells for .NET v5.3.3.1.

As you have said, this issue was not present in v4.9.1 and appeared in v5.3.3. So we can investigate this. Will you be kind enough to provide us a sample application with your version [4.9.1] that works fine. We will compare the results with latest version.

Thank you for understanding.
5

Hi,

I have tested your file [Aspose Test Template.xlsm] with the latest Aspose.Cells for .NET (Latest Version) .

I populated the smart marker tags with the sample data and then opened the output file and observed the second sheet. All the formulas refreshed fine. I have attached the output file.

For further elaboration, please see the screenshot.

C#


string filePath = @“F:\Downloads\Aspose+Test+Template.xlsm”;


WorkbookDesigner designer = new WorkbookDesigner();

designer.Workbook = new Workbook(filePath);


DataTable dt = new DataTable(“EventLog”);

dt.Columns.Add(new DataColumn(“Project Name”, typeof(string)));


DataRow dr;


for (int i = 0; i < 5; i++)

{

dr = dt.NewRow();

dr[“Project Name”] = “abc”;


dt.Rows.Add(dr);

}


designer.SetDataSource(dt);

designer.Process(true);

designer.Workbook.Save(filePath + “.out.xlsm”, SaveFormat.Xlsm);


Screenshot:

6

Hi,


Below is VB.NET version of same code snippet as shared by my colleague.

Sample Code

Dim filePath As String = “F:\Downloads\Aspose+Test+Template.xlsm” 
    Dim designer As New Aspose.Cells.WorkbookDesigner()
    designer.Workbook = New Aspose.Cells.Workbook(filePath)

    Dim dt As New DataTable("EventLog")
    dt.Columns.Add(New DataColumn("Project Name", GetType(String)))
    Dim dr As DataRow

    For i As Integer = 0 To 4
        dr = dt.NewRow()
        dr("Project Name") = "abc"
        dt.Rows.Add(dr)
    Next i

    designer.SetDataSource(dt)
    designer.Process(True)
    designer.Workbook.Save(filePath & ".out.xlsm", Aspose.Cells.SaveFormat.Xlsm)

//In web environment through HTTP response
//designer.Workbook.Save(HttpContext.Current.Response, "out.xlsm", Aspose.Cells.ContentDisposition.Attachment, SaveOptions)
7

Hi,

We have fixed this issue. Please download Aspose.Cells for .NET (Latest Version)

8

Thanks for identifying the issue and providing us with a fix! I appreciate your help with this.

Thanks,

Melanie Gruschow

9

Hi,

You might want to try our latest official version Aspose.Cells for .NET (Latest Version)

Note: your issue is also addressed in the release.

Thank you.