Click here to Skip to main content
15,886,137 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello
please help
How to export data from datagridview to excel
when i click button1 then open excel but blank data export from datagridview

when i click button2 then a error show

please help

http://www.mediafire.com/file/vcm9sg3o8qzpp5t/Capture.PNG

http://www.mediafire.com/file/6r5xgztxzqhbz1z/Button+2+error.png

What I have tried:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim app_xls As Object
        Dim lig_cpt, col_cpt As Integer
        app_xls = CreateObject("excel.application")
        app_xls.workbooks.add()

        app_xls.visible = True
        'kkkkkkk
        Try
            For col_cpt = 0 To DataGridView1.ColumnCount - 4
                app_xls.activesheet.cell(1, col_cpt, +1).value = DataGridView1.Columns(col_cpt).HeaderText
            Next
            For lig_cpt = 0 To DataGridView1.Rows.Count - 1
                For col_cpt = 0 To DataGridView1.ColumnCount - 4

                    If IsNumeric(DataGridView1.Item(col_cpt, lig_cpt).Value) Then
                        app_xls.activesheet.cells(lig_cpt + 2, col_cpt + 1).value = CDbl(DataGridView1.Item(col_cpt, lig_cpt).Value)
                    Else
                        app_xls.activesheet.cells(lig_cpt + 2, col_cpt + 1).value = DataGridView1.Item(col_cpt, lig_cpt).Value
                    End If

                Next
                
            Next
        Catch ex As Exception

        End Try
    End Sub

======================================================

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim ExcelApp As Object, ExcelBook As Object
        Dim ExcelSheet As Object
        Dim i As Integer
        Dim j As Integer

        'create object of excel
        ExcelApp = CreateObject("Excel.Application")
        ExcelBook = ExcelApp.WorkBooks.Add
        ExcelSheet = ExcelBook.WorkSheets(1)

        With ExcelSheet
            For i = 1 To Me.DataGridView1.RowCount
                .cells(i, 1) = Me.DataGridView1.Rows(i - 1).Cells("id").value
                For j = 1 To DataGridView1.Columns.Count - 1
                    .cells(i, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
                Next
            Next
        End With

        ExcelApp.Visible = True
        '
        ExcelSheet = Nothing
        ExcelBook = Nothing
        ExcelApp = Nothing
    End Sub
Posted
Updated 14-Apr-17 4:39am
v2
Comments
[no name] 13-Apr-17 12:54pm    
You got an error but can't be bothered to copy/paste the error text? Read the error message then logically apply what the message tells you to your code where the error occurs.
Jayanta Modak 13-Apr-17 13:08pm    
http://www.mediafire.com/file/6r5xgztxzqhbz1z/Button+2+error.png

error text is Column named id cannot be found. Parameter name: columnName
[no name] 13-Apr-17 13:35pm    
Okay so that is pretty self explanatory. What is your question? Can you not logically apply that error message to your code?
Jayanta Modak 13-Apr-17 14:04pm    
if I delete this line then no error, data export into excel, but now two problems
header text and id is not come in excel
Jayanta Modak 13-Apr-17 22:02pm    
I am new in vb.net so, I don't know right process. please help me sir. please

To set the value of any cell use this syntax:
VB
app_xls.activesheet.Range(Range).Value = Value

where Range contains a string with the coordinates of the desired cell - e.g.:
VB
app_xls.activesheet.Range("A1").Value = Value

to put a value to the cell in column A of the first row.
 
Share this answer
 
Comments
Jayanta Modak 14-Apr-17 4:04am    
i solve the one problem data export to excel but header text is not come on the excel, can you help me. i use the

Dim ExcelApp As Object, ExcelBook As Object
Dim ExcelSheet As Object
Dim i As Integer
Dim j As Integer

'create object of excel
ExcelApp = CreateObject("Excel.Application")
ExcelBook = ExcelApp.WorkBooks.Add
ExcelSheet = ExcelBook.WorkSheets(1)

With ExcelSheet

For i = 1 To Me.DataGridView1.RowCount
.cells(i, 1) = Me.DataGridView1.Rows(i - 1).Cells(0).Value
For j = 1 To DataGridView1.Columns.Count - 1
.cells(i, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
Next
Next

End With

ExcelApp.Visible = True
'
ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing

this coding
NightWizzard 14-Apr-17 5:41am    
If you try to replace the column headers A, B, C,....(and so forth): you can't. Put your headers in the first row (A1, B1...) and put your data in the following rows.
Jayanta Modak 14-Apr-17 9:44am    
I DON'T TRY TO REPLACE ANYTHING, I JUST WANT TO INSERT COLUMN HEADER INSERT TO EXCEL FILE. PLEASE HELP ME
NightWizzard 14-Apr-17 10:00am    
You have to put the header names the same way you put the data into the cells. Have a closer look to your nested loops and try to imagine, which cells are affected in each step - there's the error! If you use my example with the Range object, you may recognize, what is wrong with your code.
Take a look to this codeline :
VB
app_xls.activesheet.cell(1, col_cpt, +1).value = DataGridView1.Columns(col_cpt).HeaderText

Behind col_cpt you have entered an additional comma which is complete wrong here - I suppose you didn't wanted to place it there.
See what you code is doing if you remove it ...
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900