For this example we are going to create a Excel 2007 spreadsheet using the Microsoft OpenXml Sdk and Linq to XML.
To start with lets create a new Windows forms app which targets the .Net Framework 3.5. Add a Linq to Sql design surface to your project and name it Northwind and drag the Northwind Products table on to the surface. On the windows form I added a DataGridview to display the data we are going to export to excel. We also need a button named btnExport on the form.
To create a excel spreadsheet we need to use the openxml sdk to create a spreadsheet document, workbook, worksheet, and a string table.
Using doc = SpreadsheetDocument.Create("Export.xlsx", SpreadsheetDocumentType.Workbook)
Dim workbook = doc.AddWorkbookPart
Dim stringTable = workbook.AddNewPart(Of SharedStringTablePart)()
Dim worksheet = workbook.AddNewPart(Of WorksheetPart)()
The worksheet, workbook, and string table are xml documents contained inside a package. Before we get to far we need to import a few xml namespaces
Imports <xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
Now that we imported the name space we can use some of the new xml features in VB 2008 to create the xml documents. Since we are not using a string table we just need a blank xml file
Dim xmlStringTable = <sst></sst>
The workbook xml needs to relate the spreadsheet with its id
Dim xmlWorkbook = <workbook>
<sheets>
<sheet name="Exported" sheetId="1" r:id=<%= sheetId %>></sheet>
</sheets>
</workbook>
Note the <%= sheetId %> allows you to get data from a variable
Finally we need to create the worksheet. In the worksheet we set the column widths and use a linq query to populate the data.
Dim xmlWorkSheet = <worksheet>
<sheetFormatPr defaultRowHeight="15"/>
<cols>
<col min="1" max="1" width="30" bestFit="1" customWidth="1"/>
<col min="2" max="2" width="10" bestFit="1" customWidth="1"/>
</cols>
<sheetData>
<row>
<c t="inlineStr">
<is>
<t>Product Name</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Unit Price</t>
</is>
</c>
</row>
<%= From p In db.Products Select _
<row>
<c t="inlineStr">
<is>
<t><%= p.ProductName %></t>
</is>
</c>
<c>
<v><%= p.UnitPrice %></v>
</c>
</row> %>
</sheetData>
</worksheet>
Here is the function for writing the xml to the file
Sub WriteXmlToPart(ByVal part As OpenXmlPart, ByVal x As XElement)
Dim fs As New IO.StreamWriter(part.GetStream, New System.Text.UTF8Encoding)
Dim xmlWriter As New Xml.XmlTextWriter(part.GetStream, New UTF8Encoding)
xmlWriter.Formatting = Xml.Formatting.Indented
Dim enc As New UTF8Encoding
xmlWriter.WriteStartDocument()
x.WriteTo(xmlWriter)
xmlWriter.WriteEndDocument()
xmlWriter.Flush()
xmlWriter.Close()
End Sub
Here is the complete listing for program
Imports Microsoft.Office.DocumentFormat.OpenXml.Packaging
Imports System.Text
Imports <xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
Public Class Form1
Dim bs As New BindingSource
Dim db As New NorthwindDataContext
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
bs.DataSource = From p In db.Products _
Select p.ProductName, p.UnitPrice
DataGridView1.DataSource = bs
End Sub
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
Using doc = SpreadsheetDocument.Create("Export.xlsx", SpreadsheetDocumentType.Workbook)
Dim workbook = doc.AddWorkbookPart
Dim stringTable = workbook.AddNewPart(Of SharedStringTablePart)()
Dim worksheet = workbook.AddNewPart(Of WorksheetPart)()
Dim sheetId = workbook.GetIdOfPart(worksheet)
'create the string table
Dim xmlStringTable = <sst></sst>
WriteXmlToPart(stringTable, xmlStringTable)
'create the workbook
Dim xmlWorkbook = <workbook>
<sheets>
<sheet name="Exported" sheetId="1" r:id=<%= sheetId %>></sheet>
</sheets>
</workbook>
WriteXmlToPart(workbook, xmlWorkbook)
'create the spreadsheet
Dim xmlWorkSheet = <worksheet>
<sheetFormatPr defaultRowHeight="15"/>
<cols>
<col min="1" max="1" width="30" bestFit="1" customWidth="1"/>
<col min="2" max="2" width="10" bestFit="1" customWidth="1"/>
</cols>
<sheetData>
<row>
<c t="inlineStr">
<is>
<t>Product Name</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Unit Price</t>
</is>
</c>
</row>
<%= From p In db.Products Select _
<row>
<c t="inlineStr">
<is>
<t><%= p.ProductName %></t>
</is>
</c>
<c>
<v><%= p.UnitPrice %></v>
</c>
</row> %>
</sheetData>
</worksheet>
WriteXmlToPart(worksheet, xmlWorkSheet)
End Using
End Sub
Sub WriteXmlToPart(ByVal part As OpenXmlPart, ByVal x As XElement)
Dim fs As New IO.StreamWriter(part.GetStream, New System.Text.UTF8Encoding)
Dim xmlWriter As New Xml.XmlTextWriter(part.GetStream, New UTF8Encoding)
xmlWriter.Formatting = Xml.Formatting.Indented
Dim enc As New UTF8Encoding
xmlWriter.WriteStartDocument()
x.WriteTo(xmlWriter)
xmlWriter.WriteEndDocument()
xmlWriter.Flush()
xmlWriter.Close()
End Sub
End Class
