在 vb.net 中的现有 excel 文件中附加数据 [英] append data in existing excel file in vb.net

问题描述

我正在复制剪贴板中超过 20 列和 500 多行的 gridview 值，并将该值复制到 excel 文件中.

i am copying gridview value which has more than 20 columns and 500+ rows in clipboard and copying that value into excel file.

    Dim excelApp As New Excel.Application
    Dim aWorkbook As Excel.Workbook
    aWorkbook = excelApp.Workbooks.Add()
    excelApp.Visible = False
    Dim strWorkSheetName As String = "MyData"
    Dim aWorkSheet As Excel.Worksheet

    aWorkSheet = aWorkbook.Sheets.Add()


    aWorkSheet.name = TextBox1.Text

    aWorkSheet.Activate()

    Dim intRows As Integer = DataGridView1.SelectedRows.Count
    Dim strRangeString As String = "C4:G" & intRows.ToString



    'Open the existing Template
    Dim _Tpath As String = Application.StartupPath & "\Template.xlsx"
    aWorkbook = excelApp.Workbooks.Add(_Tpath)

    Dim alpha As String
    Dim num As Integer
    Dim cell As String
    Dim count As Integer = 0

    For Each col As DataGridViewColumn In DataGridView1.Columns
        alpha = Convert.ToChar(65 + count)
        num = Convert.ToInt32(7)
        cell = Convert.ToString(alpha & "7")

        aWorkSheet.Range(cell).Value = col.HeaderText.ToString
        count = count + 1
    Next

    For row As Integer = 0 To dt2.Rows.Count - 1
        For col As Integer = 0 To dt2.Columns.Count - 1
            alpha = Convert.ToChar(65 + col)
            num = Convert.ToInt32(8 + row)
            cell = Convert.ToString(alpha & num)

            aWorkSheet.Range(cell).Value = dt2(row)(col).ToString
        Next
    Next

但现在我想将该剪贴板值粘贴到假设从第 7 行及以上的特定位置上的现有 Excel 文件(用作模板)中.我写了一些代码，其中的值是逐个单元格粘贴的，这需要很多时间.有没有其他方法可以将 gridview 值粘贴到现有的 excel 文件中?

but now i want to paste that clipboard value into an existing excel file(used as template) on a specific position suppose from 7th row and above. i have written some code where values are pasting cell by cell and it is taking lot of time. is there any other way to paste gridview value in an existing excel file ?

推荐答案

如果您知道 GridView 中有多少列和行，那么您可以使用 Worksheet.Paste() 使用Range 覆盖适当大小的区域.因此，如果您的 GridView 有 5 列和 500 行，并且您希望您的选择从 2 行向下和 2 列开始，您可以这样做

If you know how many columns and rows are in your GridView then you can use Worksheet.Paste() using a Range that covers an appropriately sized area. So if your GridView has 5 columns and 500 rows and you want your selection to start 2 rows down and 2 columns in, you could do

aWorksheet.Paste(aWorksheet.Range(aWorksheet.Cells(2,2),aWorksheet.Cells(502,7)))

这篇关于在 vb.net 中的现有 excel 文件中附加数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！