Export to Excel in VB.NET

[John Z.]

I have read numerous articles and postings on various approaches to
exporting
a DataSet to excel while avoiding the use of automation. I found the
most performant approach to be assigning a string array of data to a
range in excel (similar to a copyfromrecordset). However, with this
approach, the datatype is lost and all numeric columns in excel appear
as string.

In order to overcome this, I tried calling TextToColumns on each
column. This works except oddly some string values are getting broken
up into multiple columns.

I have also tried MS code to create a RecordSet from a DataSet and
perform a CopyFromRecordset. The recordset gets created, however the
CopyFromRecordset does not work.

Has anyone had any success with either of these approach...or have
some other approach to export DataSet contents to excel without heavy
use of automation and at the same time maintaining the datatype of the
column?

Thanks in advance.

 

[Guest]

Maybe this can be of some help.

http://www.wizkil-webs.net/NET/DotNET_Code_Samples.htm#_Toc77487928

B

 

[William Ryan eMVP]

You can use it directly as a datasource:
http://www.knowdotnet.com/articles/exceldatasource.html

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[John Z.]

Thanks for your post. I see that you are querying data OUT of Excel
into a DataSet object using ADO.NET, but can the reverse be
accomplished in this manner...can data from a DataSet be pushed INTO
an Excel worksheet?

 

[Tampa .NET Koder]

You could loop through the rows of each table in your dataset seperating
each value with a tab and then each row will be ended with a vbcrlf. Then
save this output to a file with a .xls. You should then be able to open
this up using excel since its in a format that excel can understand.