Quickly Import and Export Excel Data with LINQ to XML

re: Quickly Import and Export Excel Data with LINQ to XML

Ryan — Thu, 01 Nov 2007 15:56:57 GMT

Nice work beth as usual, but you are unfair.

You should give the code in VS 2005 too.

btw good job.

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Thu, 01 Nov 2007 19:29:00 GMT

Hi Ryan,

LINQ is only available in VS 2008. You can download the beta here: http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx

I'd have to take a different approach in VS2005 that wouldn't be as quick. :-) Like using automation or some XSLT.

New Visual Basic LINQ to XML Videos Released! (Beth Massi)

The Visual Basic Team — Thu, 01 Nov 2007 22:08:17 GMT

We just released a new set of How-Do-I videos in our LINQ series on LINQ to XML in Visual Basic. These

New Visual Basic LINQ to XML Videos Released!

Beth Massi - Sharing the goodness that is VB — Fri, 02 Nov 2007 19:02:42 GMT

We just released a new set of How-Do-I videos in our LINQ series on LINQ to XML in Visual Basic. These

re: Quickly Import and Export Excel Data with LINQ to XML

Duncan — Mon, 05 Nov 2007 13:47:26 GMT

I'm getting an error message "Expression cannot be converted into an expression tree" with th efollowing part underlined:

Select <Row>

</Row>

The IDE is Visual Studio 2008 9.0.20706.1 Beta 2

.Net framework 3.5

Any ideas?

Thanks

Duncan

Content Rollup For September and October

Beth Massi - Sharing the goodness that is VB — Mon, 05 Nov 2007 23:13:38 GMT

Here's a summary of all the content the VB team members, including myself, have created for you on the

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Mon, 05 Nov 2007 23:36:01 GMT

Hi Duncan,

I recall that being a bug in Beta 2 (now fixed, of course ;-)). To work around this try adding a collection type that's not LINQ to SQL. Something like:

Dim dummy() As String = {""}

Dim customers = From d In dummy _

From customer In dbNorthwind.Customers _

Order By customer.CompanyName _

Select <Row>

</Row>

BTW, today Soma announced that VS 2008 will be released this month! http://www.microsoft.com/presspass/press/2007/nov07/11-05TechEdDevelopersPR.mspx

re: Quickly Import and Export Excel Data with LINQ to XML

Curt — Tue, 06 Nov 2007 02:39:50 GMT

I had some questions for you and figured you gave us your phone number in case we had some questions or were otherwise bored and lonely. I tried the number and. . .You're a liar! That's not your real phone number. This kind of dishonesty just will not stand.

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Tue, 06 Nov 2007 02:48:35 GMT

LOL Curt. Good one, I missed the example. If I gave my real phone number out on the internet I'd have more problems than just my backlog of email ;-)

re: Quickly Import and Export Excel Data with LINQ to XML

Kerry — Mon, 12 Nov 2007 18:12:33 GMT

Beth,

Using Beta 2 and the "dummy fix" above the xl sheet will not open, the row data is as follows???

<Cell>

</Cell>

<Cell>

<Data p4:Type="String" xmlns:p4="urn:schemas-microsoft-com:office:spreadsheet">PROTECTION SPRAY WEICON STAINLESS STEEL 400ML</Data>

</Cell>

<Cell>

</Cell>

</Row>

re: Quickly Import and Export Excel Data with LINQ to XML

Kerry — Mon, 12 Nov 2007 19:13:32 GMT

Re last post, Fixed, didnt set the ExpandedRowCount!

re: Quickly Import and Export Excel Data with LINQ to XML

Kerry — Mon, 19 Nov 2007 16:24:32 GMT

Beth,

Really useful information.

Not sure if this is an issue or is known behaviour;-

With the following in the class;-

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

This code accessing Xdocument does not work

Dim specsX = XDocument.Parse(SpecText)

Dim EQ = New With { _

.EqName = specsX...<name>.Value, _

.EqMake = specsX...<manufacturer>.Value}

Had to put the code in a seperate class, which prompts the question how do I access the properties in EQ outside of that function ?

Thanks,

Kerry

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Mon, 19 Nov 2007 19:43:47 GMT

Hi Kerry,

I'm not sure what SpecText is in the above code but in order to pass out EQ in this case you would create a class (say you call it MyStuff) with two properties EqName and EqMake then you could do something like:

Dim EQ As MyStuff = New MyStuff With {....

Since you can't pass around anonymous types you just need to explicitly create a class first and use that instead.

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Kerry — Tue, 20 Nov 2007 09:14:57 GMT

Beth,

Thanks for your prompt reply, as always you have the skill to explain in simple terms, solving the problem without going too deep! This iterative learning approach is the 'only' way for many of us trying to earn a living while embracing the new..

SpectText is a simple string of XML that was extracted from an ntext column in SQL compact. The same code works OK when it is seperate from the imports statement.

Thanks again,

Kerry

re: Quickly Import and Export Excel Data with LINQ to XML

Ken — Tue, 04 Dec 2007 22:38:14 GMT

I have an excel spreadsheet with data that I want to export to sql using vb.net and xml. I understand how you transferred data from sql to excel but what if you started with excel that has all the data and want to transfer to sql.

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Tue, 04 Dec 2007 22:42:20 GMT

Hi Ken,

The second part of the example above does exactly that. Take a look at the attached code. Basically you'll need to agree on what identifies a unique row, in this example it's Abbrev.

HTH,

-B

I'll Be Speaking at Code Camp in Victoria, BC

Beth Massi - Sharing the goodness that is VB — Fri, 04 Jan 2008 02:29:40 GMT

I'll be speaking at the Victoria Code Camp on January 26th so if you're in the area and you have nothing

re: Quickly Import and Export Excel Data with LINQ to XML

Kevin — Wed, 09 Jan 2008 23:42:28 GMT

This looks exactly like what I'm looking for. With a couple of bugs worked out. I have not modified the code in the sample.

In testing, if I add a name to the spreadsheet (which will be important to me), on import, I get the error, "Cannot set column 'Abbrev'. The value violates the MaxLength limit of this column."

Any advice?

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Wed, 09 Jan 2008 23:48:02 GMT

Hi Kevin,

You'll either need to increase the width of the column in the database or limit the Abbrev field to 5 characters in the spreadsheet.

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Kevin — Thu, 10 Jan 2008 00:08:31 GMT

:) Thank you. Simple enough :)

re: Quickly Import and Export Excel Data with LINQ to XML

Andy — Mon, 14 Jan 2008 08:03:03 GMT

How would you do this in C#? Please share.

re: Quickly Import and Export Excel Data with LINQ to XML

Kevin — Wed, 16 Jan 2008 03:24:13 GMT

Hi Beth,

I am hoping to build this so multiple users can add information and update the same table using a Windows Forms Application vs the Consul Application. Is there a good reference location for the code differences? Thank you.

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Wed, 16 Jan 2008 18:36:13 GMT

Hi Andy,

XML Literals are not supported in C# so you would have to construct the document using the LINQ to XML API directly. It's pretty ugly but I think there may be a tool that helps you paste literals into the editor. I'd ask in the forums.

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Wed, 16 Jan 2008 18:38:42 GMT

Hi Kevin,

The example above (download the attachment to this post) is already a Windows Forms application. To make it multi-user capable you would need to use SQL-Server Express as the database instead of CE. Here are some How-To videos to help get you started: http://msdn2.microsoft.com/en-us/vbasic/bb466226.aspx#formsoverdata

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Kevin — Fri, 18 Jan 2008 18:41:02 GMT

I've been using Visual Web Developer 2008 Express Edition and SQL-Server Express as a database. Are there How -To videos for incorporating Linq to SQL / XML as a Web Application?

re: Quickly Import and Export Excel Data with LINQ to XML

Jason — Mon, 21 Jan 2008 20:14:55 GMT

Hi Beth,

I am using the VB.net 2008, and I want to import my excel datas into my vb-program.. How can I made such importing from excel file into visual basic? Thanks!

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Mon, 21 Jan 2008 21:13:00 GMT

Hi Kevin,

I haven't watched them yet, but can check these videos out http://www.asp.net/learn/linq-videos/ and let us know.

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Mon, 21 Jan 2008 21:14:08 GMT

Hi Jason,

The attached example to the post above shows importing and exporting Excel data.

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Kevin — Thu, 24 Jan 2008 20:26:09 GMT

Beth, thank you

