Create an XML file using LINQ and import XML data into an Excel spreadsheet March 31, 2009
Posted by fofo in LINQ, MS Office, VB 9.0, Visual Studio 2008.Tags: Excel, Linq to Sql, VB 9.0, Visual Studio 2008, XML, XML Literals
6 comments
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!!!
XML Literals and Visual basic 9.0 November 21, 2008
Posted by fofo in VB 9.0, Visual Studio 2008, XML.Tags: VB 9.0, XML, XML Literals
1 comment so far
In this post I would like to demonstrate with some examples a new feature of Visual Basic 9.0 which is XML Literals.C# does not support XML Literals.
If you want to find out about the reasons that VB chose to support XML Literals and C# did not, click here
If you want to look more into this have a look here as well.
Basically if you are a C# fanatic (as many people out there), If you have a project that relies heavily on XML, you will be better off if you choose VB 9.0 instead. Or if it is a mixed project you can use VB 9.0 in parts of a project where literal XML would be useful, and C# in the parts of the project…
In the past If we wanted to have an XML document from code we had to use API’s to create elements and attach attributes…
In VB 9.0 you can create XML documents in code without using strings or APIs.
We need to start a new project in Visual Studio to examine some of these new features.
1) Launch Visual Studio 2008
2) Start a new project, choose VB as your preferred language and then choose Console Application as your template
3) Name the project “XmlLiterals” or any other name of your choice
Inside the Module1.vb ,place this code
Dim thexml = <books>
<book paperback=”yes” cdrom=”no”>23</book>
</books>Console.WriteLine(thexml)
Console.ReadLine()
If you run the application it will not break and it will print our small XML document on the screen.
We could re-write the above snippet of code differently. Comment out the lines above.
Have a look at the code below
Dim thexml = <books>
<%=<book paperback=”yes” cdrom=”no”>23</book> %>
</books>Console.WriteLine(thexml)
Console.ReadLine()
and paste it in the Module1.vb
If you run the application again, you will get the same output.
The syntax in red color is called embedded code block.
That sort of syntax allows us to replace the xml code with other values such as variables.
I would like to add a class in my module in order to demonstrate XML Literals better.
Paste the code below after the code for the module ends (End Module)
Class Book
Private _author As String
Private _pub_year As String
Private _stock As Integer
Public Property Author() As String
Get
Return _author
End Get
Set(ByVal value As String)
_author = value
End Set
End Property
Public Property Pub_Year() As String
Get
Return _pub_year
End Get
Set(ByVal value As String)
_pub_year = value
End Set
End Property
Public Property Stock() As Integer
Get
Return _stock
End Get
Set(ByVal value As Integer)
_stock = value
End Set
End Property
End Class
This class is called Book and it has 3 properties, author,pub_year,stock.
Now what I need to do is to create a collection of these objects and assign them some values.
I can do that by using a function which I call Createbooks.
Here is the code. It is pretty simple really…
Function CreateBooks() As List(Of Book)
Dim book1 As New Book() With {.Author = “Ian McEwan”, .Title = “Atonement”, .Pub_Year = 2002, .Stock = 23}
Dim book2 As New Book() With {.Author = “Sylvia Plath”, .Title = “The Colossus”, .Pub_Year = 1980, .Stock = 19}
Dim book3 As New Book() With {.Author = “Jane Austen”, .Title = “Pride and Prejudice”.Pub_Year = 1976, .Stock = 11}
Dim returnList As New List(Of Book)
returnList.Add(book1)
returnList.Add(book2)
returnList.Add(book3)
Return returnList
End Function
Place the function inside your module, just after Sub Main().
Comment out these lines
‘ Dim thexml = <books>
‘ <%= <book paperback=”yes” cdrom=”no”>23</book> %>
‘ </books>
Inside your Sub Main() store the list of objects in a variable.
So basically type something like this
Dim mybooks = CreateBooks()
Then in order to find all the values for each book i need to write something like this
Dim bookitems = From book In mybooks _
Select <Book Author=<%= book.Author %> Title=<%= book.Title %> Published_year=<%= book.Pub_Year %>> Stock=<%= book.Stock %></Book>
Dim theXML = <?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>
<Books>
<%= bookitems %>
</Books>
Console.WriteLine(theXML)
Console.ReadLine()
After I retrieve all the elements I create a new XML document and place them in there.
Run your application. In the console window you will see the Books element and then 3 seperate book elements each with attribute and values.
In order to find out what kind of object is the “theXML” variablejust add this line of code
Console.WriteLine(TypeName(theXML))
If your run your application you will see that .Net refers to it as XDocument.
Comment out the line above.
So what if I wanted to print one item in the screen? What if I wanted to get hold of the name of the author of the first book?
This is how I navigate the XDocument object tree.
Dim myfirstbookauthor As String = theXML.<Books>.<Book>(0).@Author
Console.WriteLine(myfirstbookauthor)
Run you application and will get “Ian McEwan”.
If you wanted to loop through all the items in the object tree and get a specific attribute(like the book’s title), this is one way to do it.
For Each book In theXML.<Books>.<Book>
Console.WriteLine(book.@Title)
Next
Make sure that you comment these lines out, so you do not get confused
‘Dim myfirstbookauthor As String = theXML.<Books>.<Book>(0).@Author
‘Console.WriteLine(myfirstbookauthor)
Run you application. In the Console window you will see these values,
“Atonement”
“The Colossus”
“Pride and Prejudice”
To recap, VB 9.0 treats XML Literals like String Literals, number Literals and allows you to work directly with XML.
If you need the source code email me…
Hope it helps!!!
DOT NET RULES 