DataGridView需要导出到excel文件中

问题描述：

我的程序包含一个datagridview，用户在运行时用手动填充数据。当用户点击保存时，我希望将datagridview导出到稍后可以访问的excel文件。DataGridView需要导出到excel文件中

目前的代码，我想出了一个错误类型为“空引用异常”

Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application() 

    If xlApp Is Nothing Then 
     MessageBox.Show("Excel is not properly installed!!") 
     Return 
    End If 


    Dim xlWorkBook As Excel.Workbook 
    Dim xlWorkSheet As Excel.Worksheet 
    Dim misValue As Object = System.Reflection.Missing.Value 
    Dim i As Integer 
    Dim j As Integer 

    xlWorkBook = xlApp.Workbooks.Add(misValue) 
    xlWorkSheet = xlWorkBook.Sheets("sheet1") 
    'xlWorkSheet.Cells(1, 1) = "Sheet 1 content" 
    'xlWorkSheet.Cells(3, 3) = "Sheet 1 content" 

    If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then 
     Exit Sub 
    End If 



    For k As Integer = 0 To DataGridView1.Columns.Count - 1 
     xlWorkSheet.Cells(1, k + 1) = DataGridView1.Columns(k).HeaderText 
    Next 
    For i = 0 To DataGridView1.RowCount - 1 
     For j = 0 To DataGridView1.ColumnCount - 1 

      xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString() 

     Next 
    Next 



    xlWorkBook.SaveAs("C:\test\DATAGRID.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, 
    Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue) 
    xlWorkBook.Close(True, misValue, misValue) 
    xlApp.Quit() 

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

    MessageBox.Show("Excel file created , you can find the file C:\test\DATAGRID.xls")

只是出于好奇，是否有任何理由你使用System.Reflection.Missing.Value而不是Nothing？ – vbnet3d

由于某些原因，当我尝试使用Nothing保存它时出现了一些问题，因此我尝试了这种方式，代码的工作方式没有标题文本下的行代码。所以保存的文件只包含我的datagridview的头文件 – devN

DataGridView1（j，i）.Value.ToString（）的值是什么？它是空的吗？ – vbnet3d

答

谢谢你们的帮助

我发现这段代码，其实我想要做什么

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 
    Dim rowsTotal, colsTotal As Short 
    Dim I, j, iC As Short 
    System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor 
    Dim xlApp As New Excel.Application 
    Dim misValue As Object = System.Reflection.Missing.Value 


    Try 
     Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add 
     Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet) 

     rowsTotal = DataGridView1.RowCount - 1 
     colsTotal = DataGridView1.Columns.Count - 1 
     With excelWorksheet 
      .Cells.Select() 
      .Cells.Delete() 
      For iC = 0 To colsTotal 
       .Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText 
      Next 
      For I = 0 To rowsTotal - 1 
       For j = 0 To 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() 
     End With 
     excelBook.SaveAs("C:\test\DATAGRID.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, 
    Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue) 
     MessageBox.Show("Excel file created , you can find the file C:\test\DATAGRID.xls") 




    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 
    End Try 
End Sub

看到我上面的评论...这与上面的问题中的代码做了同样的事情，除了它正确地访问DataGridView的单元格值。 – vbnet3d

是的，谢谢你的帮助 – devN

答

当您访问DataGridView的单元格时，它的功能与Excel互操作对象的功能不同。而不是使用：

DataGridView1(j, i).Value.ToString()

你应该使用：

DataGridView1.Rows(i).Cells(j).Value.ToString()

Excel的互操作对象简单地使用列和行作为一个指标，但在DataGridView（和大多数其他.NET数据对象）存储它们作为嵌套集合。

DataGridView需要导出到excel文件中

相关推荐