Friday, November 12, 2010

Exporting Excel From Spring 3 Using JExcel and Template

Spring 3 has way much simplified the way Web applications are built. Spring MVC supports AbstractJExcel view to export data as Excel.

Lets go thru it step by step

The Configuration for this project is going to be : Eclipse IDE, Maven, Spring 3.0.3 Release and JExcel API.

Step 1 : Maven : Building POM

   1: <?xml version="1.0" encoding="UTF-8"?>
   2: <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   3:     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
   4:     <modelVersion>4.0.0</modelVersion>
   5:     <groupId>org.springframework.samples</groupId>
   6:     <artifactId>mvc-ajax</artifactId>
   7:     <name>mvc-ajax</name>
   8:     <packaging>war</packaging>
   9:     <version>1.0.0-SNAPSHOT</version>
  10:     <properties>
  11:         <org.springframework.version>3.0.3.RELEASE</org.springframework.version>
  12:         <org.slf4j.version>1.5.10</org.slf4j.version>
  13:     </properties>
  14:     <dependencies>
  15:  
  16:         <!--  Spring -->
  17:         <dependency>
  18:             <groupId>org.springframework</groupId>
  19:             <artifactId>spring-context</artifactId>
  20:             <version>${org.springframework.version}</version>
  21:             <exclusions>
  22:                 <!-- Exclude Commons Logging in favor of SLF4j -->
  23:                 <exclusion>
  24:                     <groupId>commons-logging</groupId>
  25:                     <artifactId>commons-logging</artifactId>
  26:                  </exclusion>
  27:             </exclusions>
  28:         </dependency>
  29:         <dependency>
  30:             <groupId>org.springframework</groupId>
  31:             <artifactId>spring-webmvc</artifactId>
  32:             <version>${org.springframework.version}</version>
  33:         </dependency>
  34:  
  35:         <!-- Logging -->
  36:         <dependency>
  37:             <groupId>org.slf4j</groupId>
  38:             <artifactId>slf4j-api</artifactId>
  39:             <version>${org.slf4j.version}</version>
  40:         </dependency>
  41:         <dependency>
  42:             <groupId>org.slf4j</groupId>
  43:             <artifactId>jcl-over-slf4j</artifactId>
  44:             <version>${org.slf4j.version}</version>
  45:             <scope>runtime</scope>
  46:         </dependency>
  47:         <dependency>
  48:             <groupId>org.slf4j</groupId>
  49:             <artifactId>slf4j-log4j12</artifactId>
  50:             <version>${org.slf4j.version}</version>
  51:             <scope>runtime</scope>
  52:         </dependency>
  53:         <dependency>
  54:             <groupId>log4j</groupId>
  55:             <artifactId>log4j</artifactId>
  56:             <version>1.2.16</version>
  57:             <scope>runtime</scope>
  58:         </dependency>
  59:  
  60:         <!--  JSR 303 with Hibernate Validator -->
  61:         <dependency>
  62:             <groupId>javax.validation</groupId>
  63:             <artifactId>validation-api</artifactId>
  64:             <version>1.0.0.GA</version>
  65:         </dependency>
  66:         <dependency>
  67:             <groupId>org.hibernate</groupId>
  68:             <artifactId>hibernate-validator</artifactId>
  69:             <version>4.0.2.GA</version>
  70:         </dependency>
  71:  
  72:         <!--  Joda Time -->
  73:         <dependency>
  74:             <groupId>joda-time</groupId>
  75:             <artifactId>joda-time</artifactId>
  76:             <version>1.6</version>
  77:             <scope>runtime</scope>
  78:         </dependency>
  79:         
  80:         <!-- Jackson JSON Mapper -->
  81:         <dependency>
  82:             <groupId>org.codehaus.jackson</groupId>
  83:             <artifactId>jackson-mapper-asl</artifactId>
  84:             <version>1.5.3</version>
  85:         </dependency>
  86:         
  87:         <!--  Jexcel -->
  88:          <dependency>
  89:             <groupId>net.sourceforge.jexcelapi</groupId>
  90:             <artifactId>jxl</artifactId>
  91:             <version>2.6.3</version>        
  92:         </dependency>
  93:         <!--  URL Rewrite -->
  94:         <dependency>
  95:             <groupId>org.tuckey</groupId>
  96:             <artifactId>urlrewritefilter</artifactId>
  97:             <version>3.1.0</version>
  98:         </dependency>
  99:  
 100:         <!-- Servlet -->
 101:         <dependency>
 102:             <groupId>javax.servlet</groupId>
 103:             <artifactId>servlet-api</artifactId>
 104:             <version>2.5</version>
 105:             <scope>provided</scope>
 106:         </dependency>
 107:         <dependency>
 108:             <groupId>javax.servlet.jsp</groupId>
 109:             <artifactId>jsp-api</artifactId>
 110:             <version>2.1</version>
 111:             <scope>provided</scope>
 112:         </dependency>
 113:         <dependency>
 114:             <groupId>javax.servlet</groupId>
 115:             <artifactId>jstl</artifactId>
 116:             <version>1.2</version>
 117:         </dependency>
 118:  
 119:         <!-- Test -->
 120:         <dependency>
 121:             <groupId>junit</groupId>
 122:             <artifactId>junit</artifactId>
 123:             <version>4.7</version>
 124:             <scope>test</scope>
 125:         </dependency>
 126:         <dependency>
 127:             <groupId>org.springframework</groupId>
 128:             <artifactId>spring-test</artifactId>
 129:             <version>${org.springframework.version}</version>
 130:             <scope>test</scope>
 131:         </dependency>
 132:     </dependencies>
 133:     <repositories>
 134:         <!-- For Hibernate Validator -->
 135:         <repository>
 136:             <id>org.jboss.repository.release</id>
 137:             <name>JBoss Maven Release Repository</name>            
 138:             <url>https://repository.jboss.org/nexus/content/repositories/releases</url>
 139:             <snapshots><enabled>false</enabled></snapshots>            
 140:         </repository>        
 141:     </repositories>
 142:     <build>
 143:         <plugins>
 144:             <plugin>
 145:                 <groupId>org.apache.maven.plugins</groupId>
 146:                 <artifactId>maven-compiler-plugin</artifactId>
 147:                 <configuration>
 148:                     <source>1.5</source>
 149:                     <target>1.5</target>
 150:                     <showWarnings>true</showWarnings>
 151:                 </configuration>
 152:             </plugin>
 153:             <plugin>
 154:                 <groupId>org.apache.maven.plugins</groupId>
 155:                 <artifactId>maven-dependency-plugin</artifactId>
 156:                 <executions>
 157:                     <execution>
 158:                         <id>install</id>
 159:                         <phase>install</phase>
 160:                         <goals>
 161:                             <goal>sources</goal>
 162:                         </goals>
 163:                     </execution>
 164:                 </executions>
 165:             </plugin>
 166:         </plugins>
 167:     </build>
 168: </project>


Step 2: Building Eclipse Project

Run commands 1. mvn eclipse:eclipse -Dwtpversion=1.5 and Import the Project

Step 3: Creating the Web.xml


   1: <?xml version="1.0" encoding="UTF-8"?>
   2: <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
   3:     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   4:     xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
   5:  
   6:     <!-- Handles all requests into the application -->
   7:     <servlet>
   8:         <servlet-name>Spring MVC Dispatcher Servlet</servlet-name>
   9:         <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  10:         <init-param>
  11:             <param-name>contextConfigLocation</param-name>
  12:             <param-value>
  13:                 /WEB-INF/spring/app-config.xml
  14:             </param-value>
  15:         </init-param>
  16:         <load-on-startup>1</load-on-startup>
  17:     </servlet>
  18:  
  19:     <servlet-mapping>
  20:         <servlet-name>Spring MVC Dispatcher Servlet</servlet-name>
  21:         <url-pattern>/app/*</url-pattern>
  22:     </servlet-mapping>
  23:  
  24: </web-app>


Step 4: Creating Spring Controller and Abstract JExceView



   1: //Controller class
   2: package com.abc.controllers;
   3: @Controller
   4: @RequestMapping(value="/wk")
   5: public class WorksheetController {
   6:  
   7:     @RequestMapping(value = "/exworksheet/{sessionId}")
   8:     public ModelAndView getWorksheet(@PathVariable String sessionId) {
   9:         Map<String,String> revenueData = new HashMap<String,String>();
  10:         revenueData.put("Jan-2010", "$100,000,000");
  11:         revenueData.put("Feb-2010", "$110,000,000");
  12:         revenueData.put("Mar-2010", "$130,000,000");
  13:         revenueData.put("Apr-2010", "$140,000,000");
  14:         revenueData.put("May-2010", "$200,000,000");
  15:         return new ModelAndView("ExcelRevenueSummary","revenueData",revenueData);
  16:     }
  17:         
  18: }    
  19:  
  20: /////View Class
  21:  
  22: package com.abc.view;
  23:  
  24: import jxl.write.*
  25:  
  26: public class ExcelWorkbookView extends AbstractJExcelView{
  27:  
  28: public ExcelWorkbookView() {
  29:         this.setUrl("http://localhost/excel_book1"); //Path where the excel_book.xls template is stored
  30:     }
  31: protected void buildExcelDocument(Map model, WritableWorkbook workbook,
  32:             HttpServletRequest request, HttpServletResponse response)
  33:             throws Exception {
  34:             WritableCellFormat wcfCurrency = new WritableCellFormat(new NumberFormat("#,##0.00"));
  35:             //Note the workbook is getting built from a template which already has the existing worksheet
  36:             WritableSheet ws = workbook.getSheet('Asset-Liability Inventory');
  37:             WritableCell  wc = new Number(1,6,9999.0,wcfCurrency);
  38:             ws.addCell(wc);
  39:     }
  40:  
  41: }



Step 5 : Bind the Controller with View, app-config.xml


   1: <?xml version="1.0" encoding="UTF-8"?>
   2: <beans xmlns="http://www.springframework.org/schema/beans"
   3:     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   4:     xmlns:mvc="http://www.springframework.org/schema/mvc"
   5:     xsi:schemaLocation="
   6:         http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   7:         http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
   8:  
   9:     <!-- Configures the @Controller programming model -->
  10:     <mvc:annotation-driven />
  11:  
  12:     <!-- Forwards requests to the "/" resource to the "welcome" view -->
  13:     <mvc:view-controller path="/" view-name="welcome"/>
  14:  
  15:  
  16:    <!-- Resolves view names to protected .jsp resources within the /WEB-INF/views directory -->
  17:     <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  18:         <property name="prefix" value="/WEB-INF/views/"/>
  19:         <property name="suffix" value=".jsp"/>
  20:         <property name="order" value="1"/>
  21:     </bean>
  22:     
  23:     <bean id="ExcelSSummary" class="org.springframework.web.servlet.view.XmlViewResolver">
  24:        <property name="location">
  25:            <value>/WEB-INF/spring-excel-views.xml</value>
  26:        </property>
  27:       <property name="order" value="0"/>
  28:     </bean>
  29:  
  30: </beans>

Step 6: Spring-excel-views.xml :

This is where the ViewName gets binded to the controller

Step 7 : Copy the Tempalte file to the WEBContent folder, and make sure the Url opens the Excel file.



   1: <beans xmlns="http://www.springframework.org/schema/beans"
   2:     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   3:     xsi:schemaLocation="http://www.springframework.org/schema/beans 
   4:     http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
   5:  
   6:     <bean id="ExcelRevenueSummary"
   7:            class="com.abc.ExcelReportView"> <!--View name which extends JExcelAPI-->
   8:     </bean>
   9:  
  10: </beans>


Step 8 : Test it as Url http://localhost:8080/app/wk/exworksheet


VOILA!! DONE!! With Spring 3 annotations everything glues together like a magic.

No comments:

Post a Comment