Import Excel Documents into LiveCycle forms


Use Case

Many of the LiveCycle developers finds it difficult to import data from Microsoft Excel documents into Adobe LiveCycle interactive PDFs. This article explains how to import a range of cell’s data from MS Excel into PDF dynamically.

The Solution
It’s relatively easily to convert the Excel document into XML using Apache POI API. Once the XML is constructed in a specific format, it can be imported into PDF/XDP using ImportData activity.

The Excel to XML conversion code can be written within a Script Activity, Web Service, or Custom DSC. I preferred writing a custom DSC which can be easily deployed into multiple environments.

The Custom DSC Source Code
Do not forget to download the Apache POI API for Excel Documents processing. Once you download the .JAR file, copy it into the lib folder of your Application Server (e.g. JBoss)

Create a Custom DSC project using Eclispe or Netbeans IDE

Write the following lines of code in the component (JAVA file)

import java.io.InputStream;
import org.apache.poi.hssf.usermodel.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;

public class ConvertExcel2XML {
public org.w3c.dom.Document ConvertExcel2XML(com.adobe.idp.Document inputDocument,boolean UseFirstRowAsColumnName)throws Exception
{
InputStream is = inputDocument.getInputStream();
HSSFWorkbook book = new HSSFWorkbook(is);
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder parser = factory.newDocumentBuilder();
Document xml = parser.newDocument();
Element rootElement = xml.createElement("ExcelContent");
xml.appendChild(rootElement);

for(int i=0;i<book.getNumberOfSheets();i++)
{
Element sheetElement = xml.createElement(book.getSheetAt(i).getSheetName().trim().replace(' ', '_'));
for(int j=UseFirstRowAsColumnName?1:0;j<book.getSheetAt(i).getPhysicalNumberOfRows();j++)
{
Element rowElement = xml.createElement("Row");
for(int k=0;k<book.getSheetAt(i).getRow(j).getPhysicalNumberOfCells();k++)
{
Element cellElement = xml.createElement(UseFirstRowAsColumnName?book.getSheetAt(i).getRow(0).getCell(k).getStringCellValue().trim():"Cell"+(k+1));
Text text = xml.createTextNode(book.getSheetAt(i).getRow(j).getCell(k).getCellType()==0?book.getSheetAt(i).getRow(j).getCell(k).getNumericCellValue()+"":book.getSheetAt(i).getRow(j).getCell(k).getStringCellValue());
cellElement.appendChild(text);
rowElement.appendChild(cellElement);
}
sheetElement.appendChild(rowElement);
}
rootElement.appendChild(sheetElement);
}
return xml;
}

}

I hope the above code is simple and self-explanatory.
Create the Component.xml file and build the project.
Deploy the compiled JAR file into LiveCycle server.

  • Goto Workbench ES2 -> Windows Menu -> Show View -> Components
  • Right click on Component and click Install Component
  • Browse the JAR file
  • Right Click on the Excel Service and click Start Component

You are ready with the newly created service running.

Utilizing the Excel2XML Custom DSC
The Excel2XML Service has one operation named ConvertExcel2XML. This operation accepts two parameters and returns an XML document.
Input Parameters:
inputDocument – denotes the required Excel document
UseFirstRowAsColumnName – denotes if the first row of the excel sheet must be ignored or considered as column names
Output Parameter:
xml – denotes the XML document converted from Excel

Please the Excel2XML service into a workflow and set the required input and output parameters.
Use the output value to import into PDF of XDP forms.

— Your comments and feedback on this article are highly appreciated —

Advertisements
This entry was posted in LiveCycle ES2 Articles. Bookmark the permalink.

2 Responses to Import Excel Documents into LiveCycle forms

  1. Pingback: Extending Adobe LiveCycle through custom DSCs - some resources | Blog in Black

  2. Pingback: Extending Adobe LiveCycle through custom DSCs – some resources | Hire Flash Developers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s