Fetch all the data from DB to Excel sheet

[Riya M S, modifié il y a 11 années.]

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

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

Or write a Report with JasperReports.

[Riya M S, modifié il y a 11 années.]

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

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

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

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

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

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

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

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

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

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

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

[Archana S, modifié il y a 9 années.]

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

can you give me source for any simple liferay database project