Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
excel sheet File Download in Liferay 6.x ramveer singh narwariya October 24, 2012 2:10 AM
RE: excel sheet File Download in Liferay 6.x Pankaj Kathiriya October 24, 2012 7:03 AM
RE: excel sheet File Download in Liferay 6.x Jitendra Rajput October 25, 2012 1:32 AM
RE: excel sheet File Download in Liferay 6.x Hitoshi Ozawa October 25, 2012 5:59 AM
RE: excel sheet File Download in Liferay 6.x ramveer singh narwariya October 25, 2012 10:33 PM
RE: excel sheet File Download in Liferay 6.x Jitendra Rajput October 25, 2012 11:04 PM
RE: excel sheet File Download in Liferay 6.x ramveer singh narwariya October 26, 2012 2:43 AM
RE: excel sheet File Download in Liferay 6.x Vishal Panchal October 28, 2012 10:30 PM
RE: excel sheet File Download in Liferay 6.x ramveer singh narwariya October 29, 2012 11:08 PM
RE: excel sheet File Download in Liferay 6.x Vishal Panchal October 29, 2012 11:42 PM
RE: excel sheet File Download in Liferay 6.x ramveer singh narwariya November 1, 2012 12:22 AM
RE: excel sheet File Download in Liferay 6.x Mittal Patoliya August 2, 2013 2:26 AM
RE: excel sheet File Download in Liferay 6.x Vishal Panchal August 2, 2013 10:44 PM
RE: excel sheet File Download in Liferay 6.x Mittal Patoliya August 7, 2013 1:00 AM
RE: excel sheet File Download in Liferay 6.x adel b January 3, 2015 6:08 AM
RE: excel sheet File Download in Liferay 6.x ismail zabiulla January 10, 2015 8:55 AM
RE: excel sheet File Download in Liferay 6.x adel b January 11, 2015 2:24 AM
RE: excel sheet File Download in Liferay 6.x Manushi Jani April 21, 2015 12:30 AM
RE: excel sheet File Download in Liferay 6.x Chandan Sharma April 21, 2015 12:54 AM
RE: excel sheet File Download in Liferay 6.x Manushi Jani May 4, 2015 3:48 AM
RE: excel sheet File Download in Liferay 6.x Chandan Sharma May 8, 2015 12:00 AM
RE: excel sheet File Download in Liferay 6.x Gaurav Jain October 30, 2012 12:23 AM
RE: excel sheet File Download in Liferay 6.x Raja Seth April 21, 2015 5:20 AM
ramveer singh narwariya
excel sheet File Download in Liferay 6.x
October 24, 2012 2:10 AM
Answer

ramveer singh narwariya

Rank: Junior Member

Posts: 64

Join Date: April 13, 2012

Recent Posts

We have a requirement that we need to export data of database table into excel sheet file and allow the client to download the excel sheet file.
We want it to display the a dialog box which asks the user to save or download.

Thanks in advance.
Pankaj Kathiriya
RE: excel sheet File Download in Liferay 6.x
October 24, 2012 7:03 AM
Answer

Pankaj Kathiriya

Rank: Liferay Master

Posts: 697

Join Date: August 4, 2010

Recent Posts

HI Ramveer,

You can utilize report portlet of Lifeary. You can get it installed from Plugin Installation.

Thanks,
Pankaj
Jitendra Rajput
RE: excel sheet File Download in Liferay 6.x
October 25, 2012 1:32 AM
Answer

Jitendra Rajput

Rank: Liferay Master

Posts: 858

Join Date: January 7, 2011

Recent Posts

Try by using jasper report.
Hitoshi Ozawa
RE: excel sheet File Download in Liferay 6.x
October 25, 2012 5:59 AM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7949

Join Date: March 23, 2010

Recent Posts

Just use something like Apache POI
http://poi.apache.org/
ramveer singh narwariya
RE: excel sheet File Download in Liferay 6.x
October 25, 2012 10:33 PM
Answer

ramveer singh narwariya

Rank: Junior Member

Posts: 64

Join Date: April 13, 2012

Recent Posts

thank you for reply
can any one send me code
Jitendra Rajput
RE: excel sheet File Download in Liferay 6.x
October 25, 2012 11:04 PM
Answer

Jitendra Rajput

Rank: Liferay Master

Posts: 858

Join Date: January 7, 2011

Recent Posts

There are so many example on internet do some googling . Any ways below is example code hope you will get some idea based on that .

 1
 2
 3    public static void reportForEmployeeData(ResourceRequest request, ResourceResponse response,
 4            List<EmployeeData> result, String path)
 5    {
 6        try
 7        {
 8            JRBeanCollectionDataSource resultList = new JRBeanCollectionDataSource(result);
 9            JasperReport report = JasperCompileManager.compileReport(path);
10            JasperPrint print = JasperFillManager.fillReport(report, new HashMap<String, Object>(), resultList);
11            OutputStream opStream = response.getPortletOutputStream();
12
13            JRXlsExporter exporterXLS = new JRXlsExporter();
14            exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, print);
15            exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, opStream);
16            exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
17            exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
18            exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
19            exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
20            exporterXLS.exportReport();
21            // Prompt user to save the excel file
22            response.setContentType("application/x-excel");
23            // response.setProperty("Content-Disposition",
24            // "attachment; filename= " + print.getName() ".xls");
25            SimpleDateFormat timeStampFormat = new SimpleDateFormat(TIMESTAMP_FORMAT_FOR_XLS);
26            String timeStamp = timeStampFormat.format(Calendar.getInstance().getTime());
27            response.setProperty("Content-Disposition", "attachment; filename= " + TEST_FILE_XLS + timeStamp
28                    + ".xls");
29        } catch (JRException e)
30        {
31            LOGGER.error( e.getMessage(),e);
32        } catch (IOException e)
33        {
34            LOGGER.error( e.getMessage(),e);
35        }
36    }
ramveer singh narwariya
RE: excel sheet File Download in Liferay 6.x
October 26, 2012 2:43 AM
Answer

ramveer singh narwariya

Rank: Junior Member

Posts: 64

Join Date: April 13, 2012

Recent Posts

public void readExcel(ActionRequest request, ActionResponse response)
throws PortalException, SystemException, Throwable {

String filename="";
String searchText = "";
String filePath="C:\\folder\\myfile.xls";
if (request.getParameter("searchtxt") != null) {
searchText = request.getParameter("searchtxt").toString();
}



try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = null;
conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/lportal61rc1","root","root");


Statement stmt = conn.createStatement();
String strQuery = "select * from tcsnsn_TrainingRecord where eventId like '%"+ inputFile +"%'" ;

ResultSet rs = stmt.executeQuery(strQuery);

HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");

HSSFRow rowhead = sheet.createRow((short) 2);
rowhead.createCell((short) 0).setCellValue("sno");
rowhead.createCell((short) 1).setCellValue("employeeID");
rowhead.createCell((short) 2).setCellValue("projectName");
rowhead.createCell((short) 3).setCellValue("employeeName");
rowhead.createCell((short) 4).setCellValue("designation");
rowhead.createCell((short) 5).setCellValue("hasAttended");

int index = 3;
int sno = 0;
String name = "";
while (rs.next()) {
sno++;

HSSFRow row = sheet.createRow((short) index);
row.createCell((short) 0).setCellValue(sno);
row.createCell((short) 1).setCellValue(rs.getString(10));
row.createCell((short) 2).setCellValue(rs.getString(7));
row.createCell((short) 3).setCellValue(rs.getString(11));
row.createCell((short) 4).setCellValue(rs.getString(8));
row.createCell((short) 5).setCellValue(rs.getString(4));
index++;

}


FileOutputStream fileOut = new FileOutputStream(filePath);
hwb.write(fileOut);
fileOut.close();


} catch (Exception ex) {

}

}




the above code are working fine that export the excel sheet in hard coded path but we do not want the hard code the path and aslo download the file at client machine from my website.
please you help me .
Vishal Panchal
RE: excel sheet File Download in Liferay 6.x
October 28, 2012 10:30 PM
Answer

Vishal Panchal

Rank: Expert

Posts: 280

Join Date: May 20, 2012

Recent Posts

Hi ramveer singh narwariya,

I did the same in one of my project , for the same requirement as you currently have.

I made a resourceURL rather than actionURL and it is working fine for me.

For that I did following steps,

1) URL on JSP page
<portlet:resourceURL id="<%=Constants.EXPORT_ACTION%>" var="exportURL" />
<a href="<%=exportURL%>"> Export </a>


2) URL Handler in controller
@ResourceMapping(value = Constants.EXPORT_ACTION)
public void exportAction(ResourceRequest resourceRequest, ResourceResponse resourceResponse) throws IOException {
ExportDataUtil.exportExcel(resourceRequest,resourceResponse);

}

3) ExportDataUtil.java

public static OutputStream exportReturnRequest(ResourceRequest resourceRequest,ResourceResponse response)
throws IOException {

//////////// Your code for export

response.setContentType("application/vnd.ms-excel");
response.setProperty("Content-Disposition", "attachment; filename=\""+ FILENAME + "\"");
workbook.write(out);
return out;
}


Now when you will click on export link from JSP it will give you an exported file by asking where to download / save , can be saved on client side.
Hope it will help you.!

Thanks&Regards,
Vishal R. Panchal
ramveer singh narwariya
RE: excel sheet File Download in Liferay 6.x
October 29, 2012 11:08 PM
Answer

ramveer singh narwariya

Rank: Junior Member

Posts: 64

Join Date: April 13, 2012

Recent Posts

thank you very much for reply
Vishal Panchal
RE: excel sheet File Download in Liferay 6.x
October 29, 2012 11:42 PM
Answer

Vishal Panchal

Rank: Expert

Posts: 280

Join Date: May 20, 2012

Recent Posts

ramveer singh narwariya:
thank you very much for reply


You are well come.
please share whether you got the downloadable excel sheet File ..?
if yes, than which changes you made.?

Thanks&Regards,
Vishal R. Panchal
Gaurav Jain
RE: excel sheet File Download in Liferay 6.x
October 30, 2012 12:23 AM
Answer

Gaurav Jain

Rank: Regular Member

Posts: 146

Join Date: January 31, 2011

Recent Posts

You may also use liferay way for it. So using open office document conversion.
For this you need to install open office and enable liferay open office setting from Control Panel > Server Admin ? third party

Then you can use a utility com.liferay.portal.kernel.util.DocumentConversionUtil.convert(String id, InputStream inputStream, String sourceExtension, String targetExtension)

and you can pass sourceExtension as "html" and targetExtension as "xls"
ramveer singh narwariya
RE: excel sheet File Download in Liferay 6.x
November 1, 2012 12:22 AM
Answer

ramveer singh narwariya

Rank: Junior Member

Posts: 64

Join Date: April 13, 2012

Recent Posts

Vishal Panchal:
ramveer singh narwariya:
thank you very much for reply


You are well come.
please share whether you got the downloadable excel sheet File ..?
if yes, than which changes you made.?

Thanks&Regards,
Vishal R. Panchal


yes i got it.
previously i was using actionURL that reason i was getting error but now am using ResourceURL and serveResource method.
Mittal Patoliya
RE: excel sheet File Download in Liferay 6.x
August 2, 2013 2:26 AM
Answer

Mittal Patoliya

Rank: New Member

Posts: 24

Join Date: December 14, 2011

Recent Posts

Hi Vishal,
I am having same requirement.
My question is it seems you were using spring mvc portlet but I am using simple Liferay MVC portlet so could you please tell me what are the changes required in your code to accomodate it in my portlet?
Vishal Panchal
RE: excel sheet File Download in Liferay 6.x
August 2, 2013 10:44 PM
Answer

Vishal Panchal

Rank: Expert

Posts: 280

Join Date: May 20, 2012

Recent Posts

Hello,

If you are using Liferay MVC then it would be pretty simple.
Try below steps,
1. create resourceURL in jsp page
2. Create handler in your PortletAction class
3. Right your code

1) URL on JSP page
<portlet:resourceURL id="exportAction" var="exportURL" />

<a href="<%=exportURL%>"> Export Link</a>

2) URL Handler in PortletAction class

serveResource(ResourceRequest resourceRequest, ResourceResponse resourceResponse){
ExportDataUtil.exportExcel(resourceRequest,resourceResponse);
}

3) ExportDataUtil.java

public static OutputStream exportReturnRequest(ResourceRequest resourceRequest,ResourceResponse response)
throws IOException {

//////////// Your code for export

response.setContentType("application/vnd.ms-excel");
response.setProperty("Content-Disposition", "attachment; filename=\""+ FILENAME + "\"");
workbook.write(out);
return out;
}

please feel free to ask if you are still having problem with this.

Thanks,
Vishal
Mittal Patoliya
RE: excel sheet File Download in Liferay 6.x
August 7, 2013 1:00 AM
Answer

Mittal Patoliya

Rank: New Member

Posts: 24

Join Date: December 14, 2011

Recent Posts

Thanks Vishal It is working now emoticon.
Now I am facing problem with aui date picker component if you get a chance please have a look.
http://stackoverflow.com/questions/18097726/date-is-not-showing-up-properly-in-liferay-portlet

Date field and date calender image both are not coming in single line
adel b
RE: excel sheet File Download in Liferay 6.x
January 3, 2015 6:08 AM
Answer

adel b

Rank: Junior Member

Posts: 47

Join Date: August 5, 2014

Recent Posts

Hello,
I used the same approach, but the downloaded xls file is empty and it appears to me that the file type is different from xls.
I used this code:

in jsp file :
<portlet:resourceURL id="exportAction" var="exportURL" />

<a href="<%=exportURL%>"> Export Link</a>

in portlet class
public void serveResource(ResourceRequest resourceRequest, ResourceResponse resourceResponse)
{
ExportDataUtil.exportReturnRequest(resourceRequest,resourceResponse);
}

in ExportDataUtil class

public static OutputStream exportReturnRequest(ResourceRequest resourceRequest,ResourceResponse response)
throws IOException {


HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");

HSSFRow rowhead = sheet.createRow((short) 2);
rowhead.createCell(0).setCellValue("Name");
rowhead.createCell(1).setCellValue("age");
rowhead.createCell(2).setCellValue("Tel");
rowhead.createCell(3).setCellValue("Email");


FileOutputStream out = new FileOutputStream("filename");

response.setContentType("application/vnd.ms-excel");
response.setProperty("Content-Disposition", "attachment; filename=\""+ "FILENAME" + "\"");
hwb.write(out);
System.out.println();
return out;

}
ismail zabiulla
RE: excel sheet File Download in Liferay 6.x
January 10, 2015 8:55 AM
Answer

ismail zabiulla

Rank: Junior Member

Posts: 67

Join Date: July 13, 2014

Recent Posts

Hi Adel Bayoudh,

hi find right approach below

Adel Bayoudh:
Hello,
I used the same approach, but the downloaded xls file is empty and it appears to me that the file type is different from xls.
I used this code:

in jsp file :
<portlet:resourceURL id="exportAction" var="exportURL" />

<a href="<%=exportURL%>"> Export Link</a>

in portlet class
public void serveResource(ResourceRequest resourceRequest, ResourceResponse resourceResponse)
{
ExportDataUtil.exportReturnRequest(resourceRequest,resourceResponse);
}

in ExportDataUtil class

public static OutputStream exportReturnRequest(ResourceRequest resourceRequest,ResourceResponse response)
throws IOException {


HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");

HSSFRow rowhead = sheet.createRow((short) 2);
rowhead.createCell(0).setCellValue("Name");
rowhead.createCell(1).setCellValue("age");
rowhead.createCell(2).setCellValue("Tel");
rowhead.createCell(3).setCellValue("Email");


FileOutputStream out = new FileOutputStream("filename");

response.setContentType("application/vnd.ms-excel");
response.setProperty("Content-Disposition", "attachment; filename=\""+ "FILENAME" + "\"");
hwb.write(out);
System.out.println();
return out;

}


/// this has to work

public void serveResource(ResourceRequest resourceRequest, ResourceResponse resourceResponse)
{
HSSFWorkbook workbook =ExportDataUtil.exportReturnRequest();

resourceResponse.setContentType("application/vnd.ms-excel");

resourceResponse.addProperty("Content-Disposition", "attachment; filename="+FILENAME);

// this will wirte to OutputStream
workbook.write(resourceResponse.getPortletOutputStream());

}


public static HSSFWorkbook exportReturnRequest()
{


HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");

HSSFRow rowhead = sheet.createRow((short) 2);
rowhead.createCell(0).setCellValue("Name");
rowhead.createCell(1).setCellValue("age");
rowhead.createCell(2).setCellValue("Tel");
rowhead.createCell(3).setCellValue("Email");

return hwb;
}
adel b
RE: excel sheet File Download in Liferay 6.x
January 11, 2015 2:24 AM
Answer

adel b

Rank: Junior Member

Posts: 47

Join Date: August 5, 2014

Recent Posts

thank you emoticon
Manushi Jani
RE: excel sheet File Download in Liferay 6.x
April 21, 2015 12:30 AM
Answer

Manushi Jani

Rank: New Member

Posts: 2

Join Date: February 13, 2015

Recent Posts

Thanks a lot, this works perfectly well.emoticon
Only thing is we need to explicit give the extension(i.e .xls) as a suffix with filename. Then only it will give proper file in excel format.
Chandan Sharma
RE: excel sheet File Download in Liferay 6.x
April 21, 2015 12:54 AM
Answer

Chandan Sharma

Rank: Junior Member

Posts: 38

Join Date: March 3, 2014

Recent Posts

Vishal Panchal:

please feel free to ask if you are still having problem with this.

Thanks,
Vishal


Hi Vishal,

I am also having similar kind of requirement, where I am using Apache POI API for generating excel file to download it. But my requirement is having one add-on constraint is:

I want to pass some data from my JSP to the controller class as parameter and from that I want to create downloadable file. The data which I am sending is getting generated dynamically based on different event that user does and it is huge in size also. So I can't pass this dynamic changing huge size data as a parameter to resourceURL.

So for I using AJAX request to do that. But the problem is that through AJAX request the generated file is not getting downloaded.

I will feel very happy, if someone will really help me to do that..

I already posted a question couple of week back but didn't got any reply. You can have a look on my code snippet from this link

I am eagerly waiting for response


Thanks in advance... emoticon
Raja Seth
RE: excel sheet File Download in Liferay 6.x
April 21, 2015 5:20 AM
Answer

Raja Seth

Rank: Regular Member

Posts: 217

Join Date: August 18, 2011

Recent Posts

Hi Ramveer,

Just check the Export User functionality in liferay-src ExportUsersAction.java. You might get an idea.

Regards,
Raja
Manushi Jani
RE: excel sheet File Download in Liferay 6.x
May 4, 2015 3:48 AM
Answer

Manushi Jani

Rank: New Member

Posts: 2

Join Date: February 13, 2015

Recent Posts

Hi Chandan,

Try passing the data through AJAX in JSON array.This may ease out the things.
Chandan Sharma
RE: excel sheet File Download in Liferay 6.x
May 8, 2015 12:00 AM
Answer

Chandan Sharma

Rank: Junior Member

Posts: 38

Join Date: March 3, 2014

Recent Posts

Manushi Jani:
Hi Chandan,

Try passing the data through AJAX in JSON array.This may ease out the things.


Getting the response in JSON format in my AJAX request will not give a download window to download the file. Can you please elaborate how to get download window for response which I am getting from AJAX