Article summary

Summary

Discusses how to create an AEM OSGi bundle that accepts Excel files and extracts data from the spreadsheet and persists the data into the AEM JCR. The Java Excel API and the JCR API libraries are used in this development article.  

This article uses an Adobe Maven Archetype project to build an OSGi bundle. If you are not familiar with an Adobe Maven Archetype project, it is recommended that you read the following article: Creating your first AEM Service using an Adobe Maven Archetype project.

This article creates a service that reads data from an Excel document. Likewise, you can create an AEM service that generate an Excel document and writes data to it. For information, see Creating a Custom Reporting Service for Adobe Experience Mananger.

This article was updated to work on AEM 6.x. 

Digital Marketing Solution(s) Adobe Experience Manager (Adobe CQ)
Audience
Developer (Intermediate)
Required Skills
JavaScript, Java. OSGi, XML, HTML
Version Adobe Experience Manager 5.5, 5.6, 6.x
Video https://youtu.be/7zIREWpTZhs

Note:

You can download an AEM package that contains code and the OSGi bundle that are used in this article. Download the package and deploy using package manager. The purpose of this code is to show the community these concepts in action. That is, it's to illustrate how to write an AEM Sling Servlet taht accepts an Excel file and writes the data to the AEM JCR. This community code is for teaching purposes only and not meant to go into production as is.

You can view the application by using the following URL: http://localhost:4502/content/ExcelApp.html (assuming you deploy on author).

* ExcelApp_Example62-1.0.zip
An AEM 6.2 Sample Excel Package
* ExcelApp_Example61-1.0.zip
An AEM 6.0/6.1 Sample Excel Package
* ExcelApp_Example-1.0.zip
An AEM 5.x Excel sample application
* Customer.xls
Sample Excel file

Introduction

You can create a custom AEM service that accepts an Excel spreadsheet, extracts the data from the spreadsheet, and persists the data in the AEM JCR. For example, consider the following spreadsheet that contains customer data. Once you upload this file to AEM, the custom AEM Excel service reads the customer data and persists the data in the AEM JCR.

Excel


In this example, each customer row that is extracted from the excel spreadsheet is stored within a JCR node. The values, such as first name and last name, are stored as node properties, as shown in the following illustration.
 

crxde

 

In this development article, the customer data that is extracted from the Excel spreadsheet is placed in the following JCR location: content/customerexcel. This is shown in the following illustration.

Client
The location in the AEM JCR where customer data is persisted (content/customerexcel)

The application logic required to extract data from the excel spreadsheet is developed by using the JExcel API. For information, see:

http://jexcelapi.sourceforge.net/

Likewise, the JCR API is used to persist data retrieved from the excel spreadsheet into the AEM JCR. It
is implemented as an OSGi bundle that is built using Declarative Services (DS) and Maven. DS is used to inject a SlingRepository instance into the service. The OSGi bundle is a managed component, which means that the OSGi service container creates the SlingRepository instance.  

Create an Experience Manager application folder structure 

Create an Experience Manager application folder structure that contains templates, components, and pages by using CRXDE Lite. 

CQAppSetup

The following describes each application folder:

  • application name: contains all of the resources that an application uses. The resources can be templates, pages, components, and so on.
  • components: contains components that your application uses.
  • page: contains page components. A page component is a script such as a JSP file.
  • global: contains global components that your application uses.
  • template: contains templates on which you base page components.
  • src: contains source code that comprises an OSGi component (this development article does not create an OSGi bundle using this folder).
  • install: contains a compiled OSGi bundles container.

To create an application folder structure:

  1. Go to CRXDE Lite at http://localhost:4502/crx/de/index.jsp#.

  2. Right-click the apps folder (or the parent folder), select Create, Create Folder.

  3. Enter the folder name into the Create Folder dialog box. Enter excelpersist. 

  4. Repeat steps 1-4 for each folder specified in the previous illustration. 

  5. Click the Save All button.

Note:

You have to click the Save All button when working in CRXDELite for the changes to be made.  

Create a template 

You can create a template by using CRXDE Lite. A template enables you to define a consistent style for the pages in your application. A template comprises of nodes that specify the page structure. For more information about templates, see Templates

To create a template, perform these tasks:

  1. Go to Select CRXDE Lite at http://localhost:4502/crx/de/index.jsp#.

  2. Right-click the template folder (within your application), select Create, Create Template.

  3. Enter the following information into the Create Template dialog box:

    • Label: The name of the template to create. Enter templateExcel.
    • Title: The title that is assigned to the template.
    • Description: The description that is assigned to the template.
    • Resource Type: The component's path that is assigned to the template and copied to implementing pages. Enter excelpersist/components/page/templateExcel.
    • Ranking: The order (ascending) in which this template will appear in relation to other templates. Setting this value to 1 ensures that the template appears first in the list.
  4. Add a path to Allowed Paths. Click on the plus sign and enter the following value: /content(/.*)?.

  5. Click Next for Allowed Parents.

  6. Select OK on Allowed Children.

Create a render component that uses the template 

Components are re-usable modules that implement specific application logic to render the content of your web site. You can think of a component as a collection of scripts (for example, JSPs, Java servlets, and so on) that completely realize a specific function. In order to realize this functionality, it is your responsibility as an Experience Manager developer to create scripts that perform specific functionality. For more information about components, see Components.

