VB-Tips

There is not a DataGrid or DataGridView in Windows Presentation Foundation.  Use the ListView to display data in a table.  In the window's Xaml define the listview and the fields you want displayed.  The code behind file is where you get the data from the Northwind database and bind the listview.

The Windows Xaml

<Window x:Class="Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="WPF1" Height="300" Width="300"
    >
  <Grid>
    <ListView Margin="25,22,30,48" Name="ListView1" >
      <ListView.View>
        <GridView >
          <GridViewColumn Header ="Last Name" DisplayMemberBinding="{Binding LastName}" Width="100"></GridViewColumn>
          <GridViewColumn Header ="First Name" DisplayMemberBinding="{Binding FirstName}" Width="100"></GridViewColumn>
        </GridView>
      </ListView.View>
    </ListView>
  </Grid>
</Window>

The Code Behind

Imports System.Data

' Interaction logic for Window1.xaml
Partial Public Class Window1
    Inherits System.Windows.Window

    Public Sub New()
        InitializeComponent()
        Dim dt As New DataTable

        Dim strConn As String = _
           "Server = .\sqlexpress;Database = NorthWind; Integrated Security = SSPI;"
        Dim conn As New SqlConnection(strConn)
        Dim da As New SqlDataAdapter("Select LastName, FirstName from Employees", conn)
        da.Fill(dt)
        ListView1.DataContext = dt
        Dim bind As New Binding
        ListView1.SetBinding(ListView.ItemsSourceProperty, bind)
    End Sub

End Class

Imports System.Speech.Synthesis

Public Class Form1

    Private Sub btnSay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSay.Click
        Dim spk As New SpeechSynthesizer
        spk.SelectVoice(lstVoice.SelectedItem.ToString)
        spk.Speak(txtSay.Text)
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim spk As New SpeechSynthesizer
        For Each voice As InstalledVoice In spk.GetInstalledVoices
            lstVoice.Items.Add(voice.VoiceInfo.Name)
        Next
        lstVoice.SelectedIndex = 0
        txtSay.Text = "Hello World!"
    End Sub
End Class

Windows Presentation Foundation (WPF) foundation is for building applications and experiences in Windows Vista that blend the application UI, documents, and media content. In this series of blog entries we willl start to explore some of the improvements to data binding in WPF. You will need to have the .Net Framework 3.0, Windows Vista SDK, and Visual Studio 2005 extensions for WPF and WCF installed for this sample.

For this example we will start off with a Windows Application (WPF). In the forms XMAL we will add a stack panel to hold a label and scrollbar. The label's content will be bound to the scrollbar's value so as we move the scrollbar the value will be displayed in the label.

In the Lets set up a DataContext which is bound to the scrollbar .

Now we can bind the label's content to the scrollbars value

The complete XMAL for the Window

Office 2007 saves office documents in a new Open XML format.  This new office format is basically a zip file with xml inside it.  John Tunnicliffe has created an ExcelPackage class for creating excel documents. You can download it from the CodePlex Website. It uses the .Net 3.0 package class for creating the excel files.  Here is a sample that uses the ExcelPackage class.  Add Imports OpenOfficeXml to the top of the code file.

        If IO.File.Exists("Test.xlsx") Then IO.File.Delete("Test.xlsx")
        Dim fi As New IO.FileInfo("Test.xlsx")
        Using pack As New ExcelPackage(fi)
            Dim ws As ExcelWorksheet = pack.Workbook.Worksheets.Add("DataGridView")
            ws.Cell(1, 1).Value = "Product Name"
            ws.Column(1).Width = 30
            ws.Cell(1, 2).Value = "Price"
            ws.Column(2).Width = 10

            For r As Integer = 0 To dt.Rows.Count - 1
                For c As Integer = 0 To DataGridView1.Columns.Count - 1
                    ws.Cell(r + 2, c + 1).Value = DataGridView1.Item(c, r).Value.ToString.Replace("'"c, "")
                Next
            Next
            pack.Workbook.Properties.Author = "Ken Tucker"
            pack.Workbook.Properties.Title = "DataGridView Export"
            pack.Save()
        End Using
        MessageBox.Show("Done")

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