asked on

.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

Bob Learned

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.

dsmrtn

ASKER

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...".

dsmrtn

ASKER

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.

Bob Learned

I used some base classes that I found here, and changed them to fit my needs:

http://www.carlosag.net/Tools/ExcelXmlWriter/

dsmrtn

ASKER

Nothing can be downloaded that hasn't gone through an approval process.

Bob Learned

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.

ASKER CERTIFIED SOLUTION

dsmrtn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial