VB,以及VB.NET自动化EXCEL

最新推荐文章于 2023-08-02 16:33:50 发布

fenglei11

最新推荐文章于 2023-08-02 16:33:50 发布

阅读量901

点赞数

文章标签： vb.net vb integer byte excel module

本文链接：https://blog.csdn.net/fenglei11/article/details/6079441

版权

最近由于工作关系，要从网站下载数据，填一个EXCEL，我一下子头大了。数据量太大。

最后写了个程序，发现EXCEL调用起来，挺方便，感觉很爽，thrilling!

vb6.0代码：

    Dim xls As Excel.Application
    Set xls = CreateObject("excel.application")
    Dim xlbook As Excel.Workbook
    Dim xlbooksource As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Set xlbook = xls.Workbooks.Open("C:/end.xls")
    Set xlbooksource = xls.Workbooks.Open("C:/book1.xls")
    Dim sheet2 As Excel.Worksheet
    Dim Num As Integer
         'Set xlsheet = xlbook.Worksheets("户县")
     Set xlsheet = xlbook.Worksheets("市区")
     xlsheet.Activate
     Dim column As Integer
     For column = 0 To 4
         Set sheet2 = xlbooksource.Worksheets(1 + column)
         Dim i As Byte
         Dim j As Byte
         Dim station As String
         Dim count As Integer
         Dim sum As Double
         sum = 0
         count = 0
         For i = 3 To xlsheet.UsedRange.Rows.count
             station = CStr(xlsheet.Cells(i, 1))
             station = Trim(station)
             Debug.Print (station)
             For j = 1 To sheet2.UsedRange.Rows.count
                 If Trim(CStr(sheet2.Cells(j, 4))) = station Then
                     xlsheet.Cells(i, 3 + column) = sheet2.Cells(j, 8)
                     sum = sum + xlsheet.Cells(i, 3 + column)
                     count = count + 1
                     Exit For
                 End If
             Next
             If station = "平均" Then
                 xlsheet.Cells(i, 3 + column) = sum / count
                 Exit For
             End If
         Next
    Next
   xlbook.SaveAs ("C:/test.xls")
   xls.Quit

VB.NET 下面代码：

.NET下面没有的CELL不能直接赋值，应该

把:cell(i,j)=xxx

改成:cell(i,j).Value= xxx

才成！

其它再无什么大的变化。

直接移值即可。

Imports Microsoft.Office Module Module1 Sub Main() Dim xls As New Microsoft.Office.Interop.Excel.Application() Dim xlsbook, xlsbookSource As Interop.Excel.Workbook xls.Visible = True 'End.xls是要保存数据的xls文件 xlsbook = xls.Workbooks.Open("C:/End.xls") Dim SheetSource As Microsoft.Office.Interop.Excel.Worksheet 'book1.xls文件为从网站复制下来xls的数据 xlsbookSource = xls.Workbooks.Open("C:/book1.xls") Dim Sheet As Interop.Excel.Worksheet = xlsbook.Worksheets("市区") Sheet.Activate() For Column As Byte = 3 To 7 SheetSource = xlsbookSource.Worksheets(Column - 2) Dim StationName As String Dim count As Byte = 0 Dim sum As Double = 0 For i As Byte = 2 To Sheet.UsedRange.Rows.Count - 1 StationName = Sheet.Cells(i, 1).Value.ToString().Trim() For j As Byte = 1 To SheetSource.UsedRange.Rows.Count If SheetSource.Cells(j, 4).Value.ToString().Trim() = StationName Then Sheet.Cells(i, Column).Value = SheetSource.Cells(j, 8).Value sum = sum + Sheet.Cells(i, Column).Value count += 1 Exit For End If Next If StationName = "平均" Then Sheet.Cells(i, Column).Value = sum / count End If Next Next xlsbook.Save() xlsbook.Close() xlsbookSource.Close() xls.Quit() End Sub End Module