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