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
|
Share the post "Can I Print ExcelWriter Generated Files from the Server?"
Follow