Write data to Excel

[TedManowar]

Hello,

I need to export data to excel, from a Dataset or DataGrid.
I know how to import but not how to export.
Can anyone help me???

TedManowar

 

[lingsn]

The slowest method

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] ExportRSData([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] pDataTables [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] excelApp [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Excel.Application
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] excelBook [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Workbook = excelApp.Workbooks.Add
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] excelWorksheet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Worksheet = _
[/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](excelBook.Worksheets(1), Excel.Worksheet)
[/SIZE][SIZE=2][COLOR=#008000]' Unlike the Word demo, we'll make the spreadsheet visible so you can see
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' the data being entered.
[/COLOR][/SIZE][SIZE=2]excelApp.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] idx [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 1
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] idx = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] pDataTables.Columns.Count - 1
excelWorksheet.Cells(1, idx + 1) = pDataTables.Columns(idx).ColumnName
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] row [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rowidx [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 2
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] row [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] pDataTables.Rows
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] idx = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] pDataTables.Columns.Count - 1
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] IsDBNull(row(idx)) = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]excelWorksheet.Cells(rowidx, idx + 1) = row(idx)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2]rowidx = rowidx + 1
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

If you want the faster performance, I think you can explore the ADO.NET to insert the data to the excel file.

 

[TedManowar]

Hello,

Thank you for yor quick reply, however
excel.application
excel.workbook
and
excel.worksheet
aren't recognized by vb.net 2005

do you know of another way to do this?

TedManowar

 

[lingsn]

You have to add the Excel interop COM object to your project by going to Main Menu->Project->Add Reference

Select the COM tab, and select "Microsoft Excel 9.9 Object Library" to your project. The 9.9 is refer to your excel object library version.

 

[TedManowar]

I don't seem to have the 9.9
i only have the 5.5
is that any good?

 

[lingsn]

Well, you can try it.

 

[TedManowar]

I'm afraid it doesn't work with the 5.5....