Format issue with excel export

[Former Member]

Bumping up to see if I can get any response.

Thank you.

Hello,

I had originally posted this issue as a reply to another poster who had posted similar issue. However no reply was posted, so I am trying again:

We are on 4.6C with GUI 720. When we migrated to 720 we also upgraded from MS Office 2003 to 2007 and from XP to Windows 7. Ever since this migration, whenever we exported reports to excel, the date field started showing up as a group of numbers (it being in General format in the exported excel file). So I found the note # 972579 and applied it (in QA for now). Ever after applying this note, the reports exported with date and time field in custom format (correct date & time format) and all the rest of the fields in text format. This is not appealing to many users since they manipulate the spreadsheets to pivot tables and they cannot do that now with the fields in mismatched format. I raised an issue with SAP through the support portal and the answer I got, I mention below:

====================================================================================================

SAP has no direct control over the import functionality of Excel hence we cannot influence the resulting file. The file is not an excel file and is not intended to behave like real excel file. I have come to realize that the behaviour you are getting is normal

after applying note 972579.

This is from our development colleague:

The format for all the key columns are "TEXT". This is how XXL Export [Microsoft Excel (In existing XXL format)] is designed and there is no change for the same in any release/SP's. Since the XXL Export functionality is designed to display "key columns"/"characteristic columns" always as "TEXT" we cannot change it.

Reason and details of change done by note 972579: With XXL Export we are exporting data to Excel and in Excel we create

the range for each column to set the required formatting. The name used was DAT1, DAT2, DAT3 and so on for all the data columns.

There was no issue with this in any R3 release or SP's also for any MS Office release.

We faced issues with Office 2007 and compatibility with older Office releases. In New Excel 2007 it was possible to have more than 256 columns and the names used for different ranges (like DAT1, DAT2,...) became the cell name in Excel 2007. This affected the behavior/design of XXL Export, it was no more possible to use the name of any range as DAT1, DAT2..... and hence it was not possible to set any format to data columns.

Since, this changed the behavior of XXL Export and hence we had to make necessary changes(as mentioned in note 972579) to ensure the original behavior of XXL Export is not affected by changes in Excel 2007 and also to ensure behavior of XXL Export is compatible for all the R3 release/SP as well as for all MS Office releases.

We regret for any inconveniences, it's not possible to change the actual behavior of XXL Export. If you need "general" format then same must be set manually.

====================================================================================================

I am not very impressed with this answer of theirs since, the OSS note once applied, actually changed all the format from General to Text. I would like to know the view point of the experts here and advices if any on how to approach this matter. It would be easy for the users to get the report in excel in the correct format especially for those who does many of them on a daily basis. Or is this the end of story and the note they developed is half solution but half trouble as well?

I look forward to valuable replies.

Thank you,

Munair

Edited by: Munair on May 12, 2011 9:28 PM

[Former Member]

Munair,

I have resolved similar issues in the past by passing an Excel formula into the internal table that is called.

So instead of:

types: begin of t_tab,

...

TheDate(8),

...

end of t_tab.

Use this:

types: begin of t_tab,

...

TheDate(20),

...

end of t_tab.

And as you popluate the int table set each date value as follows: @DATEVALUE("5/15/2011")

where 5/15/2011 is the date that you desire.

[Former Member]

Thank you John.

Since I am a functional person, I would like to clarify further - I am sure I will have to get the assistance of the ABAP team to work this out; so, in a way, you are saying that this method can be adopted to get the date format correctly without using the note, right? Also, since this only affect the date format, we can be sure that other field formats won't get affected, right?

Please reply.

Thank you,

Munair

[Former Member]

Correct - you would NOT need the SAP Note. Just change the code to force in an Excel formula that contains the desired date.

That cell - and that cell ONLY - will be altered to a date format. In your case, all dates which are in the same column will have an Excell date format applied to them.

[Former Member]

And in the data defs above, the ABAPers should have declared the date as type SY-DATUM (or the like).

[Former Member]

Dear John,

Hope you are doing great.

Even Iam not technical consultant so sorry if the question is very silly, However I would like to know about [your option of passing the excel formula into internal table .....]

Will this change not be the case of modifying the standard program ?? And since this Date problem exsits in all reports...you mean that ABAper has to change the coding of all reports ??

Please throw some light on this ?? If possible more detailed detailed explanation will be highly appreciated !!!!

When I approched the DEV team with this option there immediate response was do you want us change the standard code of the report.

Or Does your option was referring to only ADD-ON programs ??

Looking forward for response.

Cheers,

Bala...

[Former Member]

Bala,

You would do this in custom (Y and Z) programs/objects only.

[Former Member]

Dear John,

Thanks for your reply. Highly appreciated !!!

Cheers,

Bala..