The asp.net learning videos combined with yours are great.

Quickly. The spread sheet saves. The headers show up in excel but no data. I can see the data laid out in order if I view it as an xml feed in the browser.

I think its the count+1 addition to the spread sheet code. If I change this portion I get the error, "Compiler Error Message: BC30456: 'Count' is not a member of 'System.Xml.Linq.XElement'."

I'm trying to add 'count' as an included member. Any directing would be appreciated.

Thanks again, Kevin

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Sun, 27 Jan 2008 02:54:13 GMT

Hi Kevin,

Count is an extension method on the collection returned from the LINQ query above. It sounds like you are returning an XElement from your query.

-B

re: Quickly Import and Export Excel Data with LINQ to XML

ROy — Wed, 30 Jan 2008 07:03:03 GMT

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...

re: Quickly Import and Export Excel Data with LINQ to XML

Chris St. John — Mon, 04 Feb 2008 00:28:49 GMT

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

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Tue, 05 Feb 2008 02:14:46 GMT

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>

</Row>

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Chris St. John — Thu, 07 Feb 2008 03:45:56 GMT

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)?

re: Quickly Import and Export Excel Data with LINQ to XML

Rick — Thu, 21 Feb 2008 00:46:07 GMT

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!

re: Quickly Import and Export Excel Data with LINQ to XML

Jim Frith — Sat, 23 Feb 2008 22:01:22 GMT

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

re: Quickly Import and Export Excel Data with LINQ to XML

Jim Frith — Mon, 25 Feb 2008 23:04:19 GMT

Hi Beth,

I resolved the declarations issue. Thanks for the example.

Jim

re: Quickly Import and Export Excel Data with LINQ to XML

enkhbazar — Tue, 11 Mar 2008 12:19:36 GMT

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

re: Quickly Import and Export Excel Data with LINQ to XML

Jon — Wed, 26 Mar 2008 20:13:38 GMT

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>

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

<B>

</B>

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

</ss:Data>

</Comment>

</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) _

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Sat, 05 Apr 2008 01:39:01 GMT

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)

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Phil Nelson — Wed, 23 Apr 2008 14:39:28 GMT

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>

</Row>

<Row>

</Row>

<Row>

</Row>

<Row>

</Row>

</Table>

Thanks

re: Quickly Import and Export Excel Data with LINQ to XML

Phil Nelson — Wed, 23 Apr 2008 18:24:41 GMT

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)

Catch ex As Exception

End Try

re: Quickly Import and Export Excel Data with LINQ to XML

Carl Kelley — Fri, 02 May 2008 02:48:37 GMT

'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>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Font/>

</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> %>

<Panes>

<Pane>

</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))

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

re: Quickly Import and Export Excel Data with LINQ to XML

Carl Kelley — Fri, 02 May 2008 19:06:30 GMT

' 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

re: Quickly Import and Export Excel Data with LINQ to XML

Carl Kelley — Fri, 02 May 2008 22:28:30 GMT

' 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>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Font/>

</Style>

</Style>

</Style>

</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> %>

<Panes>

<Pane>

</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))

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

re: Quickly Import and Export Excel Data with LINQ to XML

Ganesh — Mon, 09 Jun 2008 11:28:46 GMT

Hai Everyone

Please suggest how to export datas from Excel file to txt using miscrosoft office vb.

Regards

Ganesh

re: Quickly Import and Export Excel Data with LINQ to XML

Coleman — Mon, 30 Jun 2008 07:52:26 GMT

Hi Beth,

I am writing an app that needs to take data from excel and put into a database - so the code you have given is a great starting point.

Unfortunately if I edit the spreadsheet or have a column with a null value the LINQ query is set to nothing. If I create a new SS it will work, however only if all columns contain a value.

I would be great to get this to work reliably, otherwise it's back to exporting the excel data to a txt file for the sake of reliability.

If anyone responds - Thanks

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Mon, 30 Jun 2008 21:48:54 GMT

Hi Coleman,

You just need to change the query to check for null and provide a default value instead. Here I modified the query to return the empty string "" if the fields are not filled out. Note that Abbrev still must not be null since that is the key we're using.

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 _

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 = If(Name Is Nothing, "", Name.Value), _

Phone = If(Phone Is Nothing, "", Phone.Value), _

Country = If(Country Is Nothing, "", Country.Value), _

Group _

Skip 1

HTH,

-Beth

re: Quickly Import and Export Excel Data with LINQ to XML

Coleman — Tue, 01 Jul 2008 01:41:34 GMT

Hi Beth,

Thank you so for much for the response, works a like a treat.

I had tried try to process the null, but was miles away on the correct syntax.

Thanks again

re: Quickly Import and Export Excel Data with LINQ to XML

Alan — Thu, 17 Jul 2008 21:06:43 GMT

Hi Beth,

Thank you for this post, it was very helpful.

I have a comment/question on it. Your approach works fine assuming that you have the structure of the Customers Excel sheet as static.

What I'm trying to ask is: Is it possible to have this work dynamically. Where the user would navigate to an Excel document to choose, then import that document (hence the document can have more columns than the original Customers Excel document), and after the document is imported, the data is displayed in the DataGridView following the same rules that you have in your original code except for the data source.

So the user would be able to import a Products table or a Locations table or any table they choose, and the DataTable in your Dataset would be created during runtime based on the source data.

Best Regards,

Alan

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Mon, 21 Jul 2008 18:43:53 GMT

Hi Alan,

You could probably do this dynamically using an untyped DataSet and just mapping the position of the fields to the Excel columns but it may be brittle. It's better to know at least part of the schema. And you'd still have to know the unique key column to do the group. I'd probably look at the Excel XML mapping or see if there was a way to infer the schema.

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Alan — Tue, 22 Jul 2008 05:31:32 GMT

Hi Beth,

Thanks for your response.

I was wondering if it is possible to pass to a LINQ query a table name and a compare value as input parameters of a function, something like this:

public Sub MyLinq(ByVal MyTable as DataTable, _

ByVal MyValue as String)

Dim customers = _

From _

Data In MyTable _

Where _

Data.Field(Of String)(1) = MyValue _

Select _

Data.Field(Of Integer)(0) _

Distinct

' some logic here...

End Sub

Thanks,

Alan

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Tue, 22 Jul 2008 23:24:56 GMT

Hi Alan,

Yep, you could do that, you'll end up with a collection of Integers in your example. Just put a try/catch around the method to catch any runtime errors (i.e. casting or missing fields) and remember to import System.Data

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Alan — Wed, 23 Jul 2008 16:45:39 GMT

Hi Beth,

Thank you for your response.

I also want to thank you for the videos on LINQ usages and the other tutorials you made. You have definitely simplified the LINQ learning experience.

Best Regards,

Alan

re: Quickly Import and Export Excel Data with LINQ to XML

Chris — Thu, 04 Dec 2008 23:38:46 GMT

I am new to programming and this information has been very helpful for a project I have been working on. I do have one question, how can I export data from my database based on current date? I would like to export just records for the current day only.

re: Quickly Import and Export Excel Data with LINQ to XML

Steve — Wed, 17 Dec 2008 23:36:15 GMT

Thank you Beth for your example.

I have an instance where I have a set number of columns, as per your example - works great.

However, in addition, there can extra, unknown, number of columns after that

If there a way to check the column - ie to see if it has a header value, and if so, then it will have a value in the row (that is my situation) ?

FirstName LastName Dynamic1 Dynamic2

Beth Massi 500.00 300.00

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Fri, 19 Dec 2008 00:28:05 GMT

Hi Steve,

If you take a close look I'm already assuming that the columns (except for Abbrev) may not exist in the spreadsheet, however they have to exist in the DataSet. So to capture the extra columns you'll need to modify the database table to to add these fields. Then you can either modify your typed dataset or you can dynamically add untyped columns to the DataTable. Then your query can just check for Nothing (null) value on the extra columns like I'm doing already for the other fields. However you may want to supply a different default value for empty fields than the empty string.

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) _

Let DynamicField1 = xmlCustomer.<Cell>(4) _

Where _

Abbrev 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 = If(Name Is Nothing, "", Name.Value), _

Phone = If(Phone Is Nothing, "", Phone.Value), _

Country = If(Country Is Nothing, "", Country.Value), _

DynamicField1 = If(DynamicField1 Is Nothing, "", DynamicField1.Value), _

Group _

Skip 1

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

David — Mon, 22 Dec 2008 17:59:44 GMT

Hi Beth,

Great example!

I have a List(Of List(String)) that stores rows and columns so I don't have the ability to specify which column should be put where. You were able to put <Cell><Data ss:Type="String"><%= customer.Abbrev %></Data></Cell> as you knew that customer contained the Abbrev field. However my "customer" is just a list of cells with any number of elements. Is there a way of dealing with this too. I guess some kind of nested structure is required but I a not too sure how that would work.

Thanks

David

re: Quickly Import and Export Excel Data with LINQ to XML

Bob C — Tue, 30 Dec 2008 23:25:42 GMT

Beth,

many thanks for the code, yet it is a bit too advanced for a mere novice like me.

I am currently trying to edit your code to populate a 2 dimentional array from the excel sheet. I want to update an old app that loads the array from 15 seperate txt files.

Any pointers will be greatly appreciated.

Rgds

Bob

re: Quickly Import and Export Excel Data with LINQ to XML

Steve — Sun, 11 Jan 2009 20:44:03 GMT

I was able to get it to work with the dataset to excel.

Great code - thanks.

Not sure if it's just a bug in Excel, but even though the type is getting set as a 'Number' (ie. a Double), it's not showing up as as double in the spreadsheet.

Dates work, not numbers ?

re: Quickly Import and Export Excel Data with LINQ to XML

pkellner — Mon, 19 Jan 2009 06:06:47 GMT

thanks for the hint on save as office 2003 xml, I've been struggling with exporting to xml all kinds of other ways. You are the best Beth!

re: Quickly Import and Export Excel Data with LINQ to XML

Ted — Fri, 30 Jan 2009 02:57:30 GMT

I am trying to import data from a spreadsheet into and SQL database. I have been trying to use your example as a framework for my situation. The problem I am experiencing is when a cell in the spreadsheet for a given field is blank, the data in the columns next to it are all shifted over to the left and end up basically in the wrong columns.

the LINQ statement is as follows:

Dim Player = From xmlPlayer In sheet...<Row> _

Let Col0 = xmlPlayer.<cell>(0) _

Let Col1 = xmlPlayer.<cell>(1) _

Let Col2 = xmlPlayer.<cell>(2) _

Let Col3 = xmlPlayer.<cell>(3) _

Let Col4 = xmlPlayer.<Cell>(4) _

Let Col5 = xmlPlayer.<Cell>(5) _

Let Col6 = xmlPlayer.<Cell>(6) _

Let Col7 = xmlPlayer.<Cell>(7) _

Let Col8 = xmlPlayer.<Cell>(8) _

Let Col9 = xmlPlayer.<Cell>(9) _

Let Col10 = xmlPlayer.<Cell>(10) _

Let Col11 = xmlPlayer.<Cell>(11) _

Let Col12 = xmlPlayer.<Cell>(12) _

Where _

Col0 IsNot Nothing _

Group xmlPlayer By Col1, Col0, Col2, Col3, Col4, Col5, Col6, Col7, _

Col8, Col9, Col10, Col11, Col12 Into Group _

Select _

Col0 = If(Col0 Is Nothing, "", Col0.Value), _

Col1 = If(Col1 Is Nothing, "", Col1.Value), _

Col2 = If(Col2 Is Nothing, "", Col2.Value), _

Col3 = If(Col3 Is Nothing, "", Col3.Value), _

Col4 = If(Col4 Is Nothing, "", Col4.Value), _

Col5 = If(Col5 Is Nothing, "", Col5.Value), _

Col6 = If(Col6 Is Nothing, "", Col6.Value), _

col7 = If(Col7 Is Nothing, "", Col7.Value), _

col8 = If(Col8 Is Nothing, "", Col8.Value), _

col9 = If(Col9 Is Nothing, "", Col9.Value), _

col10 = If(Col10 Is Nothing, "", Col10.Value), _

col11 = If(Col11 Is Nothing, "", Col11.Value), _

col12 = If(Col12 Is Nothing, "", Col12.Value), _

Group _

Skip 0

And a sample of the XML from the Excel Spreadsheet:

<ss:Worksheet ss:Name="Sheet1">

<ss:Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="24" x:FullColumns="1" x:FullRows="1">

<ss:Row>

<ss:Cell>

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

</ss:Cell>

<ss:Cell>

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

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">Account Number</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">Member Type</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">Gender</ss:Data>

</ss:Cell>

</ss:Row>

<ss:Row>

<ss:Cell>

<ss:Data ss:Type="String">Bill</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">Jones</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="Number">1222</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">Principle</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">M</ss:Data>

</ss:Cell>

</ss:Row>

<ss:Row>

<ss:Cell>

<ss:Data ss:Type="String">Ron</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">Smith</ss:Data>

</ss:Cell>

<ss:Cell ss:Index="4">

<ss:Data ss:Type="String">Guest</ss:Data>

</ss:Cell>

<ss:Cell>

<ss:Data ss:Type="String">M</ss:Data>

</ss:Cell>

</ss:Row>

Where the blank cell is located the XML returns:

<ss:Cell ss:Index="4">

For some reason it is ignored and a blank or "" is not inserted into my datatable. Hopefully you can shed some light on how to resolve this.

Thanks!!

Ted

re: Quickly Import and Export Excel Data with LINQ to XML

Paul — Tue, 21 Apr 2009 12:51:17 GMT

Great 'how to'.

I'm trying to modify it to work with my app which uses an Access database. Its a multiuser app and the data is on a different machine to the app. Connection strings are set up accordingly and the app works fine in all other respects.

My dataset contains the data that I want to export - it appears on the form as a child grid as described in one of your earlier posts/videos. It compiles OK and the app runs OK but when I hit the Export button I only get the header row appearing in the spreadsheet.

Does your example code requery the dataset or is it executing a new query against the .sdf? If so I suspect that this is where I'm having problems using a remote .mdb file.

I am obviously missing something....but what?

Hope you can help - keep up the good work!

re: Quickly Import and Export Excel Data with LINQ to XML

Mark Cafazzo — Mon, 18 May 2009 18:59:03 GMT

I've encountered an issue while trying to implement this approach (by the way, great article! - it prompted me to switch to VB for the Excel handling, while most of our code is C#!): if the excel sheet contains formulas in some of the cells, their values are not accessible at the time the rows are enumerated. Any ideas? Thanks Beth!

re: Quickly Import and Export Excel Data with LINQ to XML

Trenton — Sat, 05 Sep 2009 17:54:55 GMT

Great Stuff...

Took me a little tweaking to get it to work with my application. My question is how do I go about converting the r.mydate = whatever.mydate string so it can be inserted into a date container...I keep getting a wrong format error telling me I need to parse the string before inserting it any help would be great

Thanks

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Tue, 08 Sep 2009 22:55:14 GMT

Hi Trenton,

You should be able to use the VB date functions to check the string and then convert it. Something like

If IsDate(mydate) Then

r.MyDate = CDate(mydate)

End If

HTH,

-B

re: Quickly Import and Export Excel Data with LINQ to XML

Eric Gelders — Mon, 05 Oct 2009 22:38:51 GMT

Excellent stuff Beth, especially for a starter on LINQ to SQL. Took me some tweaking to get it going (especially the Dim sheet =... statement took me some time to reproduce myself. Saving the Excel file as xml wasn't the problem; pasting it into my own project took a bit of debugging :-( ).

Anyway, I ran this stuff against Excel 2007, but I saved the Excel file as 2003 XML file. Will you ever make a version for Excel 2007? But thanx anyway!

re: Quickly Import and Export Excel Data with LINQ to XML

winsrp — Mon, 26 Oct 2009 16:46:31 GMT

there are problems if you have more than one excel sheet in the file, you should include a filter in the where condition.

Something like

AndAlso sheet...<Worksheet>.@ss:Name = "Sheet1"

But the above statement does not works if the sheet1 is the second sheet of the file. since the above is the same as

AndAlso sheet...<Worksheet>(0).@ss:Name = "Sheet1"

I'm still trying to figure out how to tell the linq statement to pick only the sheet I want

re: Quickly Import and Export Excel Data with LINQ to XML

Beth Massi — Wed, 28 Oct 2009 15:40:01 GMT

Hi winsrp,

As long as you're importing the XML namespaces above you can change the import code to this and it will select only Sheet1

Dim book = XDocument.Load(Me.FileName)

Dim sheet = From s In book...<Worksheet> Where s.@ss:Name = "Sheet1"

HTH,

-B