Can I Print ExcelWriter Generated Files from the Server?

Problem

I am trying to print ExcelWriter generated files. What are my options?

Solution

ExcelWriter does not currently support printing directly from the server.  In order to print, the contents of the Excel file must first be rendered. As ExcelWriter does not currently have the ability to render content, rendering is handled by Excel when the output file is opened by the client. This means that in order to support printing directly from a web server it would be necessary to have Excel on the server. However, having Excel on a web server is not recommended.

There are two workarounds that we recommend to meet your printing needs:

  • Save files to a dedicated printing server
  • Automate printing using a VBA macro, when the file is opened on the client

The ability to render Excel files is being planned for a future release of ExcelWriter.

Setting Print Options

Before printing you can set all your print options programatically through ExcelApplication.      Example code is below:

	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)

Note: Print options do not need to be set programmatically.  Any print options set in an input file will be preserved by ExcelWriter.

Solution 1 – Save files to a dedicated printing server

One workaround that several of our customers have implemented, is to have a separate box with Excel on it that is exclusively for printing. The files on the web server that are selected for printing are saved into the specified directory on the printing server. This can be done using OfficeWriter code and the UNC path, as shown below.

//Save the current ExcelWriter object to a UNC path
myExcelApplicaton.Save(MyWorkbook, @"\\printingServer\printingDirectory\" + fileName);

You can write a simple process that will run on the printing server and automatically print, using Excel, any file that is dropped to a certain directory.

Solution 2 – automate printing using a VBA Macro, when the file is opened on client

Using a VBA macro, you can automate printing of a file when it is opened on the client. ExcelWriter preserves macros, so they can be included in the input file or template.

This method (automatically printing 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 workbook:

'Method will run when the workbook is opened in Excel 
Private Sub Workbook_Open()
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

Related posts:

Leave a Reply

Your email address will not be published. Required fields are marked *