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;
	}

	@Override
	public 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时间+excelName
		if (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> {

	@Override
	public 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>{

			@Override
			public 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; //HttpResponse
	
	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 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;
	}

	@Override
	public 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时间+excelName
		if (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);
	}

}