All cell contents not displayed when Exporting to Excel

[former_member672670]

Hie Guys,

I am having trouble displaying all the contents of a column when Exporting to Excel. Basically, the column is a long text that explains the status of a project. Name of the column - "Status Description". Now, I have already formatted the column to "Auto- fit Height" & "Wrap Text". All the contents of the cells are displayed when viewed in "HTML" & "PDF". However, when I export it to Excel, some column cells have not been accounted for "Auto-Fit Height". I've given the example below. Thanks.

In HTML & PDF:

Project ID                    Status Description

100                         This project explains the status of

                                the project 100. The expected benefit

                                from the project is supposed to be

                                $1.2 M.

In Excel:

Project ID                    Status Description

100                         This project explains the status of

                                the project 100. The expected benefit

                                from the project is supposed to be

[Former Member · Accepted solution]

Hi, your issue is logged in SAP Note 1863037. An ADAPT for a fix was created bur refused by the dev team. Their reply is as below:

First thing to say to customer is that : we don't guarantee the exact size of cells when exporting to Excel, the reason is that Excel has its own cell size unit (not very well documented by Microsoft) that we try to get as close as possible to.

In PDF, we guarantee that size of cells will be the same as in Webi.

The goal of exporting to Excel to do some extra calculation on data.

If the goal is to have the same exact display as in Webi, we recommand the use of PDF instead.

[Former Member]

Hi,

Have you tried using the 'Wrap Text' Functionality?

Also try choosing the option "Prioritize the formatting of the documents" in Webi Preferences.

Please follow the below steps to achieve this:

Log into BI Launch Pad

1. In the "Header" panel, click Preferences.

2. Click Web Intelligence.

3. Under "Select a priority for saving to MS Excel":

   Choose Prioritize the formatting of the documents to display the data in a format that is similar

to working in Web Intelligence.

4. Click Save and Close.

Hope it will help!

Thanks,

Sakthi.

[arijit_das]

I tried in Rich client BO 4.0 SP6 to export a report containing long texts to Excel 2007 format.

Applied word rap and auto height to the column.

While exporting I selected Prioritize the formatting of the document and observed similar situation as you. The excel cell was not showing complete text.

Again I tried to export with Prioritize easy data processing in Excel option and the text appeared correctly in the exported file.

Then I tried to export the file as excel (xls format) and for both the options Prioritize the formatting of the document and Prioritize easy data processing in Excel, I observed the problem.

So, I would suggest you to please check with Prioritize easy data processing in Excel option and export as excel 2007 file (xlsx format).

[former_member672670]

I tried "Prioritize east data processing", but it did not work. I guess it is a limitation of Excel 2007 when the cell size is too big. I will have to manually expand the cells in Excel to resolve this issue since there seems to be no other way. Anyways, thanks for the input.

[arijit_das]

Could you pls specify the BO and Excel versions you are using with SP and Patch level ?

Also, is it possible to provide screenshots of format cell screen for both columns in the block ?

[former_member672670]

Business Objects Version - 4.0 service pack 7 patch 2

Excel version - 2007

I have attached the snapshots below. One is exported as PDF & the other is Exported as Excel. The Excel one is having the size issue. Thanks.

[Former Member]

Are you doing save as Excel from the report?  In that case use check the option "Prioritize formatting" to have the correct formatting..

[former_member672670]

Yes, I am exporting it to excel. I tried "Prioritize formatting", but it did not solve the problem.

[Former Member]

It does not show anything for project 100 in the screenshot.. Also looking at the row where the status is executing it looks like the cell is not fully expanded..

Also try export with "prioritize data"..

[former_member672670]

Project 100 was just an example i provided. Export with prioritize data did not work. I have formatted the column to "Auto fit height" . I guess its an issue with Excel 2007 when the cell contents are too big.

[Former Member]

Jeevan,

Check with prioritize data.. which might work for you. Basically this functionality should preserve the data into a standard spreadsheet disregarding any formatting issue at report..

If this does not work then post a screenshot of the excel after fully expanding the cell of the excel..