The PeopleSoft Tipster Blog

Using Excel as a datasource May 19, 2008

This post is from a coupl of months ago, but I don’t know how I missed it.  Tim Dexter discusses how to use an Excel spreadsheet as a data-source.

http://blogs.oracle.com/xmlpublisher/2007/03/13#a192

This has the potential to be quite useful as clients frequently ask for some kind of Excel output which they then update ‘offline’ (i.e. disconnected from PeopleSoft) and then need a method of reloading the updates back into PeopleSoft.

Xls XMLP Output opens in Browser – Workaround April 18, 2008

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?

Xls XMLP Output opening in Browser, not Excel April 16, 2008

I have an issue that I wouldn’t have thought I’m the first to encounter, but I can’t see any record online of others discussing it. The problem is as follows:

I’ve written an XML Publisher report that outputs an Excel file. When I open the output from within Report Manager it opens the file in the browser, when I want it opening in Excel.

I’m aware that XMLP doesn’t output binary .XLS files, but what’s described as Excel(HTML) – which is basically a text file containing HTML that can be read and rendered as a spreadsheet when opened with Excel. The file in the report repository has an xls extension, and the Report Manager Hyperlink points to the right place. If – instead of opening the file – I save it locally then open it with Excel it renders perfectly.

Also, if I change the output to RTF instead of XLS then it opens in Word correctly. It’s also not a template issue as if I preview the output from within the XMLP Report Definition it opens in Excel. This happens on more than one client PC, and in different browsers so it doesn’t look like it’s a local machine configuration issue.

If I place the output xls file on the web server and create a hyperlink on a PIA page it works fine. However if I repoint the hyperlink to the same output file in the report repository it doesn’t work – so it seems as though the psreports servlet might be the issue. If I use a tool to examine the HTTP Header for each of these requests I can see that the Mime Type is set to “Application/MSExcel” for a PIA page hyperlink (which works), and “text/html” for the same file from the Report Repository. The mime types are set correctly (by default) in the Web Server profile, but I can’t see anywhere to set the mime-types for psreports.

I’ve been in dialogue with GSC for the last 6 weeks or so, but they say that it isn’t a misconfiguration or bug, but a ‘”potential future development with a status of ‘Awaiting review’.” The case id is:

1750412000 – “8.50: Support MHTML Excel format in XML Publisher”.

It seems Oracle eBiz suite has the same issue, but they can fix it with a configuration change.

I do have a workaround (not a solution) for this – posted here – but has anyone else faced this issue? And if so, how have you dealt with it?