Aspose.Cells save worksheets where excel object has dynamic links

Aspose.Cells Product Family
1

Hello,

I have an excel worksheet and update a lot of cells in one of the worksheets 'RAW_DATA' and then save the workbook. On the excel file saved to disk all changes are present.

I then try and save the 'DataBase' worksheet using the same workbook object, the updated values are not present, it is the same values I had in the workbook before the update.

How can I get the workbook object in memory to reload the values which are changed dynamically, based on the values I wrote to 'Raw_Data' and calculations.

To select worksheets to save as pdf/xps, is it correct to make the other sheets isVisible=false? if they have links to the worksheets which are no longer visible.

E.g.

wb.Worksheets["Raw_Data"].IsVisible = true;
wb.Worksheets["Processing"].IsVisible = false;
wb.Worksheets["DataBase"].IsVisible = false;
wb.Worksheets["Passeport_1_5"].IsVisible = false;
wb.Worksheets["Passeport_2_5"].IsVisible = false;
wb.Worksheets["Passeport_3_5"].IsVisible = false;
wb.Worksheets["Passeport_4_5"].IsVisible = false;
wb.Worksheets["Passeport_5_5"].IsVisible = false;

wb.Save(raw_file_csv, SaveFormat.CSV);

I have included the workbook which I am trying to work with.

2

Hi,


Thanks for the template file.

Yes, you are doing fine to hide hide your unwanted worksheets before rendering to PDF/XPS etc.

For your issue, i.e., values are not updated accordingly, I think you may try to call Workbook.CalculateFormula() method before rendering to PDF/XPS.

If you still find the issue, kindly create a sample project (runnable) to show the issue, we will check it soon. Also attach some screenshots highlighting the issue in details. It will help us look into your issue more precisely.

Thank you.
3

When I use the API to recalculate it certainly improves, most but all the data is refreshed and present in the worksheets which I save.

These are large files, approx. 8MB and rely calculation and graphs drawing in 4 worksheets.

The graphs are not redrawn in the object. Any ideas how to resolve.

4

Hi,


Well, surely, when you update any values in the source cells, the formulas need to be recalculated in the worksheets and you should do it before rendering to PDF/XPS file formats.

Regarding your issue “…The graphs are not redrawn in the object”, kindly create a sample project (runnable) to show the issue with the template file(s), we will check it soon. Also attach some screenshots highlighting the issue in details. It will help us look into your issue more precisely and we will figure your issue out soon.

Thank you.
5

Hello,

I have created a small sample project to show the issue I am having. The attached file contains the following the following directories:
1. Readme_Exe_program
This has readme document and also the executable to test. Also is the results directory which contains the files created from the application which show the problem/
2. SampleProjectAsposeExcel: This is the Visual Studio (2010) solution files, source code is in Form1.cs

I have explained the details in the ‘Readme_Application Details.rtf’ file.

As a side question:
I know the .Net Framework is responsible for clearing up memory, how can I dispose of the Aspose.Cells object, memory is very slow to be returned. My excel files are large and I would like to clean up resources manually.
I look forward to your response and how I can overcome the problem of the worksheets not saved correctly.
6

Hi,


Thanks for providing us sample project, Readme file and template files.

I have tested your scenario/ case a bit using your sample project with your template files, I found some discrepancies and issues for some values for the Database sheet in the CSV file and values and charts rendering issues for the Passport_xxxx sheets in the output XPS file. We appreciate if you could also provide some screen shots to highlight the problematic issues/areas comparing the output Excel file’s sheets’s contents with CSV and XPS file formats, it will help us to accurately evaluate your issue to consequently fix all your issues and parts more precisely. I will make sure to log a ticket for your issue(s) into our database after getting the screen shots and further analysis of your mentioned issues.

Thank you.
7

Hello,


I have taken one worksheet example from the excel file which is not being saved with the updates during runtime. The excel file is saved correctly to disk. The XPS must match the excel worksheets.

The example I have taken, is showing the excel worksheet ‘Passeport_1_5’ and the first page of the XPS file. All data is compressed in the zip file.
When/if we can this one sorted, then we can check the rest of the data.


Please see the enclosed zip file.
1. Data from Excel_Passeport_1_5.jpg – This is a image of the Excel Worksheet ‘Passeport_1_5’ Single showing the expected 1st page in the xps file.
2. MisssData from XPS version -Passeport_1_5.jpg – This is the image from the first page and identified with red circle the parts that are not the same as the image above, i.e. the Excel worksheet 'Passeport_1_5’

3. TestOutput_11646.xlsx --The Excel file which was updated with the application, and saved to disk. It is the updating and saving to various formats for the different sheets that is not working.

4. TestOutput_11646.xps – This is the XPS file which was created after the update of the workbook during the running of the application. This XPS file contains the five worksheets from the excel file above.
The worksheets being saved to XPS are 'Passeport_1_5 / Passeport_2_5 / Passeport_3_5 / Passeport_4_5 / Passeport_5_5 ’




8

Hi,


Thanks for providing screen shots and sharing output files with details.

I have reevaluated your issue, I can find some differences and issues for some values for the Database sheet in the CSV file and values and charts rendering issues for the Processing and Passport_xxxx sheets in the output XPS file format. I also observed/ confirmed the issues as per your screen shots you provided to highlight the problematic areas. I have logged a ticket with an id “CELLSNET-42338” for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.

Thank you.
9

Hello,

I was wondering do you have idea when this issue ‘CELLSNET-42338’ will get resolved, can you please advise is it hours/days or weeks away.
I need to get solution working as soon as possible.

Regards,
Lewis

10

Hi Lewis,

We are sorry for your inconvenience.

The ticket logged earlier as CELLSNET-42338 is currently pending for analysis, and is in the queue with other tasks. Unfortunately, we can share the estimated time frame for the fix unless we have properly analyzed the issue on our end. We have logged your concerns with the ticket, and we will post the ETA as soon as we receive any news in this regard.

11

Hello,

I tested version 7.7.2 and the problem is still present, do you have any update on a potential fix for this?

Regards,

lewis

12

Hi Lewis,

Thanks for using Aspose.Cells.

We have fixed this issue and we will provide you a fix soon.

This issue was caused by that, the number and date time value were stored as string value. It is better to use Cell.PutValue(string stringValue, true , true) method to replace worbook.Worksheets[“Raw_Data”].Cells[column].PutValue(string stringValue) in your project.

13

Thank you for the suggestion on which API to use, the data numbers/values are now displayed correctly.

The issue is resolved, thank you again.

Lewis

14

Hi Lewis,

Good to know that your issue is sorted out by using other Cell.PutValue() overloaded method as suggested.

And, we may also provide a fix for it here.

Thank you.
15

Hi Lewis,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and try the latest version: Aspose.Cells
for .NET v7.7.2.2 and let us know your feedback.

16

The issues you have found earlier (filed as CELLSNET-42338) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.