POI操作excel的导入与导出

    趁着不忙，抽空写了个用poi操作excel导入导出的功能的小例子。不多说，直接上代码！

poi的操作类：

package com.bao.excelUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.Row;

/**
 * 操作excel的工具类
 * @author Alex
 */
public class ExcelUtil {

	/**
	 * 创建一个excel,返回一个excel对象
	 * @param sheetName
	 * @param orderList
	 * @return HSSFWorkbook
	 * @throws Exception
	 * @author Alex
	 */
	public static HSSFWorkbook createExcel(String filePath, String sheetName, List<String[]> orderList) throws Exception {
		FileOutputStream fileOutputStream =  new FileOutputStream(filePath);
		HSSFWorkbook wb = new HSSFWorkbook(); //创建一个工作区
		try {
			HSSFSheet sheet = wb.createSheet(sheetName); //创建一个sheet页
			  //创建第一行,如果有多个sheet页可以自己进行拓展
			HSSFCell cell = null;                  
			if (orderList != null) {
				for (int i = 0; i < orderList.size(); i++) {
					HSSFRow row = sheet.createRow(i); 
					String[] con = (String[]) orderList.get(i);
					for (int j = 0; j < con.length; j++) {
						if (i == 0) {  //给第一行赋值,也就是标题行
							cell = row.createCell(j);
							cell.setCellValue(con[j]);
						} else {    //给其他行赋值,也就是数据
							cell = row.createCell(j);
							cell.setCellValue(con[j]);
						}
					}
				}
				CellStyle cellStyle2 = wb.createCellStyle();  //创建一个样式对象
				cellStyle2.setAlignment(CellStyle.ALIGN_CENTER); //设置内容居中
			}
			wb.write(fileOutputStream);
		} catch (Exception e) {
			throw e;
		}finally {
			fileOutputStream.flush();
			fileOutputStream.close();
		}
		return wb;
	}

	/**
	 * 读取excel数据的方法
	 * @param file
	 * @return List<HashMap>
	 * @throws Exception
	 * @author Alex
	 */
	public static List<String[]> getExcel(String filePath) throws Exception {
		List<String[]> dataList = new ArrayList<String[]>();
		File file = new File(filePath);
		FileInputStream fs = new FileInputStream(file);
		HSSFWorkbook wb = new HSSFWorkbook(fs); // 获得工作
		HSSFSheet sheet = wb.getSheetAt(0); // 拿到第一个sheet页
		Iterator<Row> rows = sheet.rowIterator(); // 拿到第一行
		while (rows.hasNext()) { // 如果有值
			HSSFRow row = (HSSFRow) rows.next(); // 拿到当前行
			if(row.getRowNum() != 0) {  //不读取标题(可以根据自己需求进行修改)
				String[] data = new String[row.getLastCellNum()];
				Iterator<Cell> cells = row.cellIterator(); // 拿到当前行所有列的集合
				while (cells.hasNext()) {
					HSSFCell cell = (HSSFCell) cells.next(); // 拿到列值
					String cellValue = ""; // 存放单元格的值
					switch (cell.getCellType()) { // 判断单元格的类型，取出单元格的值
					case HSSFCell.CELL_TYPE_NUMERIC:
						// 处理数字类型 去掉科学计数法格式
						double strCell = cell.getNumericCellValue();
						DecimalFormat formatCell = (DecimalFormat) NumberFormat.getPercentInstance();
						formatCell.applyPattern("0");
						String value = formatCell.format(strCell);
						if (Double.parseDouble(value) != strCell) {
							formatCell.applyPattern(Double.toString(strCell));
							value = formatCell.format(strCell);
						}
						cellValue = value;
						break;
					case HSSFCell.CELL_TYPE_STRING:
						cellValue = cell.getStringCellValue();
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						cellValue = String.valueOf(cell.getBooleanCellValue());
						break;
					case HSSFCell.CELL_TYPE_FORMULA:
						cellValue = cell.getCellFormula();
						break;
					default:
						break;
					}
					data[cell.getColumnIndex()] = cellValue;
				}
				dataList.add(data);
			}
		}
		return dataList;
	}
}

 测试poi的实现类:

package com.bao.test;

import java.util.ArrayList;
import java.util.List;

import com.bao.excelUtil.ExcelUtil;

public class testMain {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//模拟数据
		String filePath = "file/testDate.xls";
		String sheetName = "测试";
		List<String[]> testList = new ArrayList<String[]>();  
		String[] title = {"姓名","年龄","性别"};
		String[] data1 = {"张三","25","男"};
		String[] data2 = {"赵四","22","男"};
		String[] data3 = {"王武","12","男"};
		testList.add(title);
		testList.add(data1);
		testList.add(data2);
		testList.add(data3);
		try {
			//导出excel
			//ExcelUtil.createExcel(filePath, sheetName, testList);
			//读取excel
			List<String[]> result = ExcelUtil.getExcel(filePath);
			for(String[] data : result){
				for(int i = 0;i<data.length ;i++) {
					System.out.print(data[i] + "  ");
				}
				System.out.println();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}

用的jar包是poi-3.7-20101029.jar.如果大家想了解更多，直接下载api进行学习！写的比较简单，希望大家多多指点！

评论

1 楼 alice2012 2012-09-24  

  good~~~