Exporting data from Dataset to Excel sheet in VB.net Windows Application & vice versa

[sathya_k_83]

Exporting data from Dataset to Excel sheet in VB.net Windows Application & vice versa

How to Export data from Dataset to Excel sheet in VB.net Windows Application
and also How to convert Excel Sheets into Data set Using VB.net coding(Windows Application)


how to Convert Dataset into XL Sheet to the VB.net Windows Application only by using the default resource(dll) available in VB.net.



ie without need to add Office Primary Interop Assemblies of the Microsoft Excel 2003 or without the need for installing the Microsoft Office.



We are developing a Product using VB.net (Windows Application). After Developing the Product , we need to install it in different Location which may or may not have Microsoft office.

So is it possible to write coding in VB.net to convert a data set into Excel sheet & vice versa by using the Normal features of VB.net instead of the Need to Install Microsoft Office in the Client System & Adding reference to PIA in the VB.net Application

The Below are some method/ideas by which data set con be converted into Excel Sheet but it is not suitable for our company.
so we are searching
for another Idea / Method which does not require the installation of Microsoft Office & adding a reference to the PIA/ DLLs



Method 1:
One way is to Office Primary Interop Assemblies in the Microsoft Excel 2003.
1) By adding .net Programmability Support to the Microsoft Excel 2003.


2) When you want to use the Excel 2003 PIAs in a .NET-based application, add a reference to them in the Add Reference dialog box of Visual Studio .NET.
3) Once you add this reference, you can manipulate the Excel COM objects as though they were native .NET-based assemblies.
4) VB.net Coding can be Used to Convert the Dataset into XL Sheets.
Refer :http://msdn2.microsoft.com/en-us/library/aa159891(office.11).aspx


few other methods are

1. http://www.dotnetspider.com/kb/Article950.aspx
2. http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q318373
3. http://weblogs.asp.net/donxml/archive/2003/08/21/24908.aspx
4. http://www.dotnetjohn.com/articles.aspx?articleid=78
5. http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q317109

 

[Anti-Rich]

cant you simply use an oledbcommand to achieve this? and in the connection string specify jet as the provider?

this could work...

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] path [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"C:\Documents and Settings\grandea\My Documents\stockcodes.xls"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnnstr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="[/COLOR][/SIZE][SIZE=2] & path & [/SIZE][SIZE=2][COLOR=#800000]";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oCnn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(cnnstr)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oCmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand([/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM [Sheet1$]"[/COLOR][/SIZE][SIZE=2], oCnn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oDa [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(oCmd)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2][COLOR=#008000]'this stops changes being accepted during a fill, so every row added to the dataset[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'keeps its "Added" state and will actually update to the sql server db[/COLOR][/SIZE]
[SIZE=2]oDa.AcceptChangesDuringFill = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
 
[SIZE=2]oDa.Fill(ds)[/SIZE]
 
[/COLOR][/SIZE]

not sure about dataset ---> excel, havent needed to do anything like that but

what do you think of how i get info from an excel sheet? hope it helps

regards
adam

 

[karthiklsimha]

or you can just create a .csv (comma seperated) file, which can be opened from Excel.

Regards,
Simha

 

[sathya_k_83]

Thanks
ur code helps to convert the data in Excel sheet to filled into a data set.
which code should solve half of the problem . Is it possible to do the same with SQLDB.

We also need to get a data set and convert it into XL sheet for viewing the data

ie for example : select * from Customer will produce a Data set which contains a list of Values ie name , address that need to be converetd into CSV format(Excel Format) to store in the Hard disk & to view it later.


The values in table need to be viewed in a Excel Format