Category / Section
Convert Excel page to PDF in C#, VB.NET
9 mins read
Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can also convert each page in Excel file to an individual PDF document.
This can be achieved by identifying the page breaks in the Excel worksheet and copying the range within that page breaks to a temporary worksheet. The temporary worksheet will be used as input for ExcelToPdfConverter which generates the PDF documents.
Steps to convert each page in Excel file to an individual PDF document, programmatically:
Step 1: Create a new C# console application project.
Create a new C# console application project
Step 2: Install the Syncfusion.ExcelToPdfConverter.WinForms NuGet package as reference to your .NET Framework application from NuGet.org.
Install NuGet package to the project
Step 3: Add the Excel file from which you want to convert each page to an individual PDF document and make the file an Embedded Resource, by following the below steps.
- In Visual Studio, click the Project menu and select Add Existing Item. Find and select the Excel file you want to add to your project.
- In the Solution Explorer window, right-click on the Excel file you just added to your project and select Properties from the popup menu. The Properties tool window appears.
- In the Properties window, change the Build Action property to Embedded Resource.
- Build the project. Excel file will be compiled into your project’s assembly.
Step 4: Include the following namespaces in Program.cs file.
C#
using Syncfusion.ExcelToPdfConverter; using Syncfusion.Pdf; using Syncfusion.XlsIO; using System.IO; using System.Reflection;
VB.NET
Imports Syncfusion.ExcelToPdfConverter Imports Syncfusion.Pdf Imports Syncfusion.XlsIO Imports System.IO Imports System.Reflection
Step 5: Include the following code snippet in main method of Program.cs file, to convert each page in Excel file to an individual PDF document.
C#
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the Excel application object
IApplication application = excelEngine.Excel;
//Load an existing Excel file into IWorkbook
Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("GeneratePDF.Sample.xlsx");
IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
//Initialize IWorksheet object
IWorksheet worksheet = null;
//Convert each page in every worksheet to a PDF document
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
worksheet = workbook.Worksheets[i];
bool hasVertical = worksheet.VPageBreaks.Count > 0 ? true : false;
bool hasHorizontal = worksheet.HPageBreaks.Count > 0 ? true : false;
bool isHCompleted = false;
for (int k = 0, VCount = worksheet.VPageBreaks.Count, r = 0; (k <= VCount && VCount != 0) || (!hasVertical && hasHorizontal); k++)
{
if (isHCompleted)
break;
bool isVCompleted = false;
for (int j = 0, HCount = worksheet.HPageBreaks.Count; (j <= HCount && HCount != 0) || (hasVertical && !hasHorizontal); j++)
{
if (isVCompleted)
break;
//Create a new worksheet in the workbook
IWorksheet tempWorksheet = workbook.Worksheets.Create();
if (j == 0 && !hasVertical)
worksheet.Range[1, 1, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.UsedRange.LastColumn].CopyTo(tempWorksheet.Range[1, 1]);
else if (k == 0 && !hasHorizontal)
worksheet.Range[1, 1, worksheet.UsedRange.LastRow, worksheet.VPageBreaks[k].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
else if (j == 0 && k == 0 && hasVertical && hasHorizontal)
worksheet.Range[1, 1, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.VPageBreaks[k].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
else if (j == HCount - 1 && !hasVertical)
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row, worksheet.HPageBreaks[j - 1].Location.Column, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn].CopyTo(tempWorksheet.Range[1, 1]);
else if (k < VCount && !hasHorizontal)
worksheet.Range[1, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.UsedRange.LastRow, worksheet.VPageBreaks[k].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
else if (k == VCount && !hasHorizontal)
worksheet.Range[1, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn].CopyTo(tempWorksheet.Range[1, 1]);
else
{
if (!hasVertical)
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row, worksheet.HPageBreaks[j - 1].Location.Column, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.UsedRange.LastColumn].CopyTo(tempWorksheet.Range[1, 1]);
else if (!hasHorizontal)
worksheet.Range[1, worksheet.VPageBreaks[k].Location.Column, worksheet.UsedRange.LastRow, worksheet.VPageBreaks[k + 1].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
else if (k == 0 && j == HCount)
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row + 1, 1, worksheet.UsedRange.LastRow, worksheet.VPageBreaks[k].Location.Column].CopyTo(tempWorksheet.Range[1, 1]);
else if (k == 0)
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row, 1, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.VPageBreaks[k].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
else if (j == 0 && k == VCount)
worksheet.Range[1, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.UsedRange.LastColumn].CopyTo(tempWorksheet.Range[1, 1]);
else if (j == 0 && k > 0)
worksheet.Range[1, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.VPageBreaks[k].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
else if (k == VCount && j == HCount)
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn].CopyTo(tempWorksheet.Range[1, 1]);
else if (k > 0 && j == HCount)
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.UsedRange.LastRow, worksheet.VPageBreaks[k].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
else if (j > 0 && k == VCount)
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.UsedRange.LastColumn].CopyTo(tempWorksheet.Range[1, 1]);
else
worksheet.Range[worksheet.HPageBreaks[j - 1].Location.Row, worksheet.VPageBreaks[k - 1].Location.Column, worksheet.HPageBreaks[j].Location.Row - 1, worksheet.VPageBreaks[k].Location.Column - 1].CopyTo(tempWorksheet.Range[1, 1]);
}
//Initialize the Excel to PDF converter
ExcelToPdfConverter converter = new ExcelToPdfConverter(tempWorksheet);
//Initialize the Excel to PDF converter settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the layout options of converter settings
if (!hasHorizontal && hasVertical)
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
else
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
//Initialize the PdfDocument object
PdfDocument document = converter.Convert(settings);
r++;
//Save the PDF document
document.Save("../../Output/Page" + r + ".pdf");
//Remove the worksheet from the workbook
workbook.Worksheets.Remove(tempWorksheet);
if (j == HCount - 1 && !hasVertical)
{
isHCompleted = true;
break;
}
if (j == 0 && hasVertical && !hasHorizontal)
{
isVCompleted = true;
break;
}
//Close the instance of PdfDocument
document.Close();
}
}
}
}
VB.NET
Using excelEngine As ExcelEngine = New ExcelEngine()
'Instantiate the Excel application object
Dim application As IApplication = excelEngine.Excel
'Load an existing Excel file into IWorkbook
Dim assembly As Assembly = GetType(Module1).GetTypeInfo.Assembly
Dim fileStream As Stream = assembly.GetManifestResourceStream("GeneratePDF.Sample.xlsx")
Dim workbook As IWorkbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic)
'Initialize IWorksheet object
Dim worksheet As IWorksheet = Nothing
'Convert each page in every worksheet to a PDF document
For i As Integer = 0 To workbook.Worksheets.Count - 1 Step 1
worksheet = workbook.Worksheets(i)
Dim hasVertical As Boolean = If(worksheet.VPageBreaks.Count > 0, True, False)
Dim hasHorizontal As Boolean = If(worksheet.HPageBreaks.Count > 0, True, False)
Dim isHCompleted As Boolean = False
Dim k As Integer = 0, VCount As Integer = worksheet.VPageBreaks.Count, r As Integer = 0
While (k <= VCount AndAlso VCount <> 0) OrElse (Not hasVertical AndAlso hasHorizontal)
If isHCompleted Then
Exit While
End If
Dim isVCompleted As Boolean = False
Dim j As Integer = 0, HCount As Integer = worksheet.HPageBreaks.Count
While (j <= HCount AndAlso HCount <> 0) OrElse (hasVertical AndAlso Not hasHorizontal)
If isVCompleted Then
Exit While
End If
'Create a new worksheet in the workbook
Dim tempWorksheet As IWorksheet = workbook.Worksheets.Create()
If j = 0 AndAlso Not hasVertical Then
worksheet.Range(1, 1, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.UsedRange.LastColumn).CopyTo(tempWorksheet.Range(1, 1))
ElseIf k = 0 AndAlso Not hasHorizontal Then
worksheet.Range(1, 1, worksheet.UsedRange.LastRow, worksheet.VPageBreaks(k).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
ElseIf j = 0 AndAlso k = 0 AndAlso hasVertical AndAlso hasHorizontal Then
worksheet.Range(1, 1, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.VPageBreaks(k).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
ElseIf j = HCount - 1 AndAlso Not hasVertical Then
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row, worksheet.HPageBreaks(j - 1).Location.Column, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn).CopyTo(tempWorksheet.Range(1, 1))
ElseIf k < VCount AndAlso Not hasHorizontal Then
worksheet.Range(1, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.UsedRange.LastRow, worksheet.VPageBreaks(k).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
ElseIf k = VCount AndAlso Not hasHorizontal Then
worksheet.Range(1, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn).CopyTo(tempWorksheet.Range(1, 1))
Else
If Not hasVertical Then
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row, worksheet.HPageBreaks(j - 1).Location.Column, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.UsedRange.LastColumn).CopyTo(tempWorksheet.Range(1, 1))
ElseIf Not hasHorizontal Then
worksheet.Range(1, worksheet.VPageBreaks(k).Location.Column, worksheet.UsedRange.LastRow, worksheet.VPageBreaks(k + 1).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
ElseIf k = 0 AndAlso j = HCount Then
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row + 1, 1, worksheet.UsedRange.LastRow, worksheet.VPageBreaks(k).Location.Column).CopyTo(tempWorksheet.Range(1, 1))
ElseIf k = 0 Then
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row, 1, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.VPageBreaks(k).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
ElseIf j = 0 AndAlso k = VCount Then
worksheet.Range(1, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.UsedRange.LastColumn).CopyTo(tempWorksheet.Range(1, 1))
ElseIf j = 0 AndAlso k > 0 Then
worksheet.Range(1, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.VPageBreaks(k).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
ElseIf k = VCount AndAlso j = HCount Then
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn).CopyTo(tempWorksheet.Range(1, 1))
ElseIf k > 0 AndAlso j = HCount Then
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.UsedRange.LastRow, worksheet.VPageBreaks(k).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
ElseIf j > 0 AndAlso k = VCount Then
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.UsedRange.LastColumn).CopyTo(tempWorksheet.Range(1, 1))
Else
worksheet.Range(worksheet.HPageBreaks(j - 1).Location.Row, worksheet.VPageBreaks(k - 1).Location.Column, worksheet.HPageBreaks(j).Location.Row - 1, worksheet.VPageBreaks(k).Location.Column - 1).CopyTo(tempWorksheet.Range(1, 1))
End If
End If
'Initialize the Excel to PDF converter
Dim converter As New ExcelToPdfConverter(tempWorksheet)
'Initialize the Excel to PDF converter settings
Dim settings As New ExcelToPdfConverterSettings()
'Set the layout options of converter settings
If Not hasHorizontal AndAlso hasVertical Then
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage
Else
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage
End If
'Initialize the PdfDocument object
Dim document As PdfDocument = converter.Convert(settings)
r += 1
'Save the PDF document
document.Save("../../Output/Page" & r & ".pdf")
'Remove the worksheet from the workbook
workbook.Worksheets.Remove(tempWorksheet)
If j = HCount - 1 AndAlso Not hasVertical Then
isHCompleted = True
Exit While
End If
If j = 0 AndAlso hasVertical AndAlso Not hasHorizontal Then
isVCompleted = True
Exit While
End If
'Close the instance of PdfDocument
document.Close()
j += 1
End While
k += 1
End While
Next
End Using
A complete working sample to convert each page in Excel file to an individual PDF document can be downloaded from GeneratePDF.zip.
The Excel file used in this sample has 16 pages and its print preview looks as follows.
Input Excel document
By executing the program, you will get 16 PDF files.
Individual PDF documents created for each page in Excel file
PDF document created for the first page in Excel file
Take a moment to peruse the documentation, where you will find other options like move or copy a worksheet, freeze, unfreeze and split panes, show or hide worksheet and worksheet tabs, page setup settings and more with code examples.
Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.
An online sample link for Excel to PDF conversion
See Also:
Different layout options in Excel to PDF conversion
Convert an Excel file to PDF document in C#, VB.NET
Adjust the zoom ratio in Excel to PDF conversion
Convert an Excel file to PDF document in Azure platform
Change the display mode of an Excel to PDF converted document
Note:
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.
