1 Previous Next 

OpenXml: Using Linq to XML to create an excel spreadsheet


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





1 Previous Next