Zoom Down in EXCEL from an ASP.NET application

Question

Can anybody tell me how to set the zoom factor on an Excel sheet via a ASP.NET application. I believe the Excel Sheet object has a PageSetup.Zoom property which does not seem to be working. When I generate a report in Excel programatically through ASP.NET I get the zoom factor of all sheets in the workbook as 100%.

Here is a code sample

oSheet.PageSetup.CenterHorizontally = true;
oSheet.PageSetup.CenterVertically = true;
oSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
oSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
oSheet.PageSetup.Order = XlOrder.xlDownThenOver;
oSheet.PageSetup.Zoom = 85;

Another option is ActiveWindow.Zoom as generated by a macro but it is macro-specific. The whole point of asking this question is after the report has been generated the zoom factor of all the pages in the Excel workbook should be 85%. Any inputs would be highly appreciated.

Answer 1

PageSetup only affects printing. You need to use the Window Zoom. In C# it's something like:

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.ActiveWindow.Zoom = 150;

This only affects the sheet in the active window, not every sheet. You can also set the zoom through the workbook (wbk.Windows[0].Zoom) rather than xlApp.ActiveWindow.

The way to implement this for all sheets in a Workbook object MyWorkbook is to select all of the sheets and then hit the ActiveWindow:

MyWorkbook.Sheets.Select(Type.Missing);
MyWorkbook.Windows[1].Zoom = 150;

I tested this and Windows[1] seems to give you document-level customization. I had a problem embedding this code in the workbook's Startup event, but it seems to be fine further down the line.

Answer 2

SpreadsheetGear for .NET allows you to set the zoom factor of an Excel worksheet like this:

SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
worksheet.WindowInfo.Zoom = 150;

Microsoft does not support and recommends against using Office with COM Interop in ASP.NET (see this Considerations for server-side Automation of Office KB article).

You can see live C# and VB examples which demonstrate using SpreadsheetGear from ASP.NET here and download a free trial here.

Disclaimer: I own SpreadsheetGear LLC