[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

.Net Excel 2007 Automation Performance Took HUGE Hit

Posted on 2009-02-20
7
Medium Priority
?
389 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:dsmrtn
  • 4
  • 3
7 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23699930
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
 

Author Comment

by:dsmrtn
ID: 23703175
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
 

Author Comment

by:dsmrtn
ID: 23703187
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 23704957
I used some base classes that I found here, and changed them to fit my needs:

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

Author Comment

by:dsmrtn
ID: 23706921
Nothing can be downloaded that hasn't gone through an approval process.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23707432
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
 

Accepted Solution

by:
dsmrtn earned 0 total points
ID: 23712642
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question