Create an XML file using LINQ and import XML data into an Excel spreadsheet

Hey all,

You must think that this post title is a rather long one. Yes it is, but the concepts are rather easy and I have been posting lots of stuff regarding LINQ to SQL,VB.Net, XML Literals, e.t.c.

A friend of mine had a project which required to get some data from an SQL Server database table, create an XML document from the relational data and import it to an excel file. Another thing to bear in mind here is that we needed to create the excel file programmatically.

I tried to help him out and then thought that it might be a good idea to write a post about the solution I proposed.

So I will try to show all these, by (a hands-on example) building a windows form application using VB 2008(9.0).

I am going to use Visual Studio 2008 professional but Visual studio 2008 Express edition will do just fine.You can download Visual Studio 2008 from here .

Obviously for this example we will need a database. I will use the Pubs database. This is a well known database and many people are familiar with its schema.You can download the Pubs database from this link. If you need some help on how to install the database have a look here .

I have installed SQL Server 2008 Standard edition in my PC. SQL Server 2008/2005 Express editions will work just fine.You can download SQL Server 2008 Express edition from this link. I have attached the Pubs database in my local instance of the SQL Server.

In order to follow along you must have all the above running smoothly in your pc.It will be great for you in order to better understand what I am about to show you, If you are familiar with LINQ to SQL and XML Literals.

If you want to read another post of mine regarding XML Literals click here .

If you want to read another post of mine regarding LINQ to SQL click here

So let’s start…

1) Lauch Visual Studio 2008

2) Create a new project. From the available templates choose “Windows form Application”. Make sure VB is selected as your language of choice for this project.

3) Give your project a suitable name, e.g “sqltoxmlandimporttoexcel”

4) We will create the excel file programmatically first.In the form (Form1) just drag and drop a button on it. Name it “btnCreateExcel”.

5) Go to your c:\ drive and create a folder.Name this folder as you want. I have named this new folder after my name, thus nikos. So I have a path of “c:\nikos”. In this folder I will place the excel file that I will create programmatically.

6) Double click on the button. In the event handling routine that is created by VS 2008 for us, paste the code below

Dim xlApp As excel.Application

Dim xlWorkBook As excel.Workbook

Dim xlWorkSheet As excel.Worksheet

xlApp = New excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Add

xlWorkSheet = xlWorkBook.Sheets(“sheet1”)

xlWorkSheet.Cells(1, 1) = “XML Code starts here”

xlWorkSheet.SaveAs(“C:\nikos\mydataxml.xls”)

xlWorkBook.Close()

xlApp.Quit()

xlApp = Nothing

MsgBox(“Your Excel file has been created successfully”)

As you see I have called my excel workfile, “mydataxml.xls”

7) You must do 2 two things in order for this code to run. First you must add a reference to the Microsoft Excel Interop Library which can be found in the COM components tab.

• Right-click on your Project and select “Add reference” and then from the “Add reference” window, Select the COM tab. Scroll down till you find the Microsoft Excel 11.0 Object Library and click OK.

Have a look at the picture below.

• Add this line of code just above the “Public Class Form 1”

Imports excel = Microsoft.Office.Interop.Excel

Public Class Form1

8) Run your application by hitting F5. Click on the button and then check on your specified folder to see if the excel file has been created.

9) Now we need to query our database table. We are going to use this by using LINQ to SQL. Go to your Solutions Explorer, select your project and then right click and choose “Add new item” and choose LINQ to SQL classes. Name the .dbml file as Pubs.dbml. If you open the Pubs.dbml file, you will see the O-R designer surface.From your Server Explorer window, click to the Connect to Database button. Select the Sql Server instance name and select the Pubs database and hit the OK button.See the picture below:

10) After establishing the connection with the Pubs database, expand the Tables node and drag – drop the Authors table in the O-R designer surface. An “Author” data class is automatically created for us.

11) Place another button on your form. Name the button “CreateXML”. Double click on the button. In the event handling routine type the code below. I am trying to get all the rows of the authors table and save them in an .xml file.

Dim mydb As New PubsDataContext

Dim myauthors = <authors>

<%= From author In mydb.authors Select _
<author>
<lname>

<%= author.au_lname %>
</lname>
<fname>

<%= author.au_fname %>
</fname>

<phone>

<%= author.phone %>
</phone>
<address>

<%= author.address %>
</address>
<city>

<%= author.city %>
</city>
<state>

<%= author.state %>
</state>

<zip>

<%= author.zip %>
</zip>

<contract>

<%= author.contract %>
</contract>

</author> %>

</authors>

myauthors.Save(“c:\nikos\myxmldata.xml”)

12) As you see what I am doing is pretty simple. I type XML statements that form the structure of my XML document using LINQ to SQL expressions to query the relational data.If you run your application and click the “CreateXML” button, you will have in your specified folder (c:\nikos\ , in my case) a document called “myxmldata.xml”. Browse to the specified folder and select the “myxmldata.xml”, right-click on this file and Open it with Internet Explorer. You will see all 23 rows of data that resides in the Authors table to be represented as nodes in our newly created xml file.

13) So now we are on the final part of our solution. Yes you guessed it!!! We will place another button on the form. We will call it “SaveXMLtoExcel”. By clicking on this button, all the data from the xml file should be placed in the excel file we created in the first steps of this example.

14) Double click on the “SaveXMLtoExcel” button and in the event handling routine ,and paste the code below. Do no forget to include the Imports System.Xml namespace at the beginning of your Form1.vb file.

In this routine I get an instance of the existing excel file and by using the XMLReader class and the ReadXml to read the xml data into a dataset.Then I use the dataset to populate the rows and columns of the specific worksheet.

Dim xlApp As excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet

Dim ds As New DataSet
Dim xmlFile As XmlReader
Dim i, j As Integer

xlApp = New excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open(“c:\nikos\mydataxml.xls”)
xlWorkSheet = xlWorkBook.Sheets(“sheet1”)

xmlFile = XmlReader.Create(“c:\nikos\myxmldata.xml”, New XmlReaderSettings())
ds.ReadXml(xmlFile)

For i = 0 To ds.Tables(0).Rows.Count – 1
For j = 0 To ds.Tables(0).Columns.Count – 1
xlWorkSheet.Cells(i + 1, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

xlWorkSheet.SaveAs(“C:\nikos\mydataxml.xls”)
xlWorkBook.Close()
xlApp.Quit()
xlWorkBook = Nothing
xlApp = Nothing

GC.Collect()

15) Run your application. Hit the “SaveXMLtoExcel” button. Open the excel file and hopefully you will see 23 rows of data that represent authors data

I am sure you can realise that you could combine the whole functionality in one button or routine, but I broke the code into various parts so it is easier to read. I think we have covered lots of things in this post. We talked about XML,XML literals,LINQ to SQL and how to create excel file from XML data.

Hope it helps!!!

Happy reading!!!