JavaEE_POI导出Excel (网络下载) +（本地导出） 工具类的编写

博主这几天忙活了报表数据用Excel导出的功能：

这里给出这个Demo的下载地址（MyEclipse2014 下开发，喜欢的朋友在文章下面评论 或给个赞）

http://pan.baidu.com/s/1hr9qcLY

涉及到了几方面的知识，总结如下：

1.POI 导出Excel

2.JavaEE 通过HttpResponseSevlet 实现文件下载

3.Excel 文件名下载中文的显示

http://blog.csdn.net/u010003835/article/details/50857611

1.POI 导出Excel:

先讲解步骤：

（1）.导入POI包 本文这里用的是POI 3.9   链接   http://pan.baidu.com/s/1i4xAAjz

（2）.POI 创建 Excel

  0.  创建工作本

// 0.创建工作本HSSFWorkbook excelWorkBook = new HSSFWorkbook();

  1.  创建Excel表

  excelName是指定当前Excel的表名

                // 1.创建表HSSFSheet excelSheet = null;if (this.excelName == null) {this.excelName = new String();}excelSheet = excelWorkBook.createSheet(this.excelName);

  2.   创建表头并设置表头项

  表头即第0行,其他的数据即从第1行开始，当然可以不创建表头

// 2.创建表头: 创建一行HSSFRow headerRow = excelSheet.createRow((short) 0);for (int i = 0; i < this.excelHeaders.length; i++) {// 创建一个单元格HSSFCell headerCell = headerRow.createCell((short) i);// headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);// CellStyle cs = new CellStyle();// 设置cell的值headerCell.setCellValue(excelHeaders[i]);}

 3.  根据查询出来的值(JavaBean)设置单元格

// 3.根据查询出来的结果集results，填写excel表格if (results != null) {T objectT = null;for (int index = 0; index < results.size(); index++) {// 4.创建一行HSSFRow tableRow = excelSheet.createRow((short) index + 1); // 创建行，因为第一行是表头,// 即row(0)objectT = results.get(index);//this.selectedTableCells(tableRow, objectT);// ********** selectedTableCell的大致实现 *********** HSSFRow row = demoSheet.createRow((short) index); for (short i = 0; i < cells.size(); i++) { // 创建第i个单元格 HSSFCell cell = row.createCell((short) i); // cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(cells.get(i)); }}}

 4.  导出Excel文件，通过IO流

//判断是下载到指定路径，还是网络下载，//本地下载，创建本地文件流，//否则，利用repsonse的文件流if(this.getResponse() == null){OutputStream ioFileStream = null;try {ioFileStream = new FileOutputStream(this.filePath + this.fileName +".xls");excelWorkBook.write(ioFileStream);ioFileStream.flush();ioFileStream.close();} catch (Exception e) {e.printStackTrace();}}

（3）.一个完整的栗子（还用到了JDBC连接。。。，不熟悉的同学去网上找个栗子，需要JDBC链接包）

简单例子：存粹的POI导出Excel

文档目录结构：

JavaBean: User

package bean;public class User {private Integer index;private String userName;private String password;public Integer getIndex() {return index;}public void setIndex(Integer index) {this.index = index;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public static void main(String[] args) {User user = null;user.getIndex();}}

Excel导出类:  ExcelUtilVersionTwo.java

package util;import java.io.FileOutputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import javax.swing.JOptionPane;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public abstract class ExcelUtilVersionTwo<T> implements TableCells<T> {private String[] excelHeaders = new String[] {};// excel表头private String excelName = new String(); // execl表名private Integer cellsNum = 0; // 导出的单元格有几列private String fileName = new String(); // 导出的文件的名字public String[] getExcelHeaders() {return excelHeaders;}public void setExcelHeaders(String[] excelHeaders) {this.excelHeaders = excelHeaders;}public String getExcelName() {return excelName;}public void setExcelName(String excelName) {this.excelName = excelName;}public Integer getCellsNum() {return cellsNum;}public void setCellsNum(Integer cellsNum) {this.cellsNum = cellsNum;}public String getFileName() {return fileName;}public void setFileName(String fileName) {this.fileName = fileName;}@Overridepublic abstract void selectedTableCells(HSSFRow tableRow, T t);@SuppressWarnings("deprecation")public void exportExcel(List<T> results) {// 0.创建工作本HSSFWorkbook excelWorkBook = new HSSFWorkbook();// 1.创建表HSSFSheet excelSheet = null;if (this.excelName == null) {this.excelName = new String();}excelSheet = excelWorkBook.createSheet(this.excelName);// 2.创建表头: 创建一行HSSFRow headerRow = excelSheet.createRow((short) 0);for (int i = 0; i < this.excelHeaders.length; i++) {// 创建一个单元格HSSFCell headerCell = headerRow.createCell((short) i);// headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);// CellStyle cs = new CellStyle();// 设置cell的值headerCell.setCellValue(excelHeaders[i]);}// 3.根据查询出来的结果集results，填写excel表格if (results != null) {T objectT = null;for (int index = 0; index < results.size(); index++) {// 4.创建一行HSSFRow tableRow = excelSheet.createRow((short) index + 1); // 创建行，因为第一行是表头,// 即row(0)objectT = results.get(index);this.selectedTableCells(tableRow, objectT);// ********** selectedTableCell的大致实现 ***********// HSSFRow row = demoSheet.createRow((short) index);// for (short i = 0; i < cells.size(); i++) {// // 创建第i个单元格// HSSFCell cell = row.createCell((short) i);// // cell.setEncoding(HSSFCell.ENCODING_UTF_16);// cell.setCellValue(cells.get(i));// }}}// 4.将excel导出到文件中FileOutputStream out = null;// 如果没有名字则文件名为data时间+excelNameif (this.fileName.equals(new String())) {SimpleDateFormat tmp = new SimpleDateFormat("yyyyMMddHHmmssSSS");String dateStr = tmp.format(new Date());this.setFileName(dateStr + this.getExcelName());}try {out = new FileOutputStream(fileName);// excelSheet.setGridsPrinted(true);// HSSFFooter footer = excelSheet.getFooter();// footer.setRight("Page " + HSSFFooter.page() + " of "// + HSSFFooter.numPages());excelWorkBook.write(out);JOptionPane.showMessageDialog(null, "表格已成功导出到 : " + fileName);} catch (Exception e) {JOptionPane.showMessageDialog(null, "表格导出出错，错误信息 ：" + e+ "\n错误原因可能是表格已经打开。");e.printStackTrace();}}public void exportExcel(List<T> results, String[] headers, String excelName) {this.setExcelHeaders(headers);this.setExcelName(excelName);this.exportExcel(results);}/** * @note 导出excel报表 * @param results *            查询出来的结果集 * @param headers *            表格的头 * @param excelName *            excel报表名 * @param fileName *            导出的excel文件名 */public void exportExcel(List<T> results, String[] headers,String excelName, String fileName) {this.setExcelHeaders(headers);this.setExcelName(excelName);this.setFileName(fileName);this.exportExcel(results);}public static void main(String[] args) {Date date = new Date();String fileName = "D:\\用户导出报表" + date.toString() + ".xls";System.out.println(fileName);String[] list = new String[] {};System.out.println(list.length);}}

接口： TableCells<T>

package util;import org.apache.poi.hssf.usermodel.HSSFRow;/** * @author szh * @date 2016-3-9 * @param <T> */public interface TableCells<T> {/** * @note 该接口在ExcelUtil中被实现，主要功能： 根据 T(po类)指定字段 设置tableRow的数据, *       并可以做一定的业务处理，（更好的实现方式，在ExcelUtil中做成抽象方法） * @param tableRow * @param t */void selectedTableCells(HSSFRow tableRow, T t);}

导出Excel测试类：ExportExcelUser

package service;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import util.ExcelUtilVersionTwo;import bean.User;public class ExportExcelUser extends ExcelUtilVersionTwo<User> {@Overridepublic void selectedTableCells(HSSFRow tableRow, User t) {HSSFCell indexCell = tableRow.createCell(0);indexCell.setCellValue(t.getIndex());HSSFCell userNameCell = tableRow.createCell(1);userNameCell.setCellValue(t.getUserName());HSSFCell passCell = tableRow.createCell(2);passCell.setCellValue(t.getPassword());}public List<User> getAllUser() {List<User> useList = new ArrayList<User>();try {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");Statement sm = conn.createStatement();ResultSet rs = sm.executeQuery("select id,name,password from user");while (rs.next()) {List<String> list = new ArrayList<String>();for (int i = 1; i <= 3; i++) {list.add(rs.getString(i));}User user = new User();user.setIndex(Integer.valueOf(list.get(0)));user.setUserName(list.get(1));user.setPassword(list.get(2));useList.add(user);}} catch (Exception e) {e.printStackTrace();}return useList;}public static void main(String[] args) {ExportExcelUser exportExcelUser = new ExportExcelUser();List<User> list = exportExcelUser.getAllUser();for (int i = 0; i < list.size(); i++) {System.out.println(list.get(i).getIndex() + " "+ list.get(i).getUserName() + " "+ list.get(i).getPassword());}exportExcelUser.exportExcel(list, new String[] { "序号", "用户名", "密码" },"用户信息表", "D:\\user表信息.xls");}}

表结构：

测试用例：

2.JavaEE 通过HttpResponseSevlet 实现文件下载

http://www.cnblogs.com/xdp-gacl/p/4200090.html

这里结合前后台给大家讲解一下： 项目中用到的框架是Spring+SpringMvc+Mybatis

前台页面：

前台页面中的Html 部分

点击导出按钮的js效果：（注意这里不能通过JQuery Post,Get等Ajax方法，因为JavaScrip中没有文件流的概念，讲解请参考博文  

1.   JAVA WEB用servlet下载文件不能弹出对话框

http://bbs.csdn.net/topics/390630180

2. http://www.jb51.net/article/53479.htm）

/* 导出excel */$(function(){$("#exportExcel").click(function(){var url = appCtx+"userClassDailyTotal/exportExcel.do"+"?"+$("#listForm").serialize();//将表单序列化提交window.location.href = url;});});

后台SpringMvc

Controller部分：

/** * @param userClassMonthlyTotal * @param request * @param response * @param session * @return * @throws Exception */@SuppressWarnings("unused")@RequestMapping(value = "/exportExcel.do")public ModelAndView exportExcel(UserClassMonthlyTotal userClassMonthlyTotal, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception {Object obj = session.getAttribute(ConstantAdmin.SESSION_ADMIN_INFO);if (obj instanceof UserOrganization) {UserOrganization userOrg = (UserOrganization) obj;int organizationId = userOrg.getOrganizationId();userClassMonthlyTotal.setOrganizationId(organizationId);} else if (obj instanceof SysUser) {SysUser user = (SysUser) obj;}userClassMonthlyTotalService.selectExportExcel(userClassMonthlyTotal, response);return null;}

Service部分：

@Transactional(propagation=Propagation.NOT_SUPPORTED)public void selectExportExcel(UserClassMonthlyTotal userClassMonthlyTotal, HttpServletResponse response){List<UserClassMonthlyTotal> list = userClassMonthlyTotalMapper.selectBySelectiveNoPage(userClassMonthlyTotal);class UserClassMonthlyStatExcel extends ExcelUtilVersionTwo<UserClassMonthlyTotal>{@Overridepublic void selectedTableCells(HSSFRow tableRow,UserClassMonthlyTotal t) {DecimalFormat floatConvert = new DecimalFormat("##0.00");SimpleDateFormat monthConvert = new SimpleDateFormat("yyyy-MM");tableRow.createCell(0).setCellValue(t.getUserName());tableRow.createCell(1).setCellValue(floatConvert.format(t.getShouldHour()));tableRow.createCell(2).setCellValue(floatConvert.format(t.getActualHour()));tableRow.createCell(3).setCellValue(floatConvert.format(t.getOvertimeHour()));tableRow.createCell(4).setCellValue(floatConvert.format(t.getAbsentDay()));tableRow.createCell(5).setCellValue(t.getDelayCount());tableRow.createCell(6).setCellValue(t.getEarlyLeaveCount());tableRow.createCell(7).setCellValue(monthConvert.format(t.getUserClassMonth()));}}UserClassMonthlyStatExcel userMonthlyStat = new UserClassMonthlyStatExcel();SimpleDateFormat convert = new SimpleDateFormat("yyyyMMddHHmmssSSS");Date now = new Date();String nowString = convert.format(now);userMonthlyStat.exportExcel(list, new String[]{"用户名","应工作时长(时)","实际工作时长(时)","加班时长(时)","缺勤时长(时)","迟到次数(次)","早退次数(次)","统计月份"},"每月统计报表", nowString+"每月统计报表", response);}

编写的接口：selectTableCells

主要功能：根据业务实现对查询出来的数据怎么处理，设置到Excel 单元格中。

package cn._2vin.yannan.util;import org.apache.poi.hssf.usermodel.HSSFRow;/** * @author szh * @date 2016-3-9 * @param <T> */public interface TableCells<T> {/** * @note 该接口在ExcelUtil中被实现，主要功能： 根据 T(po类)指定字段 设置tableRow的数据, *       并可以做一定的业务处理，（更好的实现方式，在ExcelUtil中做成抽象方法） * @param tableRow * @param t */void selectedTableCells(HSSFRow tableRow, T t);}

包装的接口类：可以实现下载数据到本地指定路径和从网络下载两种功能。里面涵盖了中文文件名下载解决地方法：http://blog.csdn.net/u010003835/article/details/50857611

package cn._2vin.yannan.util;import java.io.FileOutputStream;import java.io.OutputStream;import java.io.UnsupportedEncodingException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public abstract class ExcelUtilVersionTwo<T> implements TableCells<T> {private String[] excelHeaders = new String[] {};// excel表头private String excelName = new String(); // execl表名private String filePath = new String();// 存储到本地的实际路径private String fileName = new String(); // 导出的文件的名字private HttpServletResponse response = null; //HttpResponsepublic String[] getExcelHeaders() {return excelHeaders;}public void setExcelHeaders(String[] excelHeaders) {this.excelHeaders = excelHeaders;}public String getExcelName() {return excelName;}public void setExcelName(String excelName) {this.excelName = excelName;}public String getFilePath() {return filePath;}public void setFilePath(String filePath) {this.filePath = filePath;}public String getFileName() {return fileName;}public void setFileName(String fileName) {this.fileName = fileName;}private HttpServletResponse getResponse() {return response;}private void setResponse(HttpServletResponse response) {this.response = response;}@Overridepublic abstract void selectedTableCells(HSSFRow tableRow, T t);@SuppressWarnings("deprecation")private void exportExcel(List<T> results){// 0.创建工作本HSSFWorkbook excelWorkBook = new HSSFWorkbook();// 1.创建表HSSFSheet excelSheet = null;if (this.excelName == null) {this.excelName = new String();}excelSheet = excelWorkBook.createSheet(this.excelName);// 2.创建表头: 创建一行HSSFRow headerRow = excelSheet.createRow((short) 0);for (int i = 0; i < this.excelHeaders.length; i++) {// 创建一个单元格HSSFCell headerCell = headerRow.createCell((short) i);// headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);// CellStyle cs = new CellStyle();// 设置cell的值headerCell.setCellValue(excelHeaders[i]);}// 3.根据查询出来的结果集results，填写excel表格if (results != null) {T objectT = null;for (int index = 0; index < results.size(); index++) {// 4.创建一行HSSFRow tableRow = excelSheet.createRow((short) index + 1); // 创建行，因为第一行是表头,// 即row(0)objectT = results.get(index);this.selectedTableCells(tableRow, objectT);// ********** selectedTableCell的大致实现 ***********// HSSFRow row = demoSheet.createRow((short) index);// for (short i = 0; i < cells.size(); i++) {// // 创建第i个单元格// HSSFCell cell = row.createCell((short) i);// // cell.setEncoding(HSSFCell.ENCODING_UTF_16);// cell.setCellValue(cells.get(i));// }}}// 4.将excel导出到文件中// 如果没有名字则文件名为data时间+excelNameif (this.fileName.equals(new String()) || this.fileName==null) {SimpleDateFormat tmp = new SimpleDateFormat("yyyyMMddHHmmssSSS");String dateStr = tmp.format(new Date());this.setFileName(dateStr + this.getExcelName());}//判断是下载到指定路径，还是网络下载，//本地下载，创建本地文件流，//否则，利用repsonse的文件流if(this.getResponse() == null){OutputStream ioFileStream = null;try {ioFileStream = new FileOutputStream(this.filePath + this.fileName +".xls");excelWorkBook.write(ioFileStream);ioFileStream.flush();ioFileStream.close();} catch (Exception e) {e.printStackTrace();}}else{this.response.setContentType("application/vnd.ms-excel");this.response.setHeader("Cache-Control", "no-store");SimpleDateFormat createDayConvert = new SimpleDateFormat("yyyyMMddHHmmssSSS");//日期转换器String fileString = createDayConvert.format(new Date())+this.excelName + ".xls";//解决中文乱码问题try {response.setHeader("Content-Disposition", "attachment; filename="+ new String( fileString.getBytes("utf-8"), "ISO8859-1" ));} catch (UnsupportedEncodingException e1) {e1.printStackTrace();}OutputStream ioWebStream = null;try {ioWebStream = response.getOutputStream();excelWorkBook.write(ioWebStream);ioWebStream.flush();ioWebStream.close();} catch (Exception e) {e.printStackTrace();}}}/** * @note 从页面上下载excel表格 * @param results  查询出来的结果集 * @param headers  表格的头 * @param excelName excel表名 * @param fileName 导出的excel文件名 * @param response HttpServletResponse */public void exportExcel(List<T> results, String[] headers, String excelName, String fileName, HttpServletResponse response) {this.setExcelHeaders(headers);this.setExcelName(excelName);this.setFileName(fileName);this.setResponse(response);this.exportExcel(results);}/** * @note 导出excel报表到本地的指定路径 * @param results  查询出来的结果集 * @param headers  表格的头 * @param excelName excel表名 * @param filePath 本地的存储路径 * @param fileName 导出的excel文件名 */public void exportExcel(List<T> results, String[] headers,String excelName, String filePath, String fileName) {this.setExcelHeaders(headers);this.setExcelName(excelName);this.setFilePath(filePath);this.setFileName(fileName);this.exportExcel(results);}}