By default, a component has at least one default script, identical to the name of the component. To create a render component, perform these tasks:

  1. Go to Select CRXDE Lite at http://localhost:4502/crx/de/index.jsp#.

  2.  Right-click /apps/excelpersist/components/page, then select Create, Create Component.

  3. Enter the following information into the Create Component dialog box:

    • Label: The name of the component to create. Enter templateExcel
    • Title: The title that is assigned to the component.
    • Description: The description that is assigned to the template.
    • Super Type:foundation/components/page. 
  4. Select Next for Advanced Component Settings and Allowed Parents.

  5. Select OK on Allowed Children.

  6. Open the templateJCR.jsp located at: /apps/excelpersist/components/page/templateExcel/templateExcel.jsp.

  7. Enter the following JSP code.

<html>
<head>
<title>Hello World !!!</title>
</head>
<body>
<h1>Hello Excel Service!!</h1>
<h2>This page will let a user upload an Excel file to Experience Manager</h2>
</body>
</html>

Setup Maven in your development environment 

You can use Maven to build an OSGi bundle that uses the Java Excel API and the JCR API. Maven manages required JAR files that a Java project needs in its class path. Instead of searching the Internet trying to find and download third-party JAR files to include in your project’s class path, Maven manages these dependencies for you.

You can download Maven 3 from the following URL:

http://maven.apache.org/download.html

After you download and extract Maven, create an environment variable named M3_HOME. Assign the Maven install location to this environment variable. For example:

C:\Programs\Apache\apache-maven-3.0.4

Set up a system environment variable to reference Maven. To test whether you properly setup Maven, enter the following Maven command into a command prompt:

%M3_HOME%\bin\mvn -version

This command provides Maven and Java install details and resembles the following message:

Java home: C:\Programs\Java64-6\jre
Default locale: en_US, platform encoding: Cp1252
OS name: "windows 7", version: "6.1", arch: "amd64", family: "windows"

Note:

For more information about setting up Maven and the Home variable, see: Maven in 5 Minutes.

Next, copy the Maven configuration file named settings.xml from [install location]\apache-maven-3.0.4\conf\ to your user profile. For example, C:\Users\scottm\.m2\.

You have to configure your settings.xml file to use Adobe’s public repository. For information, see Adobe Public Maven Repository at http://repo.adobe.com/.

The following XML code represents a settings.xml file that you can use.

<?xml version="1.0" encoding="UTF-8"?>

<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
-->

<!--
 | This is the configuration file for Maven. It can be specified at two levels:
 |
 |  1. User Level. This settings.xml file provides configuration for a single user, 
 |                 and is normally provided in ${user.home}/.m2/settings.xml.
 |
 |                 NOTE: This location can be overridden with the CLI option:
 |
 |                 -s /path/to/user/settings.xml
 |
 |  2. Global Level. This settings.xml file provides configuration for all Maven
 |                 users on a machine (assuming they're all using the same Maven
 |                 installation). It's normally provided in 
 |                 ${maven.home}/conf/settings.xml.
 |
 |                 NOTE: This location can be overridden with the CLI option:
 |
 |                 -gs /path/to/global/settings.xml
 |
 | The sections in this sample file are intended to give you a running start at
 | getting the most out of your Maven installation. Where appropriate, the default
 | values (values used when the setting is not specified) are provided.
 |
 |-->
