java中操作Excel

在企业级开发中，经常遇到读写excel的操作，在此将一些简单的excel操作做了一个实例：

package com.test.core.utils;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.Date;
import java.util.List;
import java.util.Vector;

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 org.apache.struts2.ServletActionContext;

public abstract class ExportExcel<T> {

	/**
	 * export Excel for Data
	 * @param list
	 */
	public void exportExcel(List<T> entities, String fileName) {
		try {

			HSSFWorkbook workBoook = exportDeleteExcel(createExportData(entities, fileName));
			OutputStream outStream = getOutputStream(ServletActionContext.getRequest(),
						ServletActionContext.getResponse(), fileName);
			workBoook.write(outStream);
			outStream.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * abstract method for create excel info
	 * @param entities, operator
	 * @return
	 */
	public abstract BaseDataExportInfo createExportData(List<T> entities, String fileName);

	/**
	 * set information for Head
	 * @param baseData
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	protected HSSFWorkbook exportDeleteExcel(BaseDataExportInfo baseData)
			throws Exception {

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet();

		workbook
				.setSheetName(baseData.getSheetIndex(), baseData.getSheetName());

		HSSFRow contentsRow = sheet.createRow(0);
		contentsRow.setHeight((short) 500); // 设置行高s

		/* 设置表头信息 */
		HSSFFont titleFont = workbook.createFont();
		titleFont.setFontName("宋体");
		titleFont.setFontHeightInPoints((short) 16);
		titleFont.setBoldweight((short) 20);
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setFont(titleFont);

		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中

		List<String> head = baseData.getTableHead();
		for (short j = 0; j < head.size(); j++) {

			// 设置列宽
			if (j == 1) {
				sheet.setColumnWidth(j, (short) 14000);
			} else {
				sheet.setColumnWidth(j, (short) 8000);
			}

			HSSFCell contentsCell = contentsRow.createCell(j);
			contentsCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			contentsCell.setCellStyle(titleStyle);
			contentsCell.setCellValue(head.get(j));
		}

		/* 设置表体信息 */
		if (null != baseData.getRowList()) {
			List<Vector<String>> rowList = baseData.getRowList();
			for (short i = 0; i < rowList.size(); i++) {
				Vector<String> rowInfo = rowList.get(i);
				HSSFRow row = sheet.createRow(i + 1);
				/** 表体 */
				HSSFFont font = workbook.createFont();
				/** 设置字体样式 */
				font.setFontName("宋体");
				HSSFCellStyle cellStyle = workbook.createCellStyle();
				cellStyle.setFont(font);
				cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边
				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边
				cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边
				cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边

				cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居左
				for (short k = 0; k < rowInfo.size(); k++) {
					HSSFCell cell = row.createCell(k);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(cellStyle);

					cell.setCellValue(rowInfo.get(k));
				}

			}
		}
		return workbook;
	}

	/**
	 * OutPutStream for Excel
	 * @param request
	 * @param response
	 * @return
	 */
	protected OutputStream getOutputStream(HttpServletRequest request,HttpServletResponse response, String name) {
		OutputStream outputStream = null;    

		 try {
			outputStream = new BufferedOutputStream(response.getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}

	    String	fileName = "Excel_" + name + "_" + DateUtils.format(new Date()) + ".xls";
        response.setContentType("application/vnd.ms-excel");
        try {
       	 response.setHeader("content-disposition",
			                        "attachment;filename=\"" +
			                        new String(fileName.getBytes(),
			                                   response.getCharacterEncoding()) +
			                        "\"");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}

		return outputStream;
	}

}

只要继承上变的抽象类，实现对应的方法，然后声明该处理bean到Spring中，就可以运用该组件了。