This method to automatically print the spreadsheet when opened on the client requires that you use a pre-existing Excel file, designed directly in Microsoft Excel. This pre-existing Excel file contains VBA code in the Workbook_Open() event that prints the active sheet:
Private Sub Workbook_Open()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
You can then open the Excel file with the ExcelApplication object or ExcelTemplate object and process it as usual. If you open the file with the ExcelApplication object, you can use the PageSetup object to programmatically set print related properties of the worksheets as shown below.
|
[C#]
|
[VB.NET]
|
|
ExcelApplication xlApp = new ExcelApplication();
Workbook wb = xlApp.Open(Page.MapPath("AutoPrint.xls"));
Worksheet ws = wb.Worksheets[0];
PageSetup ps = ws.PageSetup; // get the PageSetup object
ps.PrintArea = ws.CreateArea("A1:J10"); // set the area to be printed
// format center header as Arial, Bold and display the file name
ps.CenterHeader = "&\"Arial,Bold\"&F";
ps.RightHeader = "Printed at &T on &D"; // display time and date
ps.CenterFooter = "&P of &N"; // display page numbers (“# of #”)
// set the margins
ps.LeftMargin = 0.75;
ps.RightMargin = 0.75;
ps.TopMargin = 1.0;
ps.BottomMargin = 1.0;
ps.HeaderMargin = 0.5;
ps.FooterMargin = 0.5;
ps.PrintComments = false; // don’t print comments
ps.Orientation = PageSetup.PageOrientation.Landscape; // print in landscape
ps.CenterHorizontally = true; // center print area horizontally
ps.CenterVertically = true; // center print area vertically
Dim xlApp As ExcelApplication = New ExcelApplication()
Dim wb As Workbook = xlApp.Open(Page.MapPath("AutoPrint.xls"))
Dim ws As Worksheet = wb.Worksheets(0)
'set the PageSetup properties
Dim ps As PageSetup = ws.PageSetup 'get the PageSetup object
ps.PrintArea = ws.CreateArea("A1:J10") 'set the area to be printed
'format center header as Arial, Bold and display the file name
ps.CenterHeader = "&""Arial,Bold""&F"
ps.RightHeader = "Printed at &T on &D" 'display time and date information
ps.CenterFooter = "&P of &N" 'display page numbers (“# of #”)
'set the margins
ps.LeftMargin = 0.75
ps.RightMargin = 0.75
ps.TopMargin = 1.0
ps.BottomMargin = 1.0
ps.HeaderMargin = 0.5
ps.FooterMargin = 0.5
ps.PrintComments = False 'don’t print comments
ps.Orientation = PageSetup.PageOrientation.Landscape 'print in landscape
ps.CenterHorizontally = True 'center print area horizontally
ps.CenterVertically = True 'center print area vertically
xlApp.Save(wb, Page.Response, "AutoPrint.xls", False)
|
Attached is an Excel workbook with the above macro defined (AutoPrint.xls).
|