<settings xmlns="http://maven.apache.org/SETTINGS/1.0.0" 
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
          xsi:schemaLocation="http://maven.apache.org/SETTINGS/1.0.0 http://maven.apache.org/xsd/settings-1.0.0.xsd">
  <!-- localRepository
   | The path to the local repository maven will use to store artifacts.
   |
   | Default: ~/.m2/repository
  <localRepository>/path/to/local/repo</localRepository>
  -->

  <!-- interactiveMode
   | This will determine whether maven prompts you when it needs input. If set to false,
   | maven will use a sensible default value, perhaps based on some other setting, for
   | the parameter in question.
   |
   | Default: true
  <interactiveMode>true</interactiveMode>
  -->

  <!-- offline
   | Determines whether maven should attempt to connect to the network when executing a build.
   | This will have an effect on artifact downloads, artifact deployment, and others.
   |
   | Default: false
  <offline>false</offline>
  -->

  <!-- pluginGroups
   | This is a list of additional group identifiers that will be searched when resolving plugins by their prefix, i.e.
   | when invoking a command line like "mvn prefix:goal". Maven will automatically add the group identifiers
   | "org.apache.maven.plugins" and "org.codehaus.mojo" if these are not already contained in the list.
   |-->
  <pluginGroups>
    <!-- pluginGroup
     | Specifies a further group identifier to use for plugin lookup.
    <pluginGroup>com.your.plugins</pluginGroup>
    -->
  </pluginGroups>

  <!-- proxies
   | This is a list of proxies which can be used on this machine to connect to the network.
   | Unless otherwise specified (by system property or command-line switch), the first proxy
   | specification in this list marked as active will be used.
   |-->
  <proxies>
    <!-- proxy
     | Specification for one proxy, to be used in connecting to the network.
     |
    <proxy>
      <id>optional</id>
      <active>true</active>
      <protocol>http</protocol>
      <username>proxyuser</username>
      <password>proxypass</password>
      <host>proxy.host.net</host>
      <port>80</port>
      <nonProxyHosts>local.net|some.host.com</nonProxyHosts>
    </proxy>
    -->
  </proxies>

  <!-- servers
   | This is a list of authentication profiles, keyed by the server-id used within the system.
   | Authentication profiles can be used whenever maven must make a connection to a remote server.
   |-->
  <servers>
    <!-- server
     | Specifies the authentication information to use when connecting to a particular server, identified by
     | a unique name within the system (referred to by the 'id' attribute below).
     | 
     | NOTE: You should either specify username/password OR privateKey/passphrase, since these pairings are 
     |       used together.
     |
    <server>
      <id>deploymentRepo</id>
      <username>repouser</username>
      <password>repopwd</password>
    </server>
    -->
    
    <!-- Another sample, using keys to authenticate.
    <server>
      <id>siteServer</id>
      <privateKey>/path/to/private/key</privateKey>
      <passphrase>optional; leave empty if not used.</passphrase>
    </server>
    -->
  </servers>

  <!-- mirrors
   | This is a list of mirrors to be used in downloading artifacts from remote repositories.
   | 
   | It works like this: a POM may declare a repository to use in resolving certain artifacts.
   | However, this repository may have problems with heavy traffic at times, so people have mirrored
   | it to several places.
   |
   | That repository definition will have a unique id, so we can create a mirror reference for that
   | repository, to be used as an alternate download site. The mirror site will be the preferred 
   | server for that repository.
   |-->
  <mirrors>
    <!-- mirror
     | Specifies a repository mirror site to use instead of a given repository. The repository that
     | this mirror serves has an ID that matches the mirrorOf element of this mirror. IDs are used
     | for inheritance and direct lookup purposes, and must be unique across the set of mirrors.
     |
    <mirror>
      <id>mirrorId</id>
      <mirrorOf>repositoryId</mirrorOf>
      <name>Human Readable Name for this Mirror.</name>
      <url>http://my.repository.com/repo/path</url>
    </mirror>
     -->
  </mirrors>
  
  <!-- profiles
   | This is a list of profiles which can be activated in a variety of ways, and which can modify
   | the build process. Profiles provided in the settings.xml are intended to provide local machine-
   | specific paths and repository locations which allow the build to work in the local environment.
   |
   | For example, if you have an integration testing plugin - like cactus - that needs to know where
   | your Tomcat instance is installed, you can provide a variable here such that the variable is 
   | dereferenced during the build process to configure the cactus plugin.
   |
   | As noted above, profiles can be activated in a variety of ways. One way - the activeProfiles
   | section of this document (settings.xml) - will be discussed later. Another way essentially
   | relies on the detection of a system property, either matching a particular value for the property,
   | or merely testing its existence. Profiles can also be activated by JDK version prefix, where a 
   | value of '1.4' might activate a profile when the build is executed on a JDK version of '1.4.2_07'.
   | Finally, the list of active profiles can be specified directly from the command line.
   |
   | NOTE: For profiles defined in the settings.xml, you are restricted to specifying only artifact
   |       repositories, plugin repositories, and free-form properties to be used as configuration
   |       variables for plugins in the POM.
   |
   |-->
  <profiles>
    <!-- profile
     | Specifies a set of introductions to the build process, to be activated using one or more of the
     | mechanisms described above. For inheritance purposes, and to activate profiles via <activatedProfiles/>
     | or the command line, profiles have to have an ID that is unique.
     |
     | An encouraged best practice for profile identification is to use a consistent naming convention
     | for profiles, such as 'env-dev', 'env-test', 'env-production', 'user-jdcasey', 'user-brett', etc.
     | This will make it more intuitive to understand what the set of introduced profiles is attempting
     | to accomplish, particularly when you only have a list of profile id's for debug.
     |
     | This profile example uses the JDK version to trigger activation, and provides a JDK-specific repo.
    <profile>
      <id>jdk-1.4</id>

      <activation>
        <jdk>1.4</jdk>
      </activation>

      <repositories>
        <repository>
          <id>jdk14</id>
          <name>Repository for JDK 1.4 builds</name>
          <url>http://www.myhost.com/maven/jdk14</url>
          <layout>default</layout>
          <snapshotPolicy>always</snapshotPolicy>
        </repository>
      </repositories>
    </profile>
    -->

    <!--
     | Here is another profile, activated by the system property 'target-env' with a value of 'dev',
     | which provides a specific path to the Tomcat instance. To use this, your plugin configuration
     | might hypothetically look like:
     |
     | ...
     | <plugin>
     |   <groupId>org.myco.myplugins</groupId>
     |   <artifactId>myplugin</artifactId>
     |   
     |   <configuration>
     |     <tomcatLocation>${tomcatPath}</tomcatLocation>
     |   </configuration>
     | </plugin>
     | ...
     |
     | NOTE: If you just wanted to inject this configuration whenever someone set 'target-env' to
     |       anything, you could just leave off the <value/> inside the activation-property.
     |
    <profile>
      <id>env-dev</id>

      <activation>
        <property>
          <name>target-env</name>
          <value>dev</value>
        </property>
      </activation>

      <properties>
        <tomcatPath>/path/to/tomcat/instance</tomcatPath>
      </properties>
    </profile>
    -->
  

<profile>

                <id>adobe-public</id>

                <activation>

                    <activeByDefault>true</activeByDefault>

                </activation>

                <repositories>

                  <repository>

                    <id>adobe</id>

                    <name>Nexus Proxy Repository</name>

                    <url>http://repo.adobe.com/nexus/content/groups/public/</url>

                    <layout>default</layout>

                  </repository>

                </repositories>

                <pluginRepositories>

                  <pluginRepository>

                    <id>adobe</id>

                    <name>Nexus Proxy Repository</name>

                    <url>http://repo.adobe.com/nexus/content/groups/public/</url>

                    <layout>default</layout>

                  </pluginRepository>

                </pluginRepositories>

            </profile>

</profiles>

  <!-- activeProfiles
   | List of profiles that are active for all builds.
   |
  <activeProfiles>
    <activeProfile>alwaysActiveProfile</activeProfile>
    <activeProfile>anotherAlwaysActiveProfile</activeProfile>
  </activeProfiles>
  -->
</settings>

Create an Experience Manager archetype project 

You can create an Experience Manager archetype project by using the Maven archetype plugin. In this example, assume that the working directory is C:\AdobeCQ.  

Maven

 

