如何在datagridview中导出所有数据并将其放入excels中的特定单元格？ Vb.net [英] How to can I export all the data in my datagridview and place it into a specific cells in my excels? Vb.net

问题描述

我正在创建一个按钮，将我的datagridview中的所有数据导出到excel

但是我想将它放在特定的单元格中。我怎样才能做到这一点？我仍然是编程的初学者。



这段代码会将我的Datagridview中的所有数据导出到excel，但不会导出到我想要的特定单元格中。

 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 D ataGridView1.Columns.Count 
 xlWorkSheet.Cells（1，K）= DataGridView1.Columns（K  -  1）.HeaderText 
 xlWorkSheet.Cells第（i + 2，J + 1）= DataGridView1（J，I ）.Value 
 
下一个
下一个
下一个
 
 xlWorkSheet.SaveAs（CType（xlPath，String））
 xlWorkBook.Close（） 
 xlApp.Quit（）
 
 releaseObject（xlApp）
 releaseObject（xlWorkBook）
 releaseObject（xlWorkSheet）
 
 MsgBox（你可以找到文件C：\ Users\Alex\Desktop\print2）
 Dim res As MsgBoxResult 
 res = MsgBox（处理完成，你想打开文件吗？，MsgBoxStyle。 YesNo）
 If（res = MsgBoxResult.Yes）然后
 Process.Start（xlPath）
结束如果
 

我尝试了什么：



此代码仅用于将数据置于特定行中的一行我的Excel中的单元格，但我想把所有的数据我在excel中导出到特定单元格。

< 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（付款打印）
 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（）
 
尝试
 
如果是MsgBox（你想看看数据吗？在EXCEL？，MsgBoxS tyle.YesNo）= MsgBoxResult.Yes然后
 xlWorkSheet.SaveAs（xlPath）
 Process.Start（xlPath）
否则
退出Sub 
结束如果
 xlWorkBook.Close（）
 xlApp.Quit（）
 Catch ex As Exception 
 
结束尝试
 
 ReleaseObject（xlApp）
 ReleaseObject（ xlWorkBook）
 ReleaseObject（xlWorkSheet）
 End Sub 

解决方案

假设您希望导出的数据为在特定单元格中启动，您只需要将起始单元格的偏移量添加到放置数据的地址。

 ' 在单元格C5处启动表格： 
  Dim  startRow 作为 整数 =  5  
  Dim  startColumn 作为 整数 =  3  
 
 ' 创建标题行： 
 对于 j =  0   To  DataGridView1.ColumnCount  -   1  
 xlWordSheet.Cells（startRow，startColumn + j）= DataGridView1.Columns（j）.HeaderText 
 < span class =code-keyword>下一步 
 
 ' 创建数据行： 
 对于 i =  0    DataGridView1.RowCount  -   1  
 对于 j =  0    DataGridView1.ColumnC ount  -   1  
 xlWorkSheet.Cells（startRow + i +  1 ，startColumn + j）= DataGridView1（j，i）.Value 
  Next  
  Next  

亲爱的，



请看我的代码，像这样

 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（）
下一个
下一个
下一个
 
 worksheet.saveas（d：​​\ latihan.xls）
 workbook.close（）
 APP .quit（）
 
 
我最诚挚的问候，
 
 
 
 
 Muljanto 

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 

解决方案

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.

' 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

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

这篇关于如何在datagridview中导出所有数据并将其放入excels中的特定单元格？ Vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！