Click here to Skip to main content
15,867,308 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel

Excel Add-in for Exporting Data to XML

Rate me:
Please Sign up or sign in to vote.
5.00/5 (15 votes)
21 Jan 2015CPOL3 min read 143.1K   12.6K   58   19
ExcelXMLExport is a Microsoft Excel 2010 / 2013 Add-in that generates XML data from Excel sheet.

Introduction

ExcelExportXML is a Microsoft Excel 2010/2015 Add-in that generates XML data from an Excel sheet. It is developed in Visual Studio using C#.

It is a very simple add-in. To understand how it works, consider an Excel sheet having the following data.

Country Capital Continent
France Paris Europe
Germany Berlin Europe
India New Delhi Asia
Indonesia Jakarta Asia

Using the add-in, the above can be exported to the following XML.

XML
<sheet1>
    <row>
        <country>France</country>
        <capital>Paris</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>Germany</country>
        <capital>Berlin</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>India</country>
        <capital>New Delhi</capital>
        <continent>Asia</continent>
    </row>
    <row>
        <country>Indonesia</country>
        <capital>Jakarta</capital>
        <continent>Asia</continent>
    </row>
</sheet1> 

Once the Add-in is installed, you will have a 'Generate XML' button on Add-ins tab of Excel Ribbon as shown below. Clicking 'Generate XML' will pop up the save file dialog which will allow you to save the generated XML file.

ExcelExportXML/ScreenShot.png

Use Case & Assumptions

ExcelExportXml is useful when you have tabular data in Excel and the first row contains the column headings. It works based on the following assumptions:

  1. The first row is considered as column headers and will be converted to XML tags.
  2. After encountering the first empty cell in header row, rest of the columns to the right will be ignored.
  3. Supports columns up to 'Z' only, i.e., maximum of 26 columns.
  4. The first row for which all values are empty will be considered the end of the sheet.
  5. The sheet name and column names should not have any spaces.

Why Not Use Standard Save As XML Functionality

Excel provides more than one way to export XML data. One of them is 'Save As XML Data' available in Save As dialog. This requires XML Mappings to be defined which I believe requires developer Add-in from Microsoft to be installed.

Another option is 'Save As XML Spreedsheet 2003' which generates the following XML:

XML
<Worksheet ss:Name="Sheet1">
 <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="15">
  <Column ss:Width="51"/>
  <Column ss:Width="54"/>
  <Column ss:Width="51.75"/>
  <Row ss:StyleID="s64">
   <Cell><Data ss:Type="String">Country</Data></Cell>
   <Cell><Data ss:Type="String">Capital</Data></Cell>
   <Cell><Data ss:Type="String">Continent</Data></Cell>
  </Row>
  <Row>
   <Cell><Data ss:Type="String">France</Data></Cell>
   <Cell><Data ss:Type="String">Paris</Data></Cell>
   <Cell><Data ss:Type="String">Europe</Data></Cell>
  </Row>
  <Row>
   <Cell><Data ss:Type="String">Germany</Data></Cell>
   <Cell><Data ss:Type="String">Berlin</Data></Cell>
   <Cell><Data ss:Type="String">Europe</Data></Cell>
  </Row>
  <Row>
   <Cell><Data ss:Type="String">India</Data></Cell>
   <Cell><Data ss:Type="String">New Delhi</Data></Cell>
   <Cell><Data ss:Type="String">Asia</Data></Cell>
  </Row>
  <Row>
   <Cell><Data ss:Type="String">Indonesia</Data></Cell>
   <Cell><Data ss:Type="String">Jakarta</Data></Cell>
   <Cell><Data ss:Type="String">Asia</Data></Cell>
  </Row>
 </Table>
 </Worksheet>

As you can see, the above might not be what you want. It is more a representation of Excel sheet than semantics of your data. ExcelExportXML is quite limited but effective in a common scenario where you have tabular data and the first row contains column headers.

Writing Add-in for Microsoft Excel 2010

Using Visual Studio 2010 Professional, creating Excel Add-in is pretty simple. Following are the steps I followed for ExcelExportXml:

Step # 1

Create a new project of type 'Excel 2010 Add-in'. The project will have a class file called ThisAddin.cs.

Step # 2

For creating a button on the Excel ribbon, right click on the project and select Add - > New Item.

Step # 3

Select 'Ribbon (XML)' from the list of new items and name the ribbon class, say Ribbon1. This will add two files to the project which are Ribbon1.cs and Ribbon1.xml.