To create an Experience Manager archetype project, perform these steps:

  1. Open the command prompt and go to your working directory (for example, C:\AdobeCQ).

    mvn archetype:generate -DarchetypeRepository=https://repo.adobe.com/nexus/content/groups/public/ -DarchetypeGroupId=com.day.jcr.vault -DarchetypeArtifactId=multimodule-content-package-archetype -DarchetypeVersion=1.0.2 -DgroupId=com.adobe.cq.excel  -DartifactId=excel -Dversion=1.0-SNAPSHOT -Dpackage=com.adobe.cq.excel  -DappsFolderName=myproject -DartifactName="My Project" -DcqVersion="5.6.1" -DpackageGroup="My Company"

  2. When prompted, specify Y.

  3. Once done, you will see a message like: 

     [INFO] Finished at: Wed Mar 27 13:38:58 EDT 2013
    [INFO] Final Memory: 10M/184M

  4. Change the command prompt to the generated project. For example: C:\AdobeCQ\excel.

  5. Run the following Maven command:

    mvn eclipse:eclipse

    After you run this command, you can import the project into Eclipse as discussed in the next section.

Add Java files to the Maven project using Eclipse

To make it easier to work with the Maven generated project, import it into the Eclipse development environment, as shown in the following illustration. 

Java

 

The next step is to add Java files to the com.adobe.cq.excel package. The Java file that you create in this section uses both the Java Excel API and the JCR API. The Excel API lets you dynamically extract data from an Excel spreadsheet. For information, see http://jexcelapi.sourceforge.net/.

Note:

The Java Excel API supports Excel 95, 97, 2000, XP, and 2003 workbooks. If you have a newer version, be sure to save your excel file as one of these versions.  

The Java class that you create in this section extends the Sling class named org.apache.sling.api.servlets.SlingAllMethodsServlet. This class supports the doPost method that lets you submit an excel file to Experience Manager from a client web page. For information about this class, see Class SlingAllMethodsServlet.   

Create a Java class named HandleFile located in the com.adobe.cq.excel package that extends org.apache.sling.api.servlets.SlingAllMethodsServlet. Within the doPost method, create Java Sling application logic that  reads the file that is uploaded to the Sling servlet. The fully qualified names of the Java objects are used so you understand the data types used in this code fragment.

@Override
     protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServerException, IOException {
        
                 
         try
         {
         final boolean isMultipart = org.apache.commons.fileupload.servlet.ServletFileUpload.isMultipartContent(request);
         PrintWriter out = null;
          
           out = response.getWriter();
           if (isMultipart) {
             final java.util.Map<String, org.apache.sling.api.request.RequestParameter[]> params = request.getRequestParameterMap();
             for (final java.util.Map.Entry<String, org.apache.sling.api.request.RequestParameter[]> pairs : params.entrySet()) {
               final String k = pairs.getKey();
               final org.apache.sling.api.request.RequestParameter[] pArr = pairs.getValue();
               final org.apache.sling.api.request.RequestParameter param = pArr[0];
               final InputStream stream = param.getInputStream();
               
                   //The InputStream represents the excel file
              
             }
           }
         }
          
         catch (Exception e) {
             e.printStackTrace();
         }
      
     }

In the previous code example, the application logic to get an InputStream that represents the Excel file is shown. Now you can pass the InputStream object to another method to extract the data by using the Java Excel API. In this example, to keep the application logic simple, data is extract from the following rows in the spreadsheet.

Excel2

Note:

To follow along with this development article, make sure that the Excel file that you upload to Experience Manager contains customer data in the same rows and columns shown in the previous illustration. Also make sure that the Excel file is the supported version that is specified earlier in this section.

The following Java code represents the injectSpreadSheet method. This method accepts an InputStream that represents the excel file as an input argument. It uses the Java Excel API to exact data values from the excel spreadsheet.

//Get data from the excel spreadsheet
 public int injectSpreadSheet(InputStream is)
{
try
{
	//Get the spreadsheet
	Workbook workbook = Workbook.getWorkbook(is);
 			
	Sheet sheet = workbook.getSheet(0);
	String firstName = ""; 
	String lastName = ""; 
	String address = "";
	String desc = "";
			
	for (int index=0; index<4;index++)
	{
		Cell a3 = sheet.getCell(0,index+2); 
		Cell b3 = sheet.getCell(1,index+2); 
		Cell c3 = sheet.getCell(2,index+2);
		Cell d3 = sheet.getCell(3,index+2);
	
		firstName = a3.getContents(); 
		lastName = b3.getContents(); 
		address = c3.getContents();
		desc = d3.getContents();
				
		//Store the excel data into the Adobe AEM JCR
		injestCustData(firstName,lastName,address, desc);
			
	}	
return 0; 
}
		
catch(Exception e)
	{
	e.printStackTrace();
	}	
return -1 ; 
}

The remaining part of this code persists the data extracted from the spreadsheet into the AEM JCR. For information about this code, see http://helpx.adobe.com/adobe-cq/using/persisting-cq-data-java-content.html.

The following Java code represents the entire HandleFile Java class.  

package com.adobe.cq.excel;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.rmi.ServerException;
import java.util.Dictionary;
import java.util.Calendar;
import java.io.*;
   
import org.apache.felix.scr.annotations.Properties;
import org.apache.felix.scr.annotations.Property;
import org.apache.felix.scr.annotations.Reference;
import org.apache.felix.scr.annotations.sling.SlingServlet;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingSafeMethodsServlet;
import org.apache.sling.commons.osgi.OsgiUtil;
import org.apache.sling.jcr.api.SlingRepository;
import org.apache.felix.scr.annotations.Reference;
import org.osgi.service.component.ComponentContext;
import javax.jcr.Session;
import javax.jcr.Node; 
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.Streams;
import org.apache.felix.scr.annotations.Component;
import org.apache.felix.scr.annotations.Service;
import javax.jcr.ValueFactory;
import javax.jcr.Binary;
 
