多个数据网格到一个Excel工作表 [英] Multiple datagrids to one excel sheet

问题描述

我有以下代码，它将记录从我的数据网格转移到我的Excel电子表格。

目前此代码适用于一个数据网格到Excel工作表。现在我需要改进下面的代码，以便它可以用于多个数据网格。我希望帮助扩展此代码，以便我可以将记录从3个数据网格拉到另一个下面的同一个Excel工作表。

  Dim  excel 正如 Microsoft.Office.Interop.Excel.Application 
 
 尝试 
 excel = 新 Microsoft.Office.Interop.Excel.Application 
 excel.Workbooks.Open（  C：\ Satish\TestExcel \vbexcel.xlsx）
  Dim  i 作为 整数，j 作为 整数 
  Dim  diff  As   Integer  =  1  
 ' 如果你希望来自dgv elese的列标题省略块 
 ''' '' - 这些''''''''' '
  对于 j =  0    DataGridView1.ColumnCount  -   1  
 excel.Worksheets（ 1 ）。cells（ 1 ，j +  1 ）= DataGridView1.Columns（j）.Name 
 下一步 
 diff + =  1  
 ''' '''''''''''  ''''''''
  对于 i =  0    DataGridView4.RowCount  -   1  
 
 如果 DataGridView4.Rows（i）.IsNewRow =  False  然后 
 对于 j =  0  < span class =code-keyword> To  DataGridView4.ColumnCount  -   1  
 
 excel.Worksheets（ 1 ）。cells（i + diff，j +  1 ）= DataGridView4.Item（j，i）.Value 
 下一步 
 结束 如果 
 下一步 
 excel.Worksheets（ 1 ）。选择（）
 excel.ActiveWorkbook（）。保存（）
 excel.Workbooks.Close（）
 excel.Quit（）
 excel =  Nothing  
  Catch  ex 作为 System.Runtime.InteropServices.COMException 
 MessageBox.Show（ 访问Excel时出错：  + ex.ToString（））
  Catch  ex  As  Exception 
 MessageBox.Show（ 错误： + ex.ToString（））
 < span class =code-keyword>结束 尝试 

解决方案

Hello Satish。



如果您的Datagrids填充了类似的数据集，那么只需在现有的Excel填充上添加另一个迭代。



Dim i As Integer，j As Integer，DatagridCycle as Integer



这将是Datagrids每个循环的计数器。 br />


如果需要，存储最后一个Datagrid索引或为每个Datagrid for Offset添加一个Counter。

  Dim  i 作为 整数，j  As   Integer ，DatagridCycle  as  整数 
 
 
 对于 DatagridCycle =  0   to   2  然后 
 
 
  Dim  diff  As   Integer  =  1  
 '  if你想要来自dgv elese的列标题省略块 
 ''' ''''''  '''''''
  对于 j =  0    DataGridView1.ColumnCount  -   1  
 
 选择 案例 DatagridCycle '  0 = 1. DGV; 1 = 2.DGV; 2 = 3. DGV  
 
 案例  0  
 excel.Worksheets（ 1 ）。cells（ 1 ，j +  1 ）= DataGridView1.Columns（j）.Name 
 案例  1  
 excel.Worksheets（ 1 ）。cells（ 1 ，j +  1 ）= DataGridView2.Columns（j）.Name ' 更改DGV控制名称plz  
 案例  2  
 excel.Worksheets（ 1 ）。cells（ 1 ，j +  1 ） = DataGridView3.Columns（j）.Name ' 更改DGV控制名称plz  
 
 结束 选择 
 
 下一步 
 diff + =  1  ' 问题？稍后阅读问题几行;） 
 ''' '''''  ''''''''''''
  对于 i =  0    DataGridView4.RowCount  -   1  
 
 如果 DataGridView4.Rows（i）.IsNewRow =  False  那么 
 对于 j =  0  至 DataGridView4.ColumnCount  -   1  
 
 excel.Worksheets（ 1 ）。 cells（i + diff，j +  1 ）= DataGridView4.Item（j，i）.Value 
  Next  
 
 
 下一步 '  DataGridCycle Iteration END  

问题？我认为您存储在 diff 下一个列位置？



如果是这样，并且您想要扩展Excel - 稍后，您将需要一个入口点（Offset）重新启动。



您可以将其存储在另一个变量中，或者最有效的方法是使用Excel API / Interop。



为此我会在目标表中请求Top中的下一个空闲单元格，然后存储应用程序中的索引值并将其用作Offset。 br $>


excel.Worksheets（1）.cells（1，offset + j + 1）= DataGridView1.Columns（j）.Name



我多年前用过的......但是在第一次使用EXCEL Interop时很棘手。



我总是使用我的应用程序中的单元格位置表示以供以后使用。



我希望这会有所帮助。



BTW这是我的第一个快速回应。 ;）

I have the below code which will transfer the records from my datagrid to my excel spreadsheet.
Currently this code works for one datagrid to an excel sheet. Now I need to improve the below code so that it can work for multiple datagrids. I want help to extend this code so that I can pull the records from 3 data grids to the same excel sheet one below another.

Dim excel As Microsoft.Office.Interop.Excel.Application
 
Try
excel = New Microsoft.Office.Interop.Excel.Application
excel.Workbooks.Open("C:\Satish\TestExcel\vbexcel.xlsx")
Dim i As Integer, j As Integer
Dim diff As Integer = 1
' if you want column header from dgv elese omit the block 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
For j = 0 To DataGridView1.ColumnCount - 1
excel.Worksheets(1).cells(1, j + 1) = DataGridView1.Columns(j).Name
Next
diff += 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
For i = 0 To DataGridView4.RowCount - 1
 
If DataGridView4.Rows(i).IsNewRow = False Then
For j = 0 To DataGridView4.ColumnCount - 1
 
excel.Worksheets(1).cells(i + diff, j + 1) = DataGridView4.Item(j, i).Value
Next
End If
Next
excel.Worksheets(1).select()
excel.ActiveWorkbook().Save()
excel.Workbooks.Close()
excel.Quit()
excel = Nothing
Catch ex As System.Runtime.InteropServices.COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString())
End Try

解决方案

Hello Satish.

If your Datagrids are filled with similar Datasets, then just add another Iteration over your existing Excel filling.

Dim i As Integer, j As Integer, DatagridCycle as Integer

This will be then the counter for each Cycle of the Datagrids.

If needed, store the last Datagrid Index or add just a Counter for each Datagrid for Offset(s).

Dim i As Integer, j As Integer, DatagridCycle as Integer


For DatagridCycle = 0 to 2 then


Dim diff As Integer = 1
' if you want column header from dgv elese omit the block
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For j = 0 To DataGridView1.ColumnCount - 1

    Select Case DatagridCycle ' 0 = 1. DGV ; 1 = 2.DGV ; 2 = 3. DGV 
    
    Case 0
       excel.Worksheets(1).cells(1, j + 1) = DataGridView1.Columns(j).Name
    Case 1
       excel.Worksheets(1).cells(1, j + 1) = DataGridView2.Columns(j).Name 'change DGV Control Name plz
    Case 2
       excel.Worksheets(1).cells(1, j + 1) = DataGridView3.Columns(j).Name 'change DGV Control Name plz
    
    End Select
    
Next
diff += 1  'Question? Read the Question a couple of lines later ;)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For i = 0 To DataGridView4.RowCount - 1

If DataGridView4.Rows(i).IsNewRow = False Then
For j = 0 To DataGridView4.ColumnCount - 1

excel.Worksheets(1).cells(i + diff, j + 1) = DataGridView4.Item(j, i).Value
Next


Next 'DataGridCycle Iteration END

Question? I asume You store in diff the next Column position?

If so, and you want to extend the Excel-Table later, you will need an entrypoint (Offset) where to restart.

You could store it inside another Variable or the most effective way is to use Excel API/Interop.

For that i would request inside the Target Table the next free cell from the Top, store then the index value inside your Application and use that as Offset.

excel.Worksheets(1).cells(1,offset+ j + 1) = DataGridView1.Columns(j).Name

I used that Years ago... But was tricky in the first times to get on with EXCEL Interop.

I used always a Representation of the Cell Positions inside my Application for later use.

I hope that will help.

BTW this is my first Quick Response. ;)

这篇关于多个数据网格到一个Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！