Export to Excel in vb.net

Dev Articles Community Forums Sponsor:

August 2nd, 2003, 08:04 AM

shawlin

Junior Member

Join Date: Aug 2003

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

Export to Excel in vb.net

Hi,

I want a solution how to export the data from
the data grid directly to excel file (for eg.excelExp1.xls)
which is default in my case.I do not want the excel application
to be opened in this case.Just collecting data from a
datagrid and exporting to my specified excel file.

Hope the question is clear.

Thanks and regards
shawlin

March 11th, 2008, 06:03 AM

tasnim5

Registered User

Join Date: Mar 2008

Posts: 1

Time spent in forums: 10 m 50 sec
Reputation Power: 0

How to export the data from datagrid to excell

Quote:

Originally Posted by shawlin

Hi Shawlin,

Follow the link below hope it helps:
codeproject.com/KB/vb/Senthil_S__Software_Eng_.aspx

Thanks,
Ahmad

January 28th, 2011, 09:08 PM

Echo12

Registered User

Join Date: Jan 2011

Posts: 1

Time spent in forums: 10 m 22 sec
Reputation Power: 0

free component for Exporting data to Excel

If you want to save time and work more efficiently, you can try to use the third party Add-in. Once, I have used Spire.DataExport, it works well and exports data quickly.
Recently, I heard that it published a free version. You can download the free one to evaluate if it is really useful form
E-iceblue.com/Download/download-dataexport-for-net-now.html

March 8th, 2011, 11:56 PM

alexsmth114

Registered User

Join Date: Mar 2011

Posts: 11

Time spent in forums: 1 h 17 m 20 sec
Reputation Power: 0

Nice informative post, keep them coming!!..

April 20th, 2011, 04:06 PM

JamieBarnes

Registered User

Join Date: Apr 2011

Posts: 1

Time spent in forums: 16 m 25 sec
Reputation Power: 0

Here is VB code that I found while looking for a solution for this earlier:

Code:

Private Sub CreateExcelFile(ByVal InputDataTable As DataTable, ByVal FileName As String)
        Me.Cursor = Cursors.WaitCursor
        Dim ExcelApp As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim ExcelWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
        Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
        Dim ColumnIndex As Integer = 0
        Dim RowIndex As Integer = 1
        Try
            ExcelWorkbook = ExcelApp.Workbooks.Add()
            ExcelWorkSheet = ExcelWorkbook.ActiveSheet()
            For Each c As DataColumn In InputDataTable.Columns
                ColumnIndex += 1
                ExcelApp.Cells(RowIndex, ColumnIndex) = c.ColumnName
            Next
            For Each r As DataRow In InputDataTable.Rows
                RowIndex += 1
                ColumnIndex = 0
                For Each c As DataColumn In InputDataTable.Columns
                    ColumnIndex += 1
                    ExcelApp.Cells(RowIndex, ColumnIndex) = r(c.ColumnName).ToString
                Next
            Next
            ExcelWorkSheet.Columns.AutoFit()
            ExcelWorkbook.SaveAs(FileName)
            ExcelWorkbook.Close()
            ExcelApp.Quit()
            ReleaseObject(ExcelApp)
            ReleaseObject(ExcelWorkbook)
            ReleaseObject(ExcelWorkSheet)
        Catch ex As Exception
            MessageBox.Show("There was an error creating the Excel file", "Error Creating File", MessageBoxButtons.OK)
        Finally
            ExcelApp = Nothing
            ExcelWorkbook = Nothing
            ExcelWorkSheet = Nothing
            ColumnIndex = Nothing
            RowIndex = Nothing
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComO  bject(obj)
        Catch ex As Exception
        Finally
            obj = Nothing
            GC.Collect()
        End Try
    End Sub

August 29th, 2012, 02:27 AM

roars1111

Registered User

Join Date: Aug 2012

Posts: 4

Time spent in forums: 43 m 48 sec
Reputation Power: 0

PrivateSub ButtonExport_Click(ByVal sender As System.Object,ByVal e As System.EventArgs)Handles ButtonExport.Click
Dim rowsTotal, colsTotal AsShort
Dim I, j, iC AsShort
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim xlApp AsNew Excel.Application
Try
Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet =CType(excelBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible =True
rowsTotal = DataGridView1.RowCount -1
colsTotal = DataGridView1.Columns.Count -1
With excelWorksheet
.Cells.Select()
.Cells.Delete()
For iC =0To colsTotal
.Cells(1, iC +1).Value = DataGridView1.Columns(iC).HeaderText
Next
For I =0To rowsTotal -1
For j =0To colsTotal -1
.Cells(I +2, j +1).value = DataGridView1.Rows(I).Cells(j).Value
Next j
Next I
.Rows("1:1").Font.FontStyle ="Bold"
.Rows("1:1").Font.Size =10
.Cells.Columns.AutoFit()
.Cells.Select()
.Cells.EntireColumn.AutoFit()
.Cells(1,1).Select()
EndWith
Catch ex As Exception
MsgBox("Export Excel Error "& ex.Message)
Finally
'RELEASE ALLOACTED RESOURCES
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
xlApp =Nothing
EndTry
EndSub