Print Excel files in C# and VB.NET

With GemBox.Spreadsheet you can do silent printing or provide a print dialog and print preview, as shown in the examples for printing in WPF and printing in Windows Forms.

You can print Excel workbooks to the default printer or specify any other local or network printer that's connected to your machine.

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

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

C#
VB.NET

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using 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 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

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.

Print Excel workbooks in a WPF application

In WPF applications you would commonly use PrintDialog to enable users to select a printer, configure it, and perform a print job. For example, your user may specify to print only certain pages of an Excel workbook, or to print multiple pages on one sheet of paper, or something else.

The following example shows how you can use PrintDialog to define GemBox.Spreadsheet's print options. The example also shows how you can use the DocumentViewer control for print previewing.

Printing Excel workbook from WPF application — Screenshot of printing Excel in WPF

XAML
C#
VB.NET

<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 System.Windows;
using System.Windows.Controls;
using System.Windows.Xps.Packaging;
using Microsoft.Win32;
using GemBox.Spreadsheet;

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 System.Windows
Imports System.Windows.Controls
Imports System.Windows.Xps.Packaging
Imports Microsoft.Win32
Imports GemBox.Spreadsheet

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

Print Excel workbooks in a Windows Forms application

You can use the same DocumentViewer WPF control from the above example to create a print preview in Windows Forms applications as well. You can accomplish this by hosting 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.

Printing Excel workbook from Windows Forms application — Screenshot of printing Excel in Windows Forms

C#
VB.NET

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

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 System
Imports System.Drawing
Imports System.Drawing.Printing
Imports System.IO
Imports System.Windows.Forms
Imports GemBox.Spreadsheet

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

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.

Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.

Published: December 13, 2018 | Modified: January 25, 2021 | Author: Stipo Rubic