Wednesday, April 8, 2015

Data Export as CSV Format in Liferay

  
In general we will export data into different formats in our applications and one of format is CSV (Comma-separated values). CSV is similar to Micro Soft XLS.

In Liferay Portlet Development we may get requirement to export data as different formats. This article is explaining how to export data as CSV in Liferay Portlet Development.

Assume in Liferay we have many users in portal and we will export all users details as CSV formatted file.

Steps:
  • We get all users from Portal using UserLocalServiceUtil
  • We prepare each record and its columns as CSV format like each record as new line and each column separated by comma delimiter.
  • Finally we will export this data as CSV file.

The following is sample code


public static String[] columnNames = { "UserId", "FirstName", "LastName","EmailAddress", "Screen Name" };
public static final String CSV_SEPARATOR = ",";

protected void exportCSVData(ResourceRequest resourceRequest,
ResourceResponse resourceResponse) throws Exception {
StringBundler sb = new StringBundler();
for (String columnName : columnNames) {
sb.append(getCSVFormattedValue(columnName));
sb.append(CSV_SEPARATOR);
}
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
List<User> usersList = UserLocalServiceUtil.getUsers(0,
UserLocalServiceUtil.getUsersCount());
for (User user : usersList) {
sb.append(
getCSVFormattedValue(String.valueOf(user.getUserId())));
sb.append(CSV_SEPARATOR);
sb.append(
getCSVFormattedValue(String.valueOf(user.getFirstName())));
sb.append(CSV_SEPARATOR);
sb.append(
getCSVFormattedValue(String.valueOf(user.getLastName())));
sb.append(CSV_SEPARATOR);
sb.append(getCSVFormattedValue(String.valueOf(user
.getEmailAddress())));
sb.append(CSV_SEPARATOR);
sb.append(
getCSVFormattedValue(String.valueOf(user.getScreenName())));
sb.append(CSV_SEPARATOR);
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
}

String fileName = "portalUsers.csv";
byte[] bytes = sb.toString().getBytes();
String contentType = ContentTypes.APPLICATION_TEXT;
PortletResponseUtil.sendFile(resourceRequest, resourceResponse,
fileName, bytes, contentType);
}

protected String getCSVFormattedValue(String value) {
StringBundler sb = new StringBundler(3);
sb.append(CharPool.QUOTE);
sb.append(StringUtil.replace(value, CharPool.QUOTE,
StringPool.DOUBLE_QUOTE));
sb.append(CharPool.QUOTE);
return sb.toString();
}

While export data as files we will use following Method so that it will export as desired file format


String contentType = ContentTypes.APPLICATION_TEXT;
PortletResponseUtil.sendFile(resourceRequest, resourceResponse,
fileName, bytes, contentType);

In the above code we retrieved the data from database and perepared as CSV.
Assume we have HTML table in the page now we will export this HTML tables as CSV file.

Steps:
  • We take all HTML table data and send it to Portlet action class
  • We use jsoup Java HTML Parser to manipulate and access data from HTML table.
  • We prepare each row and its columns as CSV format like each row as new line and each cell separated by comma delimiter.
  • Finally we will export this data as CSV file.

Example Code


protected void exportHTMLCSVData(ResourceRequest resourceRequest,
ResourceResponse resourceResponse) throws Exception {
String tableHTML = ParamUtil.getString(resourceRequest,"tableHTMLDataInput");
StringBundler sb = new StringBundler();
Document doc = Jsoup.parseBodyFragment(tableHTML);
Elements cells = doc.getElementsByTag("th");
for (Element cell : cells) {
sb.append(getCSVFormattedValue(String.valueOf(cell.text())));
sb.append(CSV_SEPARATOR);
}
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
Elements rows = doc.getElementsByTag("tr");
for (Element row : rows) {
Elements tdcells = row.getElementsByTag("td");
for (Element cell : tdcells) {
sb.append(getCSVFormattedValue(String.valueOf(cell.text())));
sb.append(CSV_SEPARATOR);
}
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
}
String fileName = "portalUsers.csv";
byte[] bytes = sb.toString().getBytes();
String contentType = ContentTypes.APPLICATION_TEXT;
PortletResponseUtil.sendFile(resourceRequest, resourceResponse,
fileName, bytes, contentType);
}
protected String getCSVFormattedValue(String value) {
StringBundler sb = new StringBundler(3);
sb.append(CharPool.QUOTE);
sb.append(StringUtil.replace(value, CharPool.QUOTE,
StringPool.DOUBLE_QUOTE));
sb.append(CharPool.QUOTE);
return sb.toString();
}

