java实现excel导入数据的工具类

导入excel数据的工具类，调用也就几行代码，很简单的。

import jxl.cell;
import jxl.sheet;
import jxl.workbook;
import jxl.read.biff.biffexception;
import org.apache.commons.beanutils.beanutils;
import org.slf4j.logger;
import org.slf4j.loggerfactory;

import java.io.ioexception;
import java.io.inputstream;
import java.util.*;

/**
 * excel导入的工具类.
 */
public class excelutils {
    private static final logger logger = loggerfactory.getlogger(excelutils.class);
    //成功
    public static final integer status_ok = integer.valueof(1);
    //失败
    public static final integer status_no = integer.valueof(0);
    /**
     * 私有化构造器
     */
    private excelutils(){

    }

    /**
     * 获取excel文件中的数据对象
     *
     * @param is                        excel
     * @param excelcolumnnames          excel中每个字段的英文名(应该与pojo对象的字段名一致,顺序与excel一致)
     * @return                          excel每行是list一条记录，map是对应的"字段名-->值"
     * @throws exception
     */
    public static list<map<string, string>> getimportdata(inputstream is, list<string> excelcolumnnames) throws exception {
        logger.debug("inputstream:{}", is);
        if (is == null) {
            return collections.emptylist();
        }

        workbook workbook = null;
        try {
            //拿到excel
            workbook = workbook.getworkbook(is);
        } catch (biffexception e) {
            logger.error(e.getmessage(), e);
            return collections.empty_list;
        } catch (ioexception e) {
            logger.error(e.getmessage(), e);
            return collections.empty_list;
        }
        logger.debug("workbook:{}", workbook);

        if (workbook == null) {
            return collections.emptylist();
        }

        //第一个sheet
        sheet sheet = workbook.getsheet(0);
        //行数
        int rowcounts = sheet.getrows() - 1;
        logger.debug("rowcounts:{}", rowcounts);
        list<map<string, string>> list = new arraylist<map<string, string>>(rowcounts - 1);

        //双重for循环取出数据
        for(int i = 1; i < rowcounts; i++){
            map<string, string> params = new hashmap<string, string>();
            //i,j i:行 j:列
            for(int j = 0; j < excelcolumnnames.size(); j++){
                cell cell = sheet.getcell(j, i);
                params.put(excelcolumnnames.get(j), cell.getcontents());
            }

            list.add(params);
        }

        return list;
    }

    /**
     * 获取导入数据为对象的list
     *
     * @param data
     * @param clazz
     * @param excelcolumnnames
     * @param checkexcel
     * @param <t>
     * @return
     * @throws exception
     */
    public static <t> list<t> makedata(list<map<string, string>> data, class<t> clazz, list<string> excelcolumnnames, checkexcel checkexcel) throws exception {
        if(data == null || data.isempty() || clazz == null || checkexcel == null) {
            return collections.empty_list;
        }

        list<t> result = new arraylist<t>(data.size());
        for(map<string, string> d : data) {
            if(checkexcel != null && !checkexcel.check(d)) {
                continue;
            }
            t entity = clazz.newinstance();
            for(string column : excelcolumnnames) {
                beanutils.setproperty(entity, column, d.get(column));
            }

            result.add(entity);
        }

        return result;
    }
}

检查excel中每一行的数据是否合法

import java.util.map;
/**
 * 检查excel中每一行的数据是否合法
 */
public interface checkexcel {
    /**
     * 返回true合法
     *
     * @param data      excel中每一行的数据
     * @return
     */
    public boolean check(map<string, string> data);
}

调用部分

list<map<string, string>> data = excelutils.getimportdata(is,constants.excel_column_names);
list<feeallocation> allocations = excelutils.makedata(data, feeallocation.class, constants.excel_column_names, new checkexcel() {
            public boolean check(map<string, string> data) {
                if(stringutils.isempty(data.get("name")))
                    return false;
                return true;
            }
        });