Print Excel files

The following example shows how you can silently print Excel files in C# and VB.NET without the user's interaction, using GemBox.Spreadsheet.

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Load Excel workbook from file's path.
        ExcelFile workbook = ExcelFile.Load("%#CombinedTemplate.xlsx%");

        // Set sheets print options.
        foreach (ExcelWorksheet worksheet in workbook.Worksheets)
        {
            ExcelPrintOptions sheetPrintOptions = worksheet.PrintOptions;

            sheetPrintOptions.Portrait = false;
            sheetPrintOptions.HorizontalCentered = true;
            sheetPrintOptions.VerticalCentered = true;

            sheetPrintOptions.PrintHeadings = true;
            sheetPrintOptions.PrintGridlines = true;
        }

        // Create spreadsheet's print options. 
        PrintOptions printOptions = new PrintOptions();
        printOptions.SelectionType = SelectionType.EntireFile;

        // Print Excel workbook to default printer (e.g. 'Microsoft Print to Pdf').
        string printerName = null;
        workbook.Print(printerName, printOptions);
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        ' Load Excel workbook from file's path.
        Dim workbook As ExcelFile = ExcelFile.Load("%#CombinedTemplate.xlsx%")

        ' Set sheets print options.
        For Each worksheet As ExcelWorksheet In workbook.Worksheets
            Dim sheetPrintOptions As ExcelPrintOptions = worksheet.PrintOptions

            sheetPrintOptions.Portrait = False
            sheetPrintOptions.HorizontalCentered = True
            sheetPrintOptions.VerticalCentered = True

            sheetPrintOptions.PrintHeadings = True
            sheetPrintOptions.PrintGridlines = True
        Next

        ' Create spreadsheet's print options. 
        Dim printOptions As New PrintOptions()
        printOptions.SelectionType = SelectionType.EntireFile

        ' Print Excel workbook to default printer (e.g. 'Microsoft Print to Pdf').
        Dim printerName As String = Nothing
        workbook.Print(printerName, printOptions)

    End Sub
End Module
Printed Excel workbook with virtual printer in C# and VB.NET
Screenshot of printed Excel with 'Microsoft Print to Pdf'

GemBox.Spreadsheet uses System.Printing namespace for managing print queues and print jobs. To leverage advance printing capabilities, like specifying the printer's paper source (tray) or specifying two-sided (duplex) printing, you can use the PrintTicket class.

Using the PrintTicket class, you can create an object that defines or configures the desired printer's features. You provide that configuration in the form of an XML stream (by calling PrintTicket.GetXmlStream method) to GemBox.Spreadsheet's PrintOptions.

The following example shows how you can use DocumentViewer control for print preview and allow users to define GemBox.Spreadsheet's print options via standard PrintDialog.

<Window x:Class="MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Printing in WPF application" Height="450" Width="800">
    <DockPanel>
        <StackPanel DockPanel.Dock="Top" Orientation="Horizontal" Margin="5">
            <Button x:Name="LoadFileBtn" Content="Load" Width="100" Margin="5,0" Click="LoadFileBtn_Click"/>
            <Button x:Name="PrintFileBtn" Content="Print" Width="100" Margin="5,0" Click="PrintFileBtn_Click"/>
        </StackPanel>
        <DocumentViewer x:Name="DocViewer"/>
    </DockPanel>
</Window>
using GemBox.Spreadsheet;
using Microsoft.Win32;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Xps.Packaging;

public partial class MainWindow : Window
{
    private ExcelFile workbook;

    public MainWindow()
    {
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
        InitializeComponent();
    }

    private void LoadFileBtn_Click(object sender, RoutedEventArgs e)
    {
        OpenFileDialog openFileDialog = new OpenFileDialog();
        openFileDialog.Filter =
            "XLSX files (*.xlsx, *.xltx, *.xlsm, *.xltm)|*.xlsx;*.xltx;*.xlsm;*.xltm" +
            "|XLS files (*.xls, *.xlt)|*.xls;*.xlt" +
            "|ODS files (*.ods, *.ots)|*.ods;*.ots" +
            "|CSV files (*.csv, *.tsv)|*.csv;*.tsv" +
            "|HTML files (*.html, *.htm)|*.html;*.htm";

        if (openFileDialog.ShowDialog() == true)
        {
            this.workbook = ExcelFile.Load(openFileDialog.FileName);
            this.ShowPrintPreview();
        }
    }

