Liferay is easy: Read Excel file (xlsx) using Apache POI in Liferay 7/DXP

By: Aditya on 12:23 AM

Share it Please

Today we will see how we can read excel file(.xlsx) using Apache POI .The main problem in this is POI dependencies .POI create problem with OSGI .So we add dependencies in gradle and include resources in bnd.Here i am using Liferay 7.3.

So lets start this step by step :-

Step 1:- Create a simple MVC Portlet

Create a simple Liferay module Project and create a simple form in jsp

view.jsp

	<%@ include file="/init.jsp" %>

	<portlet:actionURL var="uploadFileURL" name="uploadFile"/>

	<aui:form action="<%=uploadFileURL%>" method="post" enctype="multipart/form-data">

	<aui:input name="uploadFile" type="file">
	<aui:validator name="acceptFiles">
	'xlsx'
	</aui:validator>
	</aui:input>

	<aui:button type="submit" value="Submit"/>

	</aui:form>

view raw view.jsp hosted with ❤ by GitHub

Step 2:- Create Action Method in Portlet

Create a method uploadFile in your controller . So now your controller become :-

ReadExcelPortlet.java

	package com.aditya.portlet;

	import com.aditya.constants.ReadExcelPortletKeys;

	import com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet;
	import com.liferay.portal.kernel.upload.UploadPortletRequest;
	import com.liferay.portal.kernel.util.PortalUtil;

	import java.io.File;
	import java.io.FileInputStream;
	import java.io.IOException;
	import java.io.InputStream;
	import java.util.Iterator;

	import javax.portlet.ActionRequest;
	import javax.portlet.ActionResponse;
	import javax.portlet.Portlet;
	import javax.portlet.PortletException;

	import org.apache.poi.ss.usermodel.Row;
	import org.apache.poi.ss.usermodel.Sheet;
	import org.apache.poi.ss.usermodel.Workbook;
	import org.apache.poi.ss.usermodel.WorkbookFactory;
	import org.osgi.service.component.annotations.Component;

	/**
	* @author adit2
	*/
	@Component(
	immediate = true,
	property = {
	"com.liferay.portlet.display-category=category.Liferay Is Easy",
	"com.liferay.portlet.header-portlet-css=/css/main.css",
	"com.liferay.portlet.instanceable=true",
	"javax.portlet.display-name=ReadExcel",
	"javax.portlet.init-param.template-path=/",
	"javax.portlet.init-param.view-template=/view.jsp",
	"javax.portlet.name=" + ReadExcelPortletKeys.READEXCEL,
	"javax.portlet.resource-bundle=content.Language",
	"javax.portlet.security-role-ref=power-user,user"
	},
	service = Portlet.class
	)
	public class ReadExcelPortlet extends MVCPortlet {

	public void uploadFile(ActionRequest actionRequest, ActionResponse actionResponse) throws IOException, PortletException {
	UploadPortletRequest uploadPortletRequest = PortalUtil.getUploadPortletRequest(actionRequest);
	String fileName = uploadPortletRequest.getFileName("uploadFile");
	File file = uploadPortletRequest.getFile("uploadFile");

	InputStream inputStream = new FileInputStream(file);
	Workbook workbook = WorkbookFactory.create(inputStream);
	Sheet sheet = workbook.getSheetAt(0);
	Iterator<Row> rowIterator = sheet.rowIterator();
	while (rowIterator.hasNext()) {
	Row row = rowIterator.next();
	//For Header
	if(row.getRowNum()==0) {
	String firstHeader = row.getCell(0).getStringCellValue();
	String secondHeader = row.getCell(1).getStringCellValue();
	String thirdHeader = row.getCell(2).getStringCellValue();
	String fourHeader = row.getCell(3).getStringCellValue();
	String fiveHeader = row.getCell(4).getStringCellValue();

	System.out.println(firstHeader+" "+secondHeader+" "+thirdHeader+" "+fourHeader+" "+fiveHeader);

	}
	//For rows
	else {
	double eid = row.getCell(0).getNumericCellValue();
	String firstName = row.getCell(1).getStringCellValue();
	String lastName = row.getCell(2).getStringCellValue();
	String gender = row.getCell(3).getStringCellValue();
	double age = row.getCell(4).getNumericCellValue();

	System.out.println(eid+" "+firstName+" "+lastName+" "+gender+" "+age);
	}
	}
	}
	}

view raw ReadExcelPortlet.java hosted with ❤ by GitHub

Explanation:-

Here we are using apache poi classes so we need to add dependencies for the same.

Step 3:- Add Dependencies

Open build.gradle and paste this:-

build.gradle

	dependencies {
	compileOnly group: "com.liferay.portal", name: "release.dxp.api"

	cssBuilder group: "com.liferay", name: "com.liferay.css.builder", version: "3.0.2"
	//
	compileOnly group: "com.liferay",name:"com.liferay.application.list.api"
	compileOnly group: "org.apache.poi", name: "poi"
	compileOnly group: "org.apache.poi", name: "poi-ooxml"
	compileOnly group: "org.apache.poi", name: "poi-ooxml-schemas"
	compileOnly group: "org.apache.xmlbeans", name: "xmlbeans"
	compileOnly group: "org.apache.commons", name: "commons-collections4"
	compileOnly group: "org.apache.commons", name: "commons-lang3"
	compileOnly group: "org.apache.commons", name: "commons-compress"

	}

view raw build.gradle hosted with ❤ by GitHub

Now open bnd.bnd and paste this:-

bnd.bnd

	Bundle-Name: read-excel
	Bundle-SymbolicName: com.aditya
	Bundle-Version: 1.0.0
	Export-Package: com.aditya.constants

	Include-Resource:\
	@poi-4.1.2.jar,\
	@poi-ooxml-4.1.2.jar,\
	@poi-ooxml-schemas-4.1.2.jar,\
	@commons-collections4-4.4.jar,\
	@commons-compress-1.19.jar,\
	@commons-lang3-3.9.jar,\
	@xmlbeans-3.1.0.jar

	Import-Package: \
	!com.sun.*,\
	!junit*,\
	!org.apache.avalon.framework.logger,\
	!org.apache.crimson.jaxp,\
	!org.apache.jcp.xml.dsig.internal.dom,\
	!org.apache.log,\
	!org.apache.xml.resolver*,\
	!org.bouncycastle.*,\
	!org.gjt.xpp,\
	!org.junit*,\
	!org.relaxng.datatype,\
	!org.xmlpull.v1,\
	!com.microsoft.schemas.office.*,\
	!com.zaxxer.sparsebits,\
	!org.apache.batik.*,\
	!com.graphbuilder*,\
	!org.etsi.uri.x01903.v14,\
	!org.openxmlformats.schemas.officeDocument.x2006.*,\
	!org.openxmlformats.schemas.schemaLibrary.x2006.main,\
	!net.sf.saxon.*,\
	!org.apache.commons.codec.binary,\
	!org.apache.commons.codec.digest,\
	!org.apache.commons.math3.*,\
	!com.github.luben.*,\
	!org.brotli.*,\
	!org.tukaani.xz,\
	*