Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

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.

Avatar

Level 1

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.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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... 


   }
}

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

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... 


   }
}