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
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);
}
|
Posts
