用值计算行数(最大值) [英] Count lines (max) with values

问题描述

我想计算有值的行.我尝试了oSheet.Rows.Count，但这不起作用.关于这个有什么想法吗?

I would like to count the lines that have values. I tried oSheet.Rows.Count but that doesn't work. Any idea about this?

我的代码如下:

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets("Sheet")

oSheet.Range("A" & max).Value = "0000111"
oSheet.Range("B1").Value ="Name"

oBook.SaveAs("C:\New folder\excel\" & datenw & ".xlsx")
oExcel.Quit()

推荐答案

如注释中所述，以下代码应为您提供具有基于Range值的行数:

As said in the comments, the following code should get you the count of rows that have values based on your Range:

Dim rowCount As Integer = oSheet.UsedRange.Rows.Count()

但是，我认为您的代码有一个小问题.这可能行不通:

There is however a slight issue with your code I believe. This probably won't work:

oSheet = oBook.Worksheets("Sheet")

之所以不会，是因为新的Workbook上不存在"Sheet". "Sheet1"可以，因此需要将其更改为:

The reason it won't, is because "Sheet" doesn't exist on a new Workbook. "Sheet1" does, so this needs to be changed to:

oSheet = oBook.Worksheets("Sheet1")
'or
oSheet = oBook.Worksheets(1) 'remember Excel collections  are one based not zero based

最后我将看看您关闭Excel的方式，因为oExcel.Quit()可能会使Excel实例运行.看看链接到悉达思·劳特的位代码:

Lastly I would look at the way you are closing Excel as oExcel.Quit() is probably leaving an instance of Excel running. Have a look at this answer which links to Siddharth Rout's bit of code:

Private Sub ReleaseObject(ByVal obj As Object)
    Try
        Dim intRel As Integer = 0
        Do
            intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        Loop While intRel > 0
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

您还要确保以正确的顺序发布并发布所有内容.这通常是向后的顺序:

You also to make sure you release in the right order and release everything. This is usually in backwards order:

ReleaseObject(oSheet)
oBook.Close()
ReleaseObject(oBook)
oExcel.Quit()
ReleaseObject(oExcel)

尽管如此，我还是会考虑使用

However with all that said I would look at using the Microsoft.Office.Interop.Excel namespace directly rather than declaring objects:

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim oExcel As New Excel.Application

        Dim oWorkbooks As Excel.Workbooks = oExcel.Workbooks
        Dim oWorkbook As Excel.Workbook = oWorkbooks.Add()

        Dim oSheets As Excel.Sheets = CType(oWorkbook.Sheets, Excel.Sheets)
        Dim oWorksheet As Excel.Worksheet = CType(oSheets(1), Excel.Worksheet)

        Dim oARange As Excel.Range = oWorksheet.Range("A" & max.ToString()) 'Not sure what max is but I took the assumption it's an Integer
        oARange.Value = "0000111"

        Dim oBRange As Excel.Range = oWorksheet.Range("B1")
        oBRange.Value = "Name"

        Dim oUsedRange As Excel.Range = oWorksheet.UsedRange()
        Dim rowCount As Integer = oUsedRange.Rows.Count()

        oWorkbook.SaveAs("C:\Test.xlsx")

        ReleaseObject(oUsedRange)
        ReleaseObject(oBRange)
        ReleaseObject(oARange)

        ReleaseObject(oWorksheet)
        ReleaseObject(oSheets)

        oWorkbook.Close()
        ReleaseObject(oWorkbook)
        ReleaseObject(oWorkbooks)

        oExcel.Quit()
        ReleaseObject(oExcel)

    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            Dim intRel As Integer = 0
            Do
                intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Loop While intRel > 0
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

然后，我还将研究转向

I would also then look at turning Option Strict On:

将隐式数据类型转换限制为仅扩大转换，不允许后期绑定，并禁止导致对象类型的隐式键入.

Restricts implicit data type conversions to only widening conversions, disallows late binding, and disallows implicit typing that results in an Object type.

这篇关于用值计算行数(最大值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！