Note:

When we work with jsoup Java HTML Parser then we need to add jsoup-1.8.1.jar in plugin Portlet lib directory.

Download LiferayCSVDataExport-portlet

https://sourceforge.net/projects/meeralferay/files/LiferayCSVDataExport/

Portlet View Page



Data Exported as CSV file


Complete Portlet Example

View JSP Page (/html/liferaycsvdataexport/view.jsp)


<%@page import="com.liferay.portal.kernel.util.ParamUtil"%>
<%@page import="com.liferay.portal.kernel.util.Constants"%>
<%@page import="com.liferay.portal.service.UserLocalServiceUtil"%>
<%@page import="com.liferay.portal.model.User"%>
<%@page import="java.util.List"%>
<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>
<%@ taglib uri="http://liferay.com/tld/portlet" prefix="liferay-portlet" %>
<%@ taglib uri="http://liferay.com/tld/aui" prefix="aui" %>
<portlet:defineObjects />
<h2>Data Export as CSV in Liferay Portlet</h2>
<%
List<User> usersList=UserLocalServiceUtil.getUsers(0,
UserLocalServiceUtil.getUsersCount());
String portletResource = ParamUtil.getString(request, "portletResource");
%>
<portlet:resourceURL  var="exportCSVURL">
<portlet:param name="<%= Constants.CMD %>" value="exportCSV"/>
</portlet:resourceURL>
<portlet:resourceURL  var="exportHTMLCSVURL">
<portlet:param name="<%= Constants.CMD %>" value="exportHTMLCSV"/>
</portlet:resourceURL>
<style>
#exportlinks th{
padding: 5px 20px 5px 20px;
}

</style>
<table border="0" id="exportlinks">
<tr>
<th><a href="<%=exportCSVURL%>">Export Data as CSV</a></th>
<th><a id="<portlet:namespace/>exportHTMLCSV" href="#">Export HTML Table as CSV</a></th>
</tr>
</table>
<br/>
<div id="<portlet:namespace/>usersData">
<table border="1" >
<tr>
<th>UserID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email Address</th>
<th>Screen Name</th>
</tr>
<%for(User user:usersList) {%>
<tr>
<td><%=user.getUserId()%></td>
<td><%=user.getFirstName() %></td>
<td><%=user.getLastName() %></td>
<td><%=user.getEmailAddress()%></td>
<td><%=user.getScreenName()%></td>
</tr>
<%} %>
</table>
</div>
<form action="<%=exportHTMLCSVURL%>" name="userDataForm"  method="POST">
<input  type="text" name="<portlet:namespace/>tableHTMLDataInput" id="<portlet:namespace/>tableHTMLDataInput" value="DASFASF"/>
</form>
<aui:script>
AUI().use('aui-base', function(A){
A.one("#<portlet:namespace/>exportHTMLCSV").on('click',function(){
var tableHtml=A.one('#<portlet:namespace/>usersData').getHTML();
A.one('#<portlet:namespace/>tableHTMLDataInput').set('value', tableHtml)
document.userDataForm.submit();
});
});
</aui:script>

Portlet Action Class (LiferayCSVDataExport.java)

package com.meera.liferay.csvexport;

import java.util.List;

import javax.portlet.ResourceRequest;
import javax.portlet.ResourceResponse;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.portlet.PortletResponseUtil;
import com.liferay.portal.kernel.util.CharPool;
import com.liferay.portal.kernel.util.Constants;
import com.liferay.portal.kernel.util.ContentTypes;
import com.liferay.portal.kernel.util.ParamUtil;
import com.liferay.portal.kernel.util.StringBundler;
import com.liferay.portal.kernel.util.StringPool;
import com.liferay.portal.kernel.util.StringUtil;
import com.liferay.portal.kernel.util.WebKeys;
import com.liferay.portal.model.User;
import com.liferay.portal.service.UserLocalServiceUtil;
import com.liferay.portal.theme.ThemeDisplay;
import com.liferay.util.bridges.mvc.MVCPortlet;
public class LiferayCSVDataExport extends MVCPortlet {
public static String[] columnNames = { "UserId", "FirstName", "LastName",
"EmailAddress", "Screen Name" };
public static final String CSV_SEPARATOR = ",";

@Override
public void serveResource(ResourceRequest resourceRequest,
ResourceResponse resourceResponse) {

String cmd = ParamUtil.getString(resourceRequest, Constants.CMD);
//System.out.println("cmd"+cmd);

try {
if (cmd.equals("exportCSV")) {
exportCSVData(resourceRequest, resourceResponse);
}else if(cmd.equals("exportHTMLCSV")){
exportHTMLCSVData(resourceRequest, resourceResponse);
}
} catch (Exception e) {
_log.error(e, e);
}
}
protected void exportHTMLCSVData(ResourceRequest resourceRequest,
ResourceResponse resourceResponse) throws Exception {
String tableHTML = ParamUtil.getString(resourceRequest,"tableHTMLDataInput");
//System.out.println("tableHTMLDataInput"+tableHTML);
StringBundler sb = new StringBundler();
Document doc = Jsoup.parseBodyFragment(tableHTML);
Elements cells = doc.getElementsByTag("th");
// System.out.println("cells"+cells.size());
for (Element cell : cells) {
sb.append(getCSVFormattedValue(String.valueOf(cell.text())));
sb.append(CSV_SEPARATOR);
}
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
Elements rows = doc.getElementsByTag("tr");
for (Element row : rows) {
Elements tdcells = row.getElementsByTag("td");
for (Element cell : tdcells) {
sb.append(getCSVFormattedValue(String.valueOf(cell.text())));
sb.append(CSV_SEPARATOR);
}
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
}
String fileName = "portalUsers.csv";
byte[] bytes = sb.toString().getBytes();
String contentType = ContentTypes.APPLICATION_TEXT;
PortletResponseUtil.sendFile(resourceRequest, resourceResponse,
fileName, bytes, contentType);
}
protected void exportCSVData(ResourceRequest resourceRequest,
ResourceResponse resourceResponse) throws Exception {
StringBundler sb = new StringBundler();
for (String columnName : columnNames) {
sb.append(getCSVFormattedValue(columnName));
sb.append(CSV_SEPARATOR);
}
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
List<User> usersList = UserLocalServiceUtil.getUsers(0,
UserLocalServiceUtil.getUsersCount());
for (User user : usersList) {
sb.append(getCSVFormattedValue(String.valueOf(user.getUserId())));
sb.append(CSV_SEPARATOR);
sb.append(getCSVFormattedValue(String.valueOf(user.getFirstName())));
sb.append(CSV_SEPARATOR);
sb.append(getCSVFormattedValue(String.valueOf(user.getLastName())));
sb.append(CSV_SEPARATOR);
sb.append(getCSVFormattedValue(String.valueOf(user
.getEmailAddress())));
sb.append(CSV_SEPARATOR);
sb.append(getCSVFormattedValue(String.valueOf(user.getScreenName())));
sb.append(CSV_SEPARATOR);
sb.setIndex(sb.index() - 1);
sb.append(CharPool.NEW_LINE);
}

String fileName = "portalUsers.csv";
byte[] bytes = sb.toString().getBytes();
String contentType = ContentTypes.APPLICATION_TEXT;
PortletResponseUtil.sendFile(resourceRequest, resourceResponse,
fileName, bytes, contentType);
}

protected String getCSVFormattedValue(String value) {
StringBundler sb = new StringBundler(3);
sb.append(CharPool.QUOTE);
sb.append(StringUtil.replace(value, CharPool.QUOTE,
StringPool.DOUBLE_QUOTE));
sb.append(CharPool.QUOTE);
return sb.toString();
}

private static Log _log = LogFactoryUtil.getLog(LiferayCSVDataExport.class);
}


0 comments :

Post a Comment

Subscribe to: Post Comments ( Atom )

Recent Posts

Recent Posts Widget

Popular Posts