none
How to read Excel worksheet into memory RRS feed

  • Question

  • I open an excel file using:

    Dim MyExcel As New Microsoft.Office.Interop.Excel.Application
            MyExcel.Workbooks.Open(Me.TxtBoxPracticeFile.Text)

    And can read cell by cell. However, this goes very slowly.

    I wonder if I can copy the entire worksheet into memory, including the data and formatting, so I could speed up the work. The files are big, with few 10K lines each. Each file is read, the data is processed and closed (read only).

    Thanks for any idea

    Sunday, February 10, 2019 6:48 AM

Answers

  • No need to open Excel File. 10k rows is a joke you can read 100k rows less then second.

    I will give you my example how i read excel files into memory -> DataSet. Also [F1] [F2] it's Excel column indexes.

    DataSet ds = new DataSet();
    OleDbConnection cn = null;
    OleDbDataAdapter cmd = null;
    string SheetName = "";
    
    cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + File_Name + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"");
    cmd = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "$A1:E8]", cn);
    cmd.Fill(ds, "ExcelFile");
    cmd = new OleDbDataAdapter("SELECT [F1], [F2], [F3], [F4], [F5], [F7], [F8], [F15], [F17] FROM [" + SheetName + "$A10:Q1000" + "]", cn);
    cmd.Fill(ds, "ExcelFile");

    • Marked as answer by YigalB Saturday, February 23, 2019 6:13 AM
    Wednesday, February 20, 2019 8:38 AM

All replies

  • No need to open Excel File. 10k rows is a joke you can read 100k rows less then second.

    I will give you my example how i read excel files into memory -> DataSet. Also [F1] [F2] it's Excel column indexes.

    DataSet ds = new DataSet();
    OleDbConnection cn = null;
    OleDbDataAdapter cmd = null;
    string SheetName = "";
    
    cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + File_Name + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"");
    cmd = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "$A1:E8]", cn);
    cmd.Fill(ds, "ExcelFile");
    cmd = new OleDbDataAdapter("SELECT [F1], [F2], [F3], [F4], [F5], [F7], [F8], [F15], [F17] FROM [" + SheetName + "$A10:Q1000" + "]", cn);
    cmd.Fill(ds, "ExcelFile");

    • Marked as answer by YigalB Saturday, February 23, 2019 6:13 AM
    Wednesday, February 20, 2019 8:38 AM
  • Hi, I get that spire.xls supports copying the entire worksheet into a new memory excel. Maybe you could have a try.

    Imports Spire.Xls
    
    Dim book As Workbook = New Workbook
    book.LoadFromFile("D:\123.xlsx")
    Dim sheet As Worksheet = book.Worksheets("Sheet1")
    Dim newBook As Workbook = New Workbook
    newBook.Version = book.Version
    newBook.Worksheets.Clear
    newBook.Worksheets.AddCopy(sheet, WorksheetCopyType.CopyAll)
    newBook.SaveToStream(memoryStream, FileFormat.Version2013)



    Thursday, March 7, 2019 6:34 AM
  • I like the idea of reading into memory, Will speed up the process.

    Yet the above didn't work for me - for start I use VB for that application, yet after translating, I still get errors for the "

    OleDbConnection

    "

    Should any import be used?

    Friday, March 8, 2019 6:29 AM
  • The import doesn't work - it is marked as unnecessary, with a message of "workbook is not defined"

    Friday, March 8, 2019 6:31 AM
  • I like the idea of reading into memory, Will speed up the process.

    Yet the above didn't work for me - for start I use VB for that application, yet after translating, I still get errors for the "

    OleDbConnection

    "

    Should any import be used?

    Ah sorry, you are using vb.net. You should add: Import System.Data.OleDb, if you are using Visual Studio community or higher version it must show you error and suggestion to add Import :) try now?
    Friday, March 8, 2019 6:41 AM
  • .....

    Should any import be used?

    Ah sorry, you are using vb.net. You should add: Import System.Data.OleDb, if you are using Visual Studio community or higher version it must show you error and suggestion to add Import :) try now?

    I added the import (I use VS2017 community), and still having error on the OleDbConnection.

    Perhaps it is because I use VB wrongly translated code:

            Dim file_Name = "place holder"
            Dim ds As DataSet = New DataSet
            Dim cn As New OleDbConnection
    
            Dim cmd As OleDbDataAdapter = Nothing
            Dim SheetName As String = ""
            cn = New OleDbConnection(("Provider=Microsoft.ACE.OLEDB.12.0;" + ("Data Source=" _
                            + (File_Name + ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"""")"))), cmd = newOleDbDataAdapter(SELECT * FROM [+SheetName+$A1:E8],cnUnknown)
            cmd.Fill(ds, "ExcelFile")
            cmd = New OleDbDataAdapter(("SELECT [F1], [F2], [F3], [F4], [F5], [F7], [F8], [F15], [F17] FROM [" _
                            + (SheetName + ("$A10:Q1000" + "]"))), cn)
            cmd.Fill(ds, "ExcelFile")
    

    PS

    I chose VB because I am migrating a working VB project from excel macro, and I regret that every time. C# seems so much better.

    Friday, March 8, 2019 7:46 AM
  • Well VB.NET is very similar to Excel VBA, so it's not bad step. C# require more practice and understanding, as VB.NET is very very friendly to user.

    Your code in VB.NET should look something like this. I'm using http://converter.telerik.com/ Coverter.

    Private Sub lulu()
        Dim ds As DataSet = New DataSet()
        Dim cn As OleDbConnection = Nothing
        Dim cmd As OleDbDataAdapter = Nothing
        Dim SheetName As String = ""
        cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & File_Name & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""")
        cmd = New OleDbDataAdapter("SELECT * FROM [" & SheetName & "$A1:E8]", cn)
        cmd.Fill(ds, "ExcelFile")
        cmd = New OleDbDataAdapter("SELECT [F1], [F2], [F3], [F4], [F5], [F7], [F8], [F15], [F17] FROM [" & SheetName & "$A10:Q1000" & "]", cn)
        cmd.Fill(ds, "ExcelFile")
    End Sub

    Friday, March 8, 2019 8:04 AM
  • When the code reached 
    cmd.Fill(ds, "ExcelFile")
    Exception was invoked with "Could not find installed ISAM"
    Saturday, March 9, 2019 9:26 AM
  • "Exception was invoked with "Could not find installed ISAM""

    It says Connection string was BAD. Did you copied what I wrote? 

    Tuesday, March 12, 2019 7:51 AM
  • "Exception was invoked with "Could not find installed ISAM""

    It says Connection string was BAD. Did you copied what I wrote? 

    Yes, with the following modification: 

    1- I gave value to the File_Name variable, to point the specific file to be opened

    2- Modified the Provider to: 

    Provider=Microsoft.Jet.OLEDB.4.0;

    Because the original failed running (something like it wasn't registered). What I used was copy from a working example I had.

    The error I get:

    Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll
    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    Could not find installable ISAM.

    Tuesday, March 12, 2019 2:06 PM
  • Use my given example and install: AccessDatabaseEngine.exe

    From: https://www.microsoft.com/en-us/download/details.aspx?id=13255

    And everything should be registered.

    Wednesday, March 13, 2019 6:36 AM
  • Use my given example and install: AccessDatabaseEngine.exe

    From: https://www.microsoft.com/en-us/download/details.aspx?id=13255

    And everything should be registered.

    Does it mean it will not work on other computer without installing it? 

    This application needs to be distributed to several other machines.

    Wednesday, March 13, 2019 2:23 PM
  • In all my cases ~95% already had these drivers from Windows Update. For the rest had to install manually.
    Friday, March 15, 2019 11:22 AM