将datagridview导出为ex​​cel [英] Export datagridview to excel

问题描述

我有以下代码，我正在尝试根据记录数从datagridview导出信息到excel文档，并从特定的行和列开始。请帮忙！

I have the following code and I am trying to export information from datagridview to an excel document based on the number of records and starting with a specific row and column. Please help!

Dim rowNo1 As Integer = 1
Dim colNo1 As Integer = 1
Dim colNo2 As Integer = 1
Dim numrow As Integer = 36
'Import Information
objExcelApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
objExcelBook = CType(objExcelApp.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
objExcelSheet = CType(objExcelBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
objExcelBook = objExcelApp.Workbooks.Open(sWorkbook)

For rowNo1 = 0 To DataGridView1.RowCount - 1
    For colNo1 = 1 To DataGridView1.ColumnCount - 1
        If DataGridView1.Columns(colNo1).Width > 0 Then
           If Not IsDBNull(DataGridView1.Item(colNo1, rowNo1).Value) Then ' Added this if statement to prevent DBNull to String error
                If Trim(DataGridView1.Item(colNo1, rowNo1).Value) <> "" Then
                     objExcelApp.Cells(numrow + 1, colNo2) = DataGridView1.Item(colNo1, rowNo1).Value
                End If
           End If
           If colNo2 >= DataGridView1.ColumnCount Then
                colNo2 = 1
           Else
                colNo2 = colNo2 + 1
           End If
        End If
    Next colNo1
    numrow = numrow + 1
Next rowNo1

基本上我希望它从第37行和第d栏开始。



我试过的：



我尝试了很多方法，但都从列A开始而不是列D。

Basically I want it to start at row 37 and column "d".

What I have tried:

I have tried numerous ways but all start at Column "A" not Column "D".

推荐答案

您应该学习尽快使用调试器。而不是猜测你的代码在做什么，现在是时候看到你的代码执行并确保它完成你期望的。



调试器允许你跟踪执行逐行检查变量，你会看到它有一个停止做你期望的点。

调试器 - 维基百科，免费的百科全书 [ ^ ]

掌握Visual Studio 2010中的调试 - A初学者指南 [ ^ ]



假设您的代码有效，我会简化这种方式

You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

Assuming your code work, I would simplify that way

Dim rowNo1 As Integer = 1
Dim colNo1 As Integer = 1
Dim colNo2 As Integer = 1
Dim numrow As Integer = 36
'Import Information
objExcelApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
objExcelBook = CType(objExcelApp.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
objExcelSheet = CType(objExcelBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
objExcelBook = objExcelApp.Workbooks.Open(sWorkbook)
 
For rowNo1 = 0 To DataGridView1.RowCount - 1
    For colNo1 = 1 To DataGridView1.ColumnCount - 1
        If DataGridView1.Columns(colNo1).Width > 0 Then
           If Not IsDBNull(DataGridView1.Item(colNo1, rowNo1).Value) Then ' Added this if statement to prevent DBNull to String error
                If Trim(DataGridView1.Item(colNo1, rowNo1).Value) <> "" Then
                     objExcelApp.Cells(rowNo1 + 37, colNo1+3) = DataGridView1.Item(colNo1, rowNo1).Value
                End If
           End If
           If colNo2 >= DataGridView1.ColumnCount Then
                colNo2 = 1
           Else
                colNo2 = colNo2 + 1
           End If
        End If
    Next colNo1
    numrow = numrow + 1
Next rowNo1

使用调试器查看代码实际执行的操作。

Use the debugger to see what your code is really doing.

这篇关于将datagridview导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！