Developers need to perform many tasks in desktop and web applications that involve document manipulation. Examples include:
- Generating a PDF form to enter data
- Exporting tabular data in Excel format
- Exporting entered data in a PDF
To accomplish these tasks, we might use the Microsoft Office Interop library. But it has some drawbacks. For example, every client machine that runs an application requires a license for Microsoft Office, and all client machines must have the same version of Microsoft Excel installed. In addition, Microsoft Office applications are user interface (UI) applications, and the API only works on Windows operating systems (and, even when working, it’s sluggish).
GrapeCity Document APIs, the ultra-fast, low-footprint APIs for enterprise apps, help us quickly and efficiently create Excel spreadsheets, Word documents, and PDF documents without using Microsoft Excel, Word, or Adobe Acrobat. These APIs also support C# and Java applications on multiple platforms.
Let’s examine some use cases that demonstrate the problems we can solve with GrapeCity Document APIs. Here, code examples are in C# for demonstration purposes, but we can code the same functions in Java.
Use Case One
In this use case, we’ll bridge the gap between legacy document management and our new application.
Let’s say our customer used Excel spreadsheets to create and store invoices for many years. They recently purchased our new web application for their invoices, and they would like to import all their historical data into our system.
We want to find a Documentation API that can parse Excel spreadsheets and convert Excel to PDF programmatically. GrapeCity Documents for Excel (GcExcel) is the perfect solution for our spreadsheet challenges. We can use GcExcel in our server or desktop applications to create, manipulate, convert, and share Microsoft Excel-compatible spreadsheets. Additionally, we can call it from nearly any application and platform.
Parsing Excel to JSON
Let’s assume our customer’s legacy Excel format invoice looks like the following:
We use GcExcel .NET to import the workbook and collect the data from it. The following shows you how:
- Make the invoice number in cell F1 a string data type
- Make the “Bill To” information in cell ranges B5 to B9 an array
- Ensure the items in cell range E7 to F16 are a JSON array object
Once we have all the values from those cells, we wrap them up into a JSON object:
Workbook workbook = new Workbook();
string source = "SimpleInvoice.xlsx";
workbook.Open (source);
IWorksheet worksheet = workbook.Worksheets[0];
Workbook workbook = new Workbook();
workbook.Open("SimpleInvoice.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
var invoice = new Invoice()
var invoiceNumberRange = worksheet.Range["F1:F1"];
invoice.invoice_number = $"{invoiceNumberRange.Cells[0].Value}";
var billToRange = worksheet.Range["B5:B9"];
int rowCount = billToRange.Rows.Count;
String billTo = "";
for (int i = 0; i < rowCount; i++)
{
billTo = billTo + billToRange.Cells[i].Value + " ";
}
invoice.bill_to = billTo;
var items = new List<InvoiceItem>();
var itemsRange = worksheet.Range["E7:H16"];
int columnCount = itemsRange.Columns.Count;
rowCount = itemsRange.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
if (itemsRange.Cells[i, 0].Value == null)
{
break;
}
var item = new InvoiceItem();
item.item_number = (string) itemsRange.Cells[i, 0].Value;
item.description = (string) itemsRange.Cells[i, 1].Value;
item.price = (double) itemsRange.Cells[i, 2].Value;
item.quantity = (double) itemsRange.Cells[i, 3].Value;
items.Add (item);
}
invoice.items = (List<InvoiceItem>)items;
String jsonString = JsonSerializer.Serialize(invoice);
File.WriteAllText("invoice.json", jsonString);</pre>
We can save the data to our database or easily convert it to any format with the JSON file.
Saving Excel to PDF
After importing the Excel files, your customer might want to generate a PDF version of the original Excel file for safekeeping.
With GcExcel, it’s effortless to convert Excel to PDF. We only need to create a workbook, open the Excel file, and save it to PDF:
Workbook workbook = new Workbook();
string source = "SimpleInvoice.xlsx";
workbook.Open (source);
workbook.Save("SimpleInvoice.pdf");
Use Case Two
In this use case, let's build PDF input forms for users to fill in at their own pace.
Here, we're building an application to manage a tennis club’s membership. Users need to provide personal information when applying for membership using our application.
It would be handy if we could build editable PDF forms. With them, users can download and fill out the form with or without Internet access. After finishing entering data, they can upload their documents online. We can parse out form entries and save them into our application. A club administrator can then generate a report that includes all member information in a single Excel spreadsheet.
With GrapeCity Document APIs, we can quickly build a fillable PDF form and parse out entries in the PDF forms to an Excel spreadsheet. All we need are two templates:
- We need a form template with various fields to create a PDF form. Refer to GrapeCity's documentation for information on how to create an Excel template with form fields.
- We need a data source template with data-bound fields to bind data parsed from the PDF forms. Refer to GrapeCity's documentation for information on how to create an Excel template with data-bound fields.
Building a PDF Form
To build a PDF form, we create an Excel form template, like the following:
In the application, we open the Excel file, process the template, and save the Excel to PDF:
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open("Template_MemberInfo.xlsx");
workbook.ProcessTemplate();
workbook.Save("MemberInfo.pdf");
Parsing a PDF Form to JSON
After club members download and complete the PDF files, they upload their forms to the application. We can then parse out form entries from all members and save the information to a JSON object.
To get the form entries, we must name each field in the template. For example, we should define the form field for the first name and last name as:
{{(form={"name":"first_name","type":"text", "required": true})}}
{{(form={"name":"last_name","type":"text", "required": true})}}
This code allows us to read the form field value by the field name:
var member = new MemberInfos();
foreach (Field field in doc.AcroForm.Fields)
{
switch (field.Name)
{
case "first_name":
member.first_name = (String) field.Value;
break;
case "last_name":
member.last_name = (String) field.Value;
break;
...
default:
break;
}
}
Then, we can write the field value to a JSON file:
var billPayList = new ArrayList();
foreach (string
inputFile
in
Directory
.EnumerateFiles(Path.Combine("Resources", "Uploads"),
"*.pdf")
)
{
billPayList.Add(GatherData.getDataFromPdf(inputFile));
}
string jsonString = JsonSerializer.Serialize(billPayList);
Generating an Excel Spreadsheet
To generate an Excel spreadsheet with form entries from all members, we create an Excel data source template, like the following:
In the template, we define the data-bound fields as {{ds.FieldName}}, where the FieldName must match the key in the JSON file we created in the previous step. For example, if the JSON file has a key-value pair as {"first_name":"Jone"}, then the data-bound fields must have {{ds.first_name}}.
In the first data-bound field, we define the cell range as R=A3:C8, which expends the data from cell A3 to cell C8 for the first club member’s information. The next member’s information starts from A9 and ends at C14, and so on.
We generate the Excel spreadsheet with the following steps. Begin by opening the template file:
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open (Path.Combine("Resources", "Templates","Template_MemberReport.xlsx"));
Next, use the AddDataSource method to add JSON data to the data source object:
var datasource =
JsonSerializer.Deserialize<List<BillPayInfos>>(File.ReadAllText(dataSourceFile));
workbook.AddDataSource("ds", datasource);
Then, process the template and save it to an Excel spreadsheet:
workbook.ProcessTemplate();
workbook.Save(Path.Combine("Output", "MemberReport.xlsx"));
This code generates a member report that looks like the following:
Use Case Three
In this use case, we’ll consolidate different methods for data input.
Let’s say we’re building a multi-tenant business application for a laptop service shop that provides parts repair, system recovery, and so on. When clients send their laptops for service, the shop outsources software service orders to one subcontractor and hardware services orders to another subcontractor.
The laptop service shop needs subcontractors to download Excel expense forms to fill out their work and cost. Once the expense files are filled and uploaded back, our application generates PDF invoices for each client.
Using GrapeCity Document APIs, we can programmatically generate a customized Excel expense form for each subcontractor. Then, when each subcontractor returns the completed form, we can extract data from it to a JSON array. Using a data source template, we can quickly generate an Excel spreadsheet for each JSON object in the array and save the Excel file in PDF to send to clients.
Generating Customized Excel
Let’s assume we have a JSON file to store the service order information (to be realistic, you would use a database.) We first create an Excel data source template:
We open the template file in the application, load the data from the JSON file to this template, and generate an Excel expense form for a subcontractor to download. The code snippets below show how to do this:
workbook
.Open(Path
.Combine("Resources",
"Templates",
"Template_Service_Cost.xlsx"));
workbook.AddDataSource("ds", softwareOrderList);
workbook.ProcessTemplate();
workbook
.Save(Path.Combine("Downloads", "subcontract_software_cost.xlsx"));
Once the subcontractors have filled out and uploaded their Excel expense forms, we can parse data from Excel to JSON as we did in Use Case One.
Generating a PDF Invoice
To generate PDF invoices for clients, we create another Excel data source template, like the following:
We fill the template with the JSON data extracted from the expense forms, generate a workbook, and save the workbook in PDF:
var workbook = new GrapeCity.Documents.Excel.Workbook();
var invoiceList =
JsonSerializer
.Deserialize<List<ServiceOrder>>(File
.ReadAllText(Path
.Combine("Resources", "DataSource", "serviceOrders.json")));
foreach (var invoice in invoiceList)
{
workbook
.Open(Path
.Combine("Resources",
"Templates",
"Template_Invoice.xlsx"));
workbook.AddDataSource("ds", invoice);
workbook.ProcessTemplate();
workbook
.Save(Path
.Combine("Downloads", $"Invoice_{invoice.order_id}.pdf"));
This code generates an invoice for each client:
Conclusion
We’ve reviewed three use cases to show how to use GrapeCity Document APIs to generate PDF from Excel files programmatically, how to parse data from an Excel or PDF file form to JSON, how to use templates to create a PDF form from an Excel file, and how to transfer data from a PDF form to Excel.
Document support for Excel, Word, and PDF is often overlooked and underestimated during product development. GrapeCity Document APIs provide the desired functionality you need. The APIs push your application to the next level and set you up for future success. GrapeCity designed APIs for developers, and they are a breeze to use.
To explore and try all the C# code for the use cases in this post, visit this GitHub repository.
To see what else is possible with the Document APIs, check out the links below:
