poi操作Excel的一个小例子

访问servlet下载这个excel文件，使用poi生成一个excel文件，需要导出poi相关jar包

package com.sb.utils;

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;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

public class ExcelPoiUtils {

	/**
	 * desc 生成Excel
	 * author qiulongjie
	 * time 2015-1-7 下午3:46:08
	 * @param title 标题
	 * @param evaluator 评估人
	 * @param evaluationTime 评估时间 
	 * @param targetValue 目标值
	 * @param completeValue 达成值
	 * @param targetStatus 目标完成情况
	 * @param reportDesc 报告简要描述
	 * @param applicationDesc 应用标签描述
	 * @param targetAnalysis 目标达成分析
	 * @return
	 */
	public static HSSFWorkbook createWorkbook(String title,
			                                  String evaluator,
			                                  String evaluationTime,
			                                  String targetValue,
			                                  String completeValue,
			                                  String targetStatus,
			                                  String reportDesc,
			                                  String applicationDesc,
			                                  String targetAnalysis
			                                  ){
		//创建work
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		
		// 设置列宽:
		sheet.setColumnWidth(0, 4766);
		
		// 标题样式
		HSSFCellStyle titleCellStyle = workbook.createCellStyle();

		titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index);// 设置背景色
		titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		
		// 标题字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFFont.COLOR_NORMAL);
		font.setFontHeightInPoints((short) 18);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		titleCellStyle.setFont(font);
		
		// 边框样式
		HSSFCellStyle borderCellStyle = workbook.createCellStyle();

		borderCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		borderCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		borderCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		borderCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		
		//  第一行
		HSSFRow row = sheet.createRow(0);
		row.setHeightInPoints(20);
		HSSFCell cell = creatStringTypeCell(row,0,title,borderCellStyle);
		cell.setCellStyle(titleCellStyle);
		rangeCells(sheet, 0, 0, 0, 5);
		
		// 第二行
		HSSFRow row1 = sheet.createRow(1);
		creatStringTypeCell(row1,0,"评估人",borderCellStyle);
		creatStringTypeCell(row1,1,evaluator,borderCellStyle);
		rangeCells(sheet, 1, 1, 1, 2);
		creatStringTypeCell(row1,3,"评估时间",borderCellStyle);
		creatStringTypeCell(row1,4,evaluationTime,borderCellStyle);
		rangeCells(sheet, 1, 1, 4, 5);
		
		// 第三行
        HSSFRow row2 = sheet.createRow(2);
		creatStringTypeCell(row2,0,"",borderCellStyle);
		rangeCells(sheet, 2, 2, 0, 5);
		
		// 第4行
		HSSFRow row3 = sheet.createRow(3);
		creatStringTypeCell(row3,0,"目标值",borderCellStyle);
		creatStringTypeCell(row3,1,targetValue,borderCellStyle);
		creatStringTypeCell(row3,2,"达成值",borderCellStyle);
		creatStringTypeCell(row3,3,completeValue,borderCellStyle);
		creatStringTypeCell(row3,4,"目标完成情况",borderCellStyle);
		creatStringTypeCell(row3,5,targetStatus,borderCellStyle);
		
		// 第5行
        HSSFRow row4 = sheet.createRow(4);
		creatStringTypeCell(row4,0,"",borderCellStyle);
		rangeCells(sheet,4, 4, 0, 5);
		
		// 第6行
		HSSFRow row5 = sheet.createRow(5);
		creatStringTypeCell(row5,0,"报告简要描述",borderCellStyle);
		rangeCells(sheet,5, 5, 0, 5);
		
		// 第7行
        HSSFRow row6 = sheet.createRow(6);
		creatStringTypeCell(row6,0,reportDesc,borderCellStyle);
		rangeCells(sheet,6, 6, 0, 5);
		
		// 第8行
		HSSFRow row7 = sheet.createRow(7);
		creatStringTypeCell(row7,0,"应用标签描述",borderCellStyle);
		rangeCells(sheet,7, 7, 0, 5);
		
		// 第9行
		HSSFRow row8 = sheet.createRow(8);
		creatStringTypeCell(row8,0,applicationDesc,borderCellStyle);
		rangeCells(sheet,8, 8, 0, 5);
		
		// 第10行
		HSSFRow row9 = sheet.createRow(9);
		creatStringTypeCell(row9,0,"目标达成分析",borderCellStyle);
		rangeCells(sheet,9, 9, 0, 5);
		
		// 第11行
		HSSFRow row10 = sheet.createRow(10);
		creatStringTypeCell(row10,0,targetAnalysis,borderCellStyle);
		rangeCells(sheet,10, 10, 0, 5);
		
		return workbook;
	}

	/**
	 * desc 合并单位格
	 * author qiulongjie
	 * time 2015-1-7 下午3:34:37
	 * @param sheet
	 * @param firstRow
	 * @param lastRow
	 * @param firstCol
	 * @param lastCol
	 */
	private static void rangeCells(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol) {
		CellRangeAddress range = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
		sheet.addMergedRegion(range);
		setRegionBorder(1,range,sheet,sheet.getWorkbook());
	}
	
	/**
	 * desc 设置合并单元格的边框
	 * author qiulongjie
	 * time 2015-1-7 下午5:26:46
	 * @param border
	 * @param region
	 * @param sheet
	 * @param wb
	 */
	private static void setRegionBorder(int border, CellRangeAddress region, Sheet sheet,Workbook wb){
		RegionUtil.setBorderBottom(border,region, sheet, wb);
		RegionUtil.setBorderLeft(border,region, sheet, wb);
		RegionUtil.setBorderRight(border,region, sheet, wb);
		RegionUtil.setBorderTop(border,region, sheet, wb);
	}

	/**
	 * desc 创建单元格
	 * author qiulongjie
	 * time 2015-1-7 下午3:35:04
	 * @param row
	 * @param colIndex
	 * @param value
	 * @return
	 */
	public static HSSFCell creatStringTypeCell(HSSFRow row,int colIndex,String value,HSSFCellStyle style) {
		HSSFCell cell = row.createCell(colIndex);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		cell.setCellStyle(style);
		return cell;
	}
}

servlet

package com.sb.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

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.HSSFWorkbook;

import com.sb.utils.ExcelPoiUtils;

public class DownloadSBservlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public DownloadSBservlet() {
        super();
    }

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

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String path = this.getClass().getClassLoader().getResource("/").getPath();
		System.out.println(path);
		path = path.substring(1,path.lastIndexOf("WEB-INF/classes/"));
		path = path+"export"+File.separator+"sb.xls";
		File file = new File(path);
		if(!file.getParentFile().exists()){
			file.getParentFile().mkdirs();
		}
		if(!file.exists()){
			file.createNewFile();
		}
		
		FileOutputStream out = new FileOutputStream(file);
		
		HSSFWorkbook workbook = ExcelPoiUtils.createWorkbook("标题","嘻嘻嘻","sas","eee","dfsf","gbn","kk","uiui","adw");
		workbook.write(out);
		out.flush();
		out.close();
		
		downFile(file,response);
	}
	
	// 下载文件到客户端
	private void downFile(File file,HttpServletResponse response) throws IOException {
		// 写数据到客户端
        response.setContentType("application/octet-stream");
        response.setHeader("Cache-Control","must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        //设置超时时间
        response.setDateHeader("Expires",(System.currentTimeMillis() + 5000));
        response.addHeader("Content-Disposition","attachment; filename=\""+ file.getName() + "\"");
		FileInputStream in = new FileInputStream(file);
		OutputStream out = response.getOutputStream();
		int len = 0;
		byte[] buffer = new byte[1024];
		while((len=in.read(buffer))!=-1){
			out.write(buffer, 0, len);
			out.flush();
		}
		out.close();
		in.close();
	}

}

官方api-guide地址

http://poi.apache.org/spreadsheet/quick-guide.html

提供一些颜色类型是在HSSFColor里面定义（来源http://blog.csdn.net/hantiannan/article/details/5312133）

色	执行结果
HSSFColor.ROYAL_BLUE
HSSFColor.TEAL
HSSFColor.LIME
HSSFColor.PALE_BLUE
HSSFColor.AQUA
HSSFColor.GREEN
HSSFColor.TURQUOISE
HSSFColor.DARK_BLUE
HSSFColor.CORNFLOWER_BLUE
HSSFColor.OLIVE_GREEN
HSSFColor.WHITE
HSSFColor.LIGHT_TURQUOISE
HSSFColor.LEMON_CHIFFON
HSSFColor.LIGHT_GREEN
HSSFColor.BLUE
HSSFColor.DARK_RED
HSSFColor.CORAL
HSSFColor.RED
HSSFColor.LIGHT_YELLOW
HSSFColor.SKY_BLUE
HSSFColor.BROWN
HSSFColor.SEA_GREEN
HSSFColor.INDIGO
HSSFColor.MAROON
HSSFColor.GREY_80_PERCENT
HSSFColor.GREY_25_PERCENT
HSSFColor.DARK_GREEN
HSSFColor.YELLOW
HSSFColor.GOLD
HSSFColor.GREY_40_PERCENT
HSSFColor.DARK_TEAL
HSSFColor.PINK
HSSFColor.ORCHID
HSSFColor.LIGHT_BLUE
HSSFColor.LIGHT_CORNFLOWER_BLUE
HSSFColor.BLACK
HSSFColor.DARK_YELLOW
HSSFColor.VIOLET
HSSFColor.LAVENDER
HSSFColor.ROSE
HSSFColor.BLUE_GREY
HSSFColor.LIGHT_ORANGE
HSSFColor.ORANGE
HSSFColor.GREY_50_PERCENT