Render Excel Ranges Inside PDF Files in .NET Core

PDF Files and Excel Data

Reports generated for business, customers, educational institutions, or research papers are a combination of textual description and numbers. Financial & budget reports, timesheet forms, salary slips, brochures, telecom bills, etc. are report examples that are generally shared as PDF files and include data from Excel files. The final PDF reports are a mix of descriptive text and Excel tables.

The general mode of generating documents is through Microsoft Word or Microsoft Powerpoint, which provides the flexibility to include Excel tables by direct copy/pasting the content from MS Excel to Word or Powerpoint documents. From there, you convert the documents into a final storage format - PDF - since it is the most preferred format for archiving documents or distribution. But,

1. What if you have thousands of such documents being released every year?
2. What if you already have an existing PDF, and still want to include data from Excel tables at a certain location in the PDF? You can no longer edit a PDF, and you may not have an original MS Word document copy to modify.

In such cases, there is a need for Document APIs that can take data programmatically and automate the process of combining data from documents of different formats. There is also a need to inject the data in the final document at the desired location in order to generate complete reports as desired above. Interestingly, there are several different Document APIs on the web - from Open XML SDKs to Development Tools like Microsoft VSTO Add-is. There are also several other 3rd party Document APIs that provide full features to generate documents in a certain format. But what is needed is flexibility and a solution to combine data from multiple formats to be able to inject it at any desired location.

GrapeCity Documents APIs will provide you the flexibility of merging data from any Excel range into a PDF document. In its new Service Pack 2 release for v2, GrapeCity Documents for Excel API (GcExcel) introduces the new PrintManager class to control pagination and spreadsheet content while exporting the spreadsheets to PDF. The feature helps control printing of specific parts of Excel to PDF documents and provides the user specific API for the pagination result in the PageInfo object. This includes a list of pages generated from the Excel file through the PrintManager.Paginate method. It then performs operations like:

• Add/delete/modify pages.
• Change page number, page count, page content and page settings.

To learn more about the PrintManager class, visit:

Help and Demo.

In addition, the GrapeCity Documents for PDF API can be used to load an existing PDF, save the PDF modified by the PrintManager class, and generate the final PDF report. Have a look at the example below and how it is solved step-wise using GrapeCity Documents APIs in a .NET Core application.

Note: In order to implement this use case, you will need a license for both GcPdf and GcExcel products.

Use Case

A full financial report is a mix of business activities and a company's financial performance in terms of numbers in Excel. The report is generated in a PDF to then be distributed to clients. See the snapshots below of an example Excel file. This describes the year's performance in a cash flow report and full financial report in PDF. What you see on the right is the location where the Excel data needs to be rendered in the PDF file.

When handling such documents through an API it is important to extract tables from the Excel file and render inside a PDF file to generate a complete report.

GrapeCity Documents Solution

In scenario above, you'll now be able to extract the range of data from Excel using the GcExcel PrintManager class and print it to PDF with the GrapeCity Documents for PDF API. The PrintManager helps draw a specific range of data from an Excel spreadsheet directly to a PDF page. Follow the steps below to take an existing PDF document (the financial report from the example above) and insert a range of data from Excel (the cash flow report table data) at a specific area on the PDF page.

Step 1: Open Visual Studio 2017 (or higher) and create a .NET Core Console application.

Step 2: Right-click Dependencies in Solution Explorer and add GrapeCity Documents for Excel API from nuget.org.

Step 3: In Program.cs, include the following namespaces:

using GrapeCity.Documents.Excel;
using GrapeCity.Documents.Excel.Drawing; 

Step 4: Create a GcPdfDocument and load the desired PDF file where the Excel range needs to be rendered.

GrapeCity.Documents.Pdf.GcPdfDocument doc = new GrapeCity.Documents.Pdf.GcPdfDocument();
doc.Load("Acme-Financial Report 2018.pdf");

Step 5: Create a GcExcel workbook and open the XLSX file in it:

var workbook = new GrapeCity.Documents.Excel.Workbook();

Stream fileStream = GetResourceStream("CashFlowReport.xlsx");
workbook.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];

Step 6: Create a PrintManager class:

GrapeCity.Documents.Excel.PrintManager printManager = new GrapeCity.Documents.Excel.PrintManager();

Step 7: Select the print area of the worksheet range that needs to be rendered in the PDF.

IRange printArea = workbook.Worksheets[0].Range["A4:E29"];

Step 8: Get the print manager's print area size so that the content of the same height and width can be drawn on the PDF page:

 SizeF size = printManager.GetSize(printArea);

Step 9: Create rectangle dimensions on which the Excel range will be drawn. This will involve finding the particular text in the PDF document. You'll want to get the rectangle boundaries of the text and insert the Excel table under it.

RectangleF position2 = doc.FindText(new
GrapeCity.Documents.Pdf.FindTextParams("result of the statutory loss for the 
year ended 2016.", true, true), new GrapeCity.Documents.Common.OutputRange(5, 
5))[0].Bounds.ToRect();

Step 10: Using the printManager.Draw(..) method, draw the Excel content on the desired page of the PDF document (the 4th page in this case). This will involve providing the PDF page from the GcPdfDocument, the rectangle size in which the content needs to be drawn, and the print area that contains the Excel range.

printManager.Draw(doc.Pages[4], new RectangleF(position2.X, position2.Y + position2.Height + 20, size2.Width, size2.Height), printArea2);

Step 11: Save the PDF file.

doc.Save("FinancialReport.pdf");

Step 13: Run the application.

Congratulations, you have successfully injected the Excel range in a PDF file!

The full demo and sample are available here.

What do you think of this feature? Please leave a comment below or share how this feature would be useful in your business applications. Thanks!

Download GrapeCity Documents for Excel and GrapeCity Documents for PDF now.