VB.NET SQL将数据网格填充到Excel模板 [英] VB.NET SQL Populated datagrid to Excel template

问题描述

我是新手，我手头有问题。我的应用程序中有四个datagrids，它们由sql命令填充。数据网格是



Datagridview1，Datagridview2，Datagridview3和Datagridview4。



我分配了一个按钮来转移这些是一个excel模板，这是我正在努力的地方。基本上，当我单击传输按钮时，我希望我的数据网格上显示的记录转到我的Excel模板中的特定位置。例如



我的excel表名是LossType.xlsx，excel中的tabname是Type1



Datagridview1应该从B3开始填充tabname Type1：G10 Datagridview2应该开始填充相同的tabname Type1但是来自B13：G20 Datagridview3应该开始填充相同的tabname Type1但是来自B26：G40 Datagridview4应该开始填充相同的tabname Type1但是来自B60：G80



很抱歉要问这么多东西，但我不知道如何完成这件事。任何帮助将不胜感激。



谢谢Satish

I’m a newbie and I have a problem in hand. I have four datagrids in my application which gets populated by a sql command. The datagrids are

Datagridview1, Datagridview2, Datagridview3 and Datagridview4.

I have assigned a button to transfer these to an excel template which is where I’m struggling. Basically when I click the transfer button I want the records displayed on my datagrid to go to a specific location in my excel template. For example

My excel sheet name is LossType.xlsx and tabname in excel is Type1

Datagridview1 should start populating tabname Type1 from B3:G10 Datagridview2 should start populating same tabname Type1 but from B13:G20 Datagridview3 should start populating same tabname Type1 but from B26:G40 Datagridview4 should start populating same tabname Type1 but from B60:G80

Sorry for asking so many things but I have no idea how to get this done. Any help would be greatly appreciated.

Thanks Satish

推荐答案

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(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")


For i = 0 To 7
    For j = 0 To 5
            xlWorkSheet.Cells(i + 3, j + 2) = datagridview1(j, i).Value.ToString()
    Next
Next

For i = 0 To 7
    For j = 0 To 5
            xlWorkSheet.Cells(i + 13, j + 2) = datagridview2(j, i).Value.ToString()
    Next
Next

For i = 0 To 14
    For j = 0 To 5
            xlWorkSheet.Cells(i + 13, j + 2) = datagridview3(j, i).Value.ToString()
    Next
Next

For i = 0 To 20
    For j = 0 To 5
            xlWorkSheet.Cells(i + 60, j + 2) = datagridview4(j, i).Value.ToString()
    Next
Next

xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

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

Dim res As MsgBoxResult
res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)
If (res = MsgBoxResult.Yes) Then
    Process.Start("d:\vbexcel.xlsx")
End If

您可以使用互操作 [ ^ ]创建MS Excel的新实例，打开现有文件并将数据从DataGridViews保存到其中。通过说保存数据，我的意思是：循环遍历每个网格中的行和列的集合。



伪代码：

You can use Interop[^] to create new instance of MS Excel, open existing file and save data from DataGridViews into it. By saying "save data", i mean: loop through the collection of rows and columns in each grid.

Pseudo code:

'DataGridView1
j = 3
for r=0 to DataGridView1.Rows.Count -1
    for c=0 to DataGridView1.Rows(r).Columns.Count -1
        SheetType1.Cells(j, c) = DataGridView1.Cell(r,c).Value
    next
    j += 1
next
'DataGridView2
j = 13
for r=0 to DataGridView2.Rows.Count -1
    for c=0 to DataGridView2.Rows(r).Columns.Count -1
        SheetType1.Cells(j, c) = DataGridView2.Cell(r,c).Value
    next
    j += 1
next
'and so on....

注意：

如您所见，重复了一些代码。有两个更改：我们操作的DataGridView（分别是DataGridView1到DataGridView4）和Sheet中的起始行（ j ）。在这种情况下，强烈建议将此代码分为程序或功能。



我建议阅读：如何从Visual Basic .NET自动化Microsoft Excel [ ^ ]



试试！当你遇到困难并回答详细问题时回到这里。

Note:
As you can see, some piece of code is repeated. There are two changes: DataGridView (respectively DataGridView1 to DataGridView4) on which we operate and starting row in Sheet (j). In that case it's strongly recommended to separate this code into procedure or function.

I suggest to read this: How to automate Microsoft Excel from Visual Basic .NET[^]

Try! Come back here when you get stuck and ask detailed question.

这篇关于VB.NET SQL将数据网格填充到Excel模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！