POI 解析Excel（兼容 2003,2007）

最新推荐文章于 2019-02-26 15:09:41 发布

Nick_Lele

最新推荐文章于 2019-02-26 15:09:41 发布

阅读量1.3k

点赞数

文章标签： POI java Excel解析

本文链接：https://blog.csdn.net/java_le/article/details/8847212

版权

（1）下载poi的jar包
 geronimo-stax-api_1.0_spec-1.0.jar
 dom4j-1.6.1.jar
 log4j-1.2.13.jar
 poi-3.7-20101029.jar
 poi-ooxml-3.7-20101029.jar
 poi-ooxml-schemas-3.7-20101029.jar
 poi-scratchpad-3.7-20101029.jar
 xmlbeans-2.3.0.jar
（2）创建一个javaBean来映射一列数据
package poi;

package poi;


public class HolidayBean {
	
	private String year ;
	private String start_date ;
	private String end_date ;
	private String is_dayoff ;
	private String remeark ;
	public String getYear() {
		return year;
	}
	public String getStart_date() {
		return start_date;
	}
	public void setStart_date(String startDate) {
		start_date = startDate;
	}
	public String getEnd_date() {
		return end_date;
	}
	public void setEnd_date(String endDate) {
		end_date = endDate;
	}
	public String getIs_dayoff() {
		return is_dayoff;
	}
	public void setIs_dayoff(String isDayoff) {
		is_dayoff = isDayoff;
	}
	public String getRemeark() {
		return remeark;
	}
	public void setRemeark(String remeark) {
		this.remeark = remeark;
	}
	public void setYear(String year) {
		this.year = year;
	}
}

(3) 创建一个解析Excel的类

package poi;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;

public class PoiDo {
	private static Logger logger = Logger.getLogger(PoiDo.class);
	private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd") ;
	// return Workbook by filepath
	public static Workbook getWorkbook(String path) {
		Workbook workBook = null;
		try {
			InputStream in = new FileInputStream(path);
			workBook = WorkbookFactory.create(in);
		} catch (Exception e) {
			logger.error("Exception occur when create Workbook:" + e);
		}
		return workBook;
	}

	// parse WorkBook
	public static ArrayList<HolidayBean> parseWorkBook(String filePath) {
		Workbook workBook = getWorkbook(filePath);
		ArrayList<HolidayBean> holidayBeans = new ArrayList<HolidayBean>();
		// recycle sheet when Workbook contains sheet more than one
		for (int i = 0, sheetSize = workBook.getNumberOfSheets(); i < sheetSize; i++) {
			Sheet sheet = workBook.getSheetAt(i);
			// recycle row
			for (int j = 0, rowSize = sheet.getLastRowNum(); j < rowSize; j++) {
				Row row = sheet.getRow(j);
				// recycle cell to holidayBean
				HolidayBean hb = new HolidayBean();
				hb.setYear(parseCell(row.getCell(0))) ;
				hb.setStart_date(parseCell(row.getCell(1))) ;
				hb.setEnd_date(parseCell(row.getCell(2))) ;
				hb.setIs_dayoff(parseCell(row.getCell(3))) ;
				hb.setRemeark(parseCell(row.getCell(4))) ;
				holidayBeans.add(hb) ;
			}
		}

		return holidayBeans;
	}

	// parse cell to String
	private static String parseCell(Cell cell) {
		String cellValue;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			cellValue = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			cellValue = Boolean.toString(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:
			cellValue = "";
			break;
		case Cell.CELL_TYPE_NUMERIC:{
			if(DateUtil.isCellDateFormatted(cell)){
				Date date = cell.getDateCellValue() ;
				cellValue = format.format(date) ;
			}else{
				cellValue = String.valueOf(cell.getNumericCellValue());
			}
		}break;
		case Cell.CELL_TYPE_STRING:
			cellValue = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA: {
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cellValue = cell.getStringCellValue();
		}break;

		default:
			cellValue = "";
		}
		return cellValue;
	}
	public static void print(ArrayList<HolidayBean> list){
		for(int i=0;i<list.size();i++){
			HolidayBean hb = list.get(i) ;
			System.out.print(hb.getYear()+"  "+hb.getStart_date()+"--"+hb.getEnd_date()+"   "+hb.getIs_dayoff()+"   "+hb.getRemeark());
			System.out.println();
		}
	}
	public static void main(String[] args) {
		ArrayList<HolidayBean> hbList = parseWorkBook("D:\\biyesheji\\holiday.xlsx");
		print(hbList) ;
	}
}

其中parseCell是解析Cell，Excel 中日期的存储形式也是Numeric类型,所以在解析日期的时候判断这个cell是日期还是数字

if(DateUtil.isCellDateFormatted(cell)){
				Date date = cell.getDateCellValue() ;
				cellValue = format.format(date) ;
			}else{
				cellValue = String.valueOf(cell.getNumericCellValue());
			}