Poi对Excel写入和读取

官网上下载最新poi版本
[url]http://poi.apache.org/download.html[/url]


// 测试工具

public class Test {
	public static void main(String[] args) throws Exception {


		FileUtil util = new ExcelHandleUtil();//创建文件工具对象FileUtil

		List<Object> studentList = new ArrayList<Object>();
		Student t = new Student();
		t.setId(1001);
		t.setName("luxi");
		t.setDiscript("dddddddddd");
		studentList.add(t);//创建写入对象集合
		util.exportFile(studentList,"F:\\2013wirte.xls","aaa");//调用方法
		util.parseFile(new File("F:\\2013wirte.xls"),0, new ReadExcelCallback());
	}
}

//实现类

package cn.magicbeans.java.excel;

import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelHandleUtil implements FileUtil{

	private boolean invalid = true;

	public void setInvalid(boolean invalid) {
		this.invalid = invalid;
	}

	/**
	 * 解析文件方法
	 * 
	 * @param file
	 *            文件流
	 * @param sheetNum
	 *            文件页
	 * @param callback
	 *            回调函数
	 */
	public void parseFile(File file, int sheetNum, OnExcelContentCallback callback) {
		String ExtensionName = getExtensionName(file.getPath().toString());
		if (ExtensionName.equalsIgnoreCase("xls")) {
			parse2003(file, sheetNum, callback);
		} else if (ExtensionName.equalsIgnoreCase("xlsx")) {
			parse2007(file, sheetNum, callback);
		}
	}

	/**
	 * 写入文件方法
	 * 
	 * @param list
	 *            对象集合
	 * @param path
	 *            存储路径
	 * @param sheetNum
	 *            文件页
	 */
	@Override
	public void exportFile(List<Object> list,String path, String sheetNum) throws Exception {
		String ExtensionName = getExtensionName(path);
		if (ExtensionName.equalsIgnoreCase("xls")) {
			exportExcel2003(list,path, sheetNum);
		} else if (ExtensionName.equalsIgnoreCase("xlsx")) {
			exportExcel2007(list,path, sheetNum);
		}
	}

	/**
	 * 写入2007版本文件
	 * @param list对象集合
	 * @param path存储路径
	 * @param sheetName页名
	 * @throws Exception
	 */
	public static void exportExcel2007(List<Object> list,String path,String sheetName) throws Exception { // 创建excel文件对象
		XSSFWorkbook wb = new XSSFWorkbook();
		// 创建一个张表
		XSSFSheet sheet = wb.createSheet(sheetName==null?"sheet1":sheetName);
		// 创建第一行
		XSSFRow row1 = sheet.createRow(0);
		Font font = createFonts(wb, Font.BOLDWEIGHT_NORMAL, "宋体", false,(short) 200);
		// 给第一行
		Field[] fields = list.get(0).getClass().getDeclaredFields();
		for(int j=0;j<fields.length;j++){
			Field field = fields[j];
			createXSSFCell(wb, row1, j, field.getName(), font);
		}

		// 表示第二行
		int l = 1;
		// 这里将学员的信心存入到表格中
		for (int i = 0; i < list.size(); i++) {
			// 创建二行
			XSSFRow rowData = sheet.createRow(l++);
			Object obj =list.get(i);
			for(int j=0;j<fields.length;j++){
				Field field = fields[j];
				PropertyDescriptor pd = new PropertyDescriptor(field.getName(),	obj.getClass());
				Method getMethod = pd.getReadMethod();// 获得get方法
				Object o = getMethod.invoke(obj);// 执行get方法返回一个Object
				createXSSFCell(wb, rowData, j, o.toString(), font);
			}
		}
		FileOutputStream os = new FileOutputStream(path);
		wb.write(os);
		os.close();
	}

	/**
	 * 写入2003版本文件
	 * @param list对象集合
	 * @param path存储路径
	 * @param sheetName页名
	 * @throws Exception
	 */
	public static void exportExcel2003(List<Object> list,String path,String sheetName) throws Exception { // 创建excel文件对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建一个张表
		Sheet sheet = wb.createSheet(sheetName==null?"sheet1":sheetName);
		// 创建第一行
		Row row1 = sheet.createRow(0);
		Font font = createFonts(wb, Font.BOLDWEIGHT_NORMAL, "宋体", false,(short) 200);
		// 给第一行
		Field[] fields = list.get(0).getClass().getDeclaredFields();
		for(int j=0;j<fields.length;j++){
			Field field = fields[j];
			createCell(wb, row1, j, field.getName(), font);
		}

		// 表示第二行
		int l = 1;
		// 这里将学员的信心存入到表格中
		for (int i = 0; i < list.size(); i++) {
			// 创建二行
			Row rowData = sheet.createRow(l++);
			Object obj =list.get(i);
			for(int j=0;j<fields.length;j++){
				Field field = fields[j];
				PropertyDescriptor pd = new PropertyDescriptor(field.getName(),	obj.getClass());
				Method getMethod = pd.getReadMethod();// 获得get方法
				Object o = getMethod.invoke(obj);// 执行get方法返回一个Object
				createCell(wb, rowData, j, o.toString(), font);
			}
		}
		FileOutputStream os = new FileOutputStream(path);
		wb.write(os);
		os.close();
	}

	/**
	 * 创建单元格并设置样式,值
	 * 
	 * @param wb
	 * @param row
	 * @param column
	 * @param
	 * @param
	 * @param value
	 */
	public static void createXSSFCell(XSSFWorkbook wb, XSSFRow row, int column,
			String value, Font font) {
		XSSFCell cell = row.createCell(column);
		cell.setCellValue(value);
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
		cellStyle.setFont(font);
		cell.setCellStyle(cellStyle);
	}

	/**
	 * 创建单元格并设置样式,值
	 * 
	 * @param wb
	 * @param row
	 * @param column
	 * @param
	 * @param
	 * @param value
	 */
	public static void createCell(Workbook wb, Row row, int column,
			String value, Font font) {
		Cell cell = row.createCell(column);
		cell.setCellValue(value);
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
		cellStyle.setFont(font);
		cell.setCellStyle(cellStyle);
	}

	/**
	 * 设置字体
	 * 
	 * @param wb
	 * @return
	 */
	public static Font createFonts(Workbook wb, short bold, String fontName,
			boolean isItalic, short hight) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		font.setBoldweight(bold);
		font.setItalic(isItalic);
		font.setFontHeight(hight);
		return font;
	}


	/**
	 * 解析2007excle文件
	 * 
	 * @param file
	 *            文件流
	 * @param sheetNum
	 *            文件页
	 * @param callback
	 *            回调函数
	 */
	public void parse2007(File file, int sheetNum,
			OnExcelContentCallback callback) {
		if (file == null || !file.exists()) {
			if (callback != null) {
				callback.onStop();
			}
			return;
		}
		FileInputStream fis = null;
		if (callback != null) {
			callback.onStart();
		}
		try {
			fis = new FileInputStream(file);
			XSSFWorkbook xwb = new XSSFWorkbook(fis); // 构造 XSSFWorkbook
			XSSFSheet sheet = xwb.getSheetAt(sheetNum); // 读取第一章表格内容
			// 定义 row、cell
			XSSFRow row;
			// 循环输出表格中的从第二行开始内容
			for (int i = sheet.getFirstRowNum(); i <= sheet
					.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				if (row != null) {
					for (int j = row.getFirstCellNum(); j <= row
							.getPhysicalNumberOfCells(); j++) {
						XSSFCell cell = row.getCell(j);
						if (cell != null) {
							String cellValue = null;
							if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
								if (DateUtil.isCellDateFormatted(cell)) {
									cellValue = new DataFormatter()
											.formatRawCellContents(
													cell.getNumericCellValue(),
													0, "yyyy-MM-dd HH:mm:ss");
								} else {
									cellValue = String.valueOf(cell
											.getNumericCellValue());
								}
							} else {
								cellValue = cell.toString();
							}

							if (callback != null) {
								callback.onContent(i, j, cellValue);
							}
						}
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fis.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		if (callback != null) {
			callback.onStop();
		}
	}

	/**
	 * 解析2003excle文件
	 * 
	 * @param file
	 *            文件流
	 * @param sheetNum
	 *            文件页
	 * @param callback
	 *            回调函数
	 */
	public void parse2003(File file, int sheetNum,
			OnExcelContentCallback callback) {
		// /解析Excel
		// 返回结果集
		if (file == null || !file.exists()) {
			if (callback != null) {
				callback.onStop();
			}
			return;
		}
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			if (callback != null) {
				callback.onStart();
			}
			HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用
			HSSFSheet sheet = wookbook.getSheetAt(sheetNum); // 在Excel文档中，第一张工作表的缺省索引是0
			int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数;
			// 遍历行­（第1行 表头） 准备Map里的key
			HSSFRow firstRow = sheet.getRow(0);
			int cells = firstRow.getPhysicalNumberOfCells();
			// 遍历行­（从第二行开始）

			for (int i = 0; i < rows; i++) {
				// 读取左上端单元格(从第二行开始)
				HSSFRow row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					for (int num = 0; num <= cells; num++) {
						HSSFCell cell = row.getCell(num);
						if (!invalid) {
							return;
						}
						String content = null;
						if (null != cell) {
							content = getCellValue(cell);
						}
						if (callback != null) {
							callback.onContent(i, num, content);
						}
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fis.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		if (callback != null) {
			callback.onStop();
		}
	}

	public static String getCellValue(HSSFCell cell) {
		DecimalFormat df = new DecimalFormat("#.#####");
		String cellValue = null;
		if (cell == null)
			return null;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell
						.getNumericCellValue()));
				break;
			}
			cellValue = df.format(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_STRING:
			cellValue = String.valueOf(cell.getStringCellValue());
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			cellValue = String.valueOf(cell.getCellFormula());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			cellValue = null;
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			cellValue = String.valueOf(cell.getErrorCellValue());
			break;
		}
		if (cellValue != null && cellValue.trim().length() <= 0) {
			cellValue = null;
		}
		return cellValue;
	}

	/**
	 * 文件操作 获取文件扩展名
	 * 
	 * @Author: sunny
	 * @param filename
	 *            文件名称包含扩展名
	 * @return
	 */
	public static String getExtensionName(String filename) {
		if ((filename != null) && (filename.length() > 0)) {
			int dot = filename.lastIndexOf('.');
			if ((dot > -1) && (dot < (filename.length() - 1))) {
				return filename.substring(dot + 1);
			}
		}
		return filename;
	}


}