Export jsp output to excel

[suresh76]

I am having reports generated in jsp with HTML codings in tabular format. On report displayed page I have to use one button , which on click has to export to excel sheet.

I tried using below code.

String mimeType = "application/vnd.ms-excel";
response.setContentType(mimeType);
response.setHeader("content-disposition","attachment; filename=totalpurgeexcel.xls");

But the problem I am facing is , in the report page, header and footer are also their. So while exporting header and footer are also saved in excel.

Another problem is I have a main page where link to particular report is displayed. When I click on one report link itself its showing me popup for saving, its not displaying the report generated page, all datas in that are saving in excel.

Please help me with suitable solution.

[Dököll]

Greetins!

You can try something like this, pretty generic code but you will need Apache's API called POI to b able to relate to Microsoft's file types, a few simple jar files. You will also need Tomcat Server for this to work. Add the jar files in Tomcat lib directory:

....Call this jsp file whatever ou want for starters
 
<%@ page language="java" import="java.io.*" %>
 
...Begin your form post to AddToExcel.jsp page
...A simple textfield t record the actuall excel file name
 
        <form method="post" action="AddToExcel.jsp">
        <p><font color="#800000" size="5">Enter File Name Here:</font>
        <input type="text" name="excel_sheet_name" size="20"></p>
        <p><input type="submit" value="Submit"         
               onclick="document.location='AddToExcel.jsp';"/>
         </p>
 
        </form>
 
 

Here is your AddToExcel.jsp:

 
...import necessary libraries
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
      <%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
       <%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
 
...import io, tells system to recieve a file
 
          <%@ page import="java.io.*" %>
 
...grab te excel file name
 
<%String name=request.getParameter("excel_sheet_name");%>
 
...create a workbook, a couple of cells, name yoursheet<%try{
 
           HSSFWorkbook wb = new HSSFWorkbook();
           HSSFSheet sheet = wb.createSheet("MyFirstExcelSheet");
           HSSFRow row = sheet.createRow((short)0);
           HSSFCell cell = row.createCell((short)0);
 
...add values to your cells, notice you are adding to 4 cells here
 
           cell.setCellValue(1);
           row.createCell((short)1).setCellValue("AddSomeTextHere");
           row.createCell((short)2).setCellValue("AddSomeTextHere");
           row.createCell((short)3).setCellValue("AddSomeTextHere");
           FileOutputStream fileOut = new FileOutputStream("C:\\"+name+".xls");
 
          wb.write(fileOut);
          fileOut.close(); 
          }catch ( Exception ex ){ 
} 
 
%>
 
Excel File added...
 

and there you have it... Hope this gives you a idea. You can then grab data from say an Access database and load to excel ta way, as needed:-)

Happy coding!