locked
Very Slow to Create a Microsoft.Office.Interop.Excel object RRS feed

  • Question

  • Dear all,

    I try to create or read a Excel by "Microsoft.Office.Interop.Excel" in .Net Framework 4.0 (by VB.NET in VS2010.

    I find that it take ~ 2 seconds when we create a blank new Excel object for new Excel file or Read a existing Excel file

    I already exclude the worksheet and cell operation in my count but only measure "New Application()"

    Can I know is it normal to take so long time to instantiate an object without other operation? is there any method that I can have faster speed on Excel operation in my case?

    Imports Microsoft.Office.Interop.Excel
    Public Class ExcelHelper
        Private excelApp As Application
        Public Sub LoadExcel(filename As String)
            excelApp = New Application()
            Dim workBook As Workbook = excelApp.Workbooks.Open(filename)
            Dim numSheets As Integer = workBook.Sheets.Count
            For idxSheet As Integer = 1 To numSheets
                Dim sheet As Worksheet = workBook.Sheets(idxSheet)
                Dim sheetName = sheet.Name
                Dim r As Range = sheet.UsedRange
                Dim array(,) As Object = r.Value(XlRangeValueDataType.xlRangeValueDefault)
                If array IsNot Nothing Then

    Above is the sample code that I open a existing Excel,

    But when I put the timestamp and the CSV is very very small (~ 1-2K file size) it still take 2 seocnds even we don't have any cell operation.

    Best Regards

    Thursday, May 30, 2013 1:28 AM

Answers

  • Hi

    Test the OLEDB for CSV file and it really very fast compare with the Interop Excel

    thx alot

    • Marked as answer by TungLee Friday, May 31, 2013 9:45 AM
    Friday, May 31, 2013 9:45 AM

All replies

  • I have stopped using the Interop library becuawe of performance issues.  The interop library uses a scripting language to second commands between the VS application and the Excel workbook.  Creating the commands in VS and then having the Excel Workbook driver interpret these commands is very time consuming.

    A CSV file is simply a text file with the fileds (columns) seperated by comas so you can open any text reader class and then use string split() method to seperate the fields.  Another mehtod is to treat the the excel file as a database and use oledb.  the oledb can use either the Jet Engine Driver (xls, or csv) or the ACE driver (xlsx or csv) to open, read, write (not format) an excel workbook.  You can use SQL stements to perform any of these examples

    There are lots of example of doing this on the web.  Reading the excel data is extremely fast using this method.  the are three steps you need to do

    1) Get a connections string.  Search web for "Connection string CSV".

    2) Open connection - Use oledbconnection

    3) Create a oledbcommand to process the SQL strings.

    Note: You can also use oledataadapter to fill a datatable and then bind a datagridview to the table to view results.


    jdweng

    • Proposed as answer by Mike Feng Friday, May 31, 2013 4:43 AM
    Thursday, May 30, 2013 4:59 AM
  • Hi

    Test the OLEDB for CSV file and it really very fast compare with the Interop Excel

    thx alot

    • Marked as answer by TungLee Friday, May 31, 2013 9:45 AM
    Friday, May 31, 2013 9:45 AM