In the Word component, I can pass in a:-
System.Drawing.Printing.PrinterSettings item to let me set advanced printer settings such as tray/duplex etc. but I cannot see how to do the same thing using Cells.
I know I can set the printer name with .ToPrinter(“printerName”); but cannot see a duplex property anywhere.
The only way I think this can be done is to convert the sheet to PDF but that then has conversion issues
Please can you also show me a code example of how to print to different trays?
Thanks
Jon
Hi,
I have logged your request into our tracking system with an id: CELLSNET-30608. We will look into it and get back to you soon.
Thank you.
Any news on this yet as I still need to print duplex sheets?
Hi,
I am afraid, this feature is not yet implement. We have logged your comment in our database. Hopefully, it will be fixed soon.
Hi,
We can support it by using CustomPrint, so you can pass CustomPrintPageEventHandler to ImageorPrintOptions and control print parperbin or print copies in CustomPrint.
Screenshot:
Please see the following code example:
C#
using System;
using System.Collections;
using System.Drawing.Printing;
using Aspose.Cells;
using Aspose.Cells.Rendering;
namespace TestAspose
{
class Test
{
private SheetRender sr = null;
private PrintDocument pd = new System.Drawing.Printing.PrintDocument();
public void ToPrinter()
{
Workbook book = new Workbook(@"f:/download/Test.xlsx");
ImageOrPrintOptions io = new ImageOrPrintOptions();
io.ImageFormat = System.Drawing.Imaging.ImageFormat.Tiff;
ArrayList prnLst = new ArrayList(PrinterSettings.InstalledPrinters);
prnLst.Sort();
Console.WriteLine("Total printers installed: {0}\n", prnLst.Count);
foreach (string printer in prnLst)
{
Console.WriteLine("\t- {0}", printer);
}
String readLine = Console.ReadLine();
PrintPageEventHandler pe = new PrintPageEventHandler(PrintPage);
io.CustomPrintPageEventHandler = pe;
foreach (Worksheet ws in book.Worksheets)
{
sr = new SheetRender(ws, io);
sr.ToPrinter((string)prnLst[int.Parse(readLine) - 1]);
}
}
void PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs ev)
{
ev.PageSettings.PrinterSettings.Duplex = Duplex.Default;
int currentIndex = sr.CustomPrint(true, ev);
if (currentIndex != sr.PageCount)
ev.HasMorePages = true;
else
ev.HasMorePages = false;
}
}
}
Hi there,
Unfortunately, that solution only works for individual sheets that extend to multiple pages whereas I need one that prints each sheet as a separate page within the duplex document. Below is my solution however there is still a slight bug with the duplex direction. Printing on our printer with Word merged PDFs is normally done with Vertical however when sending worksheets prints they print upside down so there is a bug in there somewhere… I will add a #hack to my code to make it use horizontal for the moment but please let me know when this is fixed otherwise my documents will all emerge upside down again!
Thanks
Jon
using System;
using System.Drawing.Printing;
using System.IO;
using Aspose.Cells;
namespace ExcelDuplexPrinting
{
class Program
{
<span style="color:blue;">private</span> <span style="color:blue;">static</span> <span style="color:#2b91af;">PrinterSettings</span> printsettings = <span style="color:blue;">null</span>;
<span style="color:blue;">private</span> <span style="color:blue;">static</span> <span style="color:#2b91af;">PageSettings</span> pagesettings = <span style="color:blue;">new</span> <span style="color:#2b91af;">PageSettings</span>();
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><summary></span>
<span style="color:gray;">///</span><span style="color:green;"> Set this to your duplex capable printer name</span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"></summary></span>
<span style="color:blue;">private</span> <span style="color:blue;">const</span> <span style="color:blue;">string</span> DUPLEX_PRINTER_NAME = <span style="color:#a31515;">"F1_MX_P1"</span>;
<span style="color:blue;">static</span> <span style="color:blue;">void</span> Main(<span style="color:blue;">string</span>[] args)
{
Aspose.Cells.<span style="color:#2b91af;">License</span> cellLicense = <span style="color:blue;">new</span> Aspose.Cells.<span style="color:#2b91af;">License</span>();
cellLicense.SetLicense(<span style="color:#a31515;">"Aspose.Total.lic"</span>);
Aspose.Pdf.<span style="color:#2b91af;">License</span> pdfLicense = <span style="color:blue;">new</span> Aspose.Pdf.<span style="color:#2b91af;">License</span>();
pdfLicense.SetLicense(<span style="color:#a31515;">"Aspose.Total.lic"</span>);
<span style="color:blue;">try</span>
{
printsettings = GetPrinterSettings(DUPLEX_PRINTER_NAME, <span style="color:blue;">true</span>, <span style="color:#2b91af;">Duplex</span>.Vertical, 1);
pagesettings.Margins = <span style="color:blue;">new</span> System.Drawing.Printing.<span style="color:#2b91af;">Margins</span>(0, 0, 0, 0);
PrintExcelBookAsDuplex(<span style="color:#a31515;">"sheet1.xls"</span>);
}
<span style="color:blue;">catch</span> (<span style="color:#2b91af;">Exception</span> ex)
{
<span style="color:#2b91af;">Console</span>.Write(ex.ToString());
}
<span style="color:#2b91af;">Console</span>.WriteLine(<span style="color:#a31515;">"Press Q to quit"</span>);
<span style="color:blue;">while</span> (<span style="color:#2b91af;">Console</span>.ReadKey().KeyChar != <span style="color:#a31515;">'q'</span>)
{ }
}
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><summary></span>
<span style="color:gray;">///</span><span style="color:green;"> Gets the printer settings.</span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"></summary></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><param name=</span><span style="color:gray;">"printerPathToCheck"</span><span style="color:gray;">></span><span style="color:green;">The printer path to check.</span><span style="color:gray;"></param></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><param name=</span><span style="color:gray;">"duplexRequired"</span><span style="color:gray;">></span><span style="color:green;">if set to </span><span style="color:gray;"><c></span><span style="color:green;">true</span><span style="color:gray;"></c></span><span style="color:green;"> [duplex required].</span><span style="color:gray;"></param></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><param name=</span><span style="color:gray;">"duplexMode"</span><span style="color:gray;">></span><span style="color:green;">The duplex mode.</span><span style="color:gray;"></param></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><param name=</span><span style="color:gray;">"copiesRequired"</span><span style="color:gray;">></span><span style="color:green;">The copies required.</span><span style="color:gray;"></param></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><returns></returns></span>
<span style="color:blue;">public</span> <span style="color:blue;">static</span> <span style="color:#2b91af;">PrinterSettings</span> GetPrinterSettings(<span style="color:blue;">string</span> printerPathToCheck, <span style="color:blue;">bool</span> duplexRequired, <span style="color:#2b91af;">Duplex</span> duplexMode, <span style="color:#2b91af;">Int16</span> copiesRequired)
{
<span style="color:#2b91af;">PrinterSettings</span> setting = <span style="color:blue;">new</span> <span style="color:#2b91af;">PrinterSettings</span>();
<span style="color:#2b91af;">PrinterSettings</span>.<span style="color:#2b91af;">StringCollection</span> printers = <span style="color:#2b91af;">PrinterSettings</span>.InstalledPrinters;
<span style="color:blue;">foreach</span> (<span style="color:blue;">string</span> item <span style="color:blue;">in</span> printers)
{
<span style="color:blue;">if</span> (item.Contains(printerPathToCheck))
{
setting.PrinterName = item;
<span style="color:blue;">break</span>;
}
}
<span style="color:blue;">if</span> (setting.IsValid)
{
<span style="color:blue;">if</span> (duplexRequired && setting.CanDuplex)
setting.Duplex = duplexMode;
<span style="color:blue;">else</span> <span style="color:blue;">if</span> (duplexRequired && !setting.CanDuplex)
<span style="color:blue;">throw</span> <span style="color:blue;">new</span> <span style="color:#2b91af;">ArgumentException</span>(<span style="color:#a31515;">"Cannot use duplex with this printer"</span>);
setting.Copies = copiesRequired;
}
<span style="color:blue;">return</span> setting;
}
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><summary></span>
<span style="color:gray;">///</span><span style="color:green;"> Prints the excel book as duplex. </span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><example></span><span style="color:green;">PrintExcelBookAsDuplex("sheet1.xls");</span><span style="color:gray;"></example></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"></summary></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><param name=</span><span style="color:gray;">"itemToPrint"</span><span style="color:gray;">></span><span style="color:green;">The item to print.</span><span style="color:gray;"></param></span>
<span style="color:blue;">internal</span> <span style="color:blue;">static</span> <span style="color:blue;">void</span> PrintExcelBookAsDuplex(<span style="color:blue;">string</span> itemToPrint)
{
<span style="color:#2b91af;">Workbook</span> book = <span style="color:blue;">new</span> <span style="color:#2b91af;">Workbook</span>(itemToPrint);
book.CalculateFormula();
<span style="color:green;">// Can only print duplex in PDF at the moment</span>
Aspose.Pdf.<span style="color:#2b91af;">FileSpecification</span> spec = ConvertExcelDocToFileSpecification(book, itemToPrint);
<span style="color:green;">//create PdfViewer object</span>
Aspose.Pdf.Facades.<span style="color:#2b91af;">PdfViewer</span> viewer = <span style="color:blue;">new</span> Aspose.Pdf.Facades.<span style="color:#2b91af;">PdfViewer</span>();
<span style="color:green;">// Hidden GOTCHA. Need to set resolution BEFORE opening the file</span>
viewer.Resolution = 400;
<span style="color:green;">//open input PDF file</span>
viewer.OpenPdfFile(spec.Contents);
<span style="color:green;">//set attributes for printing</span>
viewer.AutoResize = <span style="color:blue;">true</span>; <span style="color:green;">//print the file with adjusted size</span>
viewer.AutoRotate = <span style="color:blue;">true</span>; <span style="color:green;">//print the file with adjusted rotation</span>
viewer.PrintPageDialog = <span style="color:blue;">false</span>; <span style="color:green;">//do not produce the page number dialog when printing</span>
<span style="color:#2b91af;">Console</span>.WriteLine(<span style="color:#a31515;">"Started excel as duplex print: "</span> + <span style="color:#2b91af;">DateTime</span>.Now.ToString());
<span style="color:green;">//print document using printer and page settings</span>
viewer.PrintDocumentWithSettings(pagesettings, printsettings);
<span style="color:#2b91af;">Console</span>.WriteLine(<span style="color:#a31515;">"Finished excel as duplex print: "</span> + <span style="color:#2b91af;">DateTime</span>.Now.ToString());
<span style="color:green;">//close the PDF file after priting</span>
viewer.ClosePdfFile();
}
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><summary></span>
<span style="color:gray;">///</span><span style="color:green;"> Converts the excel doc to file specification.</span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"></summary></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><param name=</span><span style="color:gray;">"docSht"</span><span style="color:gray;">></span><span style="color:green;">The doc SHT.</span><span style="color:gray;"></param></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><param name=</span><span style="color:gray;">"name"</span><span style="color:gray;">></span><span style="color:green;">The name.</span><span style="color:gray;"></param></span>
<span style="color:gray;">///</span><span style="color:green;"> </span><span style="color:gray;"><returns></returns></span>
<span style="color:blue;">internal</span> <span style="color:blue;">static</span> Aspose.Pdf.<span style="color:#2b91af;">FileSpecification</span> ConvertExcelDocToFileSpecification(<span style="color:blue;">object</span> docSht, <span style="color:blue;">string</span> name)
{
Aspose.Pdf.<span style="color:#2b91af;">FileSpecification</span> returnValue = <span style="color:blue;">null</span>;
<span style="color:#2b91af;">MemoryStream</span> saveDocMS = <span style="color:blue;">new</span> <span style="color:#2b91af;">MemoryStream</span>(); <span style="color:green;">// Can't use USING because filespecification requires it still be open when leaving function</span>
<span style="color:green;">// Save this sheet to a PDF file before adding it</span>
((<span style="color:#2b91af;">Workbook</span>)docSht).CalculateFormula();
<span style="color:green;">//Implement one page per worksheet option</span>
<span style="color:#2b91af;">PdfSaveOptions</span> pdfSaveOptions = <span style="color:blue;">new</span> <span style="color:#2b91af;">PdfSaveOptions</span>();
pdfSaveOptions.OnePagePerSheet = <span style="color:blue;">true</span>;
pdfSaveOptions.PrintingPageType = <span style="color:#2b91af;">PrintingPageType</span>.IgnoreBlank;
((<span style="color:#2b91af;">Workbook</span>)docSht).Save(saveDocMS, pdfSaveOptions);
saveDocMS.Position = 0;
returnValue = <span style="color:blue;">new</span> Aspose.Pdf.<span style="color:#2b91af;">FileSpecification</span>(saveDocMS, name);
<span style="color:blue;">return</span> returnValue;
}
}
}
Hi,
We are not sure what are your needs.
If you want to print whole workbook, you should use WorkbookRender and it can also support duplex printing like SheetRender.
If you want to print one sheet in a page, you can set FitToPagesTall & FitToPagesWide =1 in each PageSetup of Worksheet, it will shrink whole sheet to certain paper size.
Hi there,
It is actually a combination of both. If you run the code I posted above with the spreadsheet posted at the start of this conversation thread you will see that I have a workbook consisting of one worksheet per printed page of my report that prints (almost) nicely.
I want to print the workbook by duplexing each worksheet (as a 1 page per sheet horizizontal/vertical) back and front.
Unfortunately the example you gave with HasMorePages only duplexes individual sheets within a workbook which extend beyond a single page. There is no easy way to specify that I want to duplex print a workbook with each sheet being automatically made to fit 1 printed page. If your workbook render method gave a complete set of rendered pages pre-shrunk, then the method you showed would work.
What is an issue though is that using the method I have and adding the sheets to a PDF file causes problems with backwards duplex printing (horizontal instead of vertical) so there is a bug in there somewhere…
Hi,
Please download: Aspose.Cells for .NET v7.0.1.4
We suggest you to use sheetRender to get all images and control print each image by yourself or move this thread to PDF team.
![]()