如何使用vb.net将数据附加到现有的excel文件？ [英] how to append data to existing excel file using vb.net?

问题描述

我有一个代码，找到一个现有的Excel文件中的最后一个空行。我想从5个文本框中将数据插入列中的下一个5个单元格。我不知道如何编码。请帮助我，这里是我的代码：

 使用xlWorkSheet 
如果EXL.WorksheetFunction.CountA（.Cells）< ;> 0然后
 lRow = .Cells.Find（What：=*，_ 
 After：=。Range（A2），_ 
 LookAt：= Excel.XlLookAt.xlPart， _ 
 LookIn：= Excel.XlFindLookIn.xlFormulas，_ 
 SearchOrder：= Excel.XlSearchOrder.xlByRows，_ 
 SearchDirection：= Excel.XlSearchDirection.xlPrevious，_ 
 MatchCase：=假的
 Else 
 lRow = 1 
如果
 
结束
  

解决方案

这是您的问题的两个不同的解决方案。我喜欢第二个更好的，因为你可以从任何列中确定一个最后一个，如果你把它引用到另一个列。

  Dim nextrow As Integer = excel.Rows.End（XlDirection.xlDown）.Row + 1 
 
 Dim ws As Worksheet = excel.ActiveSheet 
 Dim nRow = ws.Range（A& ws .Rows.Count）.End（XlDirection.xlUp）.Row + 1 
  

然后你必须分配一个值是：

  excel.Range（A& nRow）.Value =test
 excel.range（B& nRow）.value =adjacent column
  

如果你想将它循环到下面的5个单元格，然后执行以下操作：

  Dim ws As Worksheet = excel.ActiveSheet 
 Dim lRow = ws.Range（A& ws.Rows.Count）.End（XlDirection.xlUp）.Row 
 
对于i = 1至5 
 excel.Range（A & lRow）.Offset（i，0）.Value =variable here
 Next 
  

i have code which finds the last non empty row in an existing excel file. i want to insert data from 5 textbox to the next 5cells in column. i dont know how to code it. please help me, here is my code:

With xlWorkSheet
        If EXL.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A2"), _
                          LookAt:=Excel.XlLookAt.xlPart, _
                          LookIn:=Excel.XlFindLookIn.xlFormulas, _
                          SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                          SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

    End With

解决方案

Here are two different solutions to your problem. I like the second better because you can determine a lastrow from any column and if you cared reference it to another column.

Dim nextrow As Integer = excel.Rows.End(XlDirection.xlDown).Row + 1

Dim ws As Worksheet = excel.ActiveSheet
Dim nRow = ws.Range("A" & ws.Rows.Count).End(XlDirection.xlUp).Row + 1

Then all you have to do to assign a value is:

excel.Range("A" & nRow).Value = "test"
excel.range("B" & nRow).value = "adjacent column"

If you want to loop it to 5 cells below that then do:

Dim ws As Worksheet = excel.ActiveSheet
Dim lRow = ws.Range("A" & ws.Rows.Count).End(XlDirection.xlUp).Row

For i = 1 To 5
    excel.Range("A" & lRow).Offset(i, 0).Value = "variable here"
Next

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