Reading EXCEL File from Adaptive Form

[Eshwarign]

We are trying to reading an Excel file with one column data from an Adaptive Form into an backend AEM servlet. But we are facing server posts issues.

Has anyone has suggestions on this process.

Thank you

Eshwari

[Pulkit_Jain_]

@Eshwarign 

Are you facing issues reading from the excel file or posting the data in the backend servlet? If you could elaborate on the use case.

Could you share the error msg that you're seeing?

[Eshwarign]

We are facing issue with posting data in the backend servlet

This is the error msg when trying to get inputstream out of request object.

"The request doesn't contain a multipart/form-data or multipart/mixed stream, content type header is null"

[Mayank_Gandhi]

@Eshwarign AEM forms submit data in multi-part make sure you add.

'content-type: multipart/form-data

[Eshwarign]

Where should this be added in the servlet?

[Eshwarign]

Also we have used the File Attachment Component. Should we write a javascript when the attach button is invoked.

[Mayank_Gandhi]

@Eshwarign 

Header is added to the Post request

"Also we have used the File Attachment Component. Should we write a javascript when the attach button is invoked. "This doesn't give any context of what you are trying to achieve. Please share details.

[Eshwarign]

The requirement is to read a list of account numbers present in an Excel Sheet and  generate letters for those customer numbers.

We are facing issues in reading the Excel sheet.  We created an Adaptive Form with File Attachment Component and a Submit button. On Submit, it invokes an OSGI servlet to read the contents of the Excel sheet uploaded through the File Attachment Component. 

This is the place where we are facing the issues, as when the submit button is clicked, we are getting the following message.

"The request doesn't contain a multipart/form-data or multipart/mixed stream, content type header is null"

Not sure where to add the 'Content-type: multipart-form-data' or the syntax of it, in the submit button.

We have enabled the POST Request checkbox in the Adaptive form and it points to the post method in OSGI servlet.

Kindly let us know if the approach is correct or is there any other way to accomplish this.

Thank you

Eshwari

[workflowuser]

So you want to read the excel file on form submission in the servlet?

I will send you sample soon

[Eshwarign]

Yes. Kindly send us the sample.

[workflowuser]

This is the sample code to read the excel attachment on an Adaptive Form  submission

I used the following library to parse the excel file. You can parse the excel file any number of ways

https://mvnrepository.com/artifact/org.apache.commons/commons-csv/1.8

A custom submit action was created and the jsp page in the custom submit had the followng line

<%@include file="/libs/foundation/global.jsp"%>
<%@taglib prefix="cq" uri="http://www.day.com/taglibs/cq/1.0"%>
<%@ page import="java.util.*,org.apache.sling.api.request.RequestParameter,com.day.cq.wcm.api.WCMMode,org.apache.sling.api.request.*" %>

<%@page session="false" %><%
%><%

com.adobe.aemds.guide.utils.GuideSubmitUtils.setForwardPath(slingRequest,"/bin/parseExcel",null,null);

%>

Java servlet code to parse the excel attachment

package com.embedpdf.core.servlets;

import java.io.IOException;
import java.nio.charset.StandardCharsets;

import javax.servlet.Servlet;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.framework.Constants;
import org.osgi.service.component.annotations.Component;
@component(service={Servlet.class}, property={Constants.SERVICE_DESCRIPTION+"=Servlet to parse excel file","sling.servlet.methods=post", "sling.servlet.paths=/bin/parseExcel"})

public class ReadExcelAttachment extends SlingAllMethodsServlet {
/**
*
*/
private static final long serialVersionUID = 1L;

protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) throws IOException
{
CSVFormat csvFormat = CSVFormat.DEFAULT.withFirstRecordAsHeader().withIgnoreHeaderCase();

final boolean isMultipart = org.apache.commons.fileupload.servlet.ServletFileUpload.isMultipartContent(request);
if (isMultipart)
{
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 org.apache.sling.api.request.RequestParameter[] pArr = pairs.getValue();
final org.apache.sling.api.request.RequestParameter param = pArr[0];

try {
if (!param.isFormField()) {
System.out.println("Got attachment");
CSVParser csvParser = CSVParser.parse( param.getInputStream(),StandardCharsets.UTF_8, csvFormat);
for(CSVRecord csvRecord : csvParser) {
String firstName = csvRecord.get("First Name");
String lastName = csvRecord.get("Last Name");
String email = csvRecord.get("Email");
String phoneNumber = csvRecord.get("Phone Number");

System.out.println(firstName + "," + lastName + "," + email + "," + phoneNumber);
}
csvParser.close();

}
}
catch(Exception e)
{
System.out.println("Got Error "+e.getMessage());
}
}
}
}
}

The following is the output in the log file

Got attachment
James,Bond,bond@james.com,12308

Hope this helps!!!

[workflowuser]

can you please tell us more about the use case ?