`

用spring MVC 生成Excel和PDF

spring MVCExcelPDF 
阅读更多

 
1 用spring MVC 生成Excel和PDF
http://blog.csdn.net/linlzk/archive/2008/11/27/3389925.aspx
2 Spring MVC export data to Excel file via AbstractExcelView
http://www.mkyong.com/spring-mvc/spring-mvc-export-data-to-excel-file-via-abstractexcelview/
3 用Java的iText实现PDF报表
http://muder2007.blog.163.com/blog/static/45933070200793152351991/
使用JXL请关注:JXL2.6:解决JXL的IndexOutOfBoundsException getSheet问题,使用了修复版本的jxl包http://liuzidong.iteye.com/blog/1071677
一 工程代码结构图片

二 具体代码如下
1 index.jsp

Java代码 复制代码 收藏代码
  1. <%@ page language="java" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>   
  2. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">   
  3. <%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>   
  4. <html>   
  5.   <head>   
  6.     <script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery/jquery-1.4.4.min.js"></script>   
  7.     <%@ include file="/common/meta.jsp"%>   
  8.   </head>     
  9.   <script type="text/javascript">   
  10.     $(document).ready(function(){   
  11.         $("#exec").click(function(){   
  12.                //获取下拉框的值   
  13.                var titlesValue = "";//$("#columns").find("option:selected").text();                   
  14.                $("#columns").find("option:selected").each(function(){ //由于复选框一般选中的是多个,所以可以循环输出   
  15.                     titlesValue += ($(this).text())+",";               
  16.                });      
  17.                var names =  $("#columns").val();                  
  18.                $("#colums").val(names);   
  19.                $("#titles").val(titlesValue);                
  20.         });   
  21.     });        
  22.      
  23.   </script>   
  24.   <body>&nbsp;&nbsp; &nbsp;     
  25.         <div style="border: 1px solid #ccc; width: 50%;height:200px;align:center;margin-top:200px;margin-left:300px;padding:50px;">      
  26.               <form action="${pageContext.request.contextPath}/view/excel.do" method="post">     
  27.                 <input type="submit" value="使用POI导出Excel"><br>     
  28.               </form>        
  29.               <hr><br>   
  30.               <form method="post" action="${pageContext.request.contextPath}/view/jxlExcel.do">      
  31.                 <select id="columns" multiple="multiple" style="width:100px;height:120px;">   
  32.                     <option value="id">ID</option>   
  33.                     <option value="name">姓名</option>   
  34.                     <option value="sex">性别</option>   
  35.                     <option value="age">年龄</option>   
  36.                     <option value="password">密码</option>   
  37.                     <option value="address">地址</option>   
  38.                 </select>    
  39.                 <input type="hidden" id="titles" name="titles">   
  40.                 <input type="hidden" id="colums" name="colums">   
  41.                 <input type="submit" id="exec" value="使用JXL导出Excel"><br>                          
  42.               </form>      
  43.                <hr><br>   
  44.               <form action="${pageContext.request.contextPath}/view/pdf.do" method="post">           
  45.                 <input type="submit" value="导出PDF"><br>                    
  46.                 <br>   
  47.                 <img src="${pageContext.request.contextPath}/img/car.do" width="100px" height="50px"/>   
  48.               </form>       
  49.         </div>        
  50.   </body>   
  51. </html>  
<%@ page language="java" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>
<html>
  <head>
  	<script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery/jquery-1.4.4.min.js"></script>
    <%@ include file="/common/meta.jsp"%>
  </head>  
  <script type="text/javascript">
  	$(document).ready(function(){
  		$("#exec").click(function(){
	  		   //获取下拉框的值
	  		   var titlesValue = "";//$("#columns").find("option:selected").text();  	  		   
	  		   $("#columns").find("option:selected").each(function(){ //由于复选框一般选中的是多个,所以可以循环输出
			  	 	titlesValue += ($(this).text())+",";			
			   });   
	  		   var names = 	$("#columns").val();	  		   
	  		   $("#colums").val(names);
	  		   $("#titles").val(titlesValue);  			  
  		});
  	});  	
  
  </script>
  <body>&nbsp;&nbsp; &nbsp;  
  		<div style="border: 1px solid #ccc; width: 50%;height:200px;align:center;margin-top:200px;margin-left:300px;padding:50px;">  	
  			  <form action="${pageContext.request.contextPath}/view/excel.do" method="post">	
  			    <input type="submit" value="使用POI导出Excel"><br>  
  			  </form>     
  			  <hr><br>
  			  <form method="post" action="${pageContext.request.contextPath}/view/jxlExcel.do">	
  			    <select id="columns" multiple="multiple" style="width:100px;height:120px;">
  			  		<option value="id">ID</option>
  			  		<option value="name">姓名</option>
  			  		<option value="sex">性别</option>
  			  		<option value="age">年龄</option>
  			  		<option value="password">密码</option>
  			  		<option value="address">地址</option>
  			  	</select>	
  			  	<input type="hidden" id="titles" name="titles">
  			  	<input type="hidden" id="colums" name="colums">
  			    <input type="submit" id="exec" value="使用JXL导出Excel"><br>  	      			   
  			  </form>   
  			   <hr><br>
  			  <form action="${pageContext.request.contextPath}/view/pdf.do" method="post">		
  			    <input type="submit" value="导出PDF"><br>  			    
  			    <br>
  			    <img src="${pageContext.request.contextPath}/img/car.do" width="100px" height="50px"/>
  			  </form>    
  		</div>     
  </body>
</html>


2 ViewController.java

Java代码 复制代码 收藏代码
  1. package com.liuzd.sj.web;   
  2.   
  3. import java.util.ArrayList;   
  4. import java.util.HashMap;   
  5. import java.util.List;   
  6. import java.util.Map;   
  7.   
  8. import javax.servlet.http.HttpServletRequest;   
  9. import javax.servlet.http.HttpServletResponse;   
  10.   
  11. import org.springframework.stereotype.Controller;   
  12. import org.springframework.web.bind.annotation.RequestMapping;   
  13. import org.springframework.web.bind.annotation.RequestParam;   
  14. import org.springframework.web.servlet.ModelAndView;   
  15.   
  16. import com.liuzd.sj.entity.Student;   
  17. import com.liuzd.sj.entity.User;   
  18.   
  19. /**  
  20.  * 生成excel或PDF类型试图 根据参数进行数据组装，并跳转到相应的视图页面 View Controller Bean<br>  
  21.  */  
  22.   
  23. @Controller  
  24. @RequestMapping("/view")   
  25. public class ViewController {   
  26.   
  27.     @RequestMapping("/excel")   
  28.     public ModelAndView viewExcel(HttpServletRequest request,   
  29.             HttpServletResponse response) {   
  30.         Map model = new HashMap();         
  31.         model.put("list", getStudents());          
  32.         return new ModelAndView(new ViewExcel(), model);   
  33.     }   
  34.        
  35.     private List getStudents(){   
  36.         List stuList = new ArrayList();   
  37.         // 构造数据   
  38.         Student stu1 = new Student("gaoxiang1", "male1", "20060101", 1);   
  39.         Student stu2 = new Student("gaoxiang2", "male2", "20060102", 2);   
  40.         Student stu3 = new Student("gaoxiang3", "male3", "20060103", 3);   
  41.         Student stu4 = new Student("gaoxiang4", "male4", "20060104", 4);   
  42.         Student stu5 = new Student("gaoxiang5", "male5", "20060105", 5);       
  43.         stuList.add(stu1);   
  44.         stuList.add(stu2);   
  45.         stuList.add(stu3);   
  46.         stuList.add(stu4);   
  47.         stuList.add(stu5);   
  48.         return stuList;   
  49.     }   
  50.   
  51.     @RequestMapping("/jxlExcel")   
  52.     public ModelAndView viewJxlExcel(@RequestParam("titles") String titles,@RequestParam("colums") String colums,HttpServletRequest request,   
  53.             HttpServletResponse response) {        
  54.         String [] array1 = null;   
  55.         if(null != colums && colums.indexOf(",") != -1){   
  56.             array1 = colums.split(",");   
  57.         }   
  58.         String [] array2 = null;   
  59.         if(null != titles && titles.indexOf(",") != -1){   
  60.             array2 = titles.split(",");   
  61.         }              
  62.         Map model = new HashMap();   
  63.         // 构造数据   
  64.         List<User> users = new ArrayList<User>();   
  65.         users.add(new User("123456", "李逵", "123", "成都市", "1", 23));   
  66.         users.add(new User("123457", "李四", "124", "北京市", "2", 53));   
  67.         users.add(new User("123458", "李三", "125", "河南市", "0", 73));   
  68.         users.add(new User("123459", "李五", "126", "大路市", "3", 93));   
  69.         model.put("list", users);          
  70.         model.put("columns", array1);   
  71.         model.put("titles", array2);           
  72.         return new ModelAndView(new JXLExcelView(), model);   
  73.     }   
  74.        
  75.     @RequestMapping("/pdf")   
  76.     public ModelAndView viewPDF(HttpServletRequest request,   
  77.             HttpServletResponse response) throws Exception {   
  78.         Map model = new HashMap();         
  79.         model.put("list", getStudents());              
  80.         return new ModelAndView(new ViewPDF(), model);   
  81.     }   
  82. }  
package com.liuzd.sj.web;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import com.liuzd.sj.entity.Student;
import com.liuzd.sj.entity.User;

/**
 * 生成excel或PDF类型试图 根据参数进行数据组装，并跳转到相应的视图页面 View Controller Bean<br>
 */

@Controller
@RequestMapping("/view")
public class ViewController {

	@RequestMapping("/excel")
	public ModelAndView viewExcel(HttpServletRequest request,
			HttpServletResponse response) {
		Map model = new HashMap();		
		model.put("list", getStudents());		
		return new ModelAndView(new ViewExcel(), model);
	}
	
	private List getStudents(){
		List stuList = new ArrayList();
		// 构造数据
		Student stu1 = new Student("gaoxiang1", "male1", "20060101", 1);
		Student stu2 = new Student("gaoxiang2", "male2", "20060102", 2);
		Student stu3 = new Student("gaoxiang3", "male3", "20060103", 3);
		Student stu4 = new Student("gaoxiang4", "male4", "20060104", 4);
		Student stu5 = new Student("gaoxiang5", "male5", "20060105", 5);	
		stuList.add(stu1);
		stuList.add(stu2);
		stuList.add(stu3);
		stuList.add(stu4);
		stuList.add(stu5);
		return stuList;
	}

	@RequestMapping("/jxlExcel")
	public ModelAndView viewJxlExcel(@RequestParam("titles") String titles,@RequestParam("colums") String colums,HttpServletRequest request,
			HttpServletResponse response) {		
		String [] array1 = null;
		if(null != colums && colums.indexOf(",") != -1){
			array1 = colums.split(",");
		}
		String [] array2 = null;
		if(null != titles && titles.indexOf(",") != -1){
			array2 = titles.split(",");
		}			
		Map model = new HashMap();
		// 构造数据
		List<User> users = new ArrayList<User>();
		users.add(new User("123456", "李逵", "123", "成都市", "1", 23));
		users.add(new User("123457", "李四", "124", "北京市", "2", 53));
		users.add(new User("123458", "李三", "125", "河南市", "0", 73));
		users.add(new User("123459", "李五", "126", "大路市", "3", 93));
		model.put("list", users);		
		model.put("columns", array1);
		model.put("titles", array2);		
		return new ModelAndView(new JXLExcelView(), model);
	}
	
	@RequestMapping("/pdf")
	public ModelAndView viewPDF(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		Map model = new HashMap();		
		model.put("list", getStudents());			
		return new ModelAndView(new ViewPDF(), model);
	}
}


3 JXLExcelView.java

Java代码 复制代码 收藏代码
  1. package com.liuzd.sj.web;   
  2.   
  3. import java.io.IOException;   
  4. import java.io.OutputStream;   
  5. import java.net.URLEncoder;   
  6. import java.util.List;   
  7. import java.util.Map;   
  8.   
  9. import javax.servlet.http.HttpServletRequest;   
  10. import javax.servlet.http.HttpServletResponse;   
  11.   
  12. import jxl.Workbook;   
  13. import jxl.WorkbookSettings;   
  14. import jxl.format.Alignment;   
  15. import jxl.format.VerticalAlignment;   
  16. import jxl.write.WritableCellFormat;   
  17. import jxl.write.WritableFont;   
  18. import jxl.write.WritableSheet;   
  19. import jxl.write.WritableWorkbook;   
  20. import jxl.write.WriteException;   
  21. import jxl.write.biff.RowsExceededException;   
  22.   
  23. import org.apache.commons.beanutils.PropertyUtils;   
  24. import org.springframework.web.servlet.view.document.AbstractJExcelView;   
  25.   
  26. import com.liuzd.sj.entity.User;   
  27.   
  28.   
  29. public class JXLExcelView extends AbstractJExcelView {   
  30.   
  31.     private String[] columnNames = new String[] { "编号", "姓名", "年龄", "性别", "密码",   
  32.             "地址" };   
  33.   
  34.     private String[] dbColumnNames = new String[] { "id", "name", "age", "sex",   
  35.             "password", "address" };   
  36.   
  37.     private Integer[] columnWidths = new Integer[] { 20, 20, 20, 20, 20, 20 };   
  38.   
  39.     @Override  
  40.     public void buildExcelDocument(Map<String, Object> map,   
  41.             WritableWorkbook work, HttpServletRequest req,   
  42.             HttpServletResponse response) {   
  43.         String [] titles = (String[])map.get("titles");   
  44.         if(null != titles && titles.length > 0){   
  45.              columnNames = titles;   
  46.         }   
  47.         String [] columns = (String[])map.get("columns");   
  48.         if(null != columns &&  columns.length > 0){   
  49.             dbColumnNames = columns;   
  50.         }   
  51.            
  52.         OutputStream os = null;   
  53.         try {   
  54.   
  55.             String excelName = "用户信息.xls";   
  56.             // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开   
  57.             response.setContentType("APPLICATION/OCTET-STREAM");   
  58.             response.setHeader("Content-Disposition", "attachment; filename="  
  59.                     + URLEncoder.encode(excelName, "UTF-8"));   
  60.             os = response.getOutputStream();   
  61.             // sheet名称   
  62.             String sheetName = "用户信息";   
  63.   
  64.             // 全局设置   
  65.             WorkbookSettings setting = new WorkbookSettings();   
  66.             java.util.Locale locale = new java.util.Locale("zh", "CN");   
  67.             setting.setLocale(locale);   
  68.             setting.setEncoding("ISO-8859-1");   
  69.             // 创建工作薄   
  70.             work = Workbook.createWorkbook(os); // 建立excel文件   
  71.             // 创建第一个工作表   
  72.             jxl.write.WritableSheet ws = work.createSheet(sheetName, 1); // sheet名称   
  73.             // 添加标题   
  74.             addColumNameToWsheet(ws);   
  75.   
  76.             List<User> list = (List<User>) map.get("list");   
  77.             writeContext(ws, list);   
  78.   
  79.         } catch (Exception e) {   
  80.             e.printStackTrace();   
  81.         } finally {   
  82.   
  83.             // 写入文件   
  84.             try {   
  85.                 work.write();   
  86.                 work.close();   
  87.                 os.flush();   
  88.                 os.close();   
  89.             } catch (WriteException e) {   
  90.                 e.printStackTrace();   
  91.             } catch (IOException e) {   
  92.                 e.printStackTrace();   
  93.             }   
  94.   
  95.         }   
  96.   
  97.     }   
  98.   
  99.     private <T> void writeContext(WritableSheet wsheet, List<T> list) {   
  100.         int rows = list.size();   
  101.         jxl.write.Label wlabel = null;   
  102.         jxl.write.WritableCellFormat wcf = getFormat();   
  103.         int cols = dbColumnNames.length;   
  104.         String columnName = null;   
  105.         Object value = null;   
  106.         try {   
  107.             for (int i = 0; i < rows; i++) {   
  108.                 T t = (T) list.get(i);                 
  109.                 for (int j = 0; j < cols; j++) {   
  110.                     columnName = dbColumnNames[j].toLowerCase();   
  111.                     value = PropertyUtils.getProperty(t, columnName);   
  112.                     wlabel = new jxl.write.Label(j, (i + 1), value + "", wcf);   
  113.                     wlabel = new jxl.write.Label(j, (i + 1), value + "");   
  114.                     wsheet.addCell(wlabel);   
  115.                 }   
  116.             }   
  117.         } catch (Exception e) {   
  118.             e.printStackTrace();   
  119.         }   
  120.   
  121.     }   
  122.   
  123.     // 添加标题样式   
  124.     private void addColumNameToWsheet(jxl.write.WritableSheet wsheet)   
  125.             throws RowsExceededException, WriteException {   
  126.   
  127.         // 设置excel标题   
  128.         jxl.write.WritableFont wfont = getFont();   
  129.         if (null == wfont) {   
  130.             wfont = new WritableFont(WritableFont.ARIAL,   
  131.                     WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);   
  132.   
  133.         }   
  134.         jxl.write.WritableCellFormat wcfFC = getFormat();   
  135.         if (null == wcfFC) {   
  136.             wcfFC = new jxl.write.WritableCellFormat(wfont);   
  137.             try {   
  138.                 wcfFC.setWrap(true);// 自动换行   
  139.                 wcfFC.setAlignment(Alignment.CENTRE);   
  140.                 wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式   
  141.             } catch (WriteException e) {   
  142.                 e.printStackTrace();   
  143.             }   
  144.         }   
  145.   
  146.         jxl.write.Label wlabel1 = null;   
  147.         String[] columNames = columnNames;   
  148.         if (null == columNames)   
  149.             return;   
  150.         int colSize = columNames.length;   
  151.   
  152.         Integer[] colsWidth = columnWidths;   
  153.         if (null == colsWidth) {   
  154.             colsWidth = new Integer[colSize];   
  155.             for (int i = 0; i < colSize; i++) {   
  156.                 colsWidth[i] = 20;   
  157.             }   
  158.         }   
  159.   
  160.         int temp = 0;   
  161.         String colName = null;   
  162.         for (int i = 0; i < colSize; i++) {   
  163.             colName = columNames[i];   
  164.             if (null == colName || "".equals(colName))   
  165.                 colName = "";   
  166.             wlabel1 = new jxl.write.Label(i, 0, colName, wcfFC);   
  167.             wsheet.addCell(wlabel1);   
  168.             temp = colsWidth[i].intValue();   
  169.             // 默认设置列宽   
  170.             temp = temp == 0 ? 20 : temp;   
  171.             wsheet.setColumnView(i, temp);   
  172.         }   
  173.   
  174.     }   
  175.   
  176.     // 设置格式   
  177.     private WritableCellFormat getFormat() {   
  178.   
  179.         jxl.write.WritableFont wfont = getFont();   
  180.         jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(   
  181.                 wfont);   
  182.         try {   
  183.             wcfFC.setWrap(true);   
  184.             wcfFC.setAlignment(Alignment.CENTRE);   
  185.             wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   
  186.         } catch (WriteException e) {   
  187.             e.printStackTrace();   
  188.         }   
  189.         return wcfFC;   
  190.     }   
  191.   
  192.     // 设置字体   
  193.     private WritableFont getFont() {   
  194.         return new WritableFont(WritableFont.ARIAL,   
  195.                 WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);   
  196.     }   
  197.   
  198. }  
package com.liuzd.sj.web;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.web.servlet.view.document.AbstractJExcelView;

import com.liuzd.sj.entity.User;


public class JXLExcelView extends AbstractJExcelView {

	private String[] columnNames = new String[] { "编号", "姓名", "年龄", "性别", "密码",
			"地址" };

	private String[] dbColumnNames = new String[] { "id", "name", "age", "sex",
			"password", "address" };

	private Integer[] columnWidths = new Integer[] { 20, 20, 20, 20, 20, 20 };

	@Override
	public void buildExcelDocument(Map<String, Object> map,
			WritableWorkbook work, HttpServletRequest req,
			HttpServletResponse response) {
		String [] titles = (String[])map.get("titles");
		if(null != titles && titles.length > 0){
			 columnNames = titles;
		}
		String [] columns = (String[])map.get("columns");
		if(null != columns &&  columns.length > 0){
			dbColumnNames = columns;
		}
		
		OutputStream os = null;
		try {

			String excelName = "用户信息.xls";
			// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
			response.setContentType("APPLICATION/OCTET-STREAM");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ URLEncoder.encode(excelName, "UTF-8"));
			os = response.getOutputStream();
			// sheet名称
			String sheetName = "用户信息";

			// 全局设置
			WorkbookSettings setting = new WorkbookSettings();
			java.util.Locale locale = new java.util.Locale("zh", "CN");
			setting.setLocale(locale);
			setting.setEncoding("ISO-8859-1");
			// 创建工作薄
			work = Workbook.createWorkbook(os); // 建立excel文件
			// 创建第一个工作表
			jxl.write.WritableSheet ws = work.createSheet(sheetName, 1); // sheet名称
			// 添加标题
			addColumNameToWsheet(ws);

			List<User> list = (List<User>) map.get("list");
			writeContext(ws, list);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			// 写入文件
			try {
				work.write();
				work.close();
				os.flush();
				os.close();
			} catch (WriteException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}

	}

	private <T> void writeContext(WritableSheet wsheet, List<T> list) {
		int rows = list.size();
		jxl.write.Label wlabel = null;
		jxl.write.WritableCellFormat wcf = getFormat();
		int cols = dbColumnNames.length;
		String columnName = null;
		Object value = null;
		try {
			for (int i = 0; i < rows; i++) {
				T t = (T) list.get(i);				
				for (int j = 0; j < cols; j++) {
					columnName = dbColumnNames[j].toLowerCase();
					value = PropertyUtils.getProperty(t, columnName);
					wlabel = new jxl.write.Label(j, (i + 1), value + "", wcf);
					wlabel = new jxl.write.Label(j, (i + 1), value + "");
					wsheet.addCell(wlabel);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// 添加标题样式
	private void addColumNameToWsheet(jxl.write.WritableSheet wsheet)
			throws RowsExceededException, WriteException {

		// 设置excel标题
		jxl.write.WritableFont wfont = getFont();
		if (null == wfont) {
			wfont = new WritableFont(WritableFont.ARIAL,
					WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);

		}
		jxl.write.WritableCellFormat wcfFC = getFormat();
		if (null == wcfFC) {
			wcfFC = new jxl.write.WritableCellFormat(wfont);
			try {
				wcfFC.setWrap(true);// 自动换行
				wcfFC.setAlignment(Alignment.CENTRE);
				wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}

		jxl.write.Label wlabel1 = null;
		String[] columNames = columnNames;
		if (null == columNames)
			return;
		int colSize = columNames.length;

		Integer[] colsWidth = columnWidths;
		if (null == colsWidth) {
			colsWidth = new Integer[colSize];
			for (int i = 0; i < colSize; i++) {
				colsWidth[i] = 20;
			}
		}

		int temp = 0;
		String colName = null;
		for (int i = 0; i < colSize; i++) {
			colName = columNames[i];
			if (null == colName || "".equals(colName))
				colName = "";
			wlabel1 = new jxl.write.Label(i, 0, colName, wcfFC);
			wsheet.addCell(wlabel1);
			temp = colsWidth[i].intValue();
			// 默认设置列宽
			temp = temp == 0 ? 20 : temp;
			wsheet.setColumnView(i, temp);
		}

	}

	// 设置格式
	private WritableCellFormat getFormat() {

		jxl.write.WritableFont wfont = getFont();
		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
				wfont);
		try {
			wcfFC.setWrap(true);
			wcfFC.setAlignment(Alignment.CENTRE);
			wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return wcfFC;
	}

	// 设置字体
	private WritableFont getFont() {
		return new WritableFont(WritableFont.ARIAL,
				WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
	}

}


4 ViewExcel.java

Java代码 复制代码 收藏代码
  1. package com.liuzd.sj.web;   
  2. import java.net.URLEncoder;   
  3. import java.util.Iterator;   
  4. import java.util.List;   
  5. import java.util.Map;   
  6. import javax.servlet.http.HttpServletRequest;   
  7. import javax.servlet.http.HttpServletResponse;   
  8. import org.apache.poi.hssf.usermodel.HSSFCellStyle;   
  9. import org.apache.poi.hssf.usermodel.HSSFDataFormat;   
  10. import org.apache.poi.hssf.usermodel.HSSFRow;   
  11. import org.apache.poi.hssf.usermodel.HSSFSheet;   
  12. import org.apache.poi.hssf.usermodel.HSSFWorkbook;   
  13. import org.springframework.web.servlet.view.document.AbstractExcelView;   
  14.   
  15. import com.liuzd.sj.entity.Student;   
  16. /**  
  17. * 生成excel视图，可用excel工具打开或者保存  
  18. * 由ViewController的return new ModelAndView(viewExcel, model)生成  
  19. */  
  20. public class ViewExcel extends AbstractExcelView {      
  21.       
  22.     public void buildExcelDocument(Map model, HSSFWorkbook workbook,      
  23.             HttpServletRequest request, HttpServletResponse response)      
  24.             throws Exception {     
  25.            
  26.         String excelName = "用户信息.xls";   
  27.         // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开   
  28.         response.setContentType("APPLICATION/OCTET-STREAM");   
  29.         response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8"));     
  30.            
  31.         List stuList = (List) model.get("list");      
  32.         // 产生Excel表头   
  33.         HSSFSheet sheet = workbook.createSheet("studentList");   
  34.         HSSFRow header = sheet.createRow(0); // 第0行   
  35.         // 产生标题列   
  36.         header.createCell((short) 0).setCellValue("name");   
  37.         header.createCell((short) 1).setCellValue("sex");   
  38.         header.createCell((short) 2).setCellValue("date");   
  39.         header.createCell((short) 3).setCellValue("count");   
  40.         HSSFCellStyle cellStyle = workbook.createCellStyle();   
  41.         cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy"));   
  42.   
  43.         // 填充数据   
  44.         int rowNum = 1;   
  45.         for (Iterator iter = stuList.iterator(); iter.hasNext();) {   
  46.             Student element = (Student) iter.next();   
  47.             HSSFRow row = sheet.createRow(rowNum++);   
  48.             row.createCell((short) 0)   
  49.                     .setCellValue(element.getName().toString());   
  50.             row.createCell((short) 1).setCellValue(element.getSex().toString());   
  51.             row.createCell((short) 2)   
  52.                     .setCellValue(element.getDate().toString());   
  53.             row.getCell((short) 2).setCellStyle(cellStyle);   
  54.             row.createCell((short) 3).setCellValue(element.getCount());   
  55.         }   
  56.   
  57.         // 列总和计算   
  58.         HSSFRow row = sheet.createRow(rowNum);   
  59.         row.createCell((short) 0).setCellValue("TOTAL:");   
  60.         String formual = "SUM(D2:D" + rowNum + ")"; // D2到D[rowNum]单元格起(count数据)   
  61.         row.createCell((short) 3).setCellFormula(formual);   
  62.     }      
  63. }  
package com.liuzd.sj.web;
import java.net.URLEncoder;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.liuzd.sj.entity.Student;
/**
* 生成excel视图，可用excel工具打开或者保存
* 由ViewController的return new ModelAndView(viewExcel, model)生成
*/
public class ViewExcel extends AbstractExcelView {   
   
    public void buildExcelDocument(Map model, HSSFWorkbook workbook,   
            HttpServletRequest request, HttpServletResponse response)   
            throws Exception {  
    	
    	String excelName = "用户信息.xls";
		// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8"));	
		
		List stuList = (List) model.get("list");   
		// 产生Excel表头
		HSSFSheet sheet = workbook.createSheet("studentList");
		HSSFRow header = sheet.createRow(0); // 第0行
		// 产生标题列
		header.createCell((short) 0).setCellValue("name");
		header.createCell((short) 1).setCellValue("sex");
		header.createCell((short) 2).setCellValue("date");
		header.createCell((short) 3).setCellValue("count");
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy"));

		// 填充数据
		int rowNum = 1;
		for (Iterator iter = stuList.iterator(); iter.hasNext();) {
			Student element = (Student) iter.next();
			HSSFRow row = sheet.createRow(rowNum++);
			row.createCell((short) 0)
					.setCellValue(element.getName().toString());
			row.createCell((short) 1).setCellValue(element.getSex().toString());
			row.createCell((short) 2)
					.setCellValue(element.getDate().toString());
			row.getCell((short) 2).setCellStyle(cellStyle);
			row.createCell((short) 3).setCellValue(element.getCount());
		}

		// 列总和计算
		HSSFRow row = sheet.createRow(rowNum);
		row.createCell((short) 0).setCellValue("TOTAL:");
		String formual = "SUM(D2:D" + rowNum + ")"; // D2到D[rowNum]单元格起(count数据)
		row.createCell((short) 3).setCellFormula(formual);
    }   
}


5 ViewPDF.java

Java代码 复制代码 收藏代码
  1. package com.liuzd.sj.web;   
  2. import java.net.URLEncoder;   
  3. import java.util.List;   
  4. import java.util.Map;   
  5.   
  6. import javax.servlet.http.HttpServletRequest;   
  7. import javax.servlet.http.HttpServletResponse;   
  8.   
  9. import org.springframework.web.servlet.view.document.AbstractPdfView;   
  10.   
  11. import com.liuzd.sj.entity.Student;   
  12. import com.lowagie.text.Document;   
  13. import com.lowagie.text.Paragraph;   
  14. import com.lowagie.text.pdf.BaseFont;   
  15. import com.lowagie.text.pdf.PdfWriter;   
  16. /**  
  17. * 生成PDF视图，可用PDF浏览器打开或者保存  
  18. * 由ViewController的return new ModelAndView(viewPDF, model)生成  
  19. * @version Version 1.0  
  20. */  
  21. public class ViewPDF extends AbstractPdfView {      
  22.     public void buildPdfDocument(Map model, Document document,      
  23.             PdfWriter writer, HttpServletRequest request,      
  24.             HttpServletResponse response) throws Exception {      
  25.      
  26.         String excelName = "用户信息.pdf";   
  27.         // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开   
  28.         response.setContentType("APPLICATION/OCTET-STREAM");   
  29.         response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8"));     
  30.            
  31.         List stuList = (List) model.get("list");             
  32.         //显示中文   
  33.         BaseFont bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);    
  34.         com.lowagie.text.Font FontChinese = new com.lowagie.text.Font(bfChinese, 12, com.lowagie.text.Font.NORMAL );           
  35.      
  36.         String value = null;   
  37.         for (int i = 0; i < stuList.size(); i++) {     
  38.             Student s = (Student)stuList.get(i);   
  39.             value = "姓名: "+ s.getName()+",性别: "+s.getSex() + ",日期: " + s.getDate() + ",总数: " + s.getCount();   
  40.             document.add(new Paragraph(value,FontChinese));      
  41.         }   
  42.     }      
  43. }  
package com.liuzd.sj.web;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.view.document.AbstractPdfView;

import com.liuzd.sj.entity.Student;
import com.lowagie.text.Document;
import com.lowagie.text.Paragraph;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.pdf.PdfWriter;
/**
* 生成PDF视图，可用PDF浏览器打开或者保存
* 由ViewController的return new ModelAndView(viewPDF, model)生成
* @version Version 1.0
*/
public class ViewPDF extends AbstractPdfView {   
    public void buildPdfDocument(Map model, Document document,   
            PdfWriter writer, HttpServletRequest request,   
            HttpServletResponse response) throws Exception {   
  
    	String excelName = "用户信息.pdf";
		// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8"));	
		
        List stuList = (List) model.get("list");          
		//显示中文
		BaseFont bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED); 
		com.lowagie.text.Font FontChinese = new com.lowagie.text.Font(bfChinese, 12, com.lowagie.text.Font.NORMAL ); 		
  
		String value = null;
        for (int i = 0; i < stuList.size(); i++) {  
        	Student s = (Student)stuList.get(i);
        	value = "姓名: "+ s.getName()+",性别: "+s.getSex() + ",日期: " + s.getDate() + ",总数: " + s.getCount();
        	document.add(new Paragraph(value,FontChinese));   
        }
    }   
}


6 springmvc.xml

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:p="http://www.springframework.org/schema/p"  
  5.     xmlns:context="http://www.springframework.org/schema/context"  
  6.     xmlns:mvc="http://www.springframework.org/schema/mvc"  
  7.     xsi:schemaLocation="   
  8.         http://www.springframework.org/schema/beans    
  9.         http://www.springframework.org/schema/beans/spring-beans-3.0.xsd   
  10.         http://www.springframework.org/schema/context    
  11.         http://www.springframework.org/schema/context/spring-context-3.0.xsd   
  12.         http://www.springframework.org/schema/mvc       
  13.         http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">  
  14.     <!--    
  15.         自动搜索@Controller标注的类   
  16.         用于指明系统从哪个路径下寻找controller，然后提前初始化这些对象。   
  17.     -->  
  18.     <context:component-scan base-package="com.liuzd.sj.web" />       
  19.     <mvc:annotation-driven/>             
  20.   
  21.     <!--  ③：对模型视图名称的解析，即在模型视图名称添加前后缀 -->  
  22.     <bean  
  23.         class="org.springframework.web.servlet.view.InternalResourceViewResolver"  
  24.         p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />     
  25.       
  26. </beans>   
<?xml version="1.0" encoding="UTF-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="
        http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/mvc    
        http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
	<!-- 
		自动搜索@Controller标注的类
		用于指明系统从哪个路径下寻找controller，然后提前初始化这些对象。
	-->
	<context:component-scan base-package="com.liuzd.sj.web" />	
	<mvc:annotation-driven/>   		

	<!--  ③：对模型视图名称的解析，即在模型视图名称添加前后缀 -->
	<bean
		class="org.springframework.web.servlet.view.InternalResourceViewResolver"
		p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />	
   
</beans>


三 附件为工程源代码http://dl.iteye.com/topics/download/23bd290d-405a-3e9b-b557-28c3773bdeb3
四 相关jar包下载
jackson-all-1.8.1.jar请参见:SpringMVC:整合JQUERY与JSON
http://liuzidong.iteye.com/blog/1069343
jexcelapi_2_6_12_1.jar
http://dl.iteye.com/topics/download/4662fd6c-4dee-3dba-ac12-509ff9323258
iText-POI.zip中包含jar包有:iText-2.1.2.jar,iTextAsian.jar,poi-3.7-20101029.jar
http://dl.iteye.com/topics/download/a919b53f-615d-3b67-b790-32b4e0fec85f

分享到：
| POI3.8组件研究（一）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    用spring MVC 生成Excel和PDF.docx

    HTML页面并不总是向用户显示数据输出的最好方式，Spring支持从数据动态生成PDF或Excel文件，并使这一过程变得简单。文档本身就是视图，从服务器以流的方式加上内容类型返回文档，客户端PC只要运行电子表格软件或PDF...

    Spring-Reference_zh_CN（Spring中文参考手册）

    2.5.1. Spring MVC的表单标签库 2.5.2. Spring MVC合理的默认值 2.5.3. Portlet 框架 2.6. 其他特性 2.6.1. 动态语言支持 2.6.2. JMX 2.6 .3. 任务规划 2.6.4. 对Java 5（Tiger）的支持 2.7. 移植到Spring 2.0 ...

    Spring中文帮助文档

    14.6. 文档视图（PDF/Excel） 14.6.1. 简介 14.6.2. 配置和安装 14.7. JasperReports 14.7.1. 依赖的资源 14.7.2. 配置 14.7.3. 构造ModelAndView 14.7.4. 使用子报表 14.7.5. 配置Exporter的参数 15. 集成...

    Spring 2.0 开发参考手册

    14.6. 文档视图（PDF/Excel） 14.6.1. 简介 14.6.2. 配置和安装 14.7. JasperReports 14.7.1. 依赖的资源 14.7.2. 配置 14.7.3. 构造ModelAndView 14.7.4. 使用子报表 14.7.5. 配置Exporter的参数 15. 集成...

    Spring API

    14.6. 文档视图（PDF/Excel） 14.6.1. 简介 14.6.2. 配置和安装 14.7. JasperReports 14.7.1. 依赖的资源 14.7.2. 配置 14.7.3. 构造ModelAndView 14.7.4. 使用子报表 14.7.5. 配置Exporter的参数 15. 集成...

    Spring攻略(第二版 中文高清版).part1

    8.13　创建Excel和PDF视图　344 8.13.1　问题　344 8.13.2　解决方案　345 8.13.3　工作原理　345 8.14　小结　351 第9章　Spring REST　352 9.1　用Spring发布一个REST服务　352 9.1.1　问题　352 9.1.2　解决方案...

    spring chm文档

    14.6. 文档视图（PDF/Excel） 14.6.1. 简介 14.6.2. 配置和安装 14.7. JasperReports 14.7.1. 依赖的资源 14.7.2. 配置 14.7.3. 构造ModelAndView 14.7.4. 使用子报表 14.7.5. 配置Exporter的参数 15. 集成...

    Spring攻略(第二版 中文高清版).part2

    8.13　创建Excel和PDF视图　344 8.13.1　问题　344 8.13.2　解决方案　345 8.13.3　工作原理　345 8.14　小结　351 第9章　Spring REST　352 9.1　用Spring发布一个REST服务　352 9.1.1　问题　352 9.1.2　解决方案...

    SpringBoot-Web-Mvc-Security：以Spring方式构建企业Java应用程序

    搜索和排序数据使用select2 ajax远程数据的分页结果发送带有附件的电子邮件模板多种语言的Web应用程序生成PDF和Excel文件使用Ajax以一种形式上传数据和文件建立和运行mvn packagejava -jar SpringBoot-0.0.1-...

    基于jbpm与activiti的工作流平台技术架构介绍

    系统的报表管理是基于模板来进行设置管理的，目前支持FineReport及Jasper Report两种报表引擎，前者是商业报表，其功能非常强大，可以实现多样式数据呈现方式，支持HTML、PDF、EXCEL、Word、TXT、Flash样式呈，能...

Magicbox
Global site tag (gtag.js) - Google Analytics