Render Excel Range Inside PDF

DsExcel allows you to render selected worksheet ranges at specific locations in a new or existing PDF document. This is useful when a worksheet contains a large amount of data but only selected fields or ranges need to appear in the final report.

For example, a sales worksheet may contain product quantities, regions, and sales manager information. You can render only the product and region data in the PDF without including the remaining worksheet content.

Use the draw() method of the PrintManager class to render a range on a PDF page. Specify the upper-left position with a Point object, or use a Rectangle object to control both the position and size. The getSize method returns the natural rendering size of a range and can help calculate its placement.

When working with paginated PageInfo objects instead, use appendPage() to append a generated page to a GcPdfDocument. If pages are added, removed, or reordered in a PageInfo collection, call updatePageNumberAndPageSettings() to update the page numbers, page count, and page settings before saving the result.

Note: A valid Document Solutions for PDF license is required to render Excel ranges in PDF documents.

Render a Range in a New PDF Document

The following example creates a PDF document and renders the range A4:C8 at a specified position on its first page.

// Initialize workbook.
Workbook workbook = new Workbook();
    
// Fetch default worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Set value.
worksheet.getRange("A4:C4").setValue(new Object[] 
{ "Device", "Quantity", "Unit Price" });
worksheet.getRange("A5:C8").setValue(new Object[][] 
{
{ "T540p", 12, 9850 }, { "T570", 5, 7460 },
{ "Y460", 6, 5400 }, { "Y460F", 8, 6240 } 
});

// Set style.
worksheet.getRange("A4:C4").getFont().setBold(true);
worksheet.getRange("A4:C4").getFont().setColor(Color.GetWhite());
worksheet.getRange("A4:C4").getInterior().setColor(Color.GetLightBlue());
worksheet.getRange("A5:C8").getBorders().get(BordersIndex.InsideHorizontal).setColor(Color.GetOrange());
worksheet.getRange("A5:C8").getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.DashDot);

// Configure Page size.
float width = 600f;
float height = 500f;
PDRectangle pageSize = new PDRectangle(width, height);
    
// Create a PDF document.
PDDocument doc = new PDDocument();
PDPage page = new PDPage(pageSize);
doc.addPage(page);

// Create an instance of the PrintManager class.
PrintManager printManager = new PrintManager();
    
// Draw the Range"A4:C8" to the specified location on the page.
printManager.draw(doc, page, new Point(30, 100), 
worksheet.getRange("A4:C8"));

// Save the modified pages into PDF file.
try 
{
 doc.save("RenderExcelRangesInsidePDFBasic.pdf");
} 
catch (IOException e) 
{
 // TODO Auto-generated catch block.
 e.printStackTrace();
}

Render Multiple Ranges in an Existing PDF Document

You can also render ranges from different worksheets on different pages of an existing PDF document. Use getSize to determine the natural size of each range and FindText to position it relative to existing text.

The following example renders three worksheet ranges on pages 4, 5, and 6 of an existing financial report.

private static void RenderExcelRangesInPDF() throws Exception 
{
// Create to a pdf file stream
FileOutputStream outputStream = null;
    
try 
{
  outputStream = new FileOutputStream("RenderExcelRangesInsideAPDF.pdf");
} 
catch (FileNotFoundException e) 
{
  e.printStackTrace();
}
    
// Create a new workbook
Workbook workbook = new Workbook();
workbook.open(getResourceStream("xlsx/FinancialReport.xlsx"));
   
// Create a PDF document.
PDDocument doc = null;
try 
{
 doc = PDDocument.load(getResourceStream
 ("xlsx/Acme-Financial Report 2018.pdf"));
} 
catch (IOException e1) 
{
 e1.printStackTrace();
}

// Create an instance of the PrintManager class.
PrintManager printManager = new PrintManager();

// Draw the contents of the sheet3 to the fourth page.
IRange printArea1 = workbook.getWorksheets().get(2).getRange("A3:C24");
Size size1 = printManager.getSize(printArea1);
printManager.draw(doc, doc.getPage(3), new Rectangle(306, 215, 
size1.getWidth(), size1.getHeight()),
printArea1);

// Draw the contents of the sheet1 to the fifth page.
IRange printArea2 = workbook.getWorksheets().get(0).getRange("A3:F29");
Size size2 = printManager.getSize(printArea2);
printManager.draw(doc, doc.getPage(4), 
new Rectangle(71, 250, size2.getWidth(), size2.getHeight()), 
printArea2);

// Draw the contents of the sheet2 to the sixth page.
IRange printArea3 = 
workbook.getWorksheets().get(1).getRange("A3:G27");
Size size3 = printManager.getSize(printArea3);
printManager.draw(doc, doc.getPage(5), 
new Rectangle(71, 230, 783, size3.getHeight()), printArea3);

// Save the modified pages into pdf file.
try 
{
  doc.save(outputStream);
  doc.close();
} 
catch (IOException e) 
{
  e.printStackTrace();
}

// Close the file stream
try 
{
  outputStream.close();
} 
catch (IOException e) 
{
  e.printStackTrace();
}
}

private static InputStream getResourceStream(String resource) throws Exception 
{
 return UpcomingFeatures.class.getClassLoader().getResourceAsStream(resource);
}