How to convert the required range in Excel to PDF?
8 Dec 20235 minutes to read
A specific range of an Excel worksheet can be converted to PDF by applying page breaks to that particular range and copying it to a new worksheet for conversion. The following code snippet explains this.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream excelStream = new FileStream("ExceltoPDF.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(excelStream);
IWorksheet sheet = workbook.Worksheets[0];
XlsIORendererSettings settings = new XlsIORendererSettings();
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
//Set Horizontal Page Breaks
sheet.HPageBreaks.Add(sheet.Range["A40"]);
//Set Vertical Page Breaks
sheet.VPageBreaks.Add(sheet.Range["R11"]);
IWorksheet tempWorksheet = workbook.Worksheets.Create();
sheet.Range[1, 1, sheet.HPageBreaks[0].Location.Row, sheet.VPageBreaks[0].Location.Column].CopyTo(tempWorksheet.Range[1, 1]);
//Initialize XlsIO renderer.
XlsIORenderer renderer = new XlsIORenderer();
//Convert Excel document into PDF document
PdfDocument pdfDocument = renderer.ConvertToPDF(tempWorksheet, settings);
Stream stream = new FileStream("ExcelToPDF.pdf", FileMode.Create, FileAccess.ReadWrite);
pdfDocument.Save(stream);
excelStream.Dispose();
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open("ExceltoPDF.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//Initialize ExcelToPdfConverterSettings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
//Set Horizontal Page Breaks
sheet.HPageBreaks.Add(sheet.Range["A40"]);
//Set Vertical Page Breaks
sheet.VPageBreaks.Add(sheet.Range["R11"]);
IWorksheet tempWorksheet = workbook.Worksheets.Create();
sheet.Range[1, 1, sheet.HPageBreaks[0].Location.Row, sheet.VPageBreaks[0].Location.Column].CopyTo(tempWorksheet.Range[1, 1]);
//Load the Excel document into ExcelToPdfConverter
ExcelToPdfConverter converter = new ExcelToPdfConverter(tempWorksheet);
//Convert the Excel document to PDF with converter settings
PdfDocument document = converter.Convert(settings);
//Save the PDF document
document.Save("ExceltoPDF.pdf");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Open("ExceltoPDF.xlsx")
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Initialize ExcelToPdfConverterSettings
Dim settings As ExcelToPdfConverterSettings = New ExcelToPdfConverterSettings()
'Set layout option as FitAllColumnsOnOnePage
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage
'Set Horizontal Page Breaks
sheet.HPageBreaks.Add(sheet.Range("A40"))
'Set Vertical Page Breaks
sheet.VPageBreaks.Add(sheet.Range("R11"))
Dim tempWorksheet As IWorksheet = workbook.Worksheets.Create()
sheet.Range(1, 1, sheet.HPageBreaks(0).Location.Row, sheet.VPageBreaks(0).Location.Column).CopyTo(tempWorksheet.Range(1, 1))
'Load the Excel document into ExcelToPdfConverter
Dim converter As ExcelToPdfConverter = New ExcelToPdfConverter(tempWorksheet)
'Convert the Excel document to PDF with converter settings
Dim document As PdfDocument = converter.Convert(settings)
'Save the PDF document
document.Save("ExceltoPDF.pdf")
End Using
See Also
- How to overcome Parameter Not valid exception?
- How to open an existing XLSX workbook and save it as XLS?
- How to create and open Excel Template files by using XlsIO?
- How to copy a range from one workbook to another?
- Does XlsIO support Excel files with macros that are digitally signed?
- How does Excel file with uninstalled fonts is converted to PDF/Image?
- How to sort two or more columns in a pivot table?
- How to move or copy a worksheet?