.Net Excel 2007 Automation Performance Took HUGE Hit

I have a VB.Net 2005/Excel 2007 Automation app that reads in excel input, templates, and gets data from SQL server to build excel output files.  Currently it builds around a 100 files.  Originally, it took 7 minutes a file (lots of copy and paste to facilitate formatting) - or 11.5 hours to build all files.  This was on a single CPU workstation.  I moved app to  another PC with dual core and 3GB ram and process dropped to less 1 minute or 1.5 hours.  As I began to fine tune the formatting and add features, something changed and I'm now at 7-9 minutes a file which is now over 13 hours.  It is like I told it to skip the 2nd CPU or something.

I have a lot of cut-n-pasts like the one listed below.  Again, the error always trips on the paste, and if I put a break on my Catch I can then set next statement to the xlFormattingWS.Activate() and hit GO, and it runs along for a short while (error on same output file) or even a very very long while (error 10-30 output files later).

' Set Font, Size, background from template formatting values
xlFormattingWS.Activate()
xlFormattingWS.Range(xlFormattingWS.Cells(3, 2), xlFormattingWS.Cells(3, 2)).Select()
xlFormattingWS.Range(xlFormattingWS.Cells(3, 2), xlFormattingWS.Cells(3, 2)).Copy()
xlOutPutWS.Activate()
xlOutPutWS.Range(xlRateSheetWS.Cells(iNextAvailRow, iNextAvailCol), xlOutPutWS.Cells(iNextAvailRow, iNextAvailCol)).Select()
xlOutPutWS.Paste()
' Set tier title value
xlOutPutWS.Cells(iNextAvailRow, iNextAvailCol) = sMyText.TrimEnd

Open in new window

dsmrtnAsked:
Who is Participating?
 
dsmrtnConnect With a Mentor Author Commented:
I figured my issue out.  I moved massive amounts of files off then on my my hard drive, plus did a few large software installs - all related to my changing PCs - and my hard drive was severely fragmented.  Now it is an 80GB drive with 41% free space.  Prior to defragging the drive over the wekend I replaced the bulk of my Excel automation Copy and paste with specific moves, reducing the copy to just once and then doing the paste portion of just those attributes I wanted, but it didn't help.

So, I checked the drive, saw the amount of fragmentation and ran the defrag.  Prior to heavy disck activity my files were being build in the 1 minute range.  After fragmentation 8-10 minutes a file.  After defrag, back to 1 minute.
0
 
Bob LearnedCommented:
I wonder if file size is an import consideration?  I am currently working on a C# DLL to create Open XML Excel workbooks, and I would think that would be much faster (and easier) than Excel automation.
0
 
dsmrtnAuthor Commented:
I knew Automation was slow, did NOT know it was as slow as it was, and I certainly didn't know it was this unstable.  I plan to replace the Automation with some like like Open XML or a 3rd party component.  Automation seme okay aty the time because other groups are using it in my area, and I had to support both Excel 2003 & 2007 so this offered the same API for both.

My output file sizes range from 50K to 80K.  Mostly white space (about a 1/3 empty cells).

Microsoft should enhance the wording on their disclaimer using Office Automation to include - ..."potentially a negative career changing choice...".
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
dsmrtnAuthor Commented:
I should add that both CPUs show about 8-12% usage now when running slow, but ran at 50-70% usage when running faster.  So this makes sense in - just don't know what it limiting it to 8-10% CPU.

I am in the process of testing the program with the bulk of the copy-n-paste code removed and replaced with local variable declared appropriately to hold font and border attributes doing straight moves.
0
 
Bob LearnedCommented:
I used some base classes that I found here, and changed them to fit my needs:

http://www.carlosag.net/Tools/ExcelXmlWriter/
0
 
dsmrtnAuthor Commented:
Nothing can be downloaded that hasn't gone through an approval process.
0
 
Bob LearnedCommented:
I understand, so you stick to your Excel automation, and I hope you find a solution.  I was trying to find a different direction that might help you get where you need to be.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.