CODEWITHSUNDEEP
xmlexcel
FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27486

XML File as Excel file

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.

Upvotes: 3

Views: 2286

Answers (2)

Colm Fitzpatrick
Colm Fitzpatrick

Reputation: 193

I experienced this problem before in work and I found the best way to convert multiple XML files to Excel files is to use a Visual Basic (VBA) Macro. If you have little or no experience with using VBA there are many tutorials and resources online, such as http://www.anthony-vba.kefra.com/index.htm . 

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object
 Set objFolder = objFSO.GetFolder("C:\Sem-o_Archive\XML")

'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
cutting = Left(objFile.name, 15)
Workbooks.Add
ActiveWorkbook.XmlImport URL:="C:\path\to\files\" & objFile.name,      ImportMap:=Nothing, _
                     Overwrite:=True, Destination:=range("$A$1")
ActiveWorkbook.SaveAs "C:\path\to\save\location" & cutting & "xls"
ActiveWorkbook.Close True
Next objFile

The above code converts all xml files in a specified folder and saves them in a specified folder, however to use this code you will need to fix the cutting variable so that it contains the name of the xml file.

Hope this helped.

Upvotes: 0

AMissico
AMissico

Reputation: 21684

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.

Upvotes: 1

Related Questions