XML File as Excel file.

Question

I have a number of reports that I run against my database that need to eventually go to the end-users as Excel spreadsheets.

Initially, I was creating text reports, but the steps to convert the text to a spreadsheet were a bit cumbersome. There were too many steps to import text to the spreadsheet, and multi-line text rows were imported as individual rows in Excel (which was incorrect).

Currently, I am generating simple XML saving the file with an ".xls" extension. This works better, but there is still the problem of Excel prompting the user with an XML import dialogue every time they open the file, and then having to save a new file if they add notes or change the layout to the file (which they almost certainly will be doing).

Sample "xls" file:

<?xml version="1.0" standalone="yes"?>             
<report_rows>                                      
  <row>                                            
    <NAME>Test Data</NAME> 
    <COUNT>345</COUNT>                 
  </row>
  <!-- many more row elements... -->
</report_rows>

Is there any way to add markup to the file to hint to Excel how it should import and handle the file? Ideally, the end user should be able to open and save the file like any othe spreadsheet they create directly from Excel.

Is this even possible?

UPDATE:

We are running Office 2003 here.

UPDATE:

The XML is generated from a sqlplus script, no option to use C#/.NET here.

Answer 1

For Excel 2003 and 2007, use SpreadSheet XML (XMLSS), that is what it is for. You will find it easy and there is much support and libraries available. Moreover, since you are already generating XML, all you really have to do is create transformation stylesheets or just modify your code. You do not need Excel to create XMLSS.