How to get total number of rows and column from excel?

Question

 Dim oApp As New Excel.Application
    Dim oWBa As Excel.Workbook

 oWBa = oApp.Workbooks.Open(excelFile)
            Dim oWS As Excel.Worksheet = DirectCast(oWBa.Worksheets(1), Excel.Worksheet)
            oApp.Visible = False
    Dim oRng As Excel.Range
            Dim se, so As String
            oRng = oWS.Cells

Upto this fine, I need to get the count of rows and columns. Please help me..

Answer 1

Hello,

The following uses early binding, requires one OpenDialog component. One point of interest, this could be done with less code but I always code so that all automation objects are properly releases hence this requires more code.

The output will appear in the IDE output window.

Form level

Option Strict On
Option Explicit On
Option Infer On
Imports System
Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop

Code to get used rows and columns

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim InitialPath As String = "C:\DotnetLand2010\Excel\ExcelIssue\bin\Debug"
    OpenFileDialog1.Title = "Please select a file to open"
    OpenFileDialog1.FileName = ""
    If IO.Directory.Exists(InitialPath) Then
        OpenFileDialog1.InitialDirectory = InitialPath
    Else
        OpenFileDialog1.InitialDirectory = _
            System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
    End If
    OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls"
    If OpenFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName)
        xlApp.Visible = False
#If KSG_USE Then
            xlWorkSheet = CType(xlWorkBook.Sheets(3), Excel.Worksheet)
#Else
            xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
#End If
        '
        ' To ensure proper object disposal I have used three range
        ' variables.
        '
        Dim UsedRange = xlWorkSheet.UsedRange
        Dim RowRange = UsedRange.Rows
        Dim ColRange = UsedRange.Columns
        Console.WriteLine("Sheet: {0} Rows used {1} Columns used {2}",
                          xlWorkSheet.Name, RowRange.Count, ColRange.Count)
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
        '
        ' Objects are released last to first
        '
        If Not ColRange Is Nothing Then
            Marshal.FinalReleaseComObject(ColRange)
            ColRange = Nothing
        End If
        If Not RowRange Is Nothing Then
            Marshal.FinalReleaseComObject(RowRange)
            RowRange = Nothing
        End If
        If Not UsedRange Is Nothing Then
            Marshal.FinalReleaseComObject(UsedRange)
            UsedRange = Nothing
        End If
        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If
        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If
        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
#If KSG_USE Then
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
#End If
    End If
End Sub

---

KSG

Answer 2

Forgot to mention, in the code provided the default worksheet is checked, if you want to specifiy the worksheet use the code in the IF KSG_USE by defining KSG_USE under Project properties, compile tab, advance compile options, add KSG_USE to custom constants.

---

KSG

Answer 3

KSG-

Need some help with one more Excel function.  I have a dynamic dataset that I was able to insert into an Excel file using another of your responses.  I want to be able to select the range of cells based on the total row and column count.  In VBA this can be done using range(cells(row#1, Col#1), cells(row#2, Col#2)) but I cannot figure out how to do this in VS2010. 

Let me know if you need additional details.

Answer 4

You can use OLDBD application programming interface (API), with classes like Connection, Command, and DataAdapter. With them you fill DataTable, and then count number of rows and columns.

Imports System.Data
Imports System.Data.OleDb
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0")
Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM  YourSheetName", con)
Dim dt As DataTable = New DataTable
da.Fill(dt)
Dim rows As Integer = 0
If (dt.Rows.Count > 0) Then
    rows = dr.Rows.Count
    columns = dr.Columns.Count
End If

---

Mitja

Answer 5

KSG-

Need some help with one more Excel function.  I have a dynamic dataset that I was able to insert into an Excel file using another of your responses.  I want to be able to select the range of cells based on the total row and column count.  In VBA this can be done using range(cells(row#1, Col#1), cells(row#2, Col#2)) but I cannot figure out how to do this in VS2010. 

Let me know if you need additional details.

Perhaps this will assist http://support.microsoft.com/kb/291308 - start at bullet 4. Personally I use this syntax with OleDb as shown below. In short after getting the used rows and columns I create an SQL statement where my anchor is A1. The DataTable holding the data since the connection string indicates no header (first row is data) each column is F1, F2, F3 etc. There are several ways to mess with this but that is over what you are looking for and can get tricky but totally doable. Any ways start with the link.

Note  the last code section must be placed into a code module.

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim SelectStatement As String = ""
        Dim InitialPath As String = "C:\Dotnet2010\Excel\GetUsedRowsAndColumns\bin\Debug"
        OpenFileDialog1.Title = "Please select a file to open"
        OpenFileDialog1.FileName = ""
        If IO.Directory.Exists(InitialPath) Then
            OpenFileDialog1.InitialDirectory = InitialPath
        Else
            OpenFileDialog1.InitialDirectory = _
                System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        End If
        OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls"
        If OpenFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName)
            xlApp.Visible = False
#If KSG_USE Then
            xlWorkSheet = CType(xlWorkBook.Sheets(3), Excel.Worksheet)
#Else
            xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
#End If
            '
            ' To ensure proper object disposal I have used three range
            ' variables.
            '
            Dim UsedRange = xlWorkSheet.UsedRange
            Dim RowRange = UsedRange.Rows
            Dim ColRange = UsedRange.Columns
            SelectStatement = String.Format("{0}$A1:{1}{2}", xlWorkSheet.Name, ColRange.Count.ExcelColumnName, RowRange.Count)
            Console.WriteLine("Sheet: {0} Rows used {1} Columns used {2} [{3}]",
                              xlWorkSheet.Name,
                              RowRange.Count,
                              ColRange.Count,
                              String.Format("{0}$A1:{1}{2}", xlWorkSheet.Name, ColRange.Count.ExcelColumnName, RowRange.Count)
                              )
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
            '
            ' Objects are released last to first
            '
            If Not ColRange Is Nothing Then
                Marshal.FinalReleaseComObject(ColRange)
                ColRange = Nothing
            End If
            If Not RowRange Is Nothing Then
                Marshal.FinalReleaseComObject(RowRange)
                RowRange = Nothing
            End If
            If Not UsedRange Is Nothing Then
                Marshal.FinalReleaseComObject(UsedRange)
                UsedRange = Nothing
            End If
            If Not xlWorkSheet Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            End If
            If Not xlWorkBook Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBook)
                xlWorkBook = Nothing
            End If
            If Not xlWorkBooks Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBooks)
                xlWorkBooks = Nothing
            End If
            If Not xlApp Is Nothing Then
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
            End If
#If KSG_USE Then
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
#End If
            UseOldDbDemo(OpenFileDialog1.FileName, SelectStatement)
        End If
    End Sub
    Private Sub UseOldDbDemo(ByVal FileName As String, ByVal SQL As String)
        Dim Builder As New OleDb.OleDbConnectionStringBuilder _
            With _
            { _
                .DataSource = FileName, _
                .Provider = "Microsoft.Jet.OLEDB.4.0" _
            }
        Builder.Add("Extended Properties", "Excel 8.0; IMEX=1;HDR=No;")
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = Builder.ConnectionString
            }
            Using cmd As New OleDb.OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText = "SELECT * FROM [" & SQL & "]"
                }
                Dim dt As New Data.DataTable
                cn.Open()
                dt.Load(cmd.ExecuteReader)
                DataGridView1.DataSource = dt
            End Using
        End Using
    End Sub

Place into a code module.

<System.Runtime.CompilerServices.Extension()> _
Public Function ExcelColumnName(ByVal Index As Integer) As String
    Dim chars = New Char() _
        { _
            "A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, _
            "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
            "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c _
        }
    Index -= 1
    Dim columnName As String
    Dim quotient = Index \ 26
    If quotient > 0 Then
        columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
    Else
        columnName = chars(Index Mod 26).ToString()
    End If
    Return columnName
End Function

---

KSG

Answer 6

One last thing, in my last reply my OleDb connection is hard wired to pre-Excel 2007, easy enough to change but might as well (if trying it) test with a .xls file first.

---

KSG

Answer 7

The answer given is working perfectly. We need to get Workbook and sheet then rows and columns.. Fine.. Thank you Kevin..

Answer 8

Ok I had another problem, Actually my excel sheet contains 512X512 data, If I started to read the data means System getting slow. Is there any way to read the data in efficient manner..?

Answer 9

Here is a thought, if you select all data in an Excel worksheet via OleDb it should pick up all rows and all columns. Download my demo solution and work with the project ReadSheet1_xlsx_demo which is hard-wired to read a Excel 2007 file. If you don't have Excel 2007 then change the private form level variable ExcelFileName to a pre-Excel 2007 file then change the connection string using the connection string in my last code reply.

Project http://kevininstructor.home.comcast.net/~kevininstructor/ProjectsDotNet.html (second project from the top of list).

Once downloaded and loaded in Visual Studio replace all code in form1 with the following

Public Class Form1
    Private ExcelFileName As String = IO.Path.Combine(Application.StartupPath, "Sample.xlsx")
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IO.File.Exists(ExcelFileName) Then
            MessageBox.Show("Missing Excel file.")
            Exit Sub
        End If
        Dim dtSheet1 As New DataTable
        Using cn As New System.Data.OleDb.OleDbConnection
            ' OleDbConnectionStringBuilder is nice in that you do not need to concatenate your connection string together
            Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = ExcelFileName, .Provider = "Microsoft.ACE.OLEDB.12.0"}
            Builder.Add("Extended Properties", "Excel 12.0; IMEX=1;HDR=No;")
            cn.ConnectionString = Builder.ConnectionString
            cn.Open()
            Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
                ' Alias each column and put them in a different order then in Sheet1
                cmd.CommandText = "SELECT * FROM [Sheet1$]"
                Dim dr As System.Data.IDataReader = cmd.ExecuteReader
                dtSheet1.Load(dr)
            End Using
        End Using
        DataGridView1.DataSource = dtSheet1
    End Sub
End Class

See if this speeds things up along with getting all of the data. I would time from the start of form load to the line dtSheet1.Load as the following line which assigns the DataTable to the DataGridView might take time and is really not put of the read operation. Any ways this is the best I have to offer other than using Aspose Cells library which I do not think would be much faster.

There are other projects which are there for other reasons, basically the solution was created to answer multiple question in the past.

---

KSG