import javax.jcr.RepositoryException;
import org.apache.felix.scr.annotations.Activate;
import org.apache.felix.scr.annotations.Reference;
import org.apache.jackrabbit.commons.JcrUtils;
import org.osgi.service.component.ComponentContext;
  
import javax.servlet.Servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.io.OutputStream;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import jxl.*; 
 
//Sling Imports
import org.apache.sling.api.resource.ResourceResolverFactory ; 
import org.apache.sling.api.resource.ResourceResolver; 
import org.apache.sling.api.resource.Resource; 
  
//This is a component so it can provide or consume services
@SlingServlet(paths="/bin/excelfile", methods = "POST", metatype=true)
public class HandleFile extends org.apache.sling.api.servlets.SlingAllMethodsServlet {
     private static final long serialVersionUID = 2598426539166789515L;
        
   //Inject a Sling ResourceResolverFactory
     @Reference
     private ResourceResolverFactory resolverFactory;
      
     private Session session;
      
     /** Default log. */
     protected final Logger log = LoggerFactory.getLogger(this.getClass());
       
                
     @Override
     protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServerException, IOException {
     
          
  
         
     }
       
       
     @Override
     protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServerException, IOException {
         
                  
         try
         {
         final boolean isMultipart = org.apache.commons.fileupload.servlet.ServletFileUpload.isMultipartContent(request);
         PrintWriter out = null;
           
           
         
         log.info("GET THE STREAM"); 
         
         out = response.getWriter();
           if (isMultipart) {
             final java.util.Map<String, org.apache.sling.api.request.RequestParameter[]> params = request.getRequestParameterMap();
             for (final java.util.Map.Entry<String, org.apache.sling.api.request.RequestParameter[]> pairs : params.entrySet()) {
               final String k = pairs.getKey();
               final org.apache.sling.api.request.RequestParameter[] pArr = pairs.getValue();
               final org.apache.sling.api.request.RequestParameter param = pArr[0];
               final InputStream stream = param.getInputStream();
                
               
               
               log.info("GET THE STREAM22"); 
               //Save the uploaded file into the Adobe CQ DAM
               int excelValue = injectSpreadSheet(stream);
               if (excelValue == 0)
                     out.println("Customer data from the Excel Spread Sheet has been successfully imported into the AEM JCR");
               else
                   out.println("Customer data could not be imported into the AEM JCR");
               
             }
           }
         }
           
         catch (Exception e) {
             e.printStackTrace();
         }
       
     }
       
   //Get data from the excel spreadsheet
     public int injectSpreadSheet(InputStream is)
     {
         try
         {
             
        	 log.info("GET THE STREAM33");
        	 //Get the spreadsheet
             Workbook workbook = Workbook.getWorkbook(is);
              
             log.info("GET THE STREAMWorkbook");
             Sheet sheet = workbook.getSheet(0);
              
             
             log.info("GET THE STREAMWorkbook");
            String firstName = ""; 
            String lastName = ""; 
            String address = "";
            String desc = "";
             
            log.info("GET THE STREAM44"); 
            for (int index=0; index<4;index++)
            {
                Cell a3 = sheet.getCell(0,index+2); 
                Cell b3 = sheet.getCell(1,index+2); 
                Cell c3 = sheet.getCell(2,index+2);
                Cell d3 = sheet.getCell(3,index+2);
     
                firstName = a3.getContents(); 
                lastName = b3.getContents(); 
                address = c3.getContents();
                desc = d3.getContents();
                 
                
                log.info("About to inject cust data ..." +firstName);
                
                //Store the excel data into the Adobe AEM JCR
               injestCustData(firstName,lastName,address, desc);
             
                }    
             
            return 0; 
             
         }
         
         catch(Exception e)
         {
            e.printStackTrace();
         }    
         return -1 ; 
         }
     //Stores customer data in the Adobe CQ JCR
     public int injestCustData(String firstName, String lastName, String address, String desc)
     {
         int num  = 0; 
         try { 
         
             //Invoke the adaptTo method to create a Session used to create a QueryManager
             ResourceResolver resourceResolver = resolverFactory.getAdministrativeResourceResolver(null);
             session = resourceResolver.adaptTo(Session.class);
                    
             //Create a node that represents the root node
             Node root = session.getRootNode(); 
                     
             //Get the content node in the JCR
             Node content = root.getNode("content");
                      
             //Determine if the content/customer node exists
             Node customerRoot = null;
             int custRec = doesCustExist(content);
         
             
             log.info("*** Value of  custRec is ..." +custRec);                               
             //-1 means that content/customer does not exist
             if (custRec == -1)
             {
                 //content/customer does not exist -- create it
                 customerRoot = content.addNode("customerexcel");
             }
            else
            {
                //content/customer does exist -- retrieve it
                customerRoot = content.getNode("customerexcel");
            }
                      
          int custId = custRec+1; //assign a new id to the customer node
                      
          //Store content from the client JSP in the JCR
         Node custNode = customerRoot.addNode("customer"+firstName+lastName+custId, "nt:unstructured"); 
               
             //make sure name of node is unique
         custNode.setProperty("id", custId); 
         custNode.setProperty("firstName", firstName); 
         custNode.setProperty("lastName", lastName); 
         custNode.setProperty("address", address);  
         custNode.setProperty("desc", desc);
                                    
         // Save the session changes and log out
         session.save(); 
         session.logout();
         return custId; 
         }
       
      catch(Exception  e){
          log.error("RepositoryException: " + e);
           }
      return 0 ; 
      } 
       
      
      