Step # 4

Add the following code in ThisAddin class:

C#
protected override Microsoft.Office.Core.IRibbonExtensibility
    CreateRibbonExtensibilityObject()
{
     return new Ribbon1();
}

Step # 5

In Ribbon1.xml, define the properties of the button that will be displayed on the Excel ribbon. The onAction attribute specifies the method which will be called from Ribbon1.cs on clicking the button (onAction="OnTextButton").

XML
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns=http://schemas.microsoft.com/office/2009/07/customui
onLoad="Ribbon_Load">
  <ribbon>
    <tabs>
      <tab idMso="TabAddIns">
        <group id="MyGroup"
               label="Export XML">
          <button id="textButton" label="Generate XML"
             screentip="Export to XML" onAction="OnTextButton"
             supertip="Export excel sheet to XML file."/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Step # 6

Add the following method declaration to Ribbon1.cs and implement your functionality here.

C#
public void OnTextButton(Office.IRibbonControl control)
{
    //TODO:Add your implementation here
}

History

  • 21st January, 2015: Limit of number of columns removed. Unlimited number of columns can be exported now.
  • 7th December, 2011: Initial post

License

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


Written By
Software Developer (Senior)
United Arab Emirates United Arab Emirates

Comments and Discussions

 
QuestionError during installation: "Could not load assembly Microsoft.Office.BusinessApplications.Fba" - Fix Pin
SiTeW30-Jun-15 14:14
SiTeW30-Jun-15 14:14 
AnswerRe: Error during installation: "Could not load assembly Microsoft.Office.BusinessApplications.Fba" - Fix Pin
Syed Umar Anis1-Jul-15 1:47
professionalSyed Umar Anis1-Jul-15 1:47 
SuggestionAttributes instead of Nodes Pin
souitom18-Feb-15 23:41
souitom18-Feb-15 23:41 
GeneralRe: Attributes instead of Nodes Pin
Syed Umar Anis22-Feb-15 2:29
professionalSyed Umar Anis22-Feb-15 2:29 
BugOnly reading columns with A-Z Pin
ivek81cro6-Jan-15 15:43
ivek81cro6-Jan-15 15:43 
GeneralRe: Only reading columns with A-Z Pin
Syed Umar Anis21-Jan-15 0:35
professionalSyed Umar Anis21-Jan-15 0:35 
BugError while installation Pin
Member 1122971212-Nov-14 19:32
Member 1122971212-Nov-14 19:32 
GeneralRe: Error while installation Pin
Syed Umar Anis13-Nov-14 20:08
professionalSyed Umar Anis13-Nov-14 20:08 
GeneralMy vote of 2 Pin
huangyunfeng31-Oct-13 19:07
huangyunfeng31-Oct-13 19:07 
GeneralRe: My vote of 2 Pin
Afzaal Ahmad Zeeshan20-Oct-14 6:07
professionalAfzaal Ahmad Zeeshan20-Oct-14 6:07 
Questionerror while installing Pin
Cool Smith10-Jun-13 1:25
Cool Smith10-Jun-13 1:25 
QuestionChanging Element names and Line Feeds Pin
xml_man5-Mar-12 10:42
xml_man5-Mar-12 10:42 
AnswerRe: Changing Element names and Line Feeds Pin
Syed Umar Anis12-Mar-12 18:50
professionalSyed Umar Anis12-Mar-12 18:50 
Questiondo you have this for Excel 2007? on x64? Windows 7? Pin
Tarek Mourad22-Feb-12 13:18
Tarek Mourad22-Feb-12 13:18 
AnswerRe: do you have this for Excel 2007? on x64? Windows 7? Pin
Syed Umar Anis25-Feb-12 0:41
professionalSyed Umar Anis25-Feb-12 0:41 
BugThanks, just what I've been looking for. However.... Pin
Member 860149230-Jan-12 10:04
Member 860149230-Jan-12 10:04 
GeneralRe: Thanks, just what I've been looking for. However.... Pin
Runlin Zhang12-Mar-12 11:20
Runlin Zhang12-Mar-12 11:20 
GeneralRe: Thanks, just what I've been looking for. However.... Pin
Wayne Loschen4-Aug-12 8:03
Wayne Loschen4-Aug-12 8:03 
GeneralRe: Thanks, just what I've been looking for. However.... Pin
Syed Umar Anis4-Aug-12 21:41
professionalSyed Umar Anis4-Aug-12 21:41 

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.