Specify spreadsheet rendering options

GroupDocs.Viewer ships with the SpreadsheetOptions class that allows you to specify different spreadsheet rendering options (for example, you can display row and column headings in the output file, render grid lines, or adjust cell text overflow). To access these options, use the SpreadsheetOptions property for one of the following classes (depending on the output file format):

Render row and column headings

Rows and columns in a worksheet have unique names displayed on the worksheet’s left and top side. Rows are numbered (1, 2, 3, …, 1048576), and columns are lettered (A, B, C, …, XFD).

Row and column headings in a worksheet

Enable the SpreadsheetOptions.RenderHeadings property to display row and column headings in the output file when you render your spreadsheet in HTML, PDF, PNG, or JPEG format.

The following example demonstrates how to convert an Excel workbook to PDF and display row and column headings in the output PDF file:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Render row and column headings.
    viewOptions.SpreadsheetOptions.RenderHeadings = true;
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Render row and column headings.
            viewOptions.SpreadsheetOptions.RenderHeadings = True
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The following image demonstrates the result:

Render an Excel file with row and column headings to PDF

Render worksheet gridlines

Use the SpreadsheetOptions.RenderGridLines property to display gridlines (lines that separate worksheet rows and columns) in the output file.

The following code example demonstrates how to convert an Excel workbook to PDF and display gridlines in the output PDF file:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Render grid lines.
    viewOptions.SpreadsheetOptions.RenderGridLines = true;
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Render grid lines.
            viewOptions.SpreadsheetOptions.RenderGridLines = True
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The following image demonstrates the result:

Render an Excel file with gridlines to PDF

Control cell text overflow

The SpreadsheetOptions.TextOverflowMode option allows you to prevent text overflow in worksheet cells (see the image below) when you convert your spreadsheet file to HTML, PDF, or image format.

Text overflow in a cell

You can set the TextOverflowMode property to one of the following values:

The following example demonstrates how to set this option in code:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Specify the AutoFitColumn mode.
    viewOptions.SpreadsheetOptions.TextOverflowMode = TextOverflowMode.AutoFitColumn;
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Specify the AutoFitColumn mode.
            viewOptions.SpreadsheetOptions.TextOverflowMode = TextOverflowMode.AutoFitColumn
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

Render hidden rows and columns

Use the ViewOptions.SpreadsheetOptions.RenderHiddenRows and ViewOptions.SpreadsheetOptions.RenderHiddenColumns properties to display hidden rows and columns in the output file when you render your spreadsheet in HTML, PDF, PNG, or JPEG format.

The example below demonstrates how to set this option in code. The rows 20 and 21 and the column E are hidden in the source Excel workbook.

Hidden rows and columns in a worksheet

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Enable rendering hidden rows and columns.
    viewOptions.SpreadsheetOptions.RenderHiddenRows = true;
    viewOptions.SpreadsheetOptions.RenderHiddenColumns = true;
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Enable rendering hidden rows and columns.
            viewOptions.SpreadsheetOptions.RenderHiddenRows = True
            viewOptions.SpreadsheetOptions.RenderHiddenColumns = True
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The image below demonstrates the result. Hidden rows and columns appear in the generated PDF file.

Hidden rows and columns in a worksheet

Render hidden worksheets

If your spreadsheet file contains hidden worksheets, enable the ViewOptions.RenderHiddenPages property to display data from hidden worksheets in the output HTML, PDF, or image files.

The following example demonstrates how to set this option in code:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Enable rendering hidden pages.
    viewOptions.RenderHiddenPages = true;
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Enable rendering hidden pages.
            viewOptions.RenderHiddenPages = True
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

Skip empty rows and columns

GroupDocs.Viewer supports the SpreadsheetOptions.SkipEmptyRows and SpreadsheetOptions.SkipEmptyColumns properties that allow you to skip blank rows and columns when you convert your spreadsheet file to HTML, PDF, or image format.

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Enable skipping blank rows and columns.
    viewOptions.SpreadsheetOptions.SkipEmptyRows = true;
    viewOptions.SpreadsheetOptions.SkipEmptyColumns = true;
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Enable skipping blank rows and columns.
            viewOptions.SpreadsheetOptions.SkipEmptyRows = True
            viewOptions.SpreadsheetOptions.SkipEmptyColumns = True
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The following image demonstrates the result:

Skip empty columns and rows

Render cell comments

Use the ViewOptions.RenderComments option to display cell comments in the output file when you render your spreadsheet in HTML, PDF, PNG, or JPEG format.

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PNG.
    // {0} is replaced with the current page number in the file names.
    var viewOptions = new PngViewOptions("output_{0}.png");
    // Enable rendering comments.
    viewOptions.RenderComments = true;
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PNG.
            ' {0} is replaced with the current page number in the file names.
            Dim viewOptions = New PngViewOptions("output_{0}.png")
            ' Enable rendering comments.
            viewOptions.RenderComments = True
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The following image demonstrates the result:

Render cell comments

Set worksheet margins in the output pdf pages

Use the SpreadsheetOptions.RenderGridLines properties to set margins for worksheets in the output pdf. If margins are set to value less than 0 or not set then default value will be used.

The following code example demonstrates how to convert an Excel workbook to PDF and set optional margins for worksheets in the output PDF file:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    var viewOptions = new PdfViewOptions("output.pdf");
	
    // Set margins for worksheets in the output pdf pages
    viewOptions.SpreadsheetOptions.LeftMargin = 0;
    viewOptions.SpreadsheetOptions.RightMargin = 0.5;
    viewOptions.SpreadsheetOptions.TopMargin = 1;
    viewOptions.SpreadsheetOptions.BottomMargin = -10; // set to default value
	
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            Dim viewOptions = New PdfViewOptions("output.pdf")
        
            ' Set margins for worksheets in the output pdf pages
            viewOptions.SpreadsheetOptions.LeftMargin = 0
            viewOptions.SpreadsheetOptions.RightMargin = 0.5
            viewOptions.SpreadsheetOptions.TopMargin = 1
            viewOptions.SpreadsheetOptions.BottomMargin = -10 ' set to default value
        
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The following image demonstrates the result:

Render an Excel file with worksheet margins on page to PDF