poi excel导入工具类 -凯发k8国际

poi excel导入工具类importutil

import java.beans.beaninfo;
import java.beans.introspector;
import java.beans.propertydescriptor;
import java.io.inputstream;
import java.lang.reflect.method;
import java.math.bigdecimal;
import java.sql.date;
import java.text.decimalformat;
import java.text.parseexception;
import java.text.simpledateformat;
import java.util.arraylist;
import java.util.hashmap;
import java.util.list;
import java.util.map;
import org.apache.poi.hssf.usermodel.hssfworkbook;
import org.apache.poi.ss.usermodel.cell;
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.xssfworkbook;
/**
 * excel导入工具类
 * 
 * @author lihua_java@163.com
 * @version 2.0
 * @since 2019-11-19
 *
 */
public class importutil {
	private final static string excel2003l = ".xls"; // 2003- 版本的excel
	private final static string excel2007u = ".xlsx"; // 2007  版本的excel
	private static final string date_format = "yyyy-mm-dd";
	
	/**
	 * 获取io流中的数据，组装成list对象
	 * @param in	输入流
	 * @param filename	文件名称，根据后缀判断excel版本
	 * @param fields	导入字段名称
	 * @param fieldsclazz	导入字段类型，目前只支持基本类型
	 * @param clazz		bean的class
	 * @return	class对应的实例对象list集合
	 * @throws exception
	 */
	public static  list getexcel(inputstream in, string filename, string[] fields, class[] fieldsclazz, class clazz) throws exception {
		list list = new arraylist<>();
		if(fields == null || fieldsclazz == null || fields.length != fieldsclazz.length){
			throw new exception("需导入的列设置错误！");
		}
		map> fieldmap = new hashmap<>();
		for (int i = 0, j = fields.length; i < j; i  ) {
			fieldmap.put(fields[i], fieldsclazz[i]);
		}
		// 创建excel工作薄
		workbook work = getworkbook(in, filename);
		if (null == work) {
			throw new exception("excel工作薄为空！");
		}
		sheet sheet = null;
		row row = null;
		cell cell = null;
		sheet = work.getsheetat(0);
		if (sheet == null) {
			return list;
		}
		
		// 遍历当前sheet中的所有行
		for (int j = sheet.getfirstrownum(), y = sheet.getlastrownum(); j <= y; j  ) {
			row = sheet.getrow(j);
			if (row == null || row.getfirstcellnum() == j) {
				continue;
			}
			
			// 遍历所有的列
			map fielddatas = new hashmap<>();
			for (int k = row.getfirstcellnum(), z = row.getlastcellnum(); k < z; k  ) {
				cell = row.getcell(k);
				if(cell == null){
					continue;
				}
				object value = getcellvalue(cell);
				if(value == null){
					continue;
				}
				fielddatas.put(fields[k], value);					
			}
			// bean添加到集合
			list.add(cashtobean(fieldmap, fielddatas, clazz));			
		}
		return list;
	}
	
	/**
	 * 将读取的行数据转为实例对象
	 * @param fieldsclazz	字段名称和字段类型集合
	 * @param fielddatas	行数据
	 * @param clazz			实例对象的class
	 * @return
	 * @throws exception
	 */
	private static  t cashtobean(map> fieldsclazz, map fielddatas, class clazz)
		throws exception{
		t entry = clazz.newinstance();
		beaninfo beaninfo = introspector.getbeaninfo(clazz);
		propertydescriptor[] propertydescriptors = beaninfo.getpropertydescriptors();
		for (propertydescriptor property : propertydescriptors) {
			string fieldname = property.getname();
			if (fielddatas.containskey(fieldname)) {
				method setter = property.getwritemethod();
				object _value = cashfieldclass(fieldsclazz.get(fieldname), fielddatas.get(fieldname));
				setter.invoke(entry, _value);
			}
		}
		return entry;
	}
	/**
	 * 属性类型转换
	 * @param fieldsclazz	字段名称和字段类型集合
	 * @param fielddata		行数据
	 * @param fieldname		字段名称
	 * @return
	 * @throws parseexception
	 */
	private static object cashfieldclass(class fieldclazz, object value) throws parseexception {
		object _value = value;
		if(!fieldclazz.equals(value.getclass())){
			string valuestr = value.tostring();
			if(fieldclazz.equals(long.class)){
				_value = long.valueof(valuestr);
			}else if(fieldclazz.equals(integer.class)){
				_value = integer.valueof(valuestr);
			}else if(fieldclazz.equals(date.class)){
				_value = new simpledateformat(date_format).parse(valuestr);
			}else if(fieldclazz.equals(bigdecimal.class)){
				_value = new bigdecimal(valuestr);
			}else if(fieldclazz.equals(boolean.class)){
				_value = boolean.valueof(valuestr);
			}else if(fieldclazz.equals(double.class)){
				_value = double.valueof(valuestr);
			}else if(fieldclazz.equals(float.class)){
				_value = float.valueof(valuestr);
			}
		}
		return _value;
	}
	/**
	 * 根据文件后缀，自适应上传文件的版本
	 * 
	 * @param instr
	 * @param filename
	 * @return
	 * @throws exception
	 */
	public static workbook getworkbook(inputstream instr, string filename) throws exception {
		workbook wb = null;
		string filetype = filename.substring(filename.lastindexof(".")).tolowercase();
		if (excel2003l.equals(filetype)) {
			wb = new hssfworkbook(instr); // 2003-
		} else if (excel2007u.equals(filetype)) {
			wb = new xssfworkbook(instr); // 2007 
		} else {
			throw new exception("解析的文件格式有误！");
		}
		return wb;
	}
	/**
	 * 对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	public static object getcellvalue(cell cell) {
		object value = null;
		decimalformat df = new decimalformat("0"); // 格式化number string字符
		simpledateformat sdf = new simpledateformat(date_format); // 日期格式化
		decimalformat df2 = new decimalformat("0.000"); // 格式化数字
		
		switch (cell.getcelltype()) {
		case cell.cell_type_string:
			value = cell.getrichstringcellvalue().getstring();
			break;
		case cell.cell_type_numeric:
			if ("general".equals(cell.getcellstyle().getdataformatstring())) {
				value = df.format(cell.getnumericcellvalue());
			} else if ("m/d/yy".equals(cell.getcellstyle().getdataformatstring())) {
				value = sdf.format(cell.getdatecellvalue());
			} else {
				value = df2.format(cell.getnumericcellvalue());
			}
			break;
		case cell.cell_type_boolean:
			value = cell.getbooleancellvalue();
			break;
		case cell.cell_type_blank:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
	
}

 示例代码如下：

    /**
     * 持仓划转导入
     * @param upload
     * @param request
     * @param response
     */
    @requestmapping("/modules/manage/artpos/import.htm")
    public void importpos(@requestparam(value = "upload") multipartfile upload,
    		httpservletrequest request , httpservletresponse response)throws exception{
    	string[] fields = {"fromphone", "tophone", "artcode", "transfernumber", "transferprice", "remark"};
    	class[] fieldsclazz = {string.class, string.class, string.class, integer.class, bigdecimal.class, string.class};
    	list list = importutil.getexcel(upload.getinputstream(), 
    			upload.getoriginalfilename(), fields, fieldsclazz, artpositiontransfermodel.class);
    	artpositiontransferservice.savebatch(list);
    	servletutils.success(response);
    }