SpringMVC:生成Excel和PDF

2/10/2017来源:ASP.NET技巧人气:1584

原文地址:http://liuzidong.iteye.com/blog/1071823

参考资料

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

4 报表相关知识:http://www.anyrt.com/blog/index.html

一 工程代码结构图片

二 具体代码如下 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

SpringMVCJXL_project_.zip (78.9 KB)

iText-POI.zip (2.7 MB)