excel sheet File Download in Liferay 6.x

[ramveer singh narwariya, modifié il y a 11 années.]

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, modifié il y a 11 années.]

HI Ramveer,

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

Thanks,
Pankaj

[Jitendra Rajput, modifié il y a 11 années.]

Try by using jasper report.

[Hitoshi Ozawa, modifié il y a 11 années.]

Just use something like Apache POI
http://poi.apache.org/

[ramveer singh narwariya, modifié il y a 11 années.]

thank you for reply
can any one send me code

[Jitendra Rajput, modifié il y a 11 années.]

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 .

    public static void reportForEmployeeData(ResourceRequest request, ResourceResponse response,
            List<employeedata> result, String path)
    {
        try
        {
            JRBeanCollectionDataSource resultList = new JRBeanCollectionDataSource(result);
            JasperReport report = JasperCompileManager.compileReport(path);
            JasperPrint print = JasperFillManager.fillReport(report, new HashMap<string, object>(), resultList);
            OutputStream opStream = response.getPortletOutputStream();

            JRXlsExporter exporterXLS = new JRXlsExporter();
            exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, print);
            exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, opStream);
            exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
            exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
            exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
            exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
            exporterXLS.exportReport();
            // Prompt user to save the excel file
            response.setContentType("application/x-excel");
            // response.setProperty("Content-Disposition",
            // "attachment; filename= " + print.getName() ".xls");
            SimpleDateFormat timeStampFormat = new SimpleDateFormat(TIMESTAMP_FORMAT_FOR_XLS);
            String timeStamp = timeStampFormat.format(Calendar.getInstance().getTime());
            response.setProperty("Content-Disposition", "attachment; filename= " + TEST_FILE_XLS + timeStamp
                    + ".xls");
        } catch (JRException e)
        {
            LOGGER.error( e.getMessage(),e);
        } catch (IOException e)
        {
            LOGGER.error( e.getMessage(),e);
        }
    }

</string,></employeedata>

[ramveer singh narwariya, modifié il y a 11 années.]

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, modifié il y a 11 années.]

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, modifié il y a 11 années.]

thank you very much for reply

[Vishal Panchal, modifié il y a 11 années.]

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

[ramveer singh narwariya, modifié il y a 11 années.]

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.

[me liferay, modifié il y a 10 années.]

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, modifié il y a 10 années.]

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

[me liferay, modifié il y a 10 années.]

Thanks Vishal It is working now .
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 Bayoudh, modifié il y a 9 années.]

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 s, modifié il y a 9 années.]

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 Bayoudh, modifié il y a 9 années.]

thank you

[Manushi Jani, modifié il y a 8 années.]

Thanks a lot, this works perfectly well.
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.

[Nikolaos Kroustalakis, modifié il y a 4 années.]

ismail zabiulla:

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

Been a few years since the post, so i'm not sure if you remember anything about it. Just wanted to ask you something.
I wrote the code you suggest and all i'm getting is a blank page and nothing on the tomcat console. I already made a post here:
https://liferay.dev/forums/-/message_boards/message/118917108
So if  you have any suggestions on the matter, i'd really appreciate it.
Thank you!

[Chandan Sharma, modifié il y a 8 années.]

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...

[Manushi Jani, modifié il y a 8 années.]

Hi Chandan,

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

[Chandan Sharma, modifié il y a 8 années.]

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

[Razeena P, modifié il y a 7 années.]

Hii vishal,

As you sid I used ResourceURl only and Im facing some issue.Please Help me.

Step1In view.jsp)
<portlet:resourceURL id="exportAction" var="exUrl">
<portlet:param name="cmd" value="excl"/>
</portlet:resourceURL>

Step2ReportGenerationAction.java)

public void serveResource(ResourceRequest request, ResourceResponse response) throws IOException{
String cmd = ParamUtil.getString(request, "cmd");
if(cmd.equalIgnoreCase("excl"){
HSSFWorkbook workbook =ExportDataUtil.exportReturnRequest(request, response);

response.setContentType("application/vnd.ms-excel");
response.addProperty("Content-Disposition", "inline; filename="+ "TrakcerReport.xls");

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

}}

Step3ExportDataUtil.java)

public class ExportDataUtil {

public static HSSFWorkbook exportReturnRequest(ResourceRequest request,ResourceResponse response) throws IOException{



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

HSSFRow rowhead = sheet.createRow((short)0);

rowhead.createCell(0).setCellValue("Order Item Status");
rowhead.createCell(1).setCellValue("Name");
rowhead.createCell(2).setCellValue("Quantity");
rowhead.createCell(3).setCellValue("Price");

String orderItemIds = ParamUtil.getString(request, "orderItemIds");
if(Validator.isNotNull(orderItemIds)){
System.out.println("inside validator");
String[] splittedIds = StringUtils.split(orderItemIds, StringPool.COMMA);

try {
for(int i=0; i<splittedIds.length; i++){
System.out.println("inside for loop");
OrderItem orderItem = OrderItemLocalServiceUtil.getOrderItem(Long.parseLong(splittedIds));

int index = 3;
HSSFRow row = sheet.createRow((short) index);

row.createCell(0).setCellValue(orderItem.getOrderItemStatus());
row.createCell(1).setCellValue(orderItem.getName());
row.createCell(2).setCellValue(String.valueOf(orderItem.getQuantity()));
row.createCell(3).setCellValue(String.valueOf(orderItem.getPrice()));
row.createCell(4).setCellValue(orderItemIds);
index++;


}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}}
return hwb;


}
}




Can u please help me to solve my issue its some what similar to uva issue only.
ThankYou in advance.

[Gaurav Jain, modifié il y a 11 années.]

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"

[Raja Seth, modifié il y a 8 années.]

Hi Ramveer,

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

Regards,
Raja