Fetch all the data from DB to Excel sheet

[Riya M S，修改在11 年前。]

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，修改在11 年前。]

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，修改在11 年前。]

Or write a Report with JasperReports.

[Riya M S，修改在11 年前。]

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.

[Pradeep Kumar Badhai，修改在11 年前。]

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，修改在11 年前。]

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

 
<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>

<portlet:defineobjects />
 <div>
               <portlet:actionurl name="exportEvent" var="exportEventURL" /> 
                   <form name="fm2" action="<%=exportEventURL.toString() %>" method="post">
                   <input type="text" name="eventId">
                      <div class="AddCal_button marR_10">  
                  <input name="input" type="submit" value="Export">
                  </div>
                 </form> 
             </div> is the <b>ExportExcelfile</b> portlet in View mode.

2.In service .xml

<!--?xml version="1.0" encoding="UTF-8"?-->
 
 <service-builder package-path="com.test">
         <author>Ravi</author>
    <namespace>export</namespace>
 
     <entity name="Testevent" table="Testevent" local-service="true" remote-service="true">
     <column name="id" type="long" primary="true" />
      <column name="eventId" type="long" />
      <column name="eventName" type="String" />
       <column name="createDate" type="Date" />

    </entity>
</service-builder>

3.Do ant Build-service.

4.And write these 2 methods in Action Class

public void exportEvent(ActionRequest actionRequest, ActionResponse actionResponse) {
         String serverPath = getPortletContext().getRealPath("/");
        File file = null;
        String fname = serverPath+"/Event.xls";
    
         try {
           ThemeDisplay themeDisplay =
                (ThemeDisplay)actionRequest.getAttribute(WebKeys.THEME_DISPLAY);

            long eventId = ParamUtil.getLong(actionRequest, "eventId");
            System.out.println("The tiger Eventid is-------->"+eventId);

            String exportFileName = ParamUtil.getString(
                actionRequest, "exportFileName");

            if (Validator.isNull(exportFileName)) {
                exportFileName = "Event.xls";
            }
           else {
               exportFileName = FileUtil.getShortFileName(exportFileName);
           }
  
            if (eventId > 0) {
                try {                            
                   file = exportEvent(eventId ,serverPath );                
                
                } catch (PortalException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
           }
            
           HttpServletRequest request = PortalUtil.getHttpServletRequest(
                actionRequest);
           HttpServletResponse response = PortalUtil.getHttpServletResponse(
               actionResponse);
           
            ServletResponseUtil.sendFile(
                    request, response, exportFileName, new FileInputStream(file),
                   ContentTypes.TEXT);

               
           
      }
        catch (Exception e) {
            //_log.error(e, e);
        }
       finally {
          FileUtil.delete(file);
       }
    }
    
   
    public File exportEvent(long eventId , String serverPath)
          throws PortalException, SystemException {
        
       File file = null;
        System.out.println(serverPath+"serverPathserverPathserverPathserverPath");
       int i = 0;
        i = serverPath.indexOf("temp");
        String fpath = serverPath.substring(0, i);
        fpath = fpath + "webapps/ROOT/html/mtec/images";        
        String fname = fpath+"/Event.xls";        
   
        Testevent t1=TesteventLocalServiceUtil.getTestevent(eventId);
        
        t1.getEventName();
        t1.getCreateDate();
        
       try {    
          System.out.println(fname+"-->fname1");        
            FileWriter fw = new FileWriter(fname);
           System.out.println(fname+"-->fname2");        
            
           fw.write((int) t1.getId());
          fw.write(':');
            fw.write('\n');            
           fw.write((int) t1.getEventId());
            fw.write(':');
            fw.write('\n');     
            fw.write(t1.getEventName());    
            fw.close();        
         file = new File(fname);          
        } catch (IOException e) {
            // TODO Auto-generated catch block
           e.printStackTrace();
      }
       
        return file;
    }

}

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

Thanks & Regards
Ravi

[Riya M S，修改在11 年前。]

As Hitoshi said i am trying using Apache POI and here is my code
In My Action Class code

public void readExcel(ResourceRequest request, ResourceResponse response) throws IOException
		{

			String filename="";
			String searchText = "";
			String filePath="/home/Desktop/sss";
			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/HealthCare","root","root");
		

				Statement stmt = conn.createStatement();
		
				String strQuery = "select * from contact_Contact" ;

				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("firstName");
				rowhead.createCell((short) 1).setCellValue("lastName");
				rowhead.createCell((short) 2).setCellValue("email");
				rowhead.createCell((short) 3).setCellValue("message");
				
				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));
					index++;

				}

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

			}
			catch (Exception ex)
			{

			}

		}

And in view.jsp

<%@page import="com.liferay.portal.kernel.util.Constants"%>
<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>

<portlet:defineobjects />

<portlet:resourceurl id="<%=com.liferay.portal.kernel.util.Constants.EXPORT%>" var="exportURL" />


<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，修改在11 年前。]

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

[Riya M S，修改在11 年前。]

@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，修改在11 年前。]

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，修改在11 年前。]

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.

[Riya M S，修改在11 年前。]

@ 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，修改在11 年前。]

check with below code:

/*	@Override
		code for  download data as excel sheet
		}
		*/
	 
	
	public void serveResource(ResourceRequest request, ResourceResponse response)throws IOException  {
		
		System.out.println("ramveer...");
		String inputFile="";
		if (request.getParameter("eventID") != null) {
			inputFile = request.getParameter("eventID").toString();
		}
 
		
	try {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		java.sql.Connection conn=null;
		Statement stmt=null;
		 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/lportal61rc1","root","tcs@12345");
        System.out.println("111111+++");
        
         stmt =conn.createStatement();
	        System.out.println("7777777777777777....");
        
		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++;

		}

	          String filename = "ViewAttendees.xls";
	          response.setProperty("application/x-excel","");
	          response.addProperty("content-disposition", "attachment; filename=" + filename);

	          OutputStream os =response.getPortletOutputStream(); 
	          hwb.write(os);
	          os.close();
	          
	    System.out.println("Final....");
	     
	} catch (Exception ex) {
      System.out.println("error.......: "+ex);
	}
	
	
	}
	

[Kalai arasan，修改在10 年前。]

I wants to know the jar file to be included for generate excel sheet?

[Archana S，修改在9 年前。]

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

[Manoj Powar，修改在10 年前。]

can you give me source for any simple liferay database project