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):
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).
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:
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){// Convert the spreadsheet to PDF.varviewOptions=newPdfViewOptions("output.pdf");// Render row and column headings.viewOptions.SpreadsheetOptions.RenderHeadings=true;viewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")' Convert the spreadsheet to PDF.
DimviewOptions=NewPdfViewOptions("output.pdf")' Render row and column headings.
viewOptions.SpreadsheetOptions.RenderHeadings=Trueviewer.View(viewOptions)EndUsingEndSubEndModule
The following image demonstrates the result:
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:
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){// Convert the spreadsheet to PDF.varviewOptions=newPdfViewOptions("output.pdf");// Render grid lines.viewOptions.SpreadsheetOptions.RenderGridLines=true;viewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")' Convert the spreadsheet to PDF.
DimviewOptions=NewPdfViewOptions("output.pdf")' Render grid lines.
viewOptions.SpreadsheetOptions.RenderGridLines=Trueviewer.View(viewOptions)EndUsingEndSubEndModule
The following image demonstrates the result:
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.
You can set the TextOverflowMode property to one of the following values:
TextOverflowMode.OverlayIfNextIsEmpty (default) — Allows text to overflow into adjacent cells if these cells have no data. If adjacent cells are not empty, the overflowing text is truncated.
TextOverflowMode.Overlay — Text always overflows into adjacent cells even if these cells contain data.
The following example demonstrates how to set this option in code:
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){// Convert the spreadsheet to PDF.varviewOptions=newPdfViewOptions("output.pdf");// Specify the AutoFitColumn mode.viewOptions.SpreadsheetOptions.TextOverflowMode=TextOverflowMode.AutoFitColumn;viewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")' Convert the spreadsheet to PDF.
DimviewOptions=NewPdfViewOptions("output.pdf")' Specify the AutoFitColumn mode.
viewOptions.SpreadsheetOptions.TextOverflowMode=TextOverflowMode.AutoFitColumnviewer.View(viewOptions)EndUsingEndSubEndModule
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.
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){// Convert the spreadsheet to PDF.varviewOptions=newPdfViewOptions("output.pdf");// Enable rendering hidden rows and columns.viewOptions.SpreadsheetOptions.RenderHiddenRows=true;viewOptions.SpreadsheetOptions.RenderHiddenColumns=true;viewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")' Convert the spreadsheet to PDF.
DimviewOptions=NewPdfViewOptions("output.pdf")' Enable rendering hidden rows and columns.
viewOptions.SpreadsheetOptions.RenderHiddenRows=TrueviewOptions.SpreadsheetOptions.RenderHiddenColumns=Trueviewer.View(viewOptions)EndUsingEndSubEndModule
The image below demonstrates the result. Hidden rows and columns appear in the generated PDF file.
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:
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){// Convert the spreadsheet to PDF.varviewOptions=newPdfViewOptions("output.pdf");// Enable rendering hidden pages.viewOptions.RenderHiddenPages=true;viewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")' Convert the spreadsheet to PDF.
DimviewOptions=NewPdfViewOptions("output.pdf")' Enable rendering hidden pages.
viewOptions.RenderHiddenPages=Trueviewer.View(viewOptions)EndUsingEndSubEndModule
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){// Convert the spreadsheet to PDF.varviewOptions=newPdfViewOptions("output.pdf");// Enable skipping blank rows and columns.viewOptions.SpreadsheetOptions.SkipEmptyRows=true;viewOptions.SpreadsheetOptions.SkipEmptyColumns=true;viewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")' Convert the spreadsheet to PDF.
DimviewOptions=NewPdfViewOptions("output.pdf")' Enable skipping blank rows and columns.
viewOptions.SpreadsheetOptions.SkipEmptyRows=TrueviewOptions.SpreadsheetOptions.SkipEmptyColumns=Trueviewer.View(viewOptions)EndUsingEndSubEndModule
The following image demonstrates the result:
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.
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){// Convert the spreadsheet to PNG.// {0} is replaced with the current page number in the file names.varviewOptions=newPngViewOptions("output_{0}.png");// Enable rendering comments.viewOptions.RenderComments=true;viewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")' Convert the spreadsheet to PNG.
' {0} is replaced with the current page number in the file names.
DimviewOptions=NewPngViewOptions("output_{0}.png")' Enable rendering comments.
viewOptions.RenderComments=Trueviewer.View(viewOptions)EndUsingEndSubEndModule
The following image demonstrates the result:
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:
usingGroupDocs.Viewer;usingGroupDocs.Viewer.Options;// ...using(varviewer=newViewer("invoice.xlsx")){varviewOptions=newPdfViewOptions("output.pdf");// Set margins for worksheets in the output pdf pagesviewOptions.SpreadsheetOptions.LeftMargin=0;viewOptions.SpreadsheetOptions.RightMargin=0.5;viewOptions.SpreadsheetOptions.TopMargin=1;viewOptions.SpreadsheetOptions.BottomMargin=-10;// set to default valueviewer.View(viewOptions);}
ImportsGroupDocs.ViewerImportsGroupDocs.Viewer.Options' ...
ModuleProgramSubMain(argsAsString())Usingviewer=NewViewer("invoice.xlsx")DimviewOptions=NewPdfViewOptions("output.pdf")' Set margins for worksheets in the output pdf pages
viewOptions.SpreadsheetOptions.LeftMargin=0viewOptions.SpreadsheetOptions.RightMargin=0.5viewOptions.SpreadsheetOptions.TopMargin=1viewOptions.SpreadsheetOptions.BottomMargin=-10' set to default value
viewer.View(viewOptions)EndUsingEndSubEndModule
The following image demonstrates the result:
Was this page helpful?
Any additional feedback you'd like to share with us?
Please tell us how we can improve this page.
Thank you for your feedback!
We value your opinion. Your feedback will help us improve our documentation.