By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,534 Members | 910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,534 IT Pros & Developers. It's quick & easy.

Export to Excel in VB.NET

P: n/a
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.
Nov 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Maybe this can be of some help.

http://www.wizkil-webs.net/NET/DotNE...m#_Toc77487928

B

"John Z." wrote:
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.

Nov 20 '05 #2

P: n/a
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/...ity/newsgroups
"John Z." <jo******@yahoo.com> wrote in message
news:da**************************@posting.google.c om...
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.

Nov 20 '05 #3

P: n/a
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?

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message news:<ui**************@TK2MSFTNGP10.phx.gbl>...
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/...ity/newsgroups
"John Z." <jo******@yahoo.com> wrote in message
news:da**************************@posting.google.c om...
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.

Nov 20 '05 #4

P: n/a
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.
"John Z." <jo******@yahoo.com> wrote in message
news:da**************************@posting.google.c om...
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?

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message

news:<ui**************@TK2MSFTNGP10.phx.gbl>...
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/...ity/newsgroups
"John Z." <jo******@yahoo.com> wrote in message
news:da**************************@posting.google.c om...
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.

Nov 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.