none
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..

    Thursday, May 24, 2012 4:59 PM

Answers

  • 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

    Thursday, May 24, 2012 5:37 PM
  • 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

    Saturday, May 26, 2012 5:29 AM
  • 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

    Monday, May 28, 2012 2:02 PM

All replies

  • 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

    Thursday, May 24, 2012 5:37 PM
  • 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

    Thursday, May 24, 2012 5:41 PM
  • 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.

    Saturday, May 26, 2012 4:22 AM
  • 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

    Saturday, May 26, 2012 4:56 AM
  • 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

    Saturday, May 26, 2012 5:29 AM
  • 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

    Saturday, May 26, 2012 5:31 AM
  • The answer given is working perfectly. We need to get Workbook and sheet then rows and columns.. Fine.. Thank you Kevin..
    Monday, May 28, 2012 9:43 AM
  • 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..?
    Monday, May 28, 2012 1:41 PM
  • 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

    Monday, May 28, 2012 2:02 PM