Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Archana S
Fetch all the data from DB to Excel sheet
March 16, 2013 4:56 AM
Answer

Archana S

Rank: Junior Member

Posts: 45

Join Date: January 30, 2013

Recent Posts

Hello Everyone,

I have a requirnment where i need to fetch the data from DB to excel sheet and need to download that sheet .. Can anyone help me on this i am trying to search google but not getting any exact solution.

Thanks
Hitoshi Ozawa
RE: Fetch all the data from DB to Excel sheet
March 16, 2013 6:21 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7949

Join Date: March 23, 2010

Recent Posts

Just use the get method in Impl class to get the data and use Apache POI to write data to output stream.
Jan Geißler
RE: Fetch all the data from DB to Excel sheet
March 18, 2013 5:02 AM
Answer

Jan Geißler

Community Moderator

Rank: Liferay Master

Posts: 735

Join Date: July 5, 2011

Recent Posts

Or write a Report with JasperReports.
Pradeep Sharma
RE: Fetch all the data from DB to Excel sheet
March 18, 2013 6:33 AM
Answer

Pradeep Sharma

Rank: Junior Member

Posts: 49

Join Date: September 6, 2011

Recent Posts

Write a method in your controller
excelOutput(ResourceRequest resourceRequest, ResourceResponse resourceResponse){

use a class ExcelReportWriter where you define all set of report format and output what you want in a method writeExcel and call this class here like this

ExcelReportWriter rw = new ExcelReportWriter(resourceResponse.getPortletOutputStream()); [Define all cell info and all details you want to present as output]

now use POI jar in your class path and write the syntax to generate excelReport
{
new HSSFWorkbook();
resourceResponse.setContentType("application/vnd.ms-excel");
resourceResponse.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=\"" + FILENAME + ".xls\"");
OutputStream out = resourceResponse.getPortletOutputStream();
rw.writeExcel(rptInfo);

}}

Hope this will help
Ravi Teja
RE: Fetch all the data from DB to Excel sheet
March 18, 2013 7:52 AM
Answer

Ravi Teja

Rank: New Member

Posts: 21

Join Date: July 9, 2012

Recent Posts

Hai Riya,

Chek the Follwing.

here i implemented to exporting file in Excel Format(which is fetching data from Database).

Follow these steps


1.In view.jsp

 1
 2
 3<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>
 4
 5<portlet:defineObjects />
 6 <div>
 7               <portlet:actionURL name="exportEvent" var="exportEventURL" />
 8                   <form name="fm2" action="<%=exportEventURL.toString() %>" method="post" >
 9                   <input type="text"  name="eventId"  />
10                      <div class="AddCal_button marR_10"> 
11                  <input name="input" type="submit" value="Export">
12                  </div>
13                 </form>
14             </div> is the <b>ExportExcelfile</b> portlet in View mode.



2.In service .xml

 1
 2<?xml version="1.0" encoding="UTF-8"?>
 3 <!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.0.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_0_0.dtd">
 4 <service-builder package-path="com.test">
 5         <author>Ravi</author>
 6    <namespace>export</namespace>
 7
 8     <entity name="Testevent" table="Testevent" local-service="true" remote-service="true">
 9     <column name="id" type="long" primary="true" />
10      <column name="eventId" type="long" />
11      <column name="eventName" type="String" />
12       <column name="createDate" type="Date" />
13
14    </entity>
15</service-builder>


3.Do ant Build-service.

4.And write these 2 methods in Action Class

 1
 2
 3public void exportEvent(ActionRequest actionRequest, ActionResponse actionResponse) {
 4         String serverPath = getPortletContext().getRealPath("/");
 5        File file = null;
 6        String fname = serverPath+"/Event.xls";
 7   
 8         try {
 9           ThemeDisplay themeDisplay =
10                (ThemeDisplay)actionRequest.getAttribute(WebKeys.THEME_DISPLAY);
11
12            long eventId = ParamUtil.getLong(actionRequest, "eventId");
13            System.out.println("The tiger Eventid is-------->"+eventId);
14
15            String exportFileName = ParamUtil.getString(
16                actionRequest, "exportFileName");
17
18            if (Validator.isNull(exportFileName)) {
19                exportFileName = "Event.xls";
20            }
21           else {
22               exportFileName = FileUtil.getShortFileName(exportFileName);
23           }
24 
25            if (eventId > 0) {
26                try {                           
27                   file = exportEvent(eventId ,serverPath );               
28               
29                } catch (PortalException e) {
30                    // TODO Auto-generated catch block
31                    e.printStackTrace();
32                }
33           }
34            
35           HttpServletRequest request = PortalUtil.getHttpServletRequest(
36                actionRequest);
37           HttpServletResponse response = PortalUtil.getHttpServletResponse(
38               actionResponse);
39          
40            ServletResponseUtil.sendFile(
41                    request, response, exportFileName, new FileInputStream(file),
42                   ContentTypes.TEXT);
43
44               
45          
46      }
47        catch (Exception e) {
48            //_log.error(e, e);
49        }
50       finally {
51          FileUtil.delete(file);
52       }
53    }
54   
55   
56    public File exportEvent(long eventId , String serverPath)
57          throws PortalException, SystemException {
58       
59       File file = null;
60        System.out.println(serverPath+"serverPathserverPathserverPathserverPath");
61       int i = 0;
62        i = serverPath.indexOf("temp");
63        String fpath = serverPath.substring(0, i);
64        fpath = fpath + "webapps/ROOT/html/mtec/images";       
65        String fname = fpath+"/Event.xls";       
66   
67        Testevent t1=TesteventLocalServiceUtil.getTestevent(eventId);
68       
69        t1.getEventName();
70        t1.getCreateDate();
71       
72       try {   
73          System.out.println(fname+"-->fname1");       
74            FileWriter fw = new FileWriter(fname);
75           System.out.println(fname+"-->fname2");       
76            
77           fw.write((int) t1.getId());
78          fw.write(':');
79            fw.write('\n');            
80           fw.write((int) t1.getEventId());
81            fw.write(':');
82            fw.write('\n');    
83            fw.write(t1.getEventName());   
84            fw.close();       
85         file = new File(fname);         
86        } catch (IOException e) {
87            // TODO Auto-generated catch block
88           e.printStackTrace();
89      }
90      
91        return file;
92    }
93
94}


5.Deploy The portlet.
6. You can View a form which you have to enter event id.

7. Then the particular event Data will Fetch from DataBase(Check in DB. The Table should not be empty) and getting one popup window it is Asking for open (or)Download the file
.


Hope this will help to you emoticon

Thanks & Regards
Ravi
Archana S
RE: Fetch all the data from DB to Excel sheet
March 18, 2013 10:40 PM
Answer

Archana S

Rank: Junior Member

Posts: 45

Join Date: January 30, 2013

Recent Posts

Thanks Guys for your valuable reply !! @Pradeep Kumar Badhai @Ravi Teja : Thanks for the Sample Code !!
@Jan Geißler : Hi Jan I am new to Jasper report Can you pls eloborate more ont this how can i start Jasper Report.
Archana S
RE: Fetch all the data from DB to Excel sheet
March 19, 2013 11:16 PM
Answer

Archana S

Rank: Junior Member

Posts: 45

Join Date: January 30, 2013

Recent Posts

As Hitoshi said i am trying using Apache POI and here is my code
In My Action Class code
 1
 2public void readExcel(ResourceRequest request, ResourceResponse response) throws IOException
 3        {
 4
 5            String filename="";
 6            String searchText = "";
 7            String filePath="/home/Desktop/sss";
 8            if (request.getParameter("searchtxt") != null)
 9            {
10                searchText = request.getParameter("searchtxt").toString();
11            }
12
13            try
14            {
15                Class.forName("com.mysql.jdbc.Driver").newInstance();
16                Connection conn = null;
17                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/HealthCare","root","root");
18       
19
20                Statement stmt = conn.createStatement();
21       
22                String strQuery = "select * from contact_Contact" ;
23
24                ResultSet rs = stmt.executeQuery(strQuery);
25
26                HSSFWorkbook hwb = new HSSFWorkbook();
27                HSSFSheet sheet = hwb.createSheet("new sheet");
28
29                HSSFRow rowhead = sheet.createRow((short) 2);
30                rowhead.createCell((short) 0).setCellValue("firstName");
31                rowhead.createCell((short) 1).setCellValue("lastName");
32                rowhead.createCell((short) 2).setCellValue("email");
33                rowhead.createCell((short) 3).setCellValue("message");
34               
35                int index = 3;
36                int sno = 0;
37                String name = "";
38                while (rs.next())
39                {
40                    sno++;
41
42                    HSSFRow row = sheet.createRow((short) index);
43                    row.createCell((short) 0).setCellValue(sno);
44                    row.createCell((short) 1).setCellValue(rs.getString(10));
45                    row.createCell((short) 2).setCellValue(rs.getString(7));
46                    row.createCell((short) 3).setCellValue(rs.getString(11));
47                    index++;
48
49                }
50
51                FileOutputStream fileOut = new FileOutputStream(filePath);
52                hwb.write(fileOut);
53                fileOut.close();
54
55            }
56            catch (Exception ex)
57            {
58
59            }
60
61        }


And in view.jsp

 1<%@page import="com.liferay.portal.kernel.util.Constants"%>
 2<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>
 3
 4<portlet:defineObjects />
 5
 6<portlet:resourceURL id="<%=com.liferay.portal.kernel.util.Constants.EXPORT%>" var="exportURL"/>
 7
 8
 9<a href="<%=exportURL%>"> Export </a>


So when I click on Export Link it not showing anything in my browser and my Console too !! any one can help me pls !! I am Stuck Here !!!
Jan Geißler
RE: Fetch all the data from DB to Excel sheet
March 19, 2013 4:47 AM
Answer

Jan Geißler

Community Moderator

Rank: Liferay Master

Posts: 735

Join Date: July 5, 2011

Recent Posts

I would still recommend using JasperReports directly or AperteReports.
Maybe you would like to read this WikiPage:

http://www.liferay.com/community/wiki/-/wiki/Main/How+to+Use+LiferayServiceBuilderClasses+in+JasperReports+and+IReport
Archana S
RE: Fetch all the data from DB to Excel sheet
March 22, 2013 2:07 AM
Answer

Archana S

Rank: Junior Member

Posts: 45

Join Date: January 30, 2013

Recent Posts

@Jan Geißler : Well i started and downloaded Jasper report i.e iReport Professional whats the next step !! pls help me on the this i am new to Jasper Report !!
Hitoshi Ozawa
RE: Fetch all the data from DB to Excel sheet
March 23, 2013 9:04 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7949

Join Date: March 23, 2010

Recent Posts

JasperReport probably is too much just to export to an excel sheet. There's a problem with fonts on Japanese systems and that requires too much effort just for this functionality.
Hitoshi Ozawa
RE: Fetch all the data from DB to Excel sheet
March 23, 2013 9:06 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7949

Join Date: March 23, 2010

Recent Posts

Can you check if you are actually reading in any data from your database? How about putting in a debug log to output number of records read.
Archana S
RE: Fetch all the data from DB to Excel sheet
March 24, 2013 11:05 PM
Answer

Archana S

Rank: Junior Member

Posts: 45

Join Date: January 30, 2013

Recent Posts

@ Hitoshi : I am doing from apache poi too !! so when i call resourceURL from my jsp to my action class its not getting invoke pls see the above code which i done lemme know where i am going wrong !!
ramveer singh narwariya
RE: Fetch all the data from DB to Excel sheet
March 24, 2013 11:35 PM
Answer

ramveer singh narwariya

Rank: Junior Member

Posts: 64

Join Date: April 13, 2012

Recent Posts

check with below code:


 1/*    @Override
 2        code for  download data as excel sheet
 3        }
 4        */
 5    
 6   
 7    public void serveResource(ResourceRequest request, ResourceResponse response)throws IOException  {
 8       
 9        System.out.println("ramveer...");
10        String inputFile="";
11        if (request.getParameter("eventID") != null) {
12            inputFile = request.getParameter("eventID").toString();
13        }
14
15       
16    try {
17        Class.forName("com.mysql.jdbc.Driver").newInstance();
18        java.sql.Connection conn=null;
19        Statement stmt=null;
20         conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/lportal61rc1","root","tcs@12345");
21        System.out.println("111111+++");
22       
23         stmt =conn.createStatement();
24            System.out.println("7777777777777777....");
25       
26        String strQuery = "select * from tcsnsn_TrainingRecord where eventId like '%"+ inputFile +"%'" ;
27
28        ResultSet rs = stmt.executeQuery(strQuery);
29
30
31        HSSFWorkbook hwb = new HSSFWorkbook();
32        HSSFSheet sheet = hwb.createSheet("new sheet");
33
34        HSSFRow rowhead = sheet.createRow((short) 2);
35        rowhead.createCell((short) 0).setCellValue("sno");
36        rowhead.createCell((short) 1).setCellValue("employeeID");
37        rowhead.createCell((short) 2).setCellValue("projectName");
38        rowhead.createCell((short) 3).setCellValue("employeeName");
39        rowhead.createCell((short) 4).setCellValue("designation");
40        rowhead.createCell((short) 5).setCellValue("hasAttended");
41
42        int index = 3;
43        int sno = 0;
44        String name = "";
45        while (rs.next()) {
46            sno++;
47
48            HSSFRow row = sheet.createRow((short) index);
49            row.createCell((short) 0).setCellValue(sno);
50            row.createCell((short) 1).setCellValue(rs.getString(10));
51            row.createCell((short) 2).setCellValue(rs.getString(7));
52            row.createCell((short) 3).setCellValue(rs.getString(11));
53            row.createCell((short) 4).setCellValue(rs.getString(8));
54            row.createCell((short) 5).setCellValue(rs.getString(4));
55            index++;
56
57        }
58
59              String filename = "ViewAttendees.xls";
60              response.setProperty("application/x-excel","");
61              response.addProperty("content-disposition", "attachment; filename=" + filename);
62
63              OutputStream os =response.getPortletOutputStream();
64              hwb.write(os);
65              os.close();
66             
67        System.out.println("Final....");
68         
69    } catch (Exception ex) {
70      System.out.println("error.......: "+ex);
71    }
72   
73   
74    }
75   
Manoj Powar
RE: Fetch all the data from DB to Excel sheet
October 4, 2013 3:09 AM
Answer

Manoj Powar

Rank: New Member

Posts: 17

Join Date: September 24, 2013

Recent Posts

can you give me source for any simple liferay database project
Kalai Arasan
RE: Fetch all the data from DB to Excel sheet
November 19, 2013 10:38 PM
Answer

Kalai Arasan

Rank: Regular Member

Posts: 103

Join Date: January 2, 2013

Recent Posts

I wants to know the jar file to be included for generate excel sheet?
Archana S
RE: Fetch all the data from DB to Excel sheet
June 9, 2014 2:56 AM
Answer

Archana S

Rank: Junior Member

Posts: 45

Join Date: January 30, 2013

Recent Posts

These Iare the jar you need to include
dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar