Quickly Import and Export Excel Data with LINQ to XML

Quickly Import and Export Excel Data with LINQ to XML

Rate This
  • Comments 94

One really handy thing to do with relational data is to import and export it to Excel. This can be a total snap using LINQ to XML in Visual Basic 2008. For example, let's say I want to import data from an Excel spreadsheet into a local database file like SQL Compact Edition. I also want to be able to export that data back into an Excel spreadsheet, basically allowing me to edit the data from either my application or Excel.

The trick really is getting the format of the Excel spreadsheet as XML and using that sheet as a template to populate your data. First open Excel (for this example I'll use the Excel 2003 format so you can use Excel 2007 or 2003 to follow along). Next let's enter some column headers, for my example I have a list of customers I want to import/export so the fields I'll want are Abbrev, Name, Phone, Country. It's also helpful to enter one line of dummy data (you'll see why in a second). So here' what we've got:

Now what I want to do is get all the customers stored in my little SQL CE local database into this spreadsheet.

(As a side note, you can easily add a SQL CE local database file to your project by selecting the "Local Database" new item template in Visual Studio 2008. You can easily access the data in SQL CE sdf files by adding a new datasource (Data --> Add New Datasource), adding a new connection, and change the data source to "Microsoft Compact SQL Server 3.5" then locate or create the .sdf file. Then you can create typed Datasets like normal.)

So I have a form where I just dragged my CustomersDataSet that I created from my local database file onto it as a DataGridView. This also sets up the BindingSource and BindingNavigator toolstrip like we'd expect. I added a couple buttons to the toolstrip one called "Import" and one called "Export".

Now in order to get the Excel spreadsheet into an XML format you can simply "Save As.." and select "XML Spreadsheet 2003 (.xml)" as the file type. Now navigate to that file and open it with notepad, select and copy all the text into your clipboard. Open up your Visual Basic program, type "Dim sheet =" and then paste the contents of your clipboard directly into the editor. It should look something like this (I collapsed some of the sections we're going to ignore):

Next we need to import the namespaces in the spreadsheet. This is necessary so that when we create and query our XML, Visual Basic will know the correct namespaces we're working with. So at the top of code file for our form we'll add:

Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">

Imports <xmlns:o="urn:schemas-microsoft-com:office:office">

Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">

Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
Imports <xmlns:html="http://www.w3.org/TR/REC-html40">

Now we can write a query that will take all of the customers in our SQL CE database file (technically the typed DataSet that was created for us) and populate the spreadsheet. You should see your dummy data in the XML above. Cut that out (ctrl-X) because we're going to replace that with all our customers using embedded expressions like so:

Dim customers = _

    From customer In Me.CustomersDataSet.Customers _

    Where customer.RowState <> DataRowState.Deleted _

        AndAlso Not customer.IsAbbrevNull _

    Order By customer.Abbrev _

    Select <Row>

               <Cell><Data ss:Type="String"><%= customer.Abbrev %></Data></Cell>

               <Cell><Data ss:Type="String"><%= If(customer.IsNameNull, "", customer.Name)%> </Data></Cell>

               <Cell><Data ss:Type="String"><%= If(customer.IsPhoneNull, "", customer.Phone)%> </Data></Cell>

               <Cell><Data ss:Type="String"><%= If(customer.IsCountryNull, "",customer.Country)%> </Data></Cell>

           </Row>

I'm just selecting the data inside my typed Dataset which is populated in the load of the form, making sure not to grab any deleted rows or rows where the abbreviation is NULL. The customers variable above becomes a collection of XElements (IEnumerable(Of XElement)) and we simply pop that back into the sheet, replacing the dummy line we removed:

<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount=<%= customers.Count + 1 %> x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">

<Row ss:StyleID="s62">

               <Cell><Data ss:Type="String">Abbrev</Data></Cell>

               <Cell><Data ss:Type="String">Name</Data></Cell>

               <Cell><Data ss:Type="String">Phone</Data></Cell>

               <Cell><Data ss:Type="String">Country</Data></Cell>

       </Row>

       <%= customers %>

</Table>

Also notice that I had to adjust the ss:ExpandedRowCount attribute as well to include the count of our customers plus one for the header row. Now we can write the results out to disk and start Excel:

sheet.Save("C:\Customers.xml")

Process.Start("Excel.exe", "C:\Customers.xml")

Cool!

Okay so now we want to be able to import this data as well. If any rows were added or edited we want that data back in our database. I'm going to use the Abbrev field as the unique identifier of our data. The query we need to write will group join (outer join) the current (non-deleted) rows in our CustomerDataSet to the rows here in the spreadsheet. That way if the resulting items in the collection contain a group (of one CustomerDataRow in this case), then we know that the CustomerDataRow needs to be updated, otherwise we know it's a new customer that needs to be added. We also need to skip the first row because that's our header row in the spreadsheet, and I'm doing an extra check for empty cells.

Dim sheet = XDocument.Load("C:\Customers.xml")

 

'Skip the first row because that is the header row in the spreadsheet.

'Notice also that we're eliminating deleted rows from the group join.

'We'll use the existence of the group collection to determine if the

' row needs to be added to the database.

Dim customers = From xmlCustomer In sheet...<Row> _

                   Let Abbrev = xmlCustomer.<Cell>(0) _

                   Let Name = xmlCustomer.<Cell>(1) _

                   Let Phone = xmlCustomer.<Cell>(2) _

                   Let Country = xmlCustomer.<Cell>(3) _

                Where _

                   Abbrev IsNot Nothing AndAlso _

                   Name IsNot Nothing AndAlso _

                   Phone IsNot Nothing AndAlso _

                   Country IsNot Nothing _

                Group Join customer In _

                    (From row In Me.CustomersDataSet.Customers _

                       Where row.RowState <> DataRowState.Deleted _

                             AndAlso Not row.IsAbbrevNull _

                       Select row) _

                    On customer.Abbrev Equals Abbrev _

                    Into Group _

                Select _

                    Abbrev = Abbrev.Value, _

                    Name = Name.Value, _

                    Phone = Phone.Value, _

                    Country = Country.Value, _

                    Group _

                Skip 1

 

 

'Add or update the customer row depending on if the

' group collection exists.

For Each customer In customers

    Dim r As CustomersDataSet.CustomersRow

    If customer.Group.Count = 0 Then

        'Add new row ands set the customer abbreviation.

        ' This field is used to determine uniqueness of the data

        r = Me.CustomersDataSet.Customers.NewCustomersRow()

        r.Abbrev = customer.Abbrev

    Else

        r = customer.Group(0)

    End If

 

    r.Name = customer.Name

    r.Phone = customer.Phone

    r.Country = customer.Country

 

    If r.RowState = DataRowState.Detached Then

        Me.CustomersDataSet.Customers.AddCustomersRow(r)

    End If

Next

Now we can make changes to the spreadsheet, import the data into our local database, or make changes to our local database and export those changes to Excel. I've attached the complete example built with Visual Basic 2008. You'll also need Excel 2003 or higher to view the spreadsheet. It's worth reiterating that you don't actually need Excel to create the spreadsheet in Visual Basic via code as long as you have the XML format, you just need it if you want to open it in Excel later. Remember we're not using automation, we're just creating XML in a specific format.

Enjoy!

Attachment: ImportExportExcel.zip
Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
  • Hi Beth

    thank You for the Forms over video series VB2005

    But my problem is that how can i export excel data to datagridview in visualbasic2005 with a checklist column in the grid...

    Thanks...

  • Beth,

    This is great, but for the export it doesn't seem to work if your database contains null values.  The dataset returns a conversion error.  How would one deal with this?

    Thanks,

    Chris

  • Hi Chris,

    You can check for nulls using the If() function in the export query:

           Dim customers = _

               From customer In Me.CustomersDataSet.Customers _

               Where customer.RowState <> DataRowState.Deleted _

                   AndAlso Not customer.IsAbbrevNull _

               Order By customer.Abbrev _

               Select <Row>

                          <Cell><Data ss:Type="String"><%= customer.Abbrev %></Data></Cell>

                          <Cell><Data ss:Type="String"><%= If(customer.IsNameNull, "", customer.Name) %></Data></Cell>

                          <Cell><Data ss:Type="String"><%= If(customer.IsPhoneNull, "", customer.Phone) %></Data></Cell>

                          <Cell><Data ss:Type="String"><%= If(customer.IsCountryNull, "", customer.Country) %></Data></Cell>

                      </Row>

    HTH,

    -B

  • Thanks for the prompt response Beth!  I'm new to VB.NET and was looking at "Iif", but that seems to evaluate the false condition (hence throwing an exception) even if the condition is true.  I wasn't aware that you could use "If" as a traditional ternary operator.  Your videos are great!

    PS - Have you covered refering to "named" cells on import (i.e. give the users the ability to move columns around and programatically know where they are still on import)?

  • Beth-

    I'm new to Visual Basic and enjoyed your videos.  I'm trying to save some data to Excel and would like to base it on your Linq example. But my data is captured in an array instead of a database.  Can I create a Query similar to your customer query from an array rather than a database?

    My array:

    CapturedData(X,1) contains an ID

    CaputerdData(X,2) Contains Milliseconds

    CapturedData(X,3) Contains Acceleration X

    CapturedData(X,3) Contains Acceleration Y

    CapturedData(X,3) Contains Acceleration Z

    Thanks!

  • I tried using the sample code to create a transfer of my own.  I am getting an error on the group join stating that the Range Variable "sightingslist" hides a variable in an enclosing block  or a range variable previously defined in the query expression.  I think that I have used the same name to difine two objects but I can't figure out which one needs to be changed.  

    Can you teel me where the reference to "customer" in your example comes from.  I can't see where it is defined.  

    here is an extract of my code.

           Dim SightingsList = From xmlSightingsList In sheet...<Row> _

                                      Let CheckList_IdNo = xmlSightingsList.<Cell>(0) _

                                      Let Line_Seq_No = xmlSightingsList.<Cell>(1) _

                                      Let Line_Type = xmlSightingsList.<Cell>(2) _

                                      Let Common = xmlSightingsList.<Cell>(3) _

                                      Let Scientific = xmlSightingsList.<Cell>(4) _

                                      Let MBP_Species_IdNo = xmlSightingsList.<Cell>(5) _

                                      Let Sighted = xmlSightingsList.<Cell>(6) _

                                      Let Sighted_Date = xmlSightingsList.<Cell>(7) _

                                     Where _

                                      CheckList_IdNo IsNot Nothing _

                                     Group Join sightingslist In _

                                      (From row In Me.SightingsListDataSet.SightingsList _

                                         Where row.RowState <> DataRowState.Deleted _

                                          AndAlso Not row.IsChecklist_IdNoNull _

                                         Select row) _

                                       On sightingslist.Checklist_IdNo Equals CheckList_IdNo And _

                                          sightingslist.Line_Seq_No Equals Line_Seq_No _

                                       Into Group _

                                      Select _

                                       CheckList_IdNo = CheckList_IdNo.Value, _

                                       Line_Seq_No = Line_Seq_No.Value, _

                                       Line_Type = Line_Type.Value, _

                                       Common = Common.Value, _

                                       Scientific = Scientific.Value, _

                                       MBP_Species_IdNo = MBP_Species_IdNo.Value, _

                                       Sighted = Sighted.Value, _

                                       Sighted_Date = Sighted_Date.Value, _

                                       Group _

                                      Skip 1

    Thanks

  • Hi Beth,

    I resolved the declarations issue.  Thanks for the example.

    Jim

  • hello my name is enkhbazar how to imported excel data in my sql database for vb.net please help me

  • Nice article Beth, although I gave up trying to convert to C#.

    I have one question. How do I get comments out of the XML. I have this piece of xml which has comments on the cell

    <Cell>

    <Data ss:Type="String">Cell value</Data>

    <Comment ss:Author="name">

    <ss:Data xmlns="http://www.w3.org/TR/REC-html40">

    <B>

    <Font html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">name</Font>

    </B>

    <Font html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">Comments here</Font>

    </ss:Data>

    </Comment>

    <NamedCell ss:Name="_FilterDatabase"/>

    </Cell>

    How can I get those comments out in the query? I tried this but it didn't work

    Let Comments = xmlCustomer.<Cell>(8).<Comments>(0) _

  • Hi Jon,

    Assuming you have the right xml namespace Imports at the top, I think what you want is something more like this:

    Dim comments = xmlcustomer...<Comment>.<ss:Data>.<html:Font>

           For Each comment In comments

               Console.WriteLine(comment.Value)

           Next

    HTH,

    -B

  • hi Beth,

    Your work is very much appreciated. Could you tell me how to get the colour of the cell from this Excel spreadsheet please ?

    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="5" x:FullColumns="1"

      x:FullRows="1">

      <Row>

       <Cell><Data ss:Type="String">AgentID</Data></Cell>

       <Cell><Data ss:Type="String">AgentName</Data></Cell>

      </Row>

      <Row>

       <Cell ss:StyleID="s62"><Data ss:Type="String">1</Data></Cell>

       <Cell ss:StyleID="s62"><Data ss:Type="String">Phil</Data></Cell>

      </Row>

      <Row>

       <Cell ss:StyleID="s64"><Data ss:Type="String">2</Data></Cell>

       <Cell ss:StyleID="s64"><Data ss:Type="String">Will</Data></Cell>

      </Row>

      <Row>

       <Cell ss:StyleID="s65"><Data ss:Type="String">3</Data></Cell>

       <Cell ss:StyleID="s65"><Data ss:Type="String">Adam</Data></Cell>

      </Row>

      <Row>

       <Cell><Data ss:Type="String">4</Data></Cell>

       <Cell><Data ss:Type="String">Liz</Data></Cell>

      </Row>

     </Table>

    Thanks

  • I have discovered the answer, but it is not well documented.

    Dim _book = XDocument.Load(pstrFilePath)

           Try

               Dim _sheets = From x In _book...<Worksheet>

               Dim Agents = From x In _sheets(0)...<Row> _

                                  Let AgentID = x.<Cell>(0) _

                                  Let AgentName = x.<Cell>(1) _

                                  Let CellColour = x.<Cell>.@ss:StyleID _

                            Select _

                                  AgentID = AgentID.Value, _

                                  AgentName = AgentName.Value, _

                                  CellColour = CellColour _

                           Skip (1)   'Skip the first row because that is the header row.

               For Each row In Agents

                   Dim _lstitem As New ListBoxItem

                   Select Case row.CellColour

                       Case "s62"

                           _lstitem.Foreground = Brushes.Red

                       Case "s64"

                           _lstitem.Foreground = Brushes.LightGreen

                       Case "s66"

                           _lstitem.Foreground = Brushes.Orange

                       Case Else

                           _lstitem.Foreground = Brushes.Black

                   End Select

                   _lstitem.Content = row.AgentID.ToString & " " & row.AgentName.ToString

                   lstAgent.Items.Add(_lstitem)

               Next

           Catch ex As Exception

           End Try

  • 'Beth,

    '

    'Thank you for presenting this technique to BayUG.NET.  I took your ball and ran with it.

    ' Below is a class that will translate any DataSet into an Excel Xml document:

    Option Strict Off

    Imports System.Data

    Imports System.Linq

    Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">

    Imports <xmlns:o="urn:schemas-microsoft-com:office:office">

    Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">

    Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

    Public Class DataSetToExcelXml

       Public Sub New()

       End Sub

       Public Shared Function ConvertDataSetToExcelXDocument(ByVal ds As DataSet) As XDocument

           Dim xmlExcel = <?xml version="1.0"?>

                          <?mso-application progid="Excel.Sheet"?>

                          <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

                              xmlns:o="urn:schemas-microsoft-com:office:office"

                              xmlns:x="urn:schemas-microsoft-com:office:excel"

                              xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

                              xmlns:html="http://www.w3.org/TR/REC-html40">

                              <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

                                  <Author><%= Environment.UserName %></Author>

                                  <LastAuthor><%= Environment.UserName %></LastAuthor>

                                  <Created><%= DateTime.Now.ToString("s") %></Created>

                                  <Version>11.9999</Version>

                              </DocumentProperties>

                              <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

                                  <WindowHeight>10000</WindowHeight>

                                  <WindowWidth>20000</WindowWidth>

                                  <WindowTopX>0</WindowTopX>

                                  <WindowTopY>105</WindowTopY>

                                  <ProtectStructure>False</ProtectStructure>

                                  <ProtectWindows>False</ProtectWindows>

                              </ExcelWorkbook>

                              <Styles>

                                  <Style ss:ID="Default" ss:Name="Normal">

                                      <Alignment ss:Vertical="Bottom"/>

                                      <Borders/>

                                      <Font/>

                                      <Interior/>

                                      <NumberFormat/>

                                      <Protection/>

                                  </Style>

                              </Styles>

                              <%= From t In ds.Tables _

                                  Let excelColumnDataType = GetColumnDataTypes(t.columns) _

                                  Select <Worksheet ss:Name=<%= t.TableName %>>

                                             <Table ss:ExpandedColumnCount=<%= t.Columns.Count %> ss:ExpandedRowCount=<%= t.Rows.Count + 1 %> x:FullColumns="1"

                                                 x:FullRows="1">

                                                 <Row>

                                                     <%= From c In Enumerable.Range(0, t.Columns.Count) _

                                                         Select <Cell><Data ss:Type="String"><%= t.Columns(c).Caption %></Data></Cell> %>

                                                 </Row>

                                                 <%= From r In Enumerable.Range(0, t.Rows.Count) _

                                                     Select <Row>

                                                                <%= From i In Enumerable.Range(0, t.Columns.Count) _

                                                                    Select <Cell><Data ss:Type=<%= excelColumnDataType(i) %>><%= (t.Rows(r))(i) %></Data></Cell> %>

                                                            </Row> %>

                                             </Table>

                                         </Worksheet> %>

                              <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

                                  <Selected/>

                                  <Panes>

                                      <Pane>

                                          <Number>1</Number>

                                          <ActiveRow>2</ActiveRow>

                                          <ActiveCol>1</ActiveCol>

                                      </Pane>

                                  </Panes>

                                  <ProtectObjects>False</ProtectObjects>

                                  <ProtectScenarios>False</ProtectScenarios>

                              </WorksheetOptions>

                          </Workbook>

           Return xmlExcel

       End Function

       Private Shared Function GetColumnDataTypes(ByVal columns As DataColumnCollection) As String()

           Dim columnDataTypes(columns.Count) As String

           For i As Integer = 0 To columns.Count - 1

               columnDataTypes(i) = GetExcelColumnType(columns(i))

           Next

           Return columnDataTypes

       End Function

       '

       ' Very simple function to convert .NET value Types

       ' to Excel DataTypeType as defined by urn:schemas-microsoft-com:office:excel,

       ' the Excel 2003 Xml Schema.

       '

       Public Shared Function GetExcelColumnType(ByVal dc As DataColumn) As String

           Dim columnType As String = "String"

           Dim dataColumnTypeName As String = dc.DataType.ToString()

           Select (dataColumnTypeName)

               Case "System.String"

                   columnType = "String"

               Case "System.Decimal"

                   columnType = "Number"

               Case "System.DateTime"

                   columnType = "DateTime"

               Case "System.Int16","System.Int32","System.Int64"

                   columnType = "Integer"

               Case "System.Single","System.Double"

                   columnType = "Float"

               Case "System.Boolean"

                   columnType = "String"

           End Select

           Return columnType

       End Function

    End Class

  • ' Correct to Function GetExcelColumnType (above)

    '

       ' Very simple function to convert .NET value Types

       ' to Excel //Cell/Data/@Type as defined by urn:schemas-microsoft-com:office:spreadsheet,

       ' the Excel 2003 Xml Schema.

       '

       Public Shared Function GetExcelColumnType(ByVal dc As DataColumn) As String

           Dim columnType As String = "String"

           Dim dataColumnTypeName As String = dc.DataType.ToString()

           Select (dataColumnTypeName)

               Case "System.String"

                   columnType = "String"

               Case "System.DateTime"

                   columnType = "DateTime"

               Case "System.Int16", "System.Int32", "System.Int64", "System.Decimal", "System.Single", "System.Double"

                   columnType = "Number"

               Case "System.Boolean"

                   columnType = "String"

           End Select

           Return columnType

       End Function

  • ' Beth,

    ' 'Thank you for presenting this technique to BayUG.NET.  I took your ball and ran with it.

    ' Below is a class that will translate any DataSet into an Excel Xml document.

    ' Please delete my previous post.  This one works better.

    Option Strict Off

    Imports System.Data

    Imports System.Linq

    Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">

    Imports <xmlns:o="urn:schemas-microsoft-com:office:office">

    Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">

    Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

    Public Class DataSetToExcelXml

       Public Sub New()

       End Sub

       ''' <summary>

       ''' Convert any ADO.NET DataSet to and Excel workbook

       ''' where each Table is becomes a Worksheet

       ''' </summary>

       ''' <param name="ds"></param>

       ''' <returns></returns>

       ''' <remarks></remarks>

       Public Shared Function ConvertDataSetToExcelXDocument(ByVal ds As DataSet) As XDocument

           Dim xmlExcel = <?xml version="1.0"?>

                          <?mso-application progid="Excel.Sheet"?>

                          <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

                              xmlns:o="urn:schemas-microsoft-com:office:office"

                              xmlns:x="urn:schemas-microsoft-com:office:excel"

                              xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

                              xmlns:html="http://www.w3.org/TR/REC-html40">

                              <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

                                  <Author><%= Environment.UserName %></Author>

                                  <LastAuthor><%= Environment.UserName %></LastAuthor>

                                  <Created><%= DateTime.Now.ToString("s") %></Created>

                                  <Version>11.9999</Version>

                              </DocumentProperties>

                              <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

                                  <WindowHeight>10000</WindowHeight>

                                  <WindowWidth>20000</WindowWidth>

                                  <WindowTopX>0</WindowTopX>

                                  <WindowTopY>105</WindowTopY>

                                  <ProtectStructure>False</ProtectStructure>

                                  <ProtectWindows>False</ProtectWindows>

                              </ExcelWorkbook>

                              <Styles>

                                  <Style ss:ID="Default" ss:Name="Normal">

                                      <Alignment ss:Vertical="Bottom"/>

                                      <Borders/>

                                      <Font/>

                                      <Interior/>

                                      <NumberFormat/>

                                      <Protection/>

                                  </Style>

                                  <Style ss:ID="String">

                                      <Alignment ss:Vertical="Bottom" ss:Horizontal="Left"/>

                                  </Style>

                                  <Style ss:ID="Number">

                                      <Alignment ss:Vertical="Bottom" ss:Horizontal="Right"/>

                                      <NumberFormat/>

                                  </Style>

                                  <Style ss:ID="DateTime">

                                      <Alignment ss:Vertical="Bottom" ss:Horizontal="Right"/>

                                      <NumberFormat ss:Format="Short Date"/>

                                  </Style>

                              </Styles>

                              <%= From t In ds.Tables _

                                  Let excelColumnDataType = GetColumnDataTypes(t.columns) _

                                  Select <Worksheet ss:Name=<%= t.TableName %>>

                                             <Table ss:ExpandedColumnCount=<%= t.Columns.Count %> ss:ExpandedRowCount=<%= t.Rows.Count + 1 %> x:FullColumns="1"

                                                 x:FullRows="1">

                                                 <%= From c In Enumerable.Range(0, t.Columns.Count) _

                                                     Select <Column ss:Index=<%= (c + 1) %> ss:StyleID=<%= excelColumnDataType(c) %> ss:AutoFitWidth="1"/> %>

                                                 <Row>

                                                     <%= From c In Enumerable.Range(0, t.Columns.Count) _

                                                         Select <Cell><Data ss:Type="String"><%= t.Columns(c).Caption %></Data></Cell> %>

                                                 </Row>

                                                 <%= From r In Enumerable.Range(0, t.Rows.Count) _

                                                     Select <Row>

                                                                <%= From i In Enumerable.Range(0, t.Columns.Count) _

                                                                    Let excelType As String = excelColumnDataType(i) _

                                                                    Select <Cell><Data ss:Type=<%= excelType %>><%= (t.Rows(r))(i) %></Data></Cell> %>

                                                            </Row> %>

                                             </Table>

                                         </Worksheet> %>

                              <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

                                  <Selected/>

                                  <Panes>

                                      <Pane>

                                          <Number>1</Number>

                                          <ActiveRow>2</ActiveRow>

                                          <ActiveCol>1</ActiveCol>

                                      </Pane>

                                  </Panes>

                                  <ProtectObjects>False</ProtectObjects>

                                  <ProtectScenarios>False</ProtectScenarios>

                              </WorksheetOptions>

                          </Workbook>

           Return xmlExcel

       End Function

       ''' <summary>

       ''' For debugging replace "(t.Rows(r))(i)" above

       ''' with "FormatData(t.Rows(r), i, excelType)"

       ''' </summary>

       ''' <param name="row"></param>

       ''' <param name="columnIndex"></param>

       ''' <param name="excelDataType"></param>

       ''' <returns></returns>

       ''' <remarks></remarks>

       Private Shared Function FormatData(ByVal row As DataRow, ByVal columnIndex As Integer, ByVal excelDataType As String) As String

           Dim stringValue As String = Nothing

           Dim data As Object = row.Item(columnIndex)

           Select Case excelDataType

               Case "DateTime"

                   stringValue = CType(data, DateTime).ToString("s")

               Case Else

                   stringValue = data.ToString()

           End Select

           Return stringValue

       End Function

       ''' <summary>

       ''' Return a string array of the Excel data types for each table column

       ''' </summary>

       ''' <param name="columns"></param>

       ''' <returns></returns>

       ''' <remarks></remarks>

       Private Shared Function GetColumnDataTypes(ByVal columns As DataColumnCollection) As String()

           Dim columnDataTypes(columns.Count) As String

           For i As Integer = 0 To columns.Count - 1

               columnDataTypes(i) = GetExcelColumnType(columns(i))

           Next

           Return columnDataTypes

       End Function

       '

       ' Very simple function to convert .NET value Types

       ' to Excel //Cell/Data/@Type as defined by urn:schemas-microsoft-com:office:spreadsheet,

       ' the Excel 2003 Xml Schema.

       '

       Public Shared Function GetExcelColumnType(ByVal dc As DataColumn) As String

           Dim columnType As String = "String"

           Dim dataColumnTypeName As String = dc.DataType.ToString()

           Select (dataColumnTypeName)

               Case "System.String"

                   columnType = "String"

               Case "System.DateTime"

                   columnType = "DateTime"

               Case "System.Int16", "System.Int32", "System.Int64", "System.Decimal", "System.Single", "System.Double"

                   columnType = "Number"

               Case "System.Boolean"

                   columnType = "String"

           End Select

           Return columnType

       End Function

    End Class

Page 3 of 7 (94 items) 12345»