     /*
      * Determines if the content/customer node exists 
      * This method returns these values:
      * -1 - if customer does not exist
      * 0 - if content/customer node exists; however, contains no children
      * number - the number of children that the content/customer node contains
     */
     private int doesCustExist(Node content)
     {
         try
         {
             int index = 0 ; 
             int childRecs = 0 ; 
               
         java.lang.Iterable<Node> custNode = JcrUtils.getChildNodes(content, "customerexcel");
         Iterator it = custNode.iterator();
                    
         //only going to be 1 content/customer node if it exists
         if (it.hasNext())
             {
             //Count the number of child nodes in content/customer
             Node customerRoot = content.getNode("customerexcel");
             Iterable itCust = JcrUtils.getChildNodes(customerRoot); 
             Iterator childNodeIt = itCust.iterator();
                   
             //Count the number of customer child nodes 
             while (childNodeIt.hasNext())
             {
                 childRecs++;
                 childNodeIt.next();
             }
              return childRecs; 
            }
         else
             return -1; //content/customer does not exist
         }
         catch(Exception e)
         {
             e.printStackTrace();
         }
         return 0;
      }
      
      
}

Add the jxl API to Experience Manager

Add the jxl.* classes to Experience Manager. The reason is because the HandleFile class (the Sling Servlet) uses these classes to extract data from the Excel spreadsheet. If you do not add the jxl.* classes to Experience Manager, then you are unable to place the OSGi bundle that contains the Sling Servlet into an Active state.

To add the jxl.* classes to Experience Manager, add it to a bundle fragment and then deploy the bundle fragment to Experience Manager, as discussed in this section. First, download the jxl JAR from the Maven site:

http://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl 

To create an OSGi bundle fragment that contains the jxl.* classes, perform these tasks:

  1. Start Eclipse (Indigo version works best). The steps below have been tested on Eclipse Java EE IDE for Web Developers version Indigo Service Release 1.

  2. Select File, New, Other.

  3. Under the Plug-in Development folder, choose Plug-in from Existing JAR Archives. Name your project excelBundle.

  4. In the JAR selection dialog, click the Add external button, and browse to the jxl JAR file that you downloaded.

  5. Click Next.

  6. In the Plug-in Project properties dialog, ensure that you check the checkbox for Analyze library contents and add dependencies.

  7. Make sure that the Target Platform is the standard OSGi framework.

  8. Ensure the checkboxes for Unzip the JAR archives into the project and Update references to the JAR files are both checked.

  9. Click Next, and then Finish.

  10. Click the Runtime tab.

  11. Make sure that the Exported Packages list is populated.

  12. Make sure these packages have been added under the Export-Package header in MANIFEST.MF. Remove the version information in the MANIFEST.MF file. Version numbers can cause conflicts when you upload the OSGi bundle.

  13. Also make sure that the Import-Package header in MANIFEST.MF is also populated, as shown here (notice that Export-Package is jxl).

    Bundle-ManifestVersion: 2
    Bundle-Name: ExcelOSGi
    Bundle-SymbolicName: ExcelOSGi
    Bundle-Version: 1.0.0
    Bundle-ClassPath: .
    Export-Package: jxl,
    jxl.biff,
    jxl.biff.drawing,
    jxl.biff.formula,
    jxl.common,
    jxl.common.log,
    jxl.demo,
    jxl.format,
    jxl.read.biff,
    jxl.write,
    jxl.write.biff
    Bundle-RequiredExecutionEnvironment: JavaSE-1.6

  14. Save the project.

  15. Build the OSGi bundle by right-clicking the project in the left pane, choose Export, Plug-in Development, Deployable plug-ins and fragments, and click Next.

  16. Select a location for the export (C:\TEMP) and click Finish. (Ignore any error messages).

  17. In C:\TEMP\plugins, you should now find the OSGi bundle.

  18. Login to Apache Felix Web Console at http://server:port/system/console/bundles (default admin user = admin with password= admin).

  19. Sort the bundle list by Id and note the Id of the last bundle.

  20. Click the Install/Update button.

  21. Check the Start Bundle checkbox.

  22. Browse to the bundle JAR file you just built. (C:\TEMP\plugins).

  23. Click Install.

  24. Click the Refresh Packages button.

  25. Check the bundle with the highest Id.

  26. Your new bundle should now be listed with the status Active.

  27.  If the status is not Active, check the error.log for exceptions. If you get “org.osgi.framework.BundleException: Unresolved constraint” errors, check the MANIFEST.MF for strict version requirements which might follow: javax.xml.namespace; version=”3.1.0”

  28. If the version requirement causes problems, remove it so that the entry looks like this: javax.xml.namespace.

  29. If the entry is not required, remove it entirely.

  30. Rebuild the bundle.

  31. Delete the previous bundle and deploy the new one.

Modify the Maven POM file 

Modify the POM files to successfully build the OSGi bundle that contains the Sling servlet. In the POM file located at C:\AdobeCQ\excel, add the following dependencies.

  1. net.sourceforge.jexcelapi

  2. Step text
  3. org.apache.felix.scr

  4. org.apache.felix.scr.annotations

  5. org.apache.jackrabbit

  6. org.apache.sling

The following XML represents this POM file.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd ">
    <modelVersion>4.0.0</modelVersion>
    <!-- ====================================================================== -->
    <!-- P A R E N T P R O J E C T D E S C R I P T I O N -->
    <!-- ====================================================================== -->
    <parent>
        <groupId>com.adobe.cq.excel</groupId>
        <artifactId>excel</artifactId>
        <version>1.0-SNAPSHOT</version>
    </parent>

    <!-- ====================================================================== -->
    <!-- P R O J E C T D E S C R I P T I O N -->
    <!-- ====================================================================== -->

    <artifactId>excel-bundle</artifactId>
    <packaging>bundle</packaging>
    <name>My Project Bundle</name>

   <dependencies>
     
    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
    </dependency>
    <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>org.osgi.compendium</artifactId>
    </dependency>
        <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>org.osgi.core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.felix</groupId>
            <artifactId>org.apache.felix.scr.annotations</artifactId>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
          
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.2</version>
        </dependency>
            
             
        <dependency>
         <groupId>org.apache.felix</groupId>
      
         <artifactId>org.osgi.core</artifactId>
      
         <version>1.4.0</version>
      </dependency>
          
    <dependency>
        <groupId>org.apache.sling</groupId>
        <artifactId>org.apache.sling.commons.osgi</artifactId>
        <version>2.2.0</version>
    </dependency>
                 
           
             
    <dependency>
    <groupId>org.apache.jackrabbit</groupId>
    <artifactId>jackrabbit-core</artifactId>
    <version>2.4.3</version>
    </dependency>
          
    <dependency>
    <groupId>org.apache.jackrabbit</groupId>
    <artifactId>jackrabbit-jcr-commons</artifactId>
    <version>2.4.3</version>
    </dependency>
      
    <dependency>
        <groupId>org.apache.sling</groupId>
        <artifactId>org.apache.sling.jcr.api</artifactId>
        <version>2.0.4</version>
      </dependency>
   
       <dependency>
        <groupId>org.apache.sling</groupId>
        <artifactId>org.apache.sling.api</artifactId>
        <version>2.0.2-incubator</version>
      </dependency>   
            
      <dependency>
         <groupId>javax.jcr</groupId>
         <artifactId>jcr</artifactId>
         <version>2.0</version>
      </dependency>
   
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>servlet-api</artifactId>
    <version>2.5</version>
</dependency>
               
    <dependency>
    <groupId>org.apache.sling</groupId>
    <artifactId>org.apache.sling.api</artifactId>
    <version>2.2.0</version>
</dependency>
        </dependencies>
    <!-- ====================================================================== -->
    <!-- B U I L D D E F I N I T I O N -->
    <!-- ====================================================================== -->
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-scr-plugin</artifactId>
                <executions>
                    <execution>
                        <id>generate-scr-descriptor</id>
                        <goals>
                            <goal>scr</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <extensions>true</extensions>
                <configuration>
                    <instructions>
                        <Bundle-SymbolicName>com.adobe.cq.excel.excel-bundle</Bundle-SymbolicName>
                    </instructions>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.sling</groupId>
                <artifactId>maven-sling-plugin</artifactId>
                <configuration>
                    <slingUrl>http://${crx.host}:${crx.port}/apps/myproject/install</slingUrl>
                    <usePut>true</usePut>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-javadoc-plugin</artifactId>
                 <configuration>
                    <excludePackageNames>
                        *.impl
                    </excludePackageNames>
                 </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Build the OSGi bundle using Maven  

To build the OSGi component by using Maven, perform these steps:

  1. Open the command prompt and go to the C:\AdobeCQ\excel folder.

  2. Run the following maven command: mvn clean install.

  3. The OSGi component can be found in the following folder: C:\AdobeCQ\excel\bundle\target. The file name of the OSGi component is excel-bundle-1.0-SNAPSHOT.

Deploy the bundle to Experience Manager

Once you deploy the OSGi bundle, you can upload an excel file to Experience Manager and save the data in the JCR. Deploy the OSGi bundle by performing these steps:

  1. Login to the Apache Felix Web Console at http://server:port/system/console/bundles (default admin user = admin with password= admin).

  2. Click the Bundles tab, sort the bundle list by Id, and note the Id of the last bundle.

  3. Click the Install/Update button.

  4. Browse to the bundle JAR file you just built using Maven. (C:\AdobeCQ\excel\bundle\target).

  5. Click Install.

  6. Click the Refresh Packages button.

  7. Check the bundle with the highest Id.

  8. Click Active.

  9. Your new bundle should now be listed with the status Active.

  10. If the status is not Active, check the error.log for exceptions.

Note:

The bundle fragment that contains the Java Excel API needs to be in an Active state before this OSGi bundle can be placed in an Active State.

Modify the templateExcel JSP to post an Excel file to the Sling Servlet  

Modify the templateExcel.jsp file to post an Excel file to the Sling Servlet that was created in this development article. In this example, a JQuery Ajax Post request is used and the file is posted to the Sling Servlet's doPost method (the method defined in the HandleFile Java class).

The following code represents the AJAX request.

 $.ajax({
                        type: 'POST',    
                        url:'/bin/excelfile',
                        processData: false,  
                        contentType: false,  
                        data:formData,
                        success: function(msg){
                          alert(msg); //display the data returned by the servlet
                        }
                    });

Notice that the url specifies the value of the path attribute in the SlingServlet annotation defined in the HandleFile method. The following code represents the templateExcel JSP page.  

<%@include file="/libs/foundation/global.jsp"%>
<cq:includeClientLib categories="cq.jquery" />
<script type="text/javascript">
  
jQuery(function ($) {
      
      });
  
</script>
  
<body>
<div>
<h2>Upload an Excel file that contains customer data to the Adobe CQ DAM</h2>
        <p id="support-notice">Your browser does not support Ajax uploads :-(The form will be submitted as normal.</p>
  
        <!-- The form starts -->
        <form action="/" method="POST" enctype="multipart/form-data" id="form-id">
  
            <!-- The file to upload -->
            <p><input id="file-id" type="file" name="our-file" />
  
                <!--
                  Also by default, we disable the upload button.
                  If Ajax uploads are supported we'll enable it.
                -->
                <input type="button" value="Upload" id="upload-button-id" disabled="disabled" /></p>
             
            <script>
                // Function that will allow us to know if Ajax uploads are supported
                function supportAjaxUploadWithProgress() {
                    return supportFileAPI() && supportAjaxUploadProgressEvents() && supportFormData();
  
                    // Is the File API supported?
                    function supportFileAPI() {
                        var fi = document.createElement('INPUT');
                        fi.type = 'file';
                        return 'files' in fi;
                    };
  
                    // Are progress events supported?
                    function supportAjaxUploadProgressEvents() {
                        var xhr = new XMLHttpRequest();
                        return !! (xhr && ('upload' in xhr) && ('onprogress' in xhr.upload));
                    };
  
                    // Is FormData supported?
                    function supportFormData() {
                        return !! window.FormData;
                    }
                }
  
                // Actually confirm support
                if (supportAjaxUploadWithProgress()) {
                    // Ajax uploads are supported!
                    // Change the support message and enable the upload button
                    var notice = document.getElementById('support-notice');
                    var uploadBtn = document.getElementById('upload-button-id');
                    notice.innerHTML = "Your browser supports HTML uploads to AEM.";
                    uploadBtn.removeAttribute('disabled');
  
                    // Init the Ajax form submission
                    initFullFormAjaxUpload();
  
                    // Init the single-field file upload
                    initFileOnlyAjaxUpload();
                }
  
                function initFullFormAjaxUpload() {
                    var form = document.getElementById('form-id');
                    form.onsubmit = function() {
                        // FormData receives the whole form
                        var formData = new FormData(form);
  
                        // We send the data where the form wanted
                        var action = form.getAttribute('action');
  
                        // Code common to both variants
                        sendXHRequest(formData, action);
  
                        // Avoid normal form submission
                        return false;
                    }
                }
  
                function initFileOnlyAjaxUpload() {
                    var uploadBtn = document.getElementById('upload-button-id');
                    uploadBtn.onclick = function (evt) {
                        var formData = new FormData();
  
                        // Since this is the file only, we send it to a specific location
                        //   var action = '/upload';
  
                        // FormData only has the file
                        var fileInput = document.getElementById('file-id');
                        var file = fileInput.files[0];
                        formData.append('our-file', file);
  
                        // Code common to both variants
                        sendXHRequest(formData);
                    }
                }
  
                // Once the FormData instance is ready and we know
                // where to send the data, the code is the same
                // for both variants of this technique
                function sendXHRequest(formData) {
  
                    var test = 0; 
  
                    $.ajax({
                        type: 'POST',    
                        url:'/bin/excelfile',
                        processData: false,  
                        contentType: false,  
                        data:formData,
                        success: function(msg){
                          alert(msg); //display the data returned by the servlet
                        }
                    });
                      
                }
  
                // Handle the start of the transmission
                function onloadstartHandler(evt) {
                    var div = document.getElementById('upload-status');
                    div.innerHTML = 'Upload started!';
                }
  
                // Handle the end of the transmission
                function onloadHandler(event) {
                    //Refresh the URL for Form Preview
                    var msg = event.target.responseText;
  
                   alert(msg);
                }
  
                // Handle the progress
                function onprogressHandler(evt) {
                    var div = document.getElementById('progress');
                    var percent = evt.loaded/evt.total*100;
                    div.innerHTML = 'Progress: ' + percent + '%';
                }
  
                // Handle the response from the server
                function onreadystatechangeHandler(evt) {
                    var status = null;
  
                    try {
                        status = evt.target.status;
                    }
                    catch(e) {
                        return;
                    }
  
                    if (status == '200' && evt.target.responseText) {
                        var result = document.getElementById('result');
                        result.innerHTML = '<p>The server saw it as:</p><pre>' + evt.target.responseText + '</pre>';
                    }
                }
            </script>
  
            <!-- Placeholders for messages set by event handlers -->
            <p id="upload-status"></p>
            <p id="progress"></p>
            <pre id="result"></pre>
  
        </form>
  
</div>
  
  
</body>
</html>

Note:

In this example, notice the use of cq:jquery for the clientlibs. By using the default JQuery clientlib, it works with AEM 6.x Cross-Site Scripting (XSS). 

Modify the templateExcel JSP file 

  1. Go to CRXDE Lite at http://localhost:4502/crx/de/index.jsp#.

  2. Double-click /apps/excelpersist/components/page/templateExcel/templateExcel.jsp.

  3.  Replace the JSP code with the new code shown in this section.

  4. Click Save All.

Create an AEM web page that lets users upload an Excel files to AEM

The final task is to create a site that contains a page that is based on the templateExcel (the template created earlier in this development article).

ClientWebApp
AEM Client Web Page

After you click the Upload button, the data in the Excel file is persisted in the AEM JCR at: /content/customerexcel. A confirmation message is displayed in the web page, as shown in this illustration. 

Message
A confirmation message

Create a web page that lets users upload excel files:

  1. Go to the Websites page at http://localhost:4502/siteadmin#/content.

  2. Select New Page.

  3. Specify the title of the page in the Title field.

  4. Specify the name of the page in the Name field.

  5. Select templateExcel from the template list that appears. This value represents the template that is created in this development article. If you do not see it, then repeat the steps in this development article. For example, if you made a typing mistake when entering in path information, the template will not show up in the New Page dialog box.

  6. Open the new page that you created by double-clicking it in the right pane. The new page opens in a web browser.  

See also

Congratulations, you have just created an AEM OSGi bundle by using an Adobe Maven Archetype project. Please refer to the AEM community page for other articles that discuss how to build AEM services/applications by using an Adobe Maven Archetype project.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy