How to can I export all the data in my datagridview and place it into a specific cells in my excels? Vb.net

Question

0.00/5 (No votes)

See more:

I am creating a button that will export all the data in my datagridview into excel
But I want to put it in a specific cell. How can I do that? I am still a beginner in programming.

This code will export all the data in my Datagridview to excel but not in a specific cells, that I want to do.

Dim xlApp As Microsoft.Office.Interop.Excel.Application
     Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
     Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
     Dim misValue As Object = System.Reflection.Missing.Value
     Dim i As Integer
     Dim j As Integer

     xlApp = New Microsoft.Office.Interop.Excel.Application
     xlWorkBook = xlApp.Workbooks.Add(xlPath)
     'xlWorkBook = xlApp.Workbooks.Add(misValue)
     'xlWorkSheet = xlWorkBook.Sheets("sheet2")
     xlWorkSheet = CType(xlWorkBook.Sheets("sheet2"), Microsoft.Office.Interop.Excel.Worksheet)

     For i = 0 To DataGridView1.RowCount - 1
         For j = 0 To DataGridView1.ColumnCount - 1

             For k As Integer = 1 To DataGridView1.Columns.Count
                 xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                 xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value

             Next
         Next
     Next

     xlWorkSheet.SaveAs(CType(xlPath, String))
     xlWorkBook.Close()
     xlApp.Quit()

     releaseObject(xlApp)
     releaseObject(xlWorkBook)
     releaseObject(xlWorkSheet)

     MsgBox("You can find the file C:\Users\Alex\Desktop\print2")
     Dim res As MsgBoxResult
     res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)
     If (res = MsgBoxResult.Yes) Then
         Process.Start(xlPath)
     End If

What I have tried:

this code is only for one row that will place the data in specific cell in my excel but I want to put all the data that i exported in my excel to a specific cell.

<pre>Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim t As Integer
        Dim s As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(xlPath)
        xlWorkSheet = xlWorkBook.Sheets("Payment Printing")
        xlWorkSheet.Cells(t + 6, s + 8) = DATE_OF_PAYMENTTextBox.Text()
        xlWorkSheet.Cells(t + 7, s + 2) = PAID_TOTextBox.Text()
        xlWorkSheet.Cells(t + 12, s + 8) = AMOUNTTextBox.Text()
        xlWorkSheet.Cells(t + 12, s + 7) = TOTextBox.Text()
        xlWorkSheet.Cells(t + 12, s + 6) = FROMTextBox.Text()
        xlWorkSheet.Cells(t + 12, s + 3) = ACTIVITYTextBox.Text()
        xlWorkSheet.Cells(t + 17, s + 3) = TYPE_OF_EXPENSETextBox.Text()
        xlWorkSheet.Cells(t + 20, s + 3) = ACCOUNT_CODETextBox.Text()
        xlWorkSheet.Cells(t + 23, s + 3) = CHECKTextBox.Text()
        xlWorkSheet.Cells(t + 24, s + 3) = BANKTextBox.Text()
        xlWorkSheet.Cells(t + 25, s + 3) = DATE1TextBox.Text()
        xlWorkSheet.Cells(t + 23, s + 6) = VATABLETextBox.Text()
        xlWorkSheet.Cells(t + 24, s + 6) = SUBJECT_TO_W_TAXTextBox.Text()
        xlWorkSheet.Cells(t + 25, s + 6) = W_TAX_RATETextBox.Text()
        xlWorkSheet.Cells(t + 23, s + 8) = INPUT_VATTextBox.Text()    
        xlWorkSheet.Cells(t + 24, s + 8) = W_TAXTextBox.Text()
        xlWorkSheet.Cells(t + 25, s + 8) = BALANCETextBox.Text()
        xlWorkSheet.Cells(t + 21, s + 8) = TOTAL_PHPTextBox.Text()

        Try

            If MsgBox("WOULD YOU LIKE TO SEE THE DATA IN EXCEL?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                xlWorkSheet.SaveAs(xlPath)
                Process.Start(xlPath)
            Else
                Exit Sub
            End If
            xlWorkBook.Close()
            xlApp.Quit()
        Catch ex As Exception

        End Try

        ReleaseObject(xlApp)
        ReleaseObject(xlWorkBook)
        ReleaseObject(xlWorkSheet)
    End Sub

Posted 5-Aug-18 17:59pm

Jamim

Updated 6-Aug-18 4:33am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2018-08-06T04:33:00

Assuming you want the exported data to start at a specific cell, you just need to add the offset of the starting cell to the address where you put the data.

VB.NET

' Start the table at cell C5:
Dim startRow As Integer = 5
Dim startColumn As Integer = 3

' Create the header row:
For j = 0 To DataGridView1.ColumnCount - 1
    xlWordSheet.Cells(startRow, startColumn + j) = DataGridView1.Columns(j).HeaderText
Next

' Create the data rows:
For i = 0 To DataGridView1.RowCount - 1
    For j = 0 To DataGridView1.ColumnCount - 1
        xlWorkSheet.Cells(startRow + i + 1, startColumn + j) = DataGridView1(j, i).Value
    Next
Next

Member 12813074 · Answer 2 · 2018-08-05T18:49:00

Dear All,

Please see my code, like this

 Dim APP As Object
        Dim worksheet As Object
        Dim workbook As Object
        APP = CreateObject("excel.application")
        workbook = APP.Workbooks.add
        worksheet = workbook.Worksheets("sheet1")
        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                For k As Integer = 1 To DataGridView1.Columns.Count
                    worksheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                    worksheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                Next
            Next
        Next

        worksheet.saveas("d:\latihan.xls")
        workbook.close()
        APP.quit()


My best regards,




Muljanto

How to can I export all the data in my datagridview and place it into a specific cells in my excels? Vb.net

2 solutions

Solution 4

Solution 1

Add your solution here

Preview 0