Exporting to Excel :JXLS

Exporting to Excel :JXLS

Posted by Sayan Guharoy

JXLS helps you format your report excel sheet with a template & a few lines of code. 

 1. First design a template with required formatting, coloring and other report specific changes. Below is a very basic template of employees.xls. 

 

2. Put this excel under C:\Report_ Export 

3. Add the following jars in your class path

Jxls-core-1.0.jar	Download
Poi-3.6-20091214.jar	Download
Commons-beanutils-1.7.0.jar	Download
poi-ooxml-3.6-20091214.jar	In POI Package

Additional Jars

commons-digester-2.1.jar	Download
commons-logging.jar	Download
Commons-jexl-2.1.1.jar	Download

4. Write your java code The Export report method

//this method is getting called from ajax request servlet which extends http servlet I am passing servletContext here from my Ajax servlet

public String exportReport(String servletContext) {
  
 
 Connection   connection  = null;
 ResultSet rs = null;

//report drive will give me the drive letter where I will save my generated 
Reports

String reportdrive=servletContext.substring(0, 2);

//I will overwriting the file if it exists ,if you want to retain all exported files use time stamp
     
String newfilename="report_output.xls";

//path for my template file name

String templateFileName =servletContext+"report-templates/envirowatch.xls";

//I will creating a folder under my reportdrive which is also my tomcat root drive

String desdirectry=reportdrive+"\\result_reports";
   
File f1 = new File(desdirectry2);
if (!f1.exists()) new File(desdirectry2).mkdir();   

//path for my destination file, where I will placing my exported report
  
String destFileName = desdirectry+"\\"+newfilename;
     
try {

//below is a simple jdbc connection pattern where I will be connecting to my database & extract beans and to the Staff arraylist

Class.forName("oracle.jdbc.OracleDriver");

connection = DriverManager.getConnection("your connection string”);

PreparedStatement ps = null;
ps   = connection.prepareStatement(DataManager.getQuery("your query"));
rs = ps.executeQuery();
List staff = new ArrayList();
        
while (rs.next()) {
 
// add your result set to employee beans

 Employee bean123 = transformResultsBean(rs);
 staff.add(bean123);
 }       

//finally I will add my array list of beans to resultReportbeans  

Map beans = new HashMap();
beans.put("employee", staff);

//lets initiate jxls transformer

XLSTransformer transformer = new XLSTransformer();

//we will catch any exception if it fails to write the destination file

try{ transformer.transformXLS( templateFileName, beans, destFileName);}

catch (IOException e)
  {   e.printStackTrace();
  }
    
}//end of try   
   
   catch (SQLException e) 
            {
      e.printStackTrace();
  }
  
 return newfilename;
 }

Remember: Result report Beans which are used in the template xls uses property name of the result set beans. Be sure that the property exists in the employee bean. It behaves very similar to JSTL.

public class Employee {

    private String name;
    private int age;
    private Double payment;
    private Double bonus;
    private Date birthDate;
    private Employee superior;

public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Double getPayment() {
        return payment;
    }

    public void setPayment(Double payment) {
        this.payment = payment;
    }

}

5. Additionally if we want a hyperlink that will allow access of this report from client side browser we will create a url pattern in the servlet and a java class that will open the exported file copy the content and stream the content in the response, also we will set the response header mime type to xls so that browser recognizes it and give user a option to save or open the excel. Let write the java file first

import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

//our servlet will extend HttpServlet  

public class MyServlet extends HttpServlet{
 
  private static final int BYTES_DOWNLOAD = 1024;
 
  public void doGet(HttpServletRequest request, 
   HttpServletResponse response) throws IOException{
 
//set the content type as excel

response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition",
                     "attachment;filename=downloadname.xls");

//we will get report drive same way we are doing while creating the report

ServletContext ctx = getServletContext();
String    reportdrive=getServletContext().getRealPath("/").toString().substring(0, 2);
 
String desdirectry=reportdrive+"\\result_reports";
String newfilename="report_output.xls";
String destFileName = desdirectry+"\\"+newfilename;

FileInputStream fstream = new FileInputStream(destFileName2);

// Get the object of DataInputStream

DataInputStream is = new DataInputStream(fstream);

 int read=0;
 byte[] bytes = new byte[BYTES_DOWNLOAD];
 OutputStream os = response.getOutputStream();
 
while((read = is.read(bytes))!= -1){
  os.write(bytes, 0, read);
 }
 os.flush();
 os.close(); 
   }
}

Let’s add the URL pattern in Web xml.

<servlet>
 <servlet-name>ServletName</servlet-name>
 <servlet-class>com.osg.sqe.env.framework.control.web.MyServlet</servlet-class> 
</servlet>

<servlet-mapping>
 <servlet-name>ServletName</servlet-name>
 <url-pattern>/DownloadDemo</url-pattern>
</servlet-mapping>

Finally we will place a hyperlink to our html page to initiate the download

http://localhost:port/modulename/DownloadDemo

Few more points while designing your jxls template 

 

In the above excel, students is a list of beans individually representing a particular student with ${students.subamarks} (subject a marks) and ${students.subbmarks} (subject b marks) To get sum total of marks obtained by all student in SUBJECT A We have to use $[SUM(D8)] But to get sum total of all subject marks obtained by individaula students We have to use $[D8+E8]//students