Xls XMLP Output opens in Browser – Workaround April 18, 2008Posted by Duncan in PeopleTools, XML Publisher.
There is a potential workaround for the issue detailed here, but it’s far from a graceful solution. Perhaps someone out there can suggest a more elegant implementation of the workaround.
Almost by accident while troubleshooting this issue I discovered that if I took the output text file (which incorrectly opens in the browser) within the Report Repository and editted it, then tried to open it again it would open in Excel. Strange! It seemed to make no difference what change I made – or even if I reversed out a previous change – all that mattered was that I’d editted it. Furthermore it only worked if I editted it with certain tools.
The upshot was that when you edit a text file using Windows Notepad it inserts an invisible non-printing character at the start called a BOM (Byte Order Mark). This is all related to whether the format is ANSI, or UTF-8 etc. Using a fuller-featured editor (I used Notepad++, see the Applications tab above for more) I toggled whether the BOM is present or not and this affected whether the file opened in Excel or a browser. Further testing seemed to show that it didn’t have to be a BOM character, as long as I didn’t interfere with the tags or their data content this workaround still worked.
Now although I have a method, I can’t decide the best method of implementing it. The method I use at the moment is after the file is generated – but before it is published – I insert the character. Using PeopleCode the only method I can think of to achieve this is to create a new text file, insert the character to ensure Excel output, then copy in the contents of the original output file. The only alternatives I can think of involve external calls from PeopleCode.
Any smarter ideas?