How to export record to excel file
- Thread starter nsoni
- Start date
Robert_Zenz
Well-known member
You could simply write the data to a CSV, or use the Office Tools (I forget what the real name was, something like Office Automation Tools).
Bobby
Bobby
You can fetch your data from your database and store it into a dataset. To export the dataset to Excel you have a few options (not limited to these choices):
01) You can output the info in a tab or comma delimited file structure similar to a CSV format which has already been suggested. This type of file can then easily be opened and viewed in Excel.
02) You could automate Excel from your VB program but this requires creating a reference to Excel and also would require that the user have the same version of Excel installed on there PC.
For outputting a DataTable where all the columns have the same structure this isnt needed. However if your outputting info where each row/column in the excel file is completly different then the rest of the file, something like this might be needed.
03) You can use OleDb to create & connect to an Excel file and then import/export data directly to the file work similar to retrieving/inserting data to a database.
01) You can output the info in a tab or comma delimited file structure similar to a CSV format which has already been suggested. This type of file can then easily be opened and viewed in Excel.
02) You could automate Excel from your VB program but this requires creating a reference to Excel and also would require that the user have the same version of Excel installed on there PC.
For outputting a DataTable where all the columns have the same structure this isnt needed. However if your outputting info where each row/column in the excel file is completly different then the rest of the file, something like this might be needed.
03) You can use OleDb to create & connect to an Excel file and then import/export data directly to the file work similar to retrieving/inserting data to a database.
cjard
Well-known member
- Joined
- Apr 25, 2006
- Messages
- 7,081
- Programming Experience
- 10+
As a fourth option, you can use Excel XML if your target excel version is 2000 or higher.
I'd recommend using this option because:
You don't need Excel installed
It's not a text-only format that causes Excel to reinterpret the data
You can output data that is typed (i.e. your db data that is a DATE goes into excel as a DATE)
You don't need an existing blank excel file to connect oledb to in order to export
see this thread:
http://www.vbdotnetforums.com/winforms-data-access/31503-export-excel.html
I wrote some code to help with this; you basically make a reference in your code to my dll, then your typed datatable inherits from my datatable and you fill it with data, then call WriteExcelXml(path) on it.. it becomes an excel file at that path. Can also write toa stream for sending over a network
Let;s hope i linked the right thread..
I'd recommend using this option because:
You don't need Excel installed
It's not a text-only format that causes Excel to reinterpret the data
You can output data that is typed (i.e. your db data that is a DATE goes into excel as a DATE)
You don't need an existing blank excel file to connect oledb to in order to export
see this thread:
http://www.vbdotnetforums.com/winforms-data-access/31503-export-excel.html
I wrote some code to help with this; you basically make a reference in your code to my dll, then your typed datatable inherits from my datatable and you fill it with data, then call WriteExcelXml(path) on it.. it becomes an excel file at that path. Can also write toa stream for sending over a network
Let;s hope i linked the right thread..
No matter what type of export format you choose, your coding is going to create a file so I'm not sure where creating an file to use with OleDb is a problem. Likewise column datatypes can still be assigned so a date field in your datatable still is a date field in your exported file.
