Issue wich charts convertion to pdf

Hello,

We have implemented such features of our application using FlexCel library:
- generate Excel reports from datasets;
- export Excel reports into PDF files.

We have found out some problems with the export to PDF when the source Excel file contains Pie charts.
The color palette and the position of data labels are distorted, as you can see comparing the source Excel file and the PDF export result. We can't attach files to the ticket. Please provide email address to send them.

Our application doesn't generate charts by FlexCel; it uses pre-designed Excel file as a template and just fills the data cells with external data, a presentation part of the report still be unchanged. So this issue occurs if the template is designed in Excel 2007, as the certain report has been. If a template is created in one of previous Excel versions, such as Excel 2003, the colors are exported properly, and the labels mostly too.
Concerning colors, we suppose that it may be caused by difference of color palette support in Excel 2003 and 2007.


Our application runs in such environment:
- Windows 2003 Server R2 & SP2 (reproduced locally in Windows 7 too);
- .NET 3.5;
- FlexCel.dll of versions 4.9.6.0 and 5.5.0.0
The stable application version uses 4.9.6.0, but this problem occurs for both FlexCel versions.


This is a source code of a method that exports Excel file to PDF. It has been called with such parameter values:

aExportAllSheets = false
aPageSize = "Legal"
aPageOrient = "portrait"

-----------------------------------------------------------------------------------------------------------------

protected FlexCel.Render.FlexCelPdfExport flexCelPdfExport_ = null;
 
public void exportFile(string aXlsFile, string aPdfFile, bool aExportAllSheets, string aPageSize, string aPageOrient)
{   
    flexCelPdfExport_.Workbook = new XlsFile();
    flexCelPdfExport_.Workbook.Open(aXlsFile);

    ExcelFile xls = flexCelPdfExport_.Workbook;
    xls.PrintOptions |= TPrintOptions.LeftToRight;           
    flexCelPdfExport_.Kerning = true;

    using (FileStream pdfStream = new FileStream(aPdfFile, FileMode.Create))
    {
        int saveSheet = xls.ActiveSheet; 
       
        try
        {
            flexCelPdfExport_.BeginExport(pdfStream);
           
            if(aExportAllSheets)
            {
                for(int i = 1; i <= flexCelPdfExport_.Workbook.SheetCount; i++ )
                {
                    flexCelPdfExport_.Workbook.ActiveSheet = i;
                    if(aPageOrient=="landscape")flexCelPdfExport_.Workbook.PrintOptions = TPrintOptions.LeftToRight;
                    flexCelPdfExport_.Workbook.SheetZoom=100;
                   
                    flexCelPdfExport_.Workbook.PrintGridLines = xls.PrintGridLines;
                    flexCelPdfExport_.Workbook.PrintScale=xls.PrintScale;                           
                    flexCelPdfExport_.Workbook.PrintPaperSize =(TPaperSize) Enum.Parse((new TPaperSize()).GetType(),aPageSize,true);
                    flexCelPdfExport_.ExportSheet();
                }
            }
            else
            {
                flexCelPdfExport_.ExportSheet();
            }
            flexCelPdfExport_.EndExport();
        }
        finally
        {
            xls.ActiveSheet = saveSheet;
        }
    }
}

 

Hi,

Sadly the chart engine in FlexCel behaves like Excel 2003, because it was written before Excel 2007 appeared. It is one of the 2 things we still have pending in xlsx/xls 2007 support, charts and conditional formats. We plan to add those 2 missing things in the following releases, but due to their complexity, it will take some time yet.

You need to understand that the xls file format for Excel 2003 and 2007 is very different, specially because Excel 2007 added true colors everywhere, instead of the indexed colors that Excel 2003 used. So Excel 2007 saves the colors twice everywhere, once for Excel 2003 (which doesn't know about true colors) and once for Excel 2007. There there are some crcs to make sure that if you change a color in Excel 2003 then Excel 2007 reads that changed color and not the old color still stored in the new xls2007 record (which Excel 2003 will preserve but not update).

FlexCel 5 implements true color almost everywhere, so it knows about both the xls2003 and xls2007 records, knows which one to read depending in the crc, and knows how to save both. But as said, in charts  we wtill have an older rendering engine, that understands only the xls2003 records, so true color won't be available when exporting charts. When you save it will be preserved (as it will be if you edit the file in Excel 2003) but it won't render it (as Excel 2003 won't render those colors either).

FlexCel 4.9 is similar to Excel 2003 in all regards, it doesn't know anything about Excel 2007 records, so it will never have this support. Also it has issues with xls2010 records (yes, xls 2010 is also different), so it is strongly suggested that you update it: 
http://www.tmssoftware.com/site/blog.asp?post=154

FlexCel 5.x should be getting this support, I will try to see and if it can be added easily, we will be publishing a new beta with it in next week. If it turns out more complex, it might have to wait until the chart engine is rewritten to support xlsx.

I will let you know.

Regards,
   Adrian.

ps: you can email me at adrian@tmssoftware.com but I imagine your issue is what I have already explained, that we don't show Excel 2007 colors in charts, and we behave as if we were Excel 2003. If it is something different (meaning that the pdf output by FlexCel is different that what Excel 2003 shows), please send me the files.

Well, after some more digging, it happens that FlexCel 5 does support Excel 2007 colors in charts, at least basic support. I had forgotten that (and I wrote the code...). Can you please send me the files so I can have a better idea of what the issues are?


Regards,
   Adrian.

Thank you Adrian.

I am ready to send files.

Please provide your email address.
Thank You.

I mentioned it in my first post, but anyway, it is adrian@tmssoftware.com


Thanks,
   Adrian.

Adrian email was sent to You.
Thanks.

Thanks, I got it and will investigate what can be done. Issues with  the labels seems to mostly because Excel basically saves 2 versions of the chart inside the xls file (one for Excel 2007 and the other so 2003 can read it),  and the Excel 2003 version /the one we read) seems to be wrong. (As you can see by opening this file in Excel 2003). 


About the colors, it seem related to the fact that those are not real colors but 3d gradients, and while we read the color information from the Excel2007 part of the file, I am not sure we read gradients from there. I will investigate.

Regards,  
    Adrian.

Thanks You.
Waiting for update from You.

Adrian, any updates on this issue ?

Hi,

Sadly this will have to wait until we write an xlsx chart renderer for 6.0  (currently we have another update scheduled, 5.7 with conditional format support, and after that, 6.0 with xlsx chart rendering).

The bugs are in the way Excel saves the chart in xls, not in the FlexCel chart rendering engine, and we can't really fix them. What we need to do is to read the xlsx chart definition which is saved together with the xls chart definition, and use the former. (as the xls is wrong).

I wish I could help more, but really, with the wrong data saved in the xls chart, we can't do much more than rendering a wrong chart. Once we can read the xlsx chart inside the same file, things should be fixed.

Adrian Thank you.
When You plan to deliver version 6 ?

Realistically, I wouldn't expect it before half of 2012. There is a lot of work to do to create a chart rendering engine for xlsx, and thousands of cases to test. And we have to deliver 5.7 first.

I see. Please contact us when this fix will be avialable.

Thank You!