POI导出Excel2003

最新推荐文章于 2025-09-11 23:50:21 发布

weixin_33720078

最新推荐文章于 2025-09-11 23:50:21 发布

阅读量57

点赞数

CC 4.0 BY-SA版权

文章标签： java python 开发工具

原文链接：https://my.oschina.net/4k9LCGA/blog/93412

本文介绍如何使用Java和Apache POI库将数据导出为Excel文件，并将其作为客户端附件发送。通过创建工作簿、设置样式、填充数据和调整列宽等步骤，实现了从数据库获取信息并导出至Excel的功能。

2019独角兽企业重金招聘Python工程师标准>>>

首先说一下环境：Windows 7、JDK 6、MyEclipse 6.0、POI 3.8（下载地址：http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.8-20120326.zip）

用到的jar包：

实体类BasicInfo.java代码：

package com.project.bean;

public class Basicinfo {

	private String name;//姓名
	private String sex;//性别
	private String depart;//部门
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getDepart() {
		return depart;
	}
	public void setDepart(String depart) {
		this.depart = depart;
	}
}

导出Excel的Servlet ExportExcel.java代码：

package com.project.servlet;

import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

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 com.project.bean.Basicinfo;

public class ExportExcel extends HttpServlet {

	public ExportExcel() {super();}

	public void destroy() {super.destroy();}

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		this.ProcessRequest(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		this.ProcessRequest(request, response);
	}
	
	/**
	 * 导出Excel
	 * @param request
	 * @param response
	 */
	public void ProcessRequest(HttpServletRequest request,HttpServletResponse response){
		java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("yyyyMMddhhmmss");
		String now = format.format(new Date());
		String exportFileName = "信息导出_"+now+".xls";//导出文件名
		List<Basicinfo> list = getInfoList();
		HSSFWorkbook workBook = null;
		String[] cellTitle = {"序号", "姓名", "性别", "部门"};
		try {
			workBook = new HSSFWorkbook();//创建工作薄
			HSSFSheet sheet = workBook.createSheet();
			workBook.setSheetName(0, "订单信息");//工作簿名称
			HSSFFont font = workBook.createFont();  
			font.setColor(HSSFFont.COLOR_NORMAL);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			HSSFCellStyle cellStyle = workBook.createCellStyle();//创建格式
			cellStyle.setFont(font);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			//创建第一行标题 
			HSSFRow titleRow = sheet.createRow((short) 0);//第一行标题
			for(int i = 0,size = cellTitle.length; i < size; i++){//创建第1行标题单元格    
				sheet.setColumnWidth(i,4000);
				HSSFCell cell = titleRow.createCell(i,0);        
				cell.setCellStyle(cellStyle);
				cell.setCellValue(cellTitle[i]);
			}
	        //从第二行开始写入数据
	        for(int i=1,size = list.size();i<size;i++){
	        	HSSFRow row = sheet.createRow((short) i);
	        	Basicinfo entity = list.get(i);
	        	for (int j = 0,length=cellTitle.length; j < length; j++) {
	        		HSSFCell cell = row.createCell(j, 0);// 在上面行索引0的位置创建单元格
	    			cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
	    			switch(j){// 在单元格中输入一些内容
	    			case 0:
	    				cell.setCellValue(String.valueOf(i));
	    				break;
	    			case 1:
	    				cell.setCellValue(entity.getName());
	    				break;
	    			case 2:
	    				cell.setCellValue(entity.getSex());
	    				break;
	    			case 3:
	    				cell.setCellValue(entity.getDepart());
	    				break;
	    			}
				}
	        }
	    	
			// 表示以附件的形式把文件发送到客户端
			response.setHeader("Content-Disposition", "attachment;filename=" + new String((exportFileName).getBytes(), "ISO8859-1"));//设定输出文件头
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型 
			
			// 通过response的输出流把工作薄的流发送浏览器形成文件
			OutputStream outStream = response.getOutputStream();
			workBook.write(outStream);
			outStream.flush();
		}catch(IOException e){
			System.out.println("IO 异常！"+e.getMessage());
			e.printStackTrace();
		}
	}
	
	/**
	 * 模拟数据库获取信息
	 * @return
	 */
	public List<Basicinfo> getInfoList(){
		List<Basicinfo> list = new ArrayList();
		for(int i=1;i<101;i++){
			Basicinfo entity = new Basicinfo();
			entity.setName("员工"+i);
			entity.setSex(i%2==1?"男":"女");
			entity.setDepart(i>80?"销售部":"财务部");
			list.add(entity);
		}
		return list;
	}

	public void init() throws ServletException {}
	
}

转载于:https://my.oschina.net/4k9LCGA/blog/93412