jsp(servlet)使用poi导出excel数据文件

浏览数：46 / 时间：2015年06月09日

网站通过Excel导出数据已经成为各种网站的一个基本功能，下面就详细介绍下如何使用poi.jar方式来导出excel文件。具体步骤如下：

1、创建一个网站，下面我创建一个叫test的网站，然后将poi的jar包放入lib文件夹。我这里是下载链接poi-2.5.1.jar.

结构目录如下：

2、然后我们可以创建一个用来访问的jsp界面（主要是提交到servlet，可有可无）

代码如下：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
  </head>
  
  <body>
    <form action="excel">
    <input value="提交" type="submit">
    </form>
  </body>
</html>

3、事先创建好的操作文档（可下载）ExcelFileGenerator

/**
 * 系统数据导出Excel 生成器
 * @version 1.0
 */
package com.whp.test;

import java.io.OutputStream;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class ExcelFileGenerator {

	private final int SPLIT_COUNT = 1500; //Excel每个工作簿的行数

	private ArrayList fieldName = null; //excel标题数据集

	private ArrayList fieldData = null; //excel数据内容	

	private HSSFWorkbook workBook = null;

	/**
	 * 构造器
	 * @param fieldName 结果集的字段名
	 * @param data
	 */
	public ExcelFileGenerator(ArrayList fieldName, ArrayList fieldData) {

		this.fieldName = fieldName;
		this.fieldData = fieldData;
	}

	/**
	 * 创建HSSFWorkbook对象
	 * @return HSSFWorkbook
	 */
	public HSSFWorkbook createWorkbook() {

		workBook = new HSSFWorkbook();
		int rows = fieldData.size();
		int sheetNum = 0;

		if (rows % SPLIT_COUNT == 0) {
			sheetNum = rows / SPLIT_COUNT;
		} else {
			sheetNum = rows / SPLIT_COUNT + 1;
		}

		for (int i = 1; i <= sheetNum; i++) {
			HSSFSheet sheet = workBook.createSheet("Page " + i);
			HSSFRow headRow = sheet.createRow((short) 0); 
			for (int j = 0; j < fieldName.size(); j++) {
				HSSFCell cell = headRow.createCell((short) j);
				//添加样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				//添加样式
				//设置所有单元格的宽度
				sheet.setColumnWidth((short)j, (short)6000);
				//创建样式(使用工作本的对象创建)
				HSSFCellStyle cellStyle = workBook.createCellStyle();
				//创建字体的对象
				HSSFFont font = workBook.createFont();
				//将字体加粗
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				//设置字体的颜色
				short color = HSSFColor.RED.index;
				font.setColor(color);
				//将新设置的字体属性放置到样式中
				cellStyle.setFont(font);
				if(fieldName.get(j) != null){
					cell.setCellStyle(cellStyle);
					cell.setCellValue((String) fieldName.get(j));
				}else{
					cell.setCellStyle(cellStyle);
					cell.setCellValue("-");
				}
			}

			for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
				HSSFRow row = sheet.createRow((short) (k + 1));
				//将数据内容放入excel单元格
				ArrayList rowList = (ArrayList) fieldData.get((i - 1)
						* SPLIT_COUNT + k);
				for (int n = 0; n < rowList.size(); n++) {
					HSSFCell cell = row.createCell((short) n);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					if(rowList.get(n) != null){
						cell.setCellValue((String) rowList.get(n).toString());
					}else{
						cell.setCellValue("");
					}
				}
			}
		}
		return workBook;
	}

	public void expordExcel(OutputStream os) throws Exception {
		workBook = createWorkbook();
		workBook.write(os);
		os.close();
	}

}

4、创建一个名称为excel的servlet. ------通过getFieldName()和getFieldData()来模拟表格数据

package com.whp.test;

import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.util.ArrayList;

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

public class excel extends HttpServlet {

	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try {
		//初始化fieldName，fieldDate
		ArrayList fieldName=getFieldName();    //excel标题数据集
		ArrayList fieldData=getFieldData();    //excel数据内容
        String myexcel="myexcel";
		//回去输出流
		OutputStream out=response.getOutputStream();
		//重置输出流
		response.reset();
		//设置导出Excel报表的导出形式
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition","attachment;filename="+myexcel+".xls");
		ExcelFileGenerator  efg=new ExcelFileGenerator(fieldName, fieldData);
		
			efg.expordExcel(out);
		//设置输出形式
		System.setOut(new PrintStream(out));
		//刷新输出流
		out.flush();
		//关闭输出流
		if(out!=null){
			out.close();
		}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	     this.doGet(request, response);
	}

	//模拟提供excel中的标题数据集
	public ArrayList getFieldName(){
		String str[]={"姓名","学号","性别"};
		ArrayList list=new ArrayList();
		for(int i=0;i<str.length;i++){
			list.add(str[i]);
		}
		return list;
	}
	//模拟提供excel中的标题数据内容
	public ArrayList getFieldData(){
		
		ArrayList list1=new ArrayList();
		String str[][]={{"wang","01","男"},{"hai","02","男"},{"ping","03","女"}};
	    
		for(int i=0;i<str.length;i++)
		{
			ArrayList list=new ArrayList();
			for(int j=0;j<str[0].length;j++)
			{
				list.add(str[i][j]);
			}
			list1.add(list);
		}
		return list1;
	}

}

5、测试：

通过localhost:8080/test/excel.jsp访问，然后点击按钮即可得到下载文件。

测试数据结果如下：