Format issue with excel export
Bumping up to see if I can get any response.
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.
Edited by: Munair on May 12, 2011 9:28 PM