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!!!
Manage files,folders, special folders with VB.Net and window forms December 1, 2008
Posted by fofo in general .net, VB 2005, VB 9.0, Visual Studio 2005, Visual Studio 2008.Tags: files, folders, System.IO, VB 2005, VB 9.0, windows forms
10 comments
Hello all !!!
I came across a project today that it was required that we manage files,folders,special folders through windows forms.
Basically that specific functionality was already implemented with scripts. Some of the tasks required were implemented with WMI and some using the Windows Script Host FileSystemObject
If you want to find more scripts like that visit the Script Center
In this post I will show you how to implement file,folder related tasks, by merely using the classes and methods that are already written for us in the FCL in .Net and reside in the System.IO namespace.
I will create an example so we can follow step by step how to accomplish a similar task
1) Launch Visual Studio 2005/2008.
2) Create a new Windows Forms project and choose VB as your development language.
3) Save your project-solution with a name of your choice.
4) Add a reference to the System.IO namespace from your project
5) Add a button and a listbox from the Toolbox on the form. Name the button “FindFiles”
6) In this first example we will see what methods to use to retrieve all files from a folder
7) Double click the button and in the event handling routine that is automatically created type this code
Dim thefiles() As String
‘change the path to a path that exists in your machine
thefiles = Directory.GetFiles(“C:\Acer\Empowering Technology”)
Me.ListBox1.DataSource = thefiles
If you wanted to get files with a specific type e.g all .dll in tha folder you can replace this line
thefiles = Directory.GetFiles(“C:\Acer\Empowering Technology”)
with this
thefiles = Directory.GetFiles(“C:\Acer\Empowering Technology”, “*.dll”)
8)Build your solution and then run your application. Click on the button and see all the files from this particular folder to get listed inside the ListBox control. We use the Directory Class and the GetFiles method of this class.
9) Add another button in the for. Name it “FindFolders”. As you can imagine we will find all the subfolders inside that folder.Double click the button and in the event handling routine that is automatically created type this code
Dim thefolders() As String
thefolders = Directory.GetDirectories(“C:\Acer\Empowering Technology”)
Me.ListBox1.DataSource = thefolders
10) Run your application. Click on the “FindFolders” button and see all the subfolders from this particular folder to get listed inside the ListBox control
11) Let’s see now how we can access special folders like “My Pictures”, “My Documents”.Add another button in your form and name it “GetSpecialFolders”.We will try to get all the files inside the My Documents special folder.
12) Double click the button and in the event handling routine that is automatically created type this code
Dim thefiles() As String
Dim specialfolder As String
specialfolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
thefiles = Directory.GetFiles(specialfolder)
Me.ListBox1.DataSource = thefiles
As you can see we use the Environment.SpecialFolder.MyDocuments toget access to that special folder.
13) In this final example I want to show you how to find information like file size for all the files that are inside the “C:\Acer\Empowering Technology” folder. We need to find the file size, the time the file was created and the extension of the file.
In order to to do that we must place 3 textboxes and 3 labels on our form. Change the text property of the first label to “FileSize” .Change the text property of the second label to “File Created” .Change the text property of the third label to “Extension”.
We will place the values we find for each file inside the textboxes.
We will use the SelectedIndexChanged event of the ListBox control to write our code. Just click on the Listbox control from the Form Designer. In the event routine that is created type the following lines.
Dim theselectedfile As String
theselectedfile = Me.ListBox1.SelectedItem.ToString
Dim myfile As New FileInfo(theselectedfile)
Me.TextBox1.Text = myfile.Length.ToString()
Me.TextBox2.Text = myfile.CreationTime.ToString()
Me.TextBox3.Text = myfile.Extension.ToString()
14) Run your application and click on the button “FindFiles”. See the listbox populated with files. Just click on several files and see the results for each file inside the textboxes.
That is all folks!!! Hope it helps.Make sure you explore the other methods and classes in the System.IO namespace.
If you need the source code then you can email me or just leave a comment.
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!!!
Nullable data types in Visual Basic 9.0 November 17, 2008
Posted by fofo in LINQ, VB 9.0, Visual Studio 2008.Tags: LINQ, VB 9.0, Visual Studio 2008
4 comments
In this post I would like to talk about language improvements in visual basic 9.0 and more specifically about nullable data types.
The largest improvement in Visual basic 9.0 is LINQ. So all these enhancements that were made in the VB 9.0 language (the same goes for c# 3.0 enhancements) had LINQ in mind and how LINQ will be able to work.
Nullable data types enable us to create data types e.g integers and set them-initialise them to the value nothing, null.
In our database design we often have tables where fields are allowed to have NULL.
Prior to Visual Basic 9.0 one should turn the null value from the database into a zero value in the application
Nullable data types help us to avoid this mismatch in LINQ to SQL.
The problem is that representing a null (nothing) value with zero is wrong, because null means something that is uknown and by setting it to zero, well you set it to something that is known….
In order to demonstrate this enhancement-new feature I will create a simple Asp.Net application using VB and Visual Studio 2008.
1) Launch Visual Studio 2008
2) Create a new project (ASP.NET web application) and choose VB as the project’s language
3) Name it “nullabledata” or any other name you want
4) Add a button in the default.aspx page
5) Double click in the button
6) In the event handling routine that is created just type the following
Dim mynum As Integer? = 4
Dim myothernum As Integer? = Nothing
Dim myres As Integer = 0
myres = mynum + myothernum
Response.Write(IsNothing(myres).ToString())
by adding the ? operator after the data type, we indicate that the variable mynum can be set to nothing.
We declare another variable in this statement
Dim myothernum As Integer? = Nothing
This can only be valid in VB 9.0
Try and run your application by hitting F5.
Click on the button. You will receive an error – exception.
the problem is that the variable with the name myres is not nullable.
When you add 4 with an uknown value you do not get 4. You get an uknown value.
The solution to this problem is to create another variable that is nullable this time.
Dim myfinalres As Integer?
comment out these lines
myres = mynum + myothernum
Response.Write(IsNothing(myres).ToString())
and type these lines
myfinalres = mynum + myothernum
Response.Write(IsNothing(myfinalres).ToString())
Now if you run the application again and press the button you will receive the value of Nothing.
Hope it helps!!!
DOT NET RULES 