Free Support Forum - aspose.com

Charts which refer other sheets are not displayed

Hi,


I am trying to copy a report worksheet into a separate workbook and then save it as pdf/xlsx.
The steps I followed :

1. workbook.CalculateFormula();

2. workbook.Worksheets[“Report”].Cells.RemoveFormulas();

3. Workbook newworkbook = new Workbook();
newworkbook.Worksheets.Add(“Report”); newworkbook.Worksheets[“Report”].Copy(workbook.Worksheets[“Report”]);
4. newworkbook.Save(fileName, SaveFormat.Pdf);
The worksheet “Report” in workbook contains charts, some of which has formulas which refer to other sheets in the workbook.
When I save the workbook as Xlsx or pdf I can see the charts correctly. 
But when I copy only the report worksheet to a new workbook after removing the formulas and then save it as pdf/xlsx, the charts which refer to other worksheets are not displayed however other charts which don’t refer to worksheet are displayed properly. 

Thanks
-Padma
Padma:
Hi,

I am trying to copy a report worksheet into a separate workbook and then save it as pdf/xlsx.
The steps I followed :

1. workbook.CalculateFormula();

2. workbook.Worksheets["Report"].Cells.RemoveFormulas();

3. Workbook newworkbook = new Workbook();
newworkbook.Worksheets.Add("Report"); newworkbook.Worksheets["Report"].Copy(workbook.Worksheets["Report"]);
4. newworkbook.Save(fileName, SaveFormat.Pdf);
The worksheet "Report" in workbook contains charts, some of which has formulas which refer to other sheets in the workbook.
When I save the workbook as Xlsx or pdf I can see the charts correctly. 
But when I copy only the report worksheet to a new workbook after removing the formulas and then save it as pdf/xlsx, the charts which refer to other worksheets are not displayed however other charts which don't refer to worksheet are displayed properly. 

Thanks
-Padma
Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version: Aspose.Cells for .NET v7.3.4.3 and see if it makes any difference.

Please also provide us your source xls/xlsx file which you are converting to pdf so that we could look into this issue at our end.

We will investigate it and help you asap.

I could see that after calling ‘worksheet.Cells.RemoveFormulas();’ and adding the worksheet in a new workbook and saving as xlsx, the formulas of all the cells are removed except the chart cells which still refer to a worksheet which doesn’t exist.

Hi,

Thanks for your feedback.

Could you please provide us your complete, simple runnable code to replicate this issue?

We will run your code at our end and log it in our database if we found it as a bug or rectify your code.

Thanks for your cooperation.

I have copied the code I tried.

1. workbook.CalculateFormula();

2. workbook.Worksheets["Report"].Cells.RemoveFormulas();
3. Workbook newworkbook = new Workbook();
newworkbook.Worksheets.Add("Report"); newworkbook.Worksheets["Report"].Copy(workbook.Worksheets["Report"]);
4. newworkbook.Save(fileName, SaveFormat.Pdf);
The worksheet "Report" in workbook contains charts, some of which has formulas which refer to other sheets in the workbook.
When I save the workbook as Xlsx or pdf I can see the charts correctly.
We have also tested it using the latest version as suggested and after that we are getting only an empty chart now. Please see the attached excel we are using. Please give me more information.

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to replicate this issue using the latest version: Aspose.Cells
for .NET v7.3.4.3
with the following code. I have attached the output pdf for your reference.

We have logged this issue in our database. We will investigate this issue further and fix the problems.

Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41275.

C#


string filePath = @“F:\Shak-Data-RW\Downloads<span class=“kwrd”>for+aspose.xlsx”;


Workbook workbook = new Workbook(filePath);


workbook.CalculateFormula();

workbook.Worksheets[“Report”].Cells.RemoveFormulas();

Workbook newworkbook = new Workbook();

newworkbook.Worksheets.Add(“Report”);

newworkbook.Worksheets[“Report”].Copy(workbook.Worksheets[“Report”]);

newworkbook.Save(filePath + “.out.pdf”, SaveFormat.Pdf);

Hi Shakeel,


Kindly let me know the status of the issue. Also give me a proposed date by which I can expect the fix, as there is dependency of pdf reports in an important module.

Thanks
-Padma

Hi,


I am afraid your issue is not resolved yet. I have asked the relevant developer to update on it. It is quite possible that we might share an eta as it does take more time than expected/usual. Once we have any update on it, we will let you know here.

Thank you.

Hi,


Please hide the worksheets except the report sheet as per the thread: http://www.aspose.com/community/forums/thread/432762/column-charts-are-saved-incorrectly-when-workbook-is-saved-as-pdf.aspx

e.g

workbook.Worksheets.ActiveSheetIndex = workbook.Worksheets["Report"].Index;

for (int i = 0; i < workbook.Worksheets.Count; i++)

{

if (i != workbook.Worksheets.ActiveSheetIndex)

workbook.Worksheets[i].IsVisible =

<span style=“font-size:
12.0pt;font-family:“Courier New”;color:blue;mso-fareast-language:ZH-CN”>false;<o:p></o:p>

}

workbook.Save(@“D:\Filetemp\dest.pdf”);

<o:p></o:p>

Padma:
Hi Shakeel,

Kindly let me know the status of the issue. Also give me a proposed date by which I can expect the fix, as there is dependency of pdf reports in an important module.

Thanks
-Padma
Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have investigated your issue further and we suggest you the following code. Now your charts will display find.

I have also attached the output pdf generated by this code and screenshot for your reference.

C#
String filePath = @"F:\Shak-Data-RW\Downloads\for+aspose.xlsx";

Workbook workbook = new Workbook(filePath);

workbook.Worksheets.ActiveSheetIndex = workbook.Worksheets["Report"].Index;

for (int i = 0; i < workbook.Worksheets.Count; i++)
{
if (i != workbook.Worksheets.ActiveSheetIndex)
workbook.Worksheets[i].IsVisible = false;
}

workbook.Save(filePath + ".out.pdf", SaveFormat.Pdf);