I have noticed very poor performance when copying worksheets between workbooks.
I am creating multiple worksheets in individual worksbooks. I add each workbook to a generic queue before processing them. I use multiple threads to populate those worksheets. When all work is done, I use a single (main) thread to combine all of my worksheets into a single workbook.
private Queue<Workbook> queueWorkBook = new Queue<Workbook>();
I then combine all of those workbook (and sheets) back into a single workbook before saving it.
Workbook dequeuedWorkBook = queueWorkBook.Dequeue();
int intWorkSheet = workBookToBeSaved.Worksheets.Add();
workBookToBeSaved.Worksheets[intWorkSheet].Name = dequeuedWorkBook.Worksheets.Name;
What supprises me most about this process it that I was able to create and populate 113 workbooks and worksheets in under six minutes. However, it took me over 18 minutes to save those sheets back into a single workbook. This is true even when I create populate each of the worksheets on a single thread. The difference in performance has nothing to do with the fact that I am using multiple threads. It still takes over 3 times as long to conbine the worksheets into a workbook.
What would you suggest is my performance problem and how can I correct this behavior?