Friday, August 02, 2013

Converting EXCEL Spread Sheet to XML format using Open XML SDK 2.0

Binary encoded WORD, EXCEL, Power Point presentations can be implemented by multiple applications on different platforms, adding to portability.This decoupling will be of great value to render existing documents into a portable open format.

You can get immediately into the heart of the matter by downloading Open XML SDK 2.0 for Microsoft office from here:

http://www.microsoft.com/en-us/download/details.aspx?id=5124

These are the kinds of stuff you can ho:
http://social.msdn.microsoft.com/Forums/office/en-US/19032efe-80af-4a16-b954-1f5a6d2f2236/open-xml-sdk-v2-faq-part-1#x_Q5

The download will bring in two MSI files:
OpenXMLSDKTool.msi(106.4 MB) and OpenXMLSDKv2.msi(3.8MB)

You need to run these MSI files to work with document conversions.

Now if you have Visual Studio (VS 2012 is used for this post) then you can work with WORD, EXCEL etc after installing the MSIs as shown in the previous link.

For example, I tried to create XML from EXCEL using one of Microsoft's examples quoted here (the comment is from Microsoft,'s how to convert EXCEL to XMl module)
"
/****************************** Module Header ******************************\
* Module Name:  ConvertExcelToXml.cs
* Project:      CSOpenXmlExcelToXml
* Copyright(c)  Microsoft Corporation.

* This class is used to convert excel data to XML format string using Open XMl
* Firstly, we use OpenXML API to get data from Excel to DataTable
* Then we Load the DataTable to Dataset.
* At Last,we call DataSet.GetXml() to get XML format string 
*
* This source is subject to the Microsoft Public License.
* See http://www.microsoft.com/en-us/openness/licenses.aspx.
* All other rights reserved.

* THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
* EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
* WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
"
When you open this file in Visual Studio,




and run by hitting F5 you can display a user interface where you can insert a EXCEL file reference and generate XML as shown.



I browsed to Simplest.xlsx (Microsoft Excel 2010) and it converted as shown.

The problem with this is the conversion is not correct. The Simplest.xlsx file is the following:



If I add one more row below with values 7,8,9 I get the following.


I suppose the code needs some fix.

Even more weird was how the following EXCEL,



was converted to XML.




May be I need to go into the C# file and find out what is happening.

Look at details of code  in object browser:

Once in VS2012, Object Browser is your best friend to get acquainted with the required information:





No comments:

DMCA.com Protection Status