SpringMVC:生成Excel和PDF

来源：互联网发布：淘宝客app推广编辑：程序博客网时间：2024/05/03 10:19

参考资料
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代码  
<%@ 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代码  
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代码  
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代码  
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代码  
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代码  
<?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

0 0