    private void PrintFileBtn_Click(object sender, RoutedEventArgs e)
    {
        if (this.workbook == null)
            return;

        PrintDialog printDialog = new PrintDialog() { UserPageRangeEnabled = true };
        if (printDialog.ShowDialog() == true)
        {
            PrintOptions printOptions = new PrintOptions(printDialog.PrintTicket.GetXmlStream())
            {
                SelectionType = SelectionType.EntireFile
            };

            printOptions.FromPage = printDialog.PageRange.PageFrom - 1;
            printOptions.ToPage = printDialog.PageRange.PageTo == 0 ? int.MaxValue : printDialog.PageRange.PageTo - 1;

            this.workbook.Print(printDialog.PrintQueue.FullName, printOptions);
        }
    }

    private void ShowPrintPreview()
    {
        XpsDocument xpsDocument = this.workbook.ConvertToXpsDocument(
            new XpsSaveOptions() { SelectionType = SelectionType.EntireFile });

        // Note, XpsDocument must stay referenced so that DocumentViewer can access additional resources from it.
        // Otherwise, GC will collect/dispose XpsDocument and DocumentViewer will no longer work.
        this.DocViewer.Tag = xpsDocument;
        this.DocViewer.Document = xpsDocument.GetFixedDocumentSequence();
    }
}
Imports GemBox.Spreadsheet
Imports Microsoft.Win32
Imports System.Windows
Imports System.Windows.Controls
Imports System.Windows.Xps.Packaging

Partial Public Class MainWindow
    Inherits Window

    Dim workbook As ExcelFile

    Public Sub New()
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
        InitializeComponent()
    End Sub

    Private Sub LoadFileBtn_Click(sender As Object, e As RoutedEventArgs)

        Dim openFileDialog As New OpenFileDialog()
        openFileDialog.Filter =
            "XLSX files (*.xlsx, *.xltx, *.xlsm, *.xltm)|*.xlsx;*.xltx;*.xlsm;*.xltm" &
            "|XLS files (*.xls, *.xlt)|*.xls;*.xlt" &
            "|ODS files (*.ods, *.ots)|*.ods;*.ots" &
            "|CSV files (*.csv, *.tsv)|*.csv;*.tsv" &
            "|HTML files (*.html, *.htm)|*.html;*.htm"

        If (openFileDialog.ShowDialog() = True) Then
            Me.workbook = ExcelFile.Load(openFileDialog.FileName)
            Me.ShowPrintPreview()
        End If

    End Sub

    Private Sub PrintFileBtn_Click(sender As Object, e As RoutedEventArgs)

        If Me.workbook Is Nothing Then Return

        Dim printDialog As New PrintDialog() With {.UserPageRangeEnabled = True}
        If (printDialog.ShowDialog() = True) Then

            Dim printOptions As New PrintOptions(printDialog.PrintTicket.GetXmlStream()) With
            {
                .SelectionType = SelectionType.EntireFile
            }

            printOptions.FromPage = printDialog.PageRange.PageFrom - 1
            printOptions.ToPage = If(printDialog.PageRange.PageTo = 0, Integer.MaxValue, printDialog.PageRange.PageTo - 1)

            Me.workbook.Print(printDialog.PrintQueue.FullName, printOptions)
        End If

    End Sub

    Private Sub ShowPrintPreview()

        Dim xpsDocument As XpsDocument = workbook.ConvertToXpsDocument(
            New XpsSaveOptions() With {.SelectionType = SelectionType.EntireFile})

        ' Note, XpsDocument must stay referenced so that DocumentViewer can access additional resources from it.
        ' Otherwise, GC will collect/dispose XpsDocument and DocumentViewer will no longer work.
        Me.DocViewer.Tag = xpsDocument
        Me.DocViewer.Document = xpsDocument.GetFixedDocumentSequence()

    End Sub

End Class
Printing Excel workbook from WPF application
Screenshot of printing Excel in WPF

