Saturday, March 01, 2008

Would the REAL Excel please stand up?!

One of the considerations of using Microsoft Excel is the underlying format of the data you're looking at.

Lets say you're viewing output from an Oracle eBusiness Suite concurrent request, and Excel is automatically opened like here (SYLK) and here (BI Publisher). These solutions pose a couple of questions:

  • How do you know what the real underlying format is for the Excel file opened?
  • What are the implications of the underlying format?

A "true" Excel workbook format is a binary format. When you view concurrent request output in EXCEL format from BI Publisher then the output is actually XHTML. If a file with .xls suffix is in XHTML format and you do a blind "save" on the file, then you wouldn't know otherwise. Other formats (SYLK, CSV, etc) prompt you with "File.xls may contain features that are not compatible with XXX format. Do you want to keep the workbook in this format." Another alternative I use to identify the format and more often to change the contents without the subtleties of Excel mangling coming into play is to use a non mangling text editor with hex/binary file editing capabilities, my preferred tool is the fantastic free PSPad. As soon as I open the file the format is obvious.

Can the underlying XHTML format cause a problem?

At this stage I haven't identified any issues, but the question has already been asked. I generally don't like to take chances when an easy workaround is available so my advice is:

Use "File, Save As" functionality and save as type "Microsoft Office Excel Workbook *.xls" whenever you're not 100% sure of your underlying Excel file format.

When you do a file save as, the format that Excel thinks the file will be defaulted in the "save as type" field. So then you'll know!

PS. If anyone has any specific isses with the XHTML output from BI Publisher I'd be keen to hear.

PPS. "True" Excel templates are supposed to be coming to the eBusiness Suite BI Publisher sometime soon ... Tim any update?

PPPS. If anyone knows a CSV editor that doesn't mangle the contents (e.g. dates, number formats) like Excel but has functionality similar then I'd also love to hear!

1 comment:

Rohit Kokate said...
This comment has been removed by the author.