Tekito Tekito - 4 years ago 274
Vb.net Question

Slow performance setting Excel sheet print settings

Using the Microsoft Office Interop libraries for Excel, I've written a routine in VB.Net that creates a large number of Excel worksheets, populates them and then formats them for printing. For every worksheet I call this small method to set the print settings:

Public Sub SetDefaultReportPrintSettings(orientation As ReportSheetOrientation, ws As Excel.Worksheet)

CType(ws.Parent, Excel.Workbook).Application.Windows(1).Zoom = 90

With ws.PageSetup
Select Case orientation
Case ReportSheetOrientation.Portrait : .Orientation = Excel.XlPageOrientation.xlPortrait
Case ReportSheetOrientation.Landscape : .Orientation = Excel.XlPageOrientation.xlLandscape
End Select
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.LeftMargin = ws.Application.InchesToPoints(If(orientation = ReportSheetOrientation.Portrait, 0.75, 0.5))
.RightMargin = ws.Application.InchesToPoints(0.5)
.TopMargin = ws.Application.InchesToPoints(0.75)
.BottomMargin = ws.Application.InchesToPoints(0.75)
.CenterHorizontally = True
End With

End Sub


When I benchmark, this sub alone is apparently taking about 0.98 seconds. I don't understand why toggling a handful of settings would take that long. It might not seem like much, but when generating hundreds (or more) sheets this really adds to the routine time. The Excel
application
instance is hidden, so I don't think this is a screen updating issue.

Any way to make this go faster?

Answer Source

Chris Neilen's comment (set Application.PrintCommunication=False during PageSetup modifications) significantly improved my run time. According to documentation, Excel by default is actively communicating with the printer, which helps explain the slow performance.

I got an even bigger speed gain by setting Excel's view to Normal instead of Page Break Preview. This made a big difference, not in the PageSetup code, but for the rest of my Excel routines. The documentation again explains Excel does additional calculations for page breaks.

https://msdn.microsoft.com/en-us/library/office/ff835544.aspx

https://support.microsoft.com/en-us/kb/199505

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download