Column charts are saved incorrectly when workbook is saved as pdf

Padma · December 17, 2012, 5:49am

Hi,

I am trying to copy a report worksheet into a separate workbook and then save it as pdf.

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);

When saved as Xlsx, the column charts appear correct; but when saved as pdf the column charts are saved incorrectly.

i.e columns in the column chart repeat (please refer the attached screenshot of the pdf report)

Thanks

-Padma

shakeel.faiz · December 17, 2012, 5:54am

Padma:
Hi,

I am trying to copy a report worksheet into a separate workbook and then save it as pdf.
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);
When saved as Xlsx, the column charts appear correct; but when saved as pdf the column charts are saved incorrectly.
i.e columns in the column chart repeat (please refer the attached screenshot of the pdf report)
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.

Padma · December 18, 2012, 12:46am

Hi,

Thanks for the reply.

I used the latest dll ‘Aspose.Cells for .NET v7.3.4.3’ , in this case the charts with formulas which refer to other worksheets appear blank.

Where as in the previous version the formulas were not removed from the chart cells even after calling Cells.RemoveFormulas(), hence it was showing wrong charts.

Thanks

-Harsha

Padma · December 18, 2012, 2:00am

Hi Shakeel,

With the old version of 'Aspose.Cells' I am getting :

1. charts with repeating bars.

2. Updated values in other worksheet not getting reflected in the barchart (ex: the cells I am updating in the code are reflected in normal cells in report worksheet but not in the chart ).

Using new version of the dll, results in empty charts.

Please find attached a sample excel to replicate the issue.

Please find the code which I am using.

****************************************************************

public void reportdemo()

        { 
            Cells cells = workbook.Worksheets["MainPage"].Cells;
        cells[95, 16].PutValue(1.90);
        cells[95, 17].PutValue(1.50);
        cells[95, 18].PutValue(1.20);
        cells[95, 19].PutValue(1.10);

        workbook.CalculateFormula();

        workbook.Worksheets[<span style="color: rgb(163, 21, 21);">"Report"</span>].Cells.RemoveFormulas();

        <span style="color: rgb(43, 145, 175);">Workbook</span> newworkbook = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">Workbook</span>();
        newworkbook.Worksheets.Clear();
        newworkbook.Worksheets.Add(<span style="color: rgb(163, 21, 21);">"Report"</span>);
        newworkbook.Worksheets[<span style="color: rgb(163, 21, 21);">"Report"</span>].Copy(workbook.Worksheets[<span style="color: rgb(163, 21, 21);">"Report"</span>]);

       <span style="color: blue;">string</span> fileName = <span style="color: rgb(163, 21, 21);">@"D:\Work\Aspose\"</span> + <span style="color: rgb(163, 21, 21);">"report.pdf"</span>;
       newworkbook.Save(fileName, <span style="color: rgb(43, 145, 175);">SaveFormat</span>.Pdf);</pre><pre style="font-family: Consolas; font-size: 13px; background-color: white;"><span class="Apple-tab-span">	</span> }</pre><pre style="font-family: Consolas; font-size: 13px; background-color: white;">*******************************************************************</pre><pre style="font-family: Consolas; font-size: 13px; background-color: white;"><br></pre><pre style="font-family: Consolas; font-size: 13px; background-color: white;">Thanks</pre><pre style="font-family: Consolas; font-size: 13px; background-color: white;">-Padma</pre></div>

amjad.sahi · December 18, 2012, 3:05am

Hi,

Thanks for the files.

I can notice the issue as you have mentioned. The charts are blank in the output PDF but in the re-saved XLSX file, the charts are shown. I used the template file provided by you with the following code.

Sample code:

string fileName = @"e:\test2\Sample.xlsx";
var workbook = new Workbook(fileName);
Cells cells = workbook.Worksheets["MainPage"].Cells;

cells[95, 16].PutValue(1.90);
cells[95, 17].PutValue(1.50);
cells[95, 18].PutValue(1.20);
cells[95, 19].PutValue(1.10);

workbook.CalculateFormula();

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

Workbook newworkbook = new Workbook();
newworkbook.Worksheets.Clear();
newworkbook.Worksheets.Add("Report");
newworkbook.Worksheets["Report"].Copy(workbook.Worksheets["Report"]);

fileName = fileName + "report.pdf";
newworkbook.Save(fileName, SaveFormat.Pdf);

newworkbook.Save(@"e:\test2\Sample2.xlsx", SaveFormat.Xlsx)

I have logged a ticket with an id: "CELLSNET-41271" for your issue. We will look into your issue soon.

Thank you.

Padma · December 24, 2012, 12:41am

Hi Amjad,

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

amjad.sahi · December 24, 2012, 1:15am

Hi,

We are working over your issue and will soon update you or share an eta for your issue here.

Thank you.

Padma · January 3, 2013, 7:20am

Hi

Any update on this? I need a deadline to be defined. Please reply on this today.

PS: is there any other work around for the issue as this is a critical bottle neck for my development.

Thanks

Padma

shakeel.faiz · January 3, 2013, 7:42am

Hi,

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

We are afraid, there is no update for you at this moment. Please spare us few weeks as it seems your issue is complex to be resolved.

However, we will look for any workaround if that could be provided earlier.

Also, we have logged your comments in our database against this issue id.

Padma · January 3, 2013, 8:00am

We bought the ASPOSE.CELLS for .Net license now can we get in this ASPOSE.PDF for getting resolved as a work around and get this for my current release as i don't have few weeks for this issue to be fixed. Please reply.

shakeel.faiz · January 3, 2013, 10:44am

Hi,

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

Well, you cannot use Aspose.Pdf with Aspose.Cells license. If you have Aspose.Total license then you can use any Aspose component.

However, I have a good news for you. I were finally able to successfully get the output pdf with your charts.

Please see the following code taken from your original code. I have added a new code inside it, which actually converts all of your charts into images and add them inside the target worksheet.

Then it saves the target workbook into pdf format. Please check the attached output pdf generated by this code and screenshot for your reference.

You can see, now your charts appear inside the output pdf.

C#

string filePath = @“F:\Shak-Data-RW\Downloads\Sample.xlsx”;

var workbook = new Workbook(filePath);

Cells cells = workbook.Worksheets[“MainPage”].Cells;

cells[95, 16].PutValue(1.90);

cells[95, 17].PutValue(1.50);

cells[95, 18].PutValue(1.20);

cells[95, 19].PutValue(1.10);

workbook.CalculateFormula();

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

Workbook newworkbook = new Workbook();

newworkbook.Worksheets.Clear();

newworkbook.Worksheets.Add(“Report”);

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

Worksheet source = workbook.Worksheets[“Report”];

Worksheet target = newworkbook.Worksheets[“Report”];

//Convert your source charts into images and insert them into the

//your target worksheet.

for (int i = 0; i < source.Charts.Count; i++)

{

Chart chart = source.Charts[i];

ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.ImageFormat = ImageFormat.Png;

ChartShape shp = chart.ChartObject;

Bitmap bmp = chart.ToImage();

if (bmp != null)

{

MemoryStream bms = new MemoryStream();

bmp.Save(bms, ImageFormat.Png);

target.Pictures.Add(shp.UpperLeftRow, shp.UpperLeftColumn, shp.LowerRightRow, shp.LowerRightColumn, bms);

}//if

}//for

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

Screenshot:

shakeel.faiz · January 9, 2013, 8:59pm

Hi,

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

If you only want to export one worksheet to pdf file, please simply hide
other worksheets.

C#

Workbook workbook = new Workbook(@“D:\Filetemp\sample.xlsx”);

Cells cells = workbook.Worksheets[“MainPage”].Cells;

cells[95, 16].PutValue(1.90);

cells[95, 17].PutValue(1.50);

cells[95, 18].PutValue(1.20);

cells[95, 19].PutValue(1.10);

workbook.CalculateFormula();

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

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(@“D:\Filetemp\dest.pdf”);

If you only copy one worksheet (which contains charts) to other workbook , the data source of the copied chart should refer to original file.

If original file is opening and the changed data is not saved, we will convert the chart to image with unchanged data in the original file because we could not know which file has been opened.

Eric.wang · June 29, 2021, 2:12am

@Padma,

Please try our latest version Aspose.Cells 21.6.0.

If you find any issue, please provide the source Excel file to help us analyze it.