Click here to Skip to main content
11,565,623 members (43,270 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Excel
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 Jase630
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
AU Jase at 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 at 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 at 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 at 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 at 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 at 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 at 13-Jul-12 8:11am
   
Don't take my word for it. Try it for yourself!

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

  Print Answers RSS


Advertise | Privacy | Mobile
Web03 | 2.8.150624.2 | Last Updated 21 Jul 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100