Forum

This content is now out of date.

Visit Our Community

Excel format is not getting retained

Hi Team ,

If a report is exported to excel and whole content is copied and pasted in another excel file , the formatting is not getting retained . The column header background color and the text color is getting changed to another color.

Is there any settings to be made to retain the format ?


Regards,
Sanjay B S
Hi Sanjay,

Can I just ask which Version and build of Yellowfin you are using?

I look forward to hearing from you.

Kind Regards,
Katie
Hi Katie,

We are using Yellowfin 7 June build

Regards,
Sanjay B S
Hi Sanjay,

Thank you for the extra information.

Unfortunately once a report is exported to Excel, Yellowfin cannot influence the format of the report.

Can I ask why you need to copy the existing report? I am just wondering if we can supply you with a work around within Yellowfin.

Kind regards,
Katie
Hi Katie,

Say we have two reports which cannot be even used as co -display reports , we need to need to apply some custom filters on each report and then combine it as different sheets in a single excel workbook and need to send. In this case we are facing this issue if we copy the entire content of one sheet to another excel sheet.

Could you please let us know id Yellowfin applies any formatting to the excel document?


Regards,
Sanjay B S
Hi Sanjay,

Thank you for the extra information.

I have two possible work arounds for this, that I hope will provide the solution you need.

[color="#3594da">Solution 1:[/color]
Place the two reports into a dashboard and export to xls.


Forum image


When the dashboard is exported to xls the reports will be displayed on two tabs and when second report is copied and pasted it onto tab 1, the report's formatting should be retained.


Forum image


[color="#3594da">Solution 2:[/color]
Export the two reports as separate reports and when you copy one report and place it with the other report then select, "Keep Source Formatting" and the colours should revert to the ones selected in Yellowfin.


Forum image


I hope these provide the solution you need.
Let me know how you get on.

Kind Regards,
Katie
Hi Katie,

Thanks for the update. However, the question our customer has on this one is why do we select 'Keep Source Formatting' every time? The main issue here is that when YF tool creates an Excel document, it applies some format to the document which is non-standard. When I copy & paste from the document created in Excel by YF tool, the format that gets applied on the new document is completely different. I would expect not having any format or have the same format as the source document. But, why does Copy&Paste change the format of the document?

This makes me believe that the YF uses some formatting that is non standard.

Let me know if you need any information to reproduce this issue on your end and I will be happy to share the screen shots.

Thanks
Guruprasad
Hi Guruprasad,

While I can understand your concern, and can agree that yellowfin is using some custom formats (e.g. Column formats, cell colors etc..), since it is displayed in Excel correctly (once exported), it then becomes an XLS document which is displaying correctly.

We cannot actually control what Excel wants to copy & paste.

Even if Yellowfin is using custom formatters, it is up to excel to copy and paste these correctly, since they are clearly supported in the document.

You might be able to post a question to Microsoft to see what they can come up with, and also see if this is occurring on different versions of Excel.
It could simply be an Excel defect ;).

I wish we could be of further assistance with this and hope you can understand our stance on supporting documents once exported out of Yellowfin.

Regards,
David



Hi David,

Unfortunately, neither myself nor our users are convinced that this is just an excel issue and YF tool has got nothing to do.

If that is the case, why this happens only in the document created by YF? By no other means we can reproduce this issue in Excel.

The main issue here seems to be that YF tool is using some old excel libraries. I have also raised this issue with Daniel who is our account manager and he has mentioned that he will look into this and get back to us on this one as there are some more issues around Excel version that YF is using.

Thanks
Guruprasad
Hi Guruprasad,

Just letting you know this has been raised as a support task (TASK ID = 172824) so that the development team can look into the libraries we are using.

At this point in time, I cannot comment if this is a defect/enhancement, as we need to look at the back-end and see if something can be done to change this.

Will have more information on this in the coming weeks.

Sorry for the inconvenience this has caused.

Regards,
David
Hi Guru,

Ok I have some good information as to why this is happening.

To understand what's happening, you have to be aware of the following:

- Each Excel document has a single colour palette that manages formatting colours and some drawing layer colours. In Excel binary files (.xls), this palette is limited to 56 colors, and all formatting colors must come from this palette. To be able to use custom colours in XLS exports, Yellowfin therefore has to alter the default colour palette.

- When using colours in an XLS document, you're not really using a colour but a reference to a colour in the colour palette. For example, let's say that Blue is the 10th colour in the default palette. This means when you set the background of a cell to Blue, you're not really telling Excel: "Paint that cell's background in blue". What you're really telling Excel is: "Take the 10th colour of the colour palette, and paint that cell's background with that colour". A consequence of this is that if you modify the palette later on so the 10th colour becomes Yellow instead of Blue, every cell referencing it will now use Yellow instead of Blue.

All this means that if you have an XLS document using an altered colour palette and copy some of its data into a document the default colour palette, the colour references won't all point to the same colours, and the two documents colours won't match. Note that this is exactly what happens when you copy a Yellowfin XLS export into a new XLS document. However, as mentioned, if you select the "Keep Source Formatting" when pasting, the colours' references will be passed to the destination document and the colours will be preserved.


Note: XLSX doesn't suffer the 56 colours limitation, so this particular scenario shouldn't happen once we support exporting to xlsx. A support task was raised for this (TASK ID = 108554), though not ETA has been given as yet.

Hope this all makes sense and please let me know if you have further questions on this.

Regards,
David

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.