Fill Worksheet with data and refresh formulas

[Tomasz Jagusz]

I'm trying to create a simple reporting solution.

The idea is to create an excel file that will be used as a template.

The first worksheet will be used to create the visualization part and the second (called Data) as a data source.

This is the code I'm using to fill the second worksheet:

public byte[] Fill(byte[] template, DataTable data)

{

    using (var excelEngine = new ExcelEngine())

    {

        using (var templateStream = new MemoryStream(template))

        {

            var application = excelEngine.Excel;

            var workbook = application.Workbooks.Open(templateStream);

            var dataWorksheet = workbook.Worksheets[1];

            dataWorksheet.ImportDataTable(data, false, 1, 1);

            using (var filledTemplate = new MemoryStream())

            {

                workbook.SaveAs(filledTemplate);

                return filledTemplate.ToArray();

            }

        }

    }

}

In the first worksheet, I have a simple formula "=Data!A1" and it shows "0" when A1 in "Data" is empty and this is ok but when I import data into that cell it still shows "0".

The cell in the first worksheet is updated when I open excel and click on formula's field: https://gyazo.com/997fb60a858be956b7e2b7c8c33a064c

How can I update fields in the first worksheet so I don't have to click on the formula's field?

In the final version, the first worksheet will have formulas, conditional formatting, and charts.

I want to update the second worksheet (fill the data) and the first worksheet should get updated.

I'm attaching a sample file I've created with the above code. As seen on the gif you need to click on the formula field to have that field updated.

Attachment: test_7bbc0d22.zip

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Tomasz, 

Greetings from Syncfusion. 

We are unable to reproduce the reported issue at our end. The sample which we have tried at our end can be downloaded from the following link. 

Sample Link: https://www.syncfusion.com/downloads/support/forum/157035/ze/F157035_Core447645850.zip  

Note: In the above sample, we have opened the shared “test.xlsx” file and tried to modify the value in A1 cell of Data sheet using Syncfusion XlsIO. 

We have also prepared a video screenshot for your reference and the same can be downloaded from the following link. 

Video Screenshot: https://www.syncfusion.com/downloads/support/forum/157035/ze/SCREEN~1-1898736825.zip  

Kindly look into the sample and video screenshot and let us know if it helps. If the issue still persists, we request you to modify and share us the issue reproducing sample which will be helpful for us in investigating the query and provide prompt solution at the earliest. 

Also, please confirm if you are using 18.1.0.42 version of Syncfusion XlsIO at your end. 

Regards, 

Keerthi. 

[Tomasz Jagusz]

Hi Keerthi,

I was able to recreate the issue in your sample.

1. Update the version to 18.2.0.54

2. in home controller replace 

table.Columns.Add();

with

table.Columns.Add("20", typeof(long));

my data comes from database so each column has a name and a type.

3. Put valid license key in CreateDocument function.

The bug now looks like this:

when I remove the license key (run as trial) the excel file is generated correctly - the value in visualization sheet reflects the value in data sheet.

when I add license key the value in the first sheet isn't updating.

I'm attaching your sample with my modifications.

Please run without license key, then with it. You should see the differences :)

Best regards,

Tomasz

Attachment: F157035_Core_bug_93c2657d.zip

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Tomasaz, 

Thanks for the update. 

We have updated the version to 18.2.0.54 and checked both the mentioned scenarios (Without License and With License). We are unable to reproduce the reported issue at our end.  

Please find the video screenshot for the same below. 

Video Screenshot: https://www.syncfusion.com/downloads/support/forum/157035/ze/SCREEN~11076050726.zip  

The Excel files generated at our end can be downloaded from the following link. 

Excel Files: https://www.syncfusion.com/downloads/support/forum/157035/ze/ExcelFiles803257416.zip  

Kindly share us a video screenshot for the mentioned scenarios, which will be helpful for us in investigating the query. 

Regards, 

Keerthi. 

[Tomasz Jagusz]

Hi again,

I was able to find the reason.

On my development machine, I'm using Excel 2013. I think this is the reason.

I've checked on my friend PC with Office 2019 and there is no problem. Could you try code I've previously posted with older excel?

I'm attaching a video showing the problem on my PC.

As you can see without license everything works fine, but with the license, I must enter the formula and click ENTER so Excel evaluates it again.

I think this is a bug because with or without a license file should work the same.

Best regards,

Tomasz

Attachment: 20200821Excel_error_3e31d.zip

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Tomsaz, 

Thanks for sharing the details. 

We are able to reproduce the reported issue in Excel2013 with license and validating it currently. We will share the validation details in two business days, on August 26^th,2020. 

Regards, 

Keerthi. 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Tomasz, 

Thanks for your patience. 

We have confirmed the issue with Formula value is not proper while resaving the Excel document with License and logged a defect report. We will include the fix to resolve this issue in our weekly NuGet release which will be available on September 8^th,2020. You can track the status of defect report through following feedback link. 

Track Status: https://www.syncfusion.com/feedback/17337/formula-value-is-not-proper-while-resaving-the-excel-document-with-license  

We will let you know once the NuGet package is available to download. 

Regards, 

Keerthi. 

[Tomasz Jagusz]

Hi Keerthi,

sadly I don't have access to that feedback item.

When trying to open that link I get access denied page with this error message: This private feedback is not associated with your account.

Best regards,

Tomasz

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Tomsaz, 

We regret for the inconvenience caused. 

We have now modified the visibility of feedback. Kindly try again and let us know if you are able to view it. 

Regards, 

Keerthi. 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Tomsaz, 

We appreciate your patience. 

We have included the fix to resolve the issue Formula value is not proper while resaving the Excel document with License in our weekly NuGet release version v18.2.0.57. You can download the package from the following link. 

https://www.nuget.org/packages/Syncfusion.XlsIO.Net.Core/  

Kindly check and let us know if the reported issue is resolved. 

Regards, 

Keerthi. 

[Tomasz Jagusz]

Hi there,

sorry for the late reply.

The fix is almost ideal.

When I generate an excel file and download it and open it it is opened in a protected view (https://support.microsoft.com/en-us/office/what-is-protected-view-d6f09ac7-e6b9-4495-8e43-2bbcdbcb6653?ui=en-us&rs=en-us&ad=us).

Without clicking on "Enable Editing" I still get old values. After clicking the button the formulas are reevaluated and the value is correct.

Can I recalculate the values before saving the file? This way I'll always get the correct values displayed, even in Protected View.

Best regards,

Tomasz

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Tomasz, 

We are glad that the provided fix helped you. 

If you use the below code after importing datatable, you can see that the value is updated properly. 

Code Snippet: 

visualizationSheet.EnableSheetCalculations(); 

string value = visualizationSheet.Range["A1"].CalculatedValue; 

As mentioned in the shared URL, to help protect your computer, files generated from potentially unsafe locations like internet, are opened as read only or in Protected View.  

Regards, 

Keerthi. 

[Tomasz Jagusz]

Hi Keerthi,

if I read calculatedValue before saving the sheet then the downloaded sheet is updated (with calculated value), but if I only call `visualizationSheet.EnableSheetCalculations();` then my values aren't updated (I still see old values in downloaded sheet)

My idea is to recalculate everything before saving the sheet, so if someone opens that sheet in protected view he will see the correct values.

I can't read calculatedValue, because I won't know what cells will have formulas.

The idea is to create a visual layer in first sheet (with all the formulas, charts and conditional formatting) that will use data from second sheet.

I want to replace data in the "Data" sheet and the Visual sheet should update before saving.

I hope this is more clear now.

Best regards,

Tomasz

[Shamini Kiruba Sobers]

Hi Tomasz, 

  

Thanks for the update. 

  

The given code to get CalculatedValue is just to check whether the values are being updated properly before saving the file using XlsIO. When opening the file, Microsoft Excel protects the values due to security settings as discussed earlier. Enabling sheet calculations and getting the calculated value using XlsIO is not related to Microsoft Excel’s protection settings. The values calculated using XlsIO will be visible only when you get out from the protection by clicking the “Enable Editing” button. 

  

Regards, 

Shamini 

[Tomasz Jagusz]

Hi Shamini,

I understand how Protected View works, but I get weird results.

My code looks like this:

            using (ExcelEngine excelEngine = new ExcelEngine())

{

IApplication application = excelEngine.Excel;

                application.DefaultVersion = ExcelVersion.Excel2016;

                FileStream inputStream = new FileStream("test.xlsx", FileMode.Open, FileAccess.Read);

                IWorkbook workbook = application.Workbooks.Open(inputStream);

IWorksheet visualizationSheet = workbook.Worksheets[0];

                IWorksheet dataSheet = workbook.Worksheets[1];

                DataTable dataTable = SampleDataTable();

                dataSheet.ImportDataTable(dataTable, false, 1, 1);

                visualizationSheet.EnableSheetCalculations();

                //string value = visualizationSheet.Range["A1"].CalculatedValue;

                //Saving the Excel to the MemoryStream 

                MemoryStream stream = new MemoryStream();

workbook.SaveAs(stream);

//Set the position as '0'.

stream.Position = 0;

//Download the Excel file in the browser

FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");

fileStreamResult.FileDownloadName = "Output.xlsx";

return fileStreamResult;

}

I've attached two files, the only difference is that I commented this line in the second file:

string value = visualizationSheet.Range["A1"].CalculatedValue;

see results:

both files are opened in protected view, but the one that was created with an uncommented line shows the calculated value.

I think that when I try to read that calculated value it is somehow updated in the output excel file, so excel don't need to calculate it again when I open the file (it can't recalculate in protected view).

This is my requirement. I want to recalculate all formulas and update the values in the saved file, so when it is sent via email and someone will open it in protected view he will see the correct values.

Sorry for not describing everything correctly, but I hope now everything will be clear.

Best regards,

Tomasz

Attachment: Test_c97aed32.zip

[Shamini Kiruba Sobers]

Hi Tomasz, 

Thanks for the detailed update. 

Yes, invoking  the calculate value after enabling the calculations will update the evaluated values. If you want to re-calculate the cells with formulas, make use of the HasFormula property in IRange and recalculate it. The below code shows how to do that. 

 

Code snippet: 

IRange usedRange = visualizationSheet.UsedRange; 

string value = string.Empty; 

visualizationSheet.EnableSheetCalculations(); 

foreach(IRange range in usedRange) 

{ 

    if (range.HasFormula) 

        value = range.CalculatedValue; 

} 

Kindly try this and let us know if this helps. 

Regards, 

Shamini