Click here to Skip to main content
15,868,016 members
Articles / Desktop Programming / Win32
Tip/Trick

XSLT to transform Excel XML worksheet to "named" nodes

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
10 Jun 2014CPOL4 min read 42.2K   565   9   10
This XSLT will output an Excel worksheet containing a CSV type of record collection in a more usable XML format.

Introduction

Many third party applications output sequential files (rows of "records") in Excel format for data sharing. Unfortunately, compared to normal "CSV" files, Excel's native XML format is not very practical to use, because the columns/record fields (= Excel cells) don't have distinct element names:

<Row>
 <Cell>
   <Data ss:Type="String">123-44-5678</Data>
  </Cell>
  <Cell>
    <Data ss:Type="String">John</Data>
  </Cell>
  <Cell>
    <Data ss:Type="String">Doe</Data>
  </Cell>
  ...
</Row>

This parameterized XSLT will transform Excel's native XML format, by either using the column names from the first row (equivalent to the CSV header row), or from "named column" information, and outputting those as the node names for each record "field".

<row number="1">
        <SSN>123-44-5678</SSN>
        <First_Name>John</First_Name>
        <Last_Name>Doe</Last_Name>
        ...
</row>

By setting a XSL parameter, the first record/row can be skipped if it contains nothing but the column names, or be included if it contains data in "named cells".

Background

The CSV file format remains popular for exchanging data between applications. Typically, the first row of a CSV file contains the column headers - making the file somewhat "self documenting" and allowing for flexible processing based on column names, rather than column number.

Many applications also output their rows of records in Excel. For this specific purpose, the first record (=row) often contains the field names (=column headers) to remain "compatible" with CSV files. If you receive a regular spreadsheet (.xlsx file), use File | Save As | Other Formats | XML Spreadsheet 2003 to create an XML file compatible with this stylesheet.

The problem is that Excel is not aware that the data in the first row is meant to be column names. Consequently it's native XML format stores every "field" in every "record" with the same node name "Cell/Data", which is not practical for processing. While one could "hard code" column numbers, this obviously lacks complete scalability, e.g. if the column order changes or if ever columns are inserted or deleted.

Using the code

  1. Place the XSL file Excel2NamedXML.xsl on your disk (per example the location of your Excel XML File).
  2. Either edit your Excel XML file by hand to include the reference to the XSL file, like so:
<?xml version="1.0" ?>
<?xml-stylesheet type="text/xsl" href="excel2NamedXML.xsl"?>

<!-- anycode line displayed below here is a bug in the codeproject web site and should be ignored -->

or use the provided JavaScript source code "Transform_Book.wsf" as a basis for your batch script or IIS web site that uses DOM methods to dynamically transform the Excel (input) XML document to a new output document.

oXMLin.transformNodeToObject( oXSL, oXMLout );

The JavaScript source code can easily be customized by changing the values in first few lines:

var strExcelFile = "Book.xml";
var strXSLFile = "Excel2NamedXML.xsl";

var aParameters = {
        'nSkipRows' :    '1',
        'nWorksheet':    '1',
	'nSkipEmpty':    '1'
        };
  • strExcelFile is path to our input document;
  • strXSLfile is the path to the Excel2NamedXML.xsl file that is included with this article.
  • If your Excel file does not have a "header" row to skip, set nSkipRows to "0".
  • If your data is not the first worksheet in your workbook, you can use nWorksheet to specify which sheet in the book to use.
  • By default empty rows (no columns have any data) will be skipped, which is particularlarly useful at the end of the spreadsheet.  To include empty rows set nSkipEmpty to "0".

Points of Interest

While digging for an existing solution to (what I assumed) was a frequently encountered problem, I did run into several related samples that each dealt with a different variation of Excel XML files:

  • Worksheet nodes with or without parent Workbook nodes (referenced by "rigid" XPATH references that only dealt with that precise structure)
  • Worksheets that used Excel's "named cell" feature to define the column names, rather than as a textual first row (NamedCell/Name nodes)
  • Multi-word column names with spaces, that can't be used as node names

The attached style sheet demonstrates how to add flexibility to an XSL template with clever use of generic XPATH references and XSL Parameters. "Field" names (the output node names) are always taken only from the first row, no matter if present as name cells or cell content. This prevents naming inconsistencies in case some rows have cells with different/additional cell names. Also, only data from a single worksheet (in a multi-sheet workbook) is processed, to prevent mixing data from one sheet with unrelated information from a different sheet.

In addition, while the standards define how to declare and use parameters in the stylesheet, they leave the details up to each implementation on how external parameter values may be passed from the outside. The attached script demonstrates how to pass parameters via MSXML's DOM extensions. (I lost an entire day debugging why the Processor interface "output" seemingly was always empty - before I finally caught a small footnote in the Microsoft documentation that referencing the "output" string also clears it!)

Credits

"jemodurn" had posted a related article XSLT to transform Excel XML into CSV files. I am reusing his "book.xml" as input.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
H&M Systems Software, Inc.
United States United States
Started programming as a 16-year old nerd on IBM /370 systems, before there were "personal computers" -- at a time when ARPAnet had barely 100 connected systems and when there you could literally "lay your hands" on your 80 column formatted code (well, really 71 columns Wink | ;-) ).

/370 Assembler, COBOL, PL/1, REXX, z80 Assembler, 8080 Assembler, C, C++, C#, Pascal, HTML, CSS, XML, XLS, JavaScript, ASP, .NET, PHP, MS Jet/Access, MS SQL, MySQL, VOIP, SIP...

Comments and Discussions

 
QuestionskipEmptyRow not working? Pin
sheir24-Jan-18 9:47
sheir24-Jan-18 9:47 
QuestionNo file in the zip file Pin
Yatin Goel27-Mar-17 9:07
Yatin Goel27-Mar-17 9:07 
QuestionNo file in the zip file Pin
Yatin Goel27-Mar-17 9:07
Yatin Goel27-Mar-17 9:07 
NewsNow skipping empty rows, and improved translation of characters in the column headers that would make invalid node names. Pin
Anamera10-Jun-14 3:06
Anamera10-Jun-14 3:06 
QuestionHow about saving the .xls file as .csv? Pin
Jaime Premy4-Jun-14 6:23
professionalJaime Premy4-Jun-14 6:23 
AnswerRe: How about saving the .xls file as .csv? Pin
Anamera4-Jun-14 7:12
Anamera4-Jun-14 7:12 
GeneralRe: How about saving the .xls file as .csv? Pin
Jaime Premy5-Jun-14 4:02
professionalJaime Premy5-Jun-14 4:02 
QuestionI don't see the xsl or link or something. Pin
Jim Meadors3-Jun-14 19:07
Jim Meadors3-Jun-14 19:07 
AnswerRe: I don't see the xsl or link or something. Pin
Anamera4-Jun-14 7:18
Anamera4-Jun-14 7:18 
GeneralRe: I don't see the xsl or link or something. Pin
Jim Meadors4-Jun-14 19:28
Jim Meadors4-Jun-14 19:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.