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.

[leojohnbosco]

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.

[Saravanan_Dharmaraj · Accepted solution]

i have used a library before just to read the Excel file and create the JCR nodes in AEM

Add this dependency
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

Under Maven plugin in pom.xml , dont forget to add export contents
<plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <configuration>
                    <instructions>
                        <Embed-Dependency>*;sco
                            pe=compile|runtime</Embed-Dependency>
                        <Embed-Directory>OSGI-INF/lib</Embed-Directory>
                        <Embed-Transitive>true</Embed-Transitive>
                        <_exportcontents>
                            jxl.*;version=2.6.12,
                        </_exportcontents>
                        <Import-Package>
                            *;resolution:=optional
                        </Import-Package>
                    </instructions>
                </configuration>
            </plugin> 

Code sample

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class JExcelAPIDemo
{
   public static void main(String[] args) 
      throws BiffException, IOException, WriteException
   {
    
    // Create a new File object by pointing to the XL file in with AEM or outside
      Workbook workbook = Workbook.getWorkbook(new File("output.xls"));
      Sheet sheet = workbook.getSheet(0);
      Cell cell1 = sheet.getCell(0, 2);
      System.out.println(cell1.getContents());
      Cell cell2 = sheet.getCell(3, 4);
      System.out.println(cell2.getContents());
      workbook.close();

      //once you got the values use JCR or Node API to create unstructured nodes or pages
      // sample is here https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/how-can-i-create-new-cq-pa... 


   }
}