C# Multithreaded Access on Excel File

Question

I'm coding on an multithreaded C# Application which provides some statistics for an Excel File. I opened the File by following Code:

    private static Excel.Application excelApp = new Excel.Application();
    private static Excel.Workbook workbook = excelApp.Workbooks.Open(path);
    private static Excel.Worksheet worksheet = workbook.ActiveSheet;
    private static Excel.Range range = worksheet.UsedRange;
    private static int totalColumns = worksheet.UsedRange.Columns.Count;

To collect the Data out of the columns I'm using one Thread per column.

for (int columnCount = 1; columnCount <= /*range.Columns.Count*/totalColumns; columnCount++)
{
    Thread worker = new Thread(printSpread);
    worker.Start(columnCount);
}

I got HRESULT: 0x800A01A8 Error if I don't start a new Excel Application in each Thread. My question is, do I have to do this or is there an opportunity to use just one Excel Application? I do think, there is a problem in accessing the data from just one Application by mulitple Threads, which would explain the COM Exception. Thanks for your time and help.

score 0 · Answer 1 · answered May 31 '16 at 10:23

Wouldn't it be an option to parse the excell file to a (temporary) database-table, since databases tend to lend theirselves much better to multiple-access situations compared to a simple excell-file on-disk?

Gathering from the fact that your current code mainly seems to count the amount of columns in a row, a simple database could have that data updated on each data-change. Considering that; if you build a good database model, parsing the excell-files would pretty much auto-fill the database with the data you want to use.

This may however be a over-complicated approach as I'm not sure of your actual goals.

Longterm goal is to build an Application which parses the Data of the Excel File to an existing Database. Like an import. The Problem is, that the file doesn't suit database standards regarding normalization for example. — P. Edge, May 31 '16 at 10:58

Glauco Cucchiar · Accepted Answer · 2016-05-31T14:27:00.293

you can read excel file like a single table and save data in memory with a simple datatable. So, you can work in multithread after closing the excel file.

sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""
using (var conn = new OleDbConnection(sConnection)
{
    conn.Open();
    string query = "SELECT  * FROM [" + mySheetName + "]";
    var adapter = new OleDbAdapter(query, conn);
    var table = new DataTable();
    adapter.Fill(table);
    //or you can use datareader

    //Now you can close excel and dbConnection
    //and work in memory with datatable and threads
}

Thanks! Found this to help the implementation [link](http://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable) — P. Edge, May 31 '16 at 13:44

C# Multithreaded Access on Excel File

2 Answers2