You can use the same DocumentViewer WPF control from the above example to create a print preview in Windows Forms applications. To accomplish this, you need to host the WPF control inside the ElementHost Windows Forms control.

Alternatively, you can use PrintPreviewControl and preview the Excel workbook by providing the PrintDocument object to the control. The following example shows how you can render a spreadsheet's pages as images and draw those images on a PrintDocument.PrintPage event for print previewing.

using GemBox.Spreadsheet;
using System;
using System.Drawing;
using System.Drawing.Printing;
using System.IO;
using System.Windows.Forms;

public partial class Form1 : Form
{
    private ExcelFile workbook;

    public Form1()
    {
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
        InitializeComponent();
    }

    private void LoadFileMenuItem_Click(object sender, EventArgs e)
    {
        OpenFileDialog openFileDialog = new OpenFileDialog();
        openFileDialog.Filter =
            "XLSX files (*.xlsx, *.xltx, *.xlsm, *.xltm)|*.xlsx;*.xltx;*.xlsm;*.xltm" +
            "|XLS files (*.xls, *.xlt)|*.xls;*.xlt" +
            "|ODS files (*.ods, *.ots)|*.ods;*.ots" +
            "|CSV files (*.csv, *.tsv)|*.csv;*.tsv" +
            "|HTML files (*.html, *.htm)|*.html;*.htm";

        if (openFileDialog.ShowDialog() == DialogResult.OK)
        {
            this.workbook = ExcelFile.Load(openFileDialog.FileName);
            this.ShowPrintPreview();
        }
    }

    private void PrintFileMenuItem_Click(object sender, EventArgs e)
    {
        if (this.workbook == null)
            return;

        PrintDialog printDialog = new PrintDialog() { AllowSomePages = true };
        if (printDialog.ShowDialog() == DialogResult.OK)
        {
            PrinterSettings printerSettings = printDialog.PrinterSettings;
            PrintOptions printOptions = new PrintOptions() { SelectionType = SelectionType.EntireFile };

            // Set PrintOptions properties based on PrinterSettings properties.
            printOptions.CopyCount = printerSettings.Copies;
            printOptions.FromPage = printerSettings.FromPage == 0 ? 0 : printerSettings.FromPage - 1;
            printOptions.ToPage = printerSettings.ToPage == 0 ? int.MaxValue : printerSettings.ToPage - 1;

            this.workbook.Print(printerSettings.PrinterName, printOptions);
        }
    }

    private void ShowPrintPreview()
    {
        // Create image for each Excel workbook's page.
        Image[] images = this.CreatePrintPreviewImages();
        int imageIndex = 0;

        // Draw each page's image on PrintDocument for print preview.
        var printDocument = new PrintDocument();
        printDocument.PrintPage += (sender, e) =>
        {
            using (Image image = images[imageIndex])
            {
                var graphics = e.Graphics;
                var region = graphics.VisibleClipBounds;

                // Rotate image if it has landscape orientation.
                if (image.Width > image.Height)
                    image.RotateFlip(RotateFlipType.Rotate270FlipNone);

                graphics.DrawImage(image, 0, 0, region.Width, region.Height);
            }

            ++imageIndex;
            e.HasMorePages = imageIndex < images.Length;
        };

        this.PageUpDown.Value = 1;
        this.PageUpDown.Maximum = images.Length;
        this.PrintPreviewControl.Document = printDocument;
    }

    private Image[] CreatePrintPreviewImages()
    {
        var paginatorOptions = new PaginatorOptions { SelectionType = SelectionType.EntireFile };
        var pages = this.workbook.GetPaginator(paginatorOptions).Pages;

        var images = new Image[pages.Count];
        var imageOptions = new ImageSaveOptions();

        for (int pageIndex = 0; pageIndex < pages.Count; ++pageIndex)
        {
            var imageStream = new MemoryStream();
            pages[pageIndex].Save(imageStream, imageOptions);
            images[pageIndex] = Image.FromStream(imageStream);
        }

        return images;
    }

    private void PageUpDown_ValueChanged(object sender, EventArgs e)
    {
        this.PrintPreviewControl.StartPage = (int)this.PageUpDown.Value - 1;
    }
}
Imports GemBox.Spreadsheet
Imports System
Imports System.Drawing
Imports System.Drawing.Printing
Imports System.IO
Imports System.Windows.Forms

Partial Public Class Form1
    Inherits Form

    Dim workbook As ExcelFile

    Public Sub New()
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
        InitializeComponent()
    End Sub

    Private Sub LoadFileMenuItem_Click(sender As Object, e As EventArgs) Handles LoadFileMenuItem.Click

        Dim openFileDialog As New OpenFileDialog()
        openFileDialog.Filter =
            "XLSX files (*.xlsx, *.xltx, *.xlsm, *.xltm)|*.xlsx;*.xltx;*.xlsm;*.xltm" &
            "|XLS files (*.xls, *.xlt)|*.xls;*.xlt" &
            "|ODS files (*.ods, *.ots)|*.ods;*.ots" &
            "|CSV files (*.csv, *.tsv)|*.csv;*.tsv" &
            "|HTML files (*.html, *.htm)|*.html;*.htm"

        If (openFileDialog.ShowDialog() = DialogResult.OK) Then
            Me.workbook = ExcelFile.Load(openFileDialog.FileName)
            Me.ShowPrintPreview()
        End If

    End Sub

    Private Sub PrintFileMenuItem_Click(sender As Object, e As EventArgs) Handles PrintFileMenuItem.Click

        If Me.workbook Is Nothing Then Return

        Dim printDialog As New PrintDialog() With {.AllowSomePages = True}
        If (printDialog.ShowDialog() = DialogResult.OK) Then

            Dim printerSettings As PrinterSettings = printDialog.PrinterSettings
            Dim printOptions As New PrintOptions() With {.SelectionType = SelectionType.EntireFile}

            ' Set PrintOptions properties based on PrinterSettings properties.
            printOptions.CopyCount = printerSettings.Copies
            printOptions.FromPage = If(printerSettings.FromPage = 0, 0, printerSettings.FromPage - 1)
            printOptions.ToPage = If(printerSettings.ToPage = 0, Integer.MaxValue, printerSettings.ToPage - 1)

            Me.workbook.Print(printerSettings.PrinterName, printOptions)
        End If

    End Sub

    Private Sub ShowPrintPreview()

        ' Create image for each Excel workbook's page.
        Dim images As Image() = Me.CreatePrintPreviewImages()
        Dim imageIndex As Integer = 0

        ' Draw each page's image on PrintDocument for print preview.
        Dim printDocument = New PrintDocument()
        AddHandler printDocument.PrintPage,
            Sub(sender, e)
                Using image As Image = images(imageIndex)
                    Dim graphics = e.Graphics
                    Dim region = graphics.VisibleClipBounds

                    ' Rotate image if it has landscape orientation.
                    If image.Width > image.Height Then image.RotateFlip(RotateFlipType.Rotate270FlipNone)

                    graphics.DrawImage(image, 0, 0, region.Width, region.Height)
                End Using

                imageIndex += 1
                e.HasMorePages = imageIndex < images.Length
            End Sub

        Me.PageUpDown.Value = 1
        Me.PageUpDown.Maximum = images.Length
        Me.printPreviewControl.Document = printDocument

    End Sub

    Private Function CreatePrintPreviewImages() As Image()

        Dim paginatorOptions As New PaginatorOptions With {.SelectionType = SelectionType.EntireFile}
        Dim pages = Me.workbook.GetPaginator(paginatorOptions).Pages

        Dim images = New Image(pages.Count - 1) {}
        Dim imageOptions As New ImageSaveOptions()

        For pageIndex As Integer = 0 To pages.Count - 1
            Dim imageStream = New MemoryStream()
            pages(pageIndex).Save(imageStream, imageOptions)
            images(pageIndex) = Image.FromStream(imageStream)
        Next

        Return images

    End Function

    Private Sub PageUpDown_ValueChanged(sender As Object, e As EventArgs) Handles PageUpDown.ValueChanged
        Me.printPreviewControl.StartPage = Me.PageUpDown.Value - 1
    End Sub

End Class
Printing Excel workbook from Windows Forms application
Screenshot of printing Excel in Windows Forms

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy