SpringMVC:生成Excel和PDF

2019-11-10 18:05:56

字體：大中小

來源：轉載

供稿：網(wǎng)友

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

參考資料

1 用sPRing MVC 生成Excel和PDFhttp://blog.csdn.net/linlzk/archive/2008/11/27/3389925.aspx2 Spring MVC export data to Excel file via AbstractExcelViewhttp://www.mkyong.com/spring-mvc/spring-mvc-export-data-to-excel-file-via-abstractexcelview/3 用java的iText實現(xiàn)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(){ //由于復選框一般選中的是多個,所以可以循環(huán)輸出 titlesValue += ($(this).text())+","; }); var names = $("#columns").val(); $("#colums").val(names); $("#titles").val(titlesValue); }); }); </script> <body>     <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.javaJava代碼

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類型試圖根據(jù)參數(shù)進行數(shù)據(jù)組裝，并跳轉到相應的視圖頁面 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(); // 構造數(shù)據(jù) 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(); // 構造數(shù)據(jù) 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.javaJava代碼

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方式,使執(zhí)行此controller時候自動出現(xiàn)下載頁面,而非直接使用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"); // 創(chuàng)建工作薄 work = Workbook.createWorkbook(os); // 建立excel文件 // 創(chuàng)建第一個工作表 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.javaJava代碼

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方式,使執(zhí)行此controller時候自動出現(xiàn)下載頁面,而非直接使用excel打開 response.setContentType("APPLICATION/OCTET-STREAM"); response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8")); List stuList = (List) model.get("list"); // 產(chǎn)生Excel表頭 HSSFSheet sheet = workbook.createSheet("studentList"); HSSFRow header = sheet.createRow(0); // 第0行 // 產(chǎn)生標題列 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")); // 填充數(shù)據(jù) 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數(shù)據(jù)) row.createCell((short) 3).setCellFormula(formual); } } 5 ViewPDF.javaJava代碼

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方式,使執(zhí)行此controller時候自動出現(xiàn)下載頁面,而非直接使用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() + ",總數(shù): " + s.getCount(); document.add(new Paragraph(value,FontChinese)); } } } 6 springmvc.xmlXml代碼

<?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">  <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與JSONhttp://liuzidong.iteye.com/blog/1069343jexcelapi_2_6_12_1.jarhttp://dl.iteye.com/topics/download/4662fd6c-4dee-3dba-ac12-509ff9323258iText-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)

上一篇：圖的一些基本概念

下一篇：Ubuntu 12.04 IP 配置