c# backgroundworker thread using COM Interop writing multiple excel .xlsx files

Question

1.00/5 (1 vote)

See more:

I need to ask how to create multiple new excel spreadsheet files problematically from an arraylist on the c# backgroundworker thread

currently I have a function that goes through a for each loop and :

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

then it disposes of the excel object

by doing this in a backgroundworker thread, it causes all sorts of problems from blank worksheets to data split on multiple worksheets.

Would appreciate any feedback on how to go about this, basically want to show a marquee progressbar when the operation is being done, so I need to use the backgroundworker thread else if I don't use threading, the progressbar does not move, cause of all processing on the main GUI thread

Posted 9-Jul-12 14:40pm

AU Jase

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

AU Jase · Accepted Answer · 2012-07-21T02:42:00

Thanks to Dave,

I managed to use Excel OLEDB to write using background worker thread then go back with interop to format (which is way less cpu/process intensive !)

My next dilemma is how to determine which Office 2010 32/64 bit version is installed then adjust the cpu build on start up to that version so the Ace OLEDB driver does not fail !

Thanks,

Jase

Dave Kreskowiak · Accepted Answer · 2012-07-09T15:11:00

Solution 1

Considering the Excel object model is not at all thread-safe I would say that you cannot do what you want. Excel doesn't work from background threads. It's on the main thread or you run into the problems you've described.

Though, I've only done interop with Excel once.

I cant believe I'm saying this, but you could put a Application.DoEvents in the ProgressBar update code. Just be very very careful and not give the user anything to click on during this operation. If you don't, those buttons or whatnot will still work if they are enabled! Don't get caught in a re-entrancy trap where a button click kicks off long running work and you didn't disable the button.

Posted 9-Jul-12 15:11pm

Dave Kreskowiak

Comments

AU Jase 9-Jul-12 21:18pm

so it would be better to use oledb and use INSERT INTO ? would that solve the problem vs Excel COM Interop ?

Dave Kreskowiak 9-Jul-12 23:06pm

I don't know what you're doing so I couldn't tell you for sure. But, if you're just adding records to a sheet, the yes, it'll work. If you're doing more than that, no.

Dave Kreskowiak 9-Jul-12 23:06pm

I don't know exactly what you're doing, so I couldn't tell you for sure.

If all you're doing is adding records to a sheet, then yes. If you're doing other stuff, such as formatting the worksheet, then no.

AU Jase 10-Jul-12 1:07am

Dave,

Basically in a nutshell what I am trying to achieve:

a list of items which are the files for writing to excel files (1...n) meaning n files

in a function I loop using a foreach to write to excel

If I use a backgroundworker thread with profiling turned on, it works !
If I use a backgroundworker thread without profiling it fails.

I appreciate your answer, I just need to clarify:

1. Can I use oleDB to write, and it will be thread safe ?
2. When I manually click an item to save (n) and not in a batch (loop) 1..n, it works in the backgroundworker thread.

Could the Excel object creation and disposing (which I have used) cause these problems ?

Dave Kreskowiak 10-Jul-12 9:54am

It may look like you're using multiple copies of Excel, but you're not. You're using one Excel object. Since Excel is not thread safe you cannot tell Excel what to do from multiple threads at the same time. The restuls are unpredictable. Excel will only do one things at a time.

Adding other variables, such a profiling (slows down code in a BIG way!), will affect how your threads run. With profiling turned on, the threads may not be stepping on each other. On the next run, they might... There's no way to tell.

As for using OleDb, yes, it'll work multithreaded writing the files.

Could the Excel object creation and disposing cause the problems?? No.

AU Jase 13-Jul-12 2:33am

so basically Dave,

using OLEDB , SQL to CREATE TABLE (COLUMN HEADINGS) where Table is the worksheet
(creates the worksheet using the oledb connection string)
with a loop on the datatable rows
then using INSERT INTO TABLE (xxx,xxx) with OLEDB
and executing in a loop ?
then the sql non query execute
this can then be moved to a background worker thread ?

Dave Kreskowiak 13-Jul-12 8:11am

Don't take my word for it. Try it for yourself!