Tech Mastery: Deep Dives into AEM, Cloud Technologies, AI Innovations, and Advanced Marketing Strate

When uploading and processing a large .xls file through Java in an AEM backend, I encountered the following exception:

Exception in thread "main" org.apache.poi.util.RecordFormatException: 
Tried to allocate an
array of length 159,135,035, but the maximum length for this record type is 100,000,000.
If the file is not corrupt and not large, please open an issue on bugzilla to 
request increasing the maximum allowable size for this record type.
You can set a higher override value with IOUtils.setByteArrayMaxOverride()

Stack Trace:

at org.apache.poi.util.IOUtils.throwRFE(IOUtils.java:596)
at org.apache.poi.util.IOUtils.checkLength(IOUtils.java:281)
...
at ExcelReader.readSpreadsheet(ExcelReader.java:17)
at ExcelReader.main(ExcelReader.java:52)

Explanation of the Issue

This exception occurs because Apache POI tries to allocate a large byte array to process the Excel file, exceeding the default size limit of 100,000,000 bytes. The issue is often due to the size or structure of the .xls file, which requires a larger array than Apache POI allows by default.

Solutions

There are two primary ways to resolve this issue:

---

Option 1: Increase the Byte Array Limit

You can override the maximum allowable byte array size using IOUtils.setByteArrayMaxOverride(). Setting this to a higher value, such as 200,000,000, may help process larger Excel files. However, this approach can impact memory usage and may lead to performance issues with very large files. Before increasing the size, make sure the issue isn’t due to a corrupted or incorrect file format.

Updated Code Snippet:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.util.IOUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

public class ExcelReader {
    public void readSpreadsheet(String filePath) {
        Workbook workBook = null;
        try (FileInputStream fis = new FileInputStream(new File(filePath))) {
            // Increase the byte array limit for larger files
            IOUtils.setByteArrayMaxOverride(200_000_000);

            // Create Workbook instance
            workBook = WorkbookFactory.create(fis);
            Sheet sheet = workBook.getSheetAt(0);
            Iterator<Row> rowIter = sheet.rowIterator();
            boolean firstRow = true;

            while (rowIter.hasNext()) {
                Row row = rowIter.next();
                if (!firstRow) {
                    Iterator<Cell> cellIter = row.cellIterator();
                    while (cellIter.hasNext()) {
                        Cell cell = cellIter.next();
                        System.out.print(cell + "\t"); // Print cell content
                    }
                    System.out.println(); // Newline for each row
                } else {
                    firstRow = false;
                }
            }
        } catch (IOException e) {
            System.err.println("IO Exception: " + e.getMessage());
        } finally {
            if (workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    System.err.println("Unable to close Workbook object: "
                     + e.getMessage());
                }
            }
        }
    }

    public static void main(String[] args) {
        String filePath = "Test.xlsx";
        ExcelReader reader = new ExcelReader();
        reader.readSpreadsheet(filePath);
    }
}

Command to Compile and Run:

javac -cp ".;poi-5.3.0.jar;poi-ooxml-5.3.0.jar;poi-ooxml-full-5.3.0.jar;poi-ooxml-schemas-5.3.0.jar;xmlbeans-5.1.1.jar;commons-collections4-4.5.0-M2.jar;commons-io-2.17.0.jar;log4j-api-2.24.1.jar;commons-compress-1.27.1.jar;log4j-core-2.24.1.jar" ExcelReader.java

java -cp ".;poi-5.3.0.jar;poi-ooxml-5.3.0.jar;poi-ooxml-full-5.3.0.jar;poi-ooxml-schemas-5.3.0.jar;xmlbeans-5.1.1.jar;commons-collections4-4.5.0-M2.jar;commons-io-2.17.0.jar;log4j-api-2.24.1.jar;commons-compress-1.27.1.jar;log4j-core-2.24.1.jar" ExcelReader

Option 2: Use the Streaming API (SXSSFWorkbook)

Using SXSSFWorkbook, a streaming API in Apache POI, allows you to process large .xlsx files in a memory-efficient way. This approach loads only a small part of the file into memory at any time, making it suitable for processing large Excel files.

Code Using SXSSFWorkbook:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

public class ExcelReaderStream {

    public void readSpreadsheet(String filePath) {
        SXSSFWorkbook streamingWorkbook = null;
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) {

            // Wrap XSSFWorkbook in SXSSFWorkbook to enable streaming
            streamingWorkbook = new SXSSFWorkbook(workbook);
            Sheet sheet = streamingWorkbook.getSheetAt(0);

            // Iterate through rows in a memory-efficient way
            for (Row row : sheet) {
                Iterator<Cell> cellIter = row.cellIterator();
                while (cellIter.hasNext()) {
                    Cell cell = cellIter.next();
                    System.out.print(getCellValue(cell) + "\t");
                }
                System.out.println(); // Newline for each row
            }

        } catch (IOException e) {
            System.err.println("IO Exception: " + e.getMessage());
        } finally {
            if (streamingWorkbook != null) {
                try {
                    streamingWorkbook.dispose(); // Dispose of temporary files
                    streamingWorkbook.close();
                } catch (IOException e) {
                    System.err.println("Unable to close SXSSFWorkbook object: " 
                    + e.getMessage());
                }
            }
        }
    }

    // Utility method to get the value of a cell as a String
    private String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    public static void main(String[] args) {
        String filePath = "Test.xlsx";
        ExcelReaderStream reader = new ExcelReaderStream();
        reader.readSpreadsheet(filePath);
    }
}

Command to Compile and Run:

javac -cp ".;poi-5.3.0.jar;poi-ooxml-5.3.0.jar;poi-ooxml-full-5.3.0.jar;poi-ooxml-schemas-5.3.0.jar;xmlbeans-5.1.1.jar;commons-collections4-4.5.0-M2.jar;commons-io-2.17.0.jar;log4j-api-2.24.1.jar;commons-compress-1.27.1.jar;log4j-core-2.24.1.jar" ExcelReaderStream.java
java -cp ".;poi-5.3.0.jar;poi-ooxml-5.3.0.jar;poi-ooxml-full-5.3.0.jar;poi-ooxml-schemas-5.3.0.jar;xmlbeans-5.1.1.jar;commons-collections4-4.5.0-M2.jar;commons-io-2.17.0.jar;log4j-api-2.24.1.jar;commons-compress-1.27.1.jar;log4j-core-2.24.1.jar" ExcelReaderStream

Conclusion

• Option 1: Increasing the byte array limit using IOUtils.setByteArrayMaxOverride() can solve the immediate issue but may impact memory usage.
• Option 2: Using SXSSFWorkbook with the streaming API is a more scalable solution for large .xlsx files, allowing memory-efficient processing.

Choose the approach that best suits your file size and memory requirements. Let me know if you have any questions!

While integrating Adobe Target with AEM, you can synchronize segments from Adobe Target to AEM. Basic configurations include:

• Enabling the project with the Adobe Target API in the Adobe Developer Console.
• Enabling the required workspaces and ensuring proper access for the API credentials via the Admin Console Product Profile (Workspace).
• Setting up the IMS configuration in AEM.
• Enabling the Adobe Target Cloud Configuration with segment syncing.

(I will be posting a detailed guide on the integration process soon.)

Issue: Segments Not Syncing

Unfortunately, even after completing all the steps, the segments were not syncing, and I encountered the following exception:

Resolution

To resolve this issue, ensure that the Day CQ Analytics Segment Importer configuration is enabled.

Additionally, segments will only sync from the Default Workspace at present. Ensure that segments are created under the Default Workspace. In my case, there were no segments under the Default Workspace, which caused the sync to fail.

You can also adjust the polling interval in the Adobe Target Cloud Configuration.

You can adjust the polling interval on Adobe Target Cloud Configuration

Successful Sync

Once I created segments in the Default Workspace, they were successfully synced to the AEM Audience Library. These segments can now be used for A/B testing or Experience Targeting for AEM pages using Adobe Target as the targeting engine.

11.10.2024 14:23:02.976 *INFO* [sling-cq-polling-importer-3-/conf/wknd/settings/cloudconfigs/target] com.day.cq.analytics.testandtarget.impl.SegmentImporter Importing in /etc/segmentation/adobe-target/tenant with dataSource /conf/wknd/settings/cloudconfigs/target

11.10.2024 14:23:02.978 *DEBUG* [sling-cq-polling-importer-3-/conf/wknd/settings/cloudconfigs/target] com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl Target API Request: GET https://mc.adobe.io/tenant/target/audiences?offset=0&modifiedAt=2024-10-10%2F2024-10-12&limit=2000&type=reusable 

11.10.2024 14:23:03.517 *DEBUG* [sling-cq-polling-importer-3-/conf/wknd/settings/cloudconfigs/target] com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl Target API Request: GET https://mc.adobe.io/tenant/target/audiences?deletedAt=2024-10-10%2F2024-10-12&offset=0&limit=2000&type=reusable&status=deleted 

11.10.2024 14:23:03.985 *INFO* [sling-cq-polling-importer-3-/conf/wknd/settings/cloudconfigs/target] com.day.cq.analytics.testandtarget.impl.SegmentImporter Wrote 4 segments under /etc/segmentation/adobe-target/tenant 

11.10.2024 14:55:59.150 *DEBUG* [[0:0:0:0:0:0:0:1] [1728676559144] POST /conf/wknd/settings/cloudconfigs/target/jcr:content.audienceupdate.json HTTP/1.1] com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl Target API Request: POST https://mc.adobe.io/tenant/target/audiences?includeMarketingCloudMetadata=true {"name":"Windows","description":"--","targetRule":{"script":"return (((user.browser!=null) && (user.browser.indexOf(\"Windows\")>-1)));"},"type":"reusable","marketingCloudMetadata":{"sourceProductName":"Adobe Experience Manager","editURL":"http://localhost:4502/libs/cq/personalization/touch-ui/content/audiences.html"}}

The Start Targeting button is disabled when switching to Targeting Mode for pages in AEM.

The issue is that a Brand has not been created. To resolve this, simply create a Brand.

Navigate to Personalization --> Activities

Create a Brand

Now that the Brand is reflected in the Targeting Mode of the page, the Start Targeting button is still disabled. To enable it, create an Activity now.

Select the Targeting Mode: ContextHub or Adobe Target (Adobe Target license required). The Adobe Target option also allows you to enable A/B testing.

Now that the Start Targeting button is enabled, you can begin targeting the content for different experiences and configure other activity settings.

We encountered the following error while trying to export Experience Fragments from AEM to the Adobe Target Offer Library, despite completing all the integration steps:

Error:
Error Exporting to Adobe Target: Create offer request failed! API request failed.

09.10.2024 15:18:17.031 *WARN* [[0:0:0:0:0:0:0:1] [1728505097029] POST /libs/cq/experience-fragments/content/commons/targetexporter.html HTTP/1.1] org.apache.sling.engine.impl.request.RequestData RequestProgressTracker not found in request attributes

09.10.2024 15:18:17.035 *INFO* [[0:0:0:0:0:0:0:1] [1728505097029] POST /libs/cq/experience-fragments/content/commons/targetexporter.html HTTP/1.1] com.day.cq.wcm.core.impl.designer.SearchPathLimiter Search path limiter configured with searchPathLimiterFeatureToggleOn: true and searchPathThreshold: true. 

09.10.2024 15:18:17.121 *INFO* [[0:0:0:0:0:0:0:1] [1728505097029] POST /libs/cq/experience-fragments/content/commons/targetexporter.html HTTP/1.1] com.day.cq.analytics.testandtarget.impl.serializer.SaveOfferRequestRestAdapterFactory The Adobe Target offer does not contain JSON.

com.google.gson.JsonSyntaxException: com.google.gson.stream.MalformedJsonException: Use JsonReader.setLenient(true) to accept malformed JSON at line 1 column 12 path $

at com.google.gson.JsonParser.parseReader(JsonParser.java:66) [com.google.gson:2.8.9]

at com.google.gson.JsonParser.parseString(JsonParser.java:47) [com.google.gson:2.8.9]

at com.google.gson.JsonParser.parse(JsonParser.java:98) [com.google.gson:2.8.9]

09.10.2024 15:18:17.766 *ERROR* [[0:0:0:0:0:0:0:1] [1728505097029] POST /libs/cq/experience-fragments/content/commons/targetexporter.html HTTP/1.1] com.adobe.cq.xf.impl.adobetarget.exporter.AbstractExperienceFragmentExporter Create offer request failed!__API request failed

com.day.cq.analytics.testandtarget.TestandtargetException: Create offer request failed!

at com.day.cq.analytics.testandtarget.impl.RestApiAdapter.create(RestApiAdapter.java:122) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.RestApiAdapter.createOffer(RestApiAdapter.java:101) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.TestandtargetServiceImpl.createHTMLOffer(TestandtargetServiceImpl.java:65) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.TestandtargetCampaignMediatorImpl.syncOffer(TestandtargetCampaignMediatorImpl.java:539) 

Caused by: com.day.cq.analytics.testandtarget.impl.service.WebServiceException: API request failed

at com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl.request(WebServiceImpl.java:646) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl.request(WebServiceImpl.java:600) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl.createOffer(WebServiceImpl.java:268) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.RestApiAdapter.create(RestApiAdapter.java:116) [com.adobe.cq.cq-target-integration:1.4.56]

... 193 common frames omitted

Caused by: com.day.cq.analytics.testandtarget.impl.service.WebServiceException: Unexpected response status code [403] for request [https://mc.adobe.io/tenant/target/offers/content?includeMarketingCloudMetadata=true]._{"httpStatus":403,"requestId":"sfdsffgsffsf","requestTime":"2024-10-09T20:18:17.696894302Z","errors":[{"errorCode":"Forbidden.Resource","message":"Access denied. To perform this operation, all of the following privileges are required \"[editor]\".","meta":{}}]}

The actual issue is that the API credentials enabled through the Developer Console project are set to Observer access by default for the selected workspace. However, to successfully export the offer into Adobe Target, a minimum of Editor or Approver access is required.

The issue can be resolved by changing the product role to Editor or Approver through the Admin Console for the corresponding Adobe Target Product Profile (Workspace).

Once the product role is updated to Editor or Approver in the Admin Console for the corresponding Adobe Target Product Profile (Workspace), the export will be successful.

You may encounter the following exception while enabling the integration between Adobe Target and AEM to share Experience Fragments (XF) and enable Targeting from AEM.

This error can occur even after completing all the steps, including creating a project in the Adobe Developer Console, adding the Adobe Target API with the required profiles, and establishing a successful connection in AEM IMS.

However, when testing the Adobe Target Cloud Services integration, the following exception is thrown:

08.10.2024 12:25:18.280 *ERROR* [[0:0:0:0:0:0:0:1] [1728408318089] POST /libs/cq/analytics/testandtarget/command HTTP/1.1] com.day.cq.analytics.testandtarget.impl.servlets.commands.ConnectCommand Get mboxes request failed!

com.day.cq.analytics.testandtarget.TestandtargetException: Get mboxes request failed!

at com.day.cq.analytics.testandtarget.impl.RestApiAdapter.listMboxes(RestApiAdapter.java:611) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.RestApiAdapter.checkCredentials(RestApiAdapter.java:88) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.TestandtargetServiceImpl.checkCredentials(TestandtargetServiceImpl.java:180) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.servlets.commands.ConnectCommand.performCommand(ConnectCommand.java:83) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.servlets.CommandServlet.performCommand(CommandServlet.java:69) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.commons.servlets.AbstractCommandServlet.doPost(AbstractCommandServlet.java:49) [com.day.cq.cq-commons:5.12.24]

at org.apache.sling.api.servlets.SlingAllMethodsServlet.mayService(SlingAllMethodsServlet.java:146) [org.apache.sling.api:2.27.2]

at org.apache.sling.api.servlets.SlingSafeMethodsServlet.service(SlingSafeMethodsServlet.java:342) [org.apache.sling.api:2.27.2]

at org.apache.sling.api.servlets.SlingSafeMethodsServlet.service(SlingSafeMethodsServlet.java:374) [org.apache.sling.api:2.27.2]

at org.apache.sling.engine.impl.request.RequestData.service(RequestData.java:551) [org.apache.sling.engine:2.15.14]

Caused by: com.day.cq.analytics.testandtarget.impl.service.WebServiceException: API request failed

at com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl.request(WebServiceImpl.java:646) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl.getMboxes(WebServiceImpl.java:413) [com.adobe.cq.cq-target-integration:1.4.56]

at com.day.cq.analytics.testandtarget.impl.RestApiAdapter.listMboxes(RestApiAdapter.java:600) [com.adobe.cq.cq-target-integration:1.4.56]

... 189 common frames omitted

Caused by: com.day.cq.analytics.testandtarget.impl.service.WebServiceException: Unexpected response status code [403] for request [https://mc.adobe.io/xxxxx/target/mboxes]._{"error_code":"502061","message":"Bad Gateway. No tenant associated with the user profile."}_

at com.day.cq.analytics.testandtarget.impl.service.WebServiceImpl.request(WebServiceImpl.java:643) [com.adobe.cq.cq-target-integration:1.4.56]

... 191 common frames omitted

The issue stems from the Adobe Target Tenant ID not being configured properly. (I will be creating a detailed blog on how to enable the required configurations for integrating AEM with Adobe Target.) It's important to note that the Adobe Target Client Code and Adobe Target Tenant ID are different. The Adobe Target Client Code can be retrieved from the Administration → Implementation section within Adobe Target.