java poi导出excel文件

1,ExcelReportHandler类

package com.poi.excel;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/**
 * 导出excel数据
 * @author Administrator
 *
 */
public abstract class ExcelReportHandler {

	/**
	 * 默认的头部样式
	 */
	protected HSSFCellStyle defaultHeadCellStyle;

	/**
	 * 默认的体部样式
	 */
	protected HSSFCellStyle defaultBodyCellStye;

	protected HSSFFont defaultHeadFont;

	protected HSSFFont defaultBodyFont;

	/**
	 * 导出excel文件
	 * @param response 
	 * @param headTitle 标题列
	 * @param fileName 文件名称
	 * @param list 数据集合
	 * @throws Exception
	 */
	public void exportExcel(HttpServletResponse response, String[] headTitle, String fileName, List list) throws Exception {
		response.reset();
		response.setHeader("Content-disposition", "attachment; filename=\"" + fileName + ".xls\"");// 设置response的Header
		ServletOutputStream out = response.getOutputStream();// 得到输出流
		HSSFWorkbook wb = new HSSFWorkbook();// 实例化工作薄对象
		defaultHeadCellStyle = wb.createCellStyle();
		defaultBodyCellStye = wb.createCellStyle();
		defaultHeadFont = wb.createFont();
		defaultBodyFont = wb.createFont();
		HSSFSheet sheet = wb.createSheet();// 创建sheet
		createHead(sheet, headTitle);// 创建execl头
		createBody(sheet, list);// 创建execl体
		wb.write(out);// 把工作薄写入到输出流
		out.flush();// 刷新流
		out.close();// 关闭流
	}

	/**
	 * 创建标题列
	 * @param sheet
	 * @param headTitle
	 * @throws IOException
	 */
	protected void createHead(HSSFSheet sheet, String[] headTitle) throws IOException {
		Row row = sheet.createRow(0);
		Cell cell = null;
		for (int i = 0; i < headTitle.length; i++) {
			cell = row.createCell((short) i);
			cell.setCellValue(headTitle[i]);
			cell.setCellStyle(getHeadCellStyle());
		}
	}

	/**
	 * 默认的excel头样式
	 * @return
	 */
	protected HSSFCellStyle getHeadCellStyle() {
		defaultHeadCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中
		defaultHeadFont.setFontHeightInPoints((short) 13);// 设置字体样式
		defaultHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		defaultHeadCellStyle.setFont(defaultHeadFont);
		return defaultHeadCellStyle;
	}

	/**
	 * 获取默认的excel体样式
	 * @return
	 */
	protected HSSFCellStyle getBodyCellStyle() {
		defaultBodyCellStye.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中
		defaultBodyFont.setFontHeightInPoints((short) 12);// 设置字体样式
		defaultBodyCellStye.setFont(defaultBodyFont);
		return defaultBodyCellStye;
	}

	/**
	 * 导出excel体内容
	 * @param sheet
	 * @param list
	 */
	public abstract void createBody(HSSFSheet sheet, List list);
}
           

2,ExportUserListExcel类

package com.poi.excel;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/**
 * 导出excel数据
 * @author Administrator
 *
 */
public class ExportUserListExcel extends ExcelReportHandler{

	@Override
	public void createBody(HSSFSheet sheet, List list) {
		Row row = null;
		Cell cell = null;
		Integer rowIndex = 0;
		for (int i = 0; i < list.size(); i++) {
			User user = (User)list.get(i);
			rowIndex ++;
			row = sheet.createRow(rowIndex);  //创建行
			cell = row.createCell((short) 0);  	    //创建单元格
			cell.setCellValue(i+1);
			cell.setCellStyle(getBodyCellStyle());
		
			cell = row.createCell((short) 1);
			cell.setCellValue(user.getName());
			cell.setCellStyle(getBodyCellStyle());
			
			cell = row.createCell((short) 2);
			cell.setCellValue(user.getEmail());
			cell.setCellStyle(getBodyCellStyle());
			
			cell = row.createCell((short) 3);
			cell.setCellValue(user.getPhone());
			cell.setCellStyle(getBodyCellStyle());
			
			cell = row.createCell((short) 4);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			cell.setCellValue(sdf.format(new Date()));
			cell.setCellStyle(getBodyCellStyle());
		}
	}

}
           

3,ExcelControler类

package com.poi.excel;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class ExcelControler
 */
@WebServlet("/ExcelControler")
public class ExcelControler extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ExcelControler() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=UTF-8");
		doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		try {
			List<User> list = new ArrayList<User>();
			User user = null;
			for(int i=0;i<100;i++){
				user = new User();
				user.setId(i);
				user.setAddress("中国广东深圳宝安区");
				user.setName("护腕"+i);
				user.setEmail("[email protected]");
				user.setPhone("125859067906798"+i);
				user.setSex((i%2==0? "男" : "女"));
				list.add(user);
			}
			ExportUserListExcel execl = new ExportUserListExcel();
			execl.exportExcel(response,new String[] {"序号","名称","邮箱","电话","时间"},"user list",list);
		} catch (Exception e) {
			response.getWriter().print("导出excel有误!");
		}
	}

}
           

第三个类为servlet,导入项目之后直接调用:http://localhost:8080/demo/ExcelControler

请自行下载POI  jar包 本项目中用的是: poi-3.9.jar