Joined:	07/09/2005
Level:	Bronze
Points:	32

Points:	2

How to export dataset contents into excel in vb.net ?

The forum thread has not been reviewed by Editors yet. Readers are advised to use their best judgement before accessing this thread.
This thread will be reviewed shortly.
If you think this thread contain inappropriate content, please report to webmaster.

i want to export datagrid contents into excel sheet by mentioning provider of excel
in connection string.

i tried this link which is exactly what i want to do.

http://www.codeproject.com/useritems/Excel_Connectivity.asp

But its giving error that sheet1 object is not declared.
i couldnt catch that where is problem persist.

pls give me some idea regarding that.
Thanks.
Preeti.

Joined:	17/08/2005
Level:	Silver
Points:	69

Points:	2

Hi,

Hope that this will helps u..

Public Sub ExportTOExcel(ByVal dtExportToExcel As DataTable)
Dim excelApp As New Excel.Application
Try
Dim alph As String
Dim iCol As Integer
Dim varfilename As String
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)

excelApp.Visible = False

With excelWorksheet
Dim counter As Integer
For counter = 0 To dtExportToExcel.Columns.Count - 1
If counter < 26 Then
.Range(Chr(65 + counter) & 1).Value = (dtExportToExcel.Columns(counter).ToString)
.Range(Chr(65 + counter) & 1).Font.Bold = True
.Range(Chr(65 + counter) & 1).ColumnWidth = 20
alph = Chr(65 + counter)
ElseIf counter > 25 And counter < 52 Then
.Range(Chr(65) & Chr(65 + counter - 26) & 1).Value = (dtExportToExcel.Columns(counter).ToString)
.Range(Chr(65) & Chr(65 + counter - 26) & 1).Font.Bold = True
.Range(Chr(65) & Chr(65 + counter - 26) & 1).ColumnWidth = 20
alph = Chr(65) & Chr(65 + counter - 26)
Else
.Range(Chr(66) & Chr(65 + counter - 26) & 1).Value = dtExportToExcel.Columns(counter).ToString
.Range(Chr(66) & Chr(65 + counter - 26) & 1).Font.Bold = True
.Range(Chr(66) & Chr(65 + counter - 26) & 1).ColumnWidth = 20
alph = Chr(66) & Chr(65 + counter - 26)
End If
Next

Dim rows, columns, i, j As Integer
rows = (dtExportToExcel.Rows.Count)
columns = dtExportToExcel.Columns.Count - 1
Try
Dim test(rows + 1, columns + 1) As Object
For counter = 0 To dtExportToExcel.Rows.Count - 1
For iCol = 0 To dtExportToExcel.Columns.Count - 1
If Not IsDBNull(dtExportToExcel.Rows(counter).Item(iCol)) Then
test(counter, iCol) = dtExportToExcel.Rows(counter).Item(iCol)
Else
test(counter, iCol) = ""
End If
Next
Next
If columns < 26 Then
.Range("A2", Chr(65 + dtExportToExcel.Columns.Count - 1) & rows + 1).Value = test
Else
.Range("A2", 1 & rows + 1).Value = test
End If

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End With
Try

varfilename "c:\pdffiles\ttt.xls"
excelWorksheet.SaveAs(varfilename)
Dim result = MsgBox("Excel file created by name" & varfilename & "Do you wish to view it?", MsgBoxStyle.YesNo, "Excel File Created")
If result = 6 Then
excelApp.Quit()
WebBrowser.Navigate(varfilename)
Else
excelApp.Quit()
End If

Catch exRpt As Exception
End Try
Catch ex As Exception
Try
excelApp.Quit()
Catch exExcel As Exception
MessageBox.Show(exExcel.Message)
End Try
MessageBox.Show(ex.Message)
End Try
End Sub

Regards

How to export dataset contents into excel in vb.net ?

About dotnetspider.com

Quick Links

General

Services