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 copyfromrecords et). 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 CopyFromRecords et. The recordset gets created, however the
CopyFromRecords et 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. 4 12032
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 copyfromrecords et). 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 CopyFromRecords et. The recordset gets created, however the CopyFromRecords et 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.
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.goo gle.com... 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 copyfromrecords et). 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 CopyFromRecords et. The recordset gets created, however the CopyFromRecords et 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.
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********@com cast.nospam.net > wrote in message news:<ui******* *******@TK2MSFT NGP10.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.goo gle.com... 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 copyfromrecords et). 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 CopyFromRecords et. The recordset gets created, however the CopyFromRecords et 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.
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.goo gle.com... 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********@com cast.nospam.net > wrote in message
news:<ui******* *******@TK2MSFT NGP10.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.goo gle.com... 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 copyfromrecords et). 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 CopyFromRecords et. The recordset gets created, however the CopyFromRecords et 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: congngo |
last post by:
Hi all
Every time I export a table into an excel spreadsheet. It has a
leading apostrophe on every cells. This drive me nut. I have to do a
work around by export table into a txt file than import in excel to get
rid of the apostrophe.
It has been happen about two months now before it didn't do
that. It is only happen when I have text data type in the table. If I
change to number data type then the problem go away but most of my...
|
by: Tim Frawley |
last post by:
I created a .NET Com Class object for use in ASP reports to export
database results directly to Excel. I have it all working just find
but I cannot get the Excel process to go away after the job is done.
I am using the following .NET code in my Com Class object:
<ComClass(DIF2XLS.ClassId, DIF2XLS.InterfaceId, DIF2XLS.EventsId)> _
Public Class DIF2XLS
#Region "COM GUIDs"
|
by: ABC |
last post by:
What methods can export excel file from stored procedure with parameters
which input from web form?
|
by: panku007 |
last post by:
hi All,
I wanted to export excel data into ms word document through
java coding.So please send me reply,according to my question.
thanks
|
by: tbenchakroune |
last post by:
How can I export Excel spreadsheets to Access db?
Thanks,
T.
| |
by: san1014 |
last post by:
Hi
How to import and export excel data into oracle database using java.
Thank you
|
by: CF FAN |
last post by:
Can Report Builder Export Excel Files with multiple Sheets
How can I create a report that it can export in excel by multiple
|
by: minhtran |
last post by:
Hi all
Anyone has any idea how to get a Excel header column in VB.NET. Please, help me. Thank you so much in advance
|
by: Dubois |
last post by:
Bonjour
Lorsque je lance un export excel au travert d'une page en ASP.NET C#,
cela fonctionne sur la machine de développement, par contre, sur le serveur
de production j'ai l'erreur suivante :
*****************
La récupération de la fabrique de classe COM pour le composant avec le CLSID
{00024500-0000-0000-C000-000000000046} a échoué en raison de l'erreur
suivante : 80070005.
|
by: prashantdixit |
last post by:
Hi,
I am trying to export excel data to text file in a particular format.
The Format for each ROW of Excel is like
IMAN_ROOT/bin/import_file -f=<Column A> -type=<Column B> -d=<Column C> -ref=<Column D> -vb -log=<Column E>
Every Row of the excel should export in text with this format.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |