POI的简单封装

在一次开发中，需要做excle数据导入。

 

开发中我发现，将一列作为一个对象来进行处理，可以大大的方便程序的操作.

 

package com.wac.business.filedataconvey.excle;

import ognl.Ognl;
import ognl.OgnlException;
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 com.wac.business.filedataconvey.util.ExcleUtil;
import com.wac.business.filedataconvey.util.MsgBuilder;

public class ExcleColumn {
 //普通列
 public final static int COLUMN_TYPE_COMMON = 0;
 //金额列
 public final static int COLUMN_TYPE_MONEY = 1;
 //时间列
 public final static int COLUMN_TYPE_DATE = 2;
 //带下拉列
 public final static int COLUMN_TYPE_LIST = 3;
 //单元格允许填入的字符的最大长度
 public final static int MAX_LENGTH = 100;
 private HSSFWorkbook workbook = null;
 private String dataSheetName;
 private String referenceSheetName;
 private boolean emptyCellAllowed = false;
 private int posStartRow;
 private int size;
 private int colIndex;
 private String propertyName;
 private int columnType;
 private String errorMsg;
 private ListMap<String, String> vs = null;

 public ExcleColumn(HSSFWorkbook workbook) {
  if (workbook == null)
   throw new NullPointerException("workbook is null");
  this.workbook = workbook;
 }

 public String getDataSheetName() {
  return dataSheetName;
 }

 public void setDataSheetName(String dataSheetName) {
  this.dataSheetName = dataSheetName;
 }
 public void setReferenceSheetName(String referenceSheetName) {
  this.referenceSheetName = referenceSheetName;
 }

 public boolean isEmptyCellAllowed() {
  return emptyCellAllowed;
 }

 public void setEmptyCellAllowed(boolean emptyCellAllowed) {
  this.emptyCellAllowed = emptyCellAllowed;
 }

 public int getPosStartRow() {
  return posStartRow;
 }

 public void setPosStartRow(int posStartRow) {
  this.posStartRow = posStartRow;
 }

 public int getSize() {
  return size;
 }

 public void setSize(int size) {
  this.size = size;
 }

 public int getColIndex() {
  return colIndex;
 }

 public void setColIndex(int colIndex) {
  this.colIndex = colIndex;
 }

 public String getPropertyName() {
  return propertyName;
 }

 public void setPropertyName(String propertyName) {
  this.propertyName = propertyName;
 }

 public int getColumnType() {
  return columnType;
 }

 public void setColumnType(int columnType) {
  this.columnType = columnType;
 }

 public String getErrorMsg() {
  return errorMsg;
 }

 public void setErrorMsg(String errorMsg) {
  this.errorMsg = errorMsg;
 }

 public ListMap<String, String> getVs() {
  return vs;
 }

 public void setVs(ListMap<String, String> vs) {
  this.vs = vs;
 }

 public HSSFWorkbook getWorkbook() {
  return workbook;
 }

 public HSSFSheet getDataSheet() {
  return workbook.getSheet(this.dataSheetName);
 }

 public HSSFSheet getRefSheet() {
  return workbook.createSheet(this.referenceSheetName + colIndex);
 }

 /**
  *
  * @param rowIndex 行
  * @return
  */
 public HSSFCell getCell(int rowIndex) {
  HSSFSheet sheet = workbook.getSheet(this.dataSheetName);
  HSSFRow row = sheet.getRow(rowIndex);
  HSSFCell cell = row.getCell(this.colIndex);
  return cell;
 }

 public Object getValue(int rowIndex, MsgBuilder mb) {
  if (rowIndex < posStartRow || rowIndex >= posStartRow + size) {
   throw new IllegalAccessError("请求解析的行超出了可以解析行的范围");
  }
  Object o = ExcleUtil.parseCell(this,rowIndex);
  if (o == null&&!isEmptyCellAllowed()) {
   if (getCell(rowIndex) == null) {
    HSSFSheet sheet = workbook.getSheet(this.dataSheetName);
    HSSFRow row = sheet.getRow(rowIndex);
    HSSFCell cell=row.createCell(this.colIndex);
    mb.add(cell, errorMsg);
   } else
    mb.add(getCell(rowIndex), errorMsg);
   return null;
  }
  return o;
 }

 public void setBeanValue(int rowIndex, MsgBuilder mb, Object bean) {
  Object o = getValue(rowIndex, mb);
  if (o != null)
   try {
    Ognl.setValue(propertyName, bean, o);
   } catch (OgnlException e) {
    // TODO Auto-generated catch block
    throw new IllegalAccessError();
   }
  else {
   //如果为空，检查是否允许为空
   if (!isEmptyCellAllowed())
    throw new RuntimeException("先检查数据在做保持操作！");
  }
 }

}

 

1.首先抽象出了4类列

     下拉列表，日期，金额，普遍

2.方法：

    getCell(int rowIndex) 获取该对象在某行的cell对象

    parse(int rowIndex,MsgBuilder mb) 解析该对象在某列的cell的值

    setProperty(int rowIndex,MsgBuilder,Object o) 上一个方法的更高级的方法，实际上这个方法是要依赖上一方法的，而且考虑到内存的消耗，所以要调用parse对excle进行检查，然后调用setProperty 进行数据提取被封装到bean中.