.Net NPOI 上传excel文件、提交后台获取excel里的数据

1、导入npoi.dll

2、添加类npoiexcel.cs

using system;
using system.collections.generic;
using system.text;
using system.data;
using system.data.oledb;
using system.io;
using system.drawing;
using npoi.hssf.usermodel;
using npoi.ss.usermodel;
using npoi.xssf.usermodel;
using npoi.hssf.util;
using npoi.ss.util;

public class npoiexcel
{
    internal iworkbook book { get; set; }

    private int sheetid = 0;
    /// <summary>
    /// 当前活动的sheetid，所有的操作将指向这个sheet
    /// </summary>
    public int activesheetid
    {
        get
        {
            return sheetid;
        }
        set
        {
            sheetid = value;
        }
    }

    /// <summary>
    /// 当前活动的sheetname，所有的操作将指向这个sheet
    /// </summary>
    public string activesheetname
    {
        get
        {
            return book.getsheetat(sheetid).sheetname;
        }
        set
        {
            sheetid = book.getsheetindex(value);
        }
    }
    /// <summary>
    /// 当前活动的sheet，所有的操作将指向这个sheet
    /// </summary>
    public isheet activesheet
    {
        get
        {
            return book.getsheetat(sheetid);
        }
    }

    /// <summary>
    /// 第一行非空行的行号
    /// </summary>
    public int firstrownum
    {
        get
        {
            return book.getsheetat(sheetid).firstrownum;
        }
    }

    /// <summary>
    /// 最后一行非空行的行号
    /// </summary>
    public int lastrostnum
    {
        get
        {
            return book.getsheetat(sheetid).lastrownum;
        }
    }

    /// <summary>
    /// 无模板的excel生成或操作
    /// </summary>
    public npoiexcel()
    {
        book = new hssfworkbook();
        book.createsheet();
    }

    public npoiexcel(stream filestream, string filename)
    {
        if (filename.substring(filename.lastindexof(".")) == ".xls")
        {
            book = new hssfworkbook(filestream);
        }
        else
        {
            book = new xssfworkbook(filestream);
        }
    }
    /// <summary>
    /// 带模板或数据的excel生成或操作
    /// </summary>
    /// <param name="filename"></param>
    public npoiexcel(string filename)
    {
        book = createbook(filename);
    }

    /// <summary>
    /// 创建excel book
    /// </summary>
    /// <param name="filename">模板文件名</param>
    /// <returns></returns>
    private iworkbook createbook(string filename)
    {
        fileinfo file = new fileinfo(filename);
        if (!file.exists)
        {
            file.create(filename).close();
        }
        filestream fs = new filestream(filename, filemode.open, fileaccess.read);
        iworkbook book;
        if (file.extension == ".xls")
        {
            book = new hssfworkbook(fs);
        }
        else
        {
            book = new xssfworkbook(fs);
        }
        fs.close();
        if (book.numberofsheets == 0)
        {
            book.createsheet();
        }
        return book;
    }

    /// <summary>
    /// 新建sheet
    /// </summary>
    /// <returns>新建sheet</returns>
    public isheet createsheet()
    {
        return book.createsheet();
    }

    /// <summary>
    /// 新建sheet
    /// </summary>
    /// <param name="sheetname">新建sheet的名称</param>
    /// <returns>新建sheet</returns>
    public isheet createsheet(string sheetname)
    {
        return book.createsheet(sheetname);
    }

    /// <summary>
    /// 设置行高
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    /// <param name="height">高度</param>
    public void setrowheight(int rowindex, float height)
    {
        irow row = book.getsheetat(sheetid).getrow(rowindex);
        if (row == null)
        {
            row = book.getsheetat(sheetid).createrow(rowindex);
        }
        row.height = (short)(height * 20);
    }

    /// <summary>
    /// 设置列宽
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="columnindex">列号</param>
    /// <param name="width">宽度</param>
    public void setcolumnwidth(int columnindex, short width)
    {
        book.getsheetat(sheetid).setcolumnwidth(columnindex, width * 256);
    }

    /// <summary>
    /// 获取或设置默认行高
    /// 注：只对当前activesheet有效
    /// </summary>
    public short defaultrowheight
    {
        get
        {
            return (short)(book.getsheetat(sheetid).defaultrowheight / 20);
        }
        set
        {
            book.getsheetat(sheetid).defaultrowheight = value * 20;
        }
    }

    /// <summary>
    /// 获取或设置默认列宽
    /// 注：只对当前activesheet有效
    /// </summary>
    public int defaultcolwidth
    {
        get
        {
            return book.getsheetat(sheetid).defaultcolumnwidth;
        }
        set
        {
            book.getsheetat(sheetid).defaultcolumnwidth = value;
        }
    }

    /// <summary>
    /// 某一列的列宽自动调整大小
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="colindex">列号</param>
    public void autocolwidth(int colindex)
    {
        book.getsheetat(sheetid).autosizecolumn(colindex, true);
    }

    /// <summary>
    /// 隐藏一行
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    public void hiddenrow(int rowindex)
    {
        irow row = book.getsheetat(sheetid).getrow(rowindex);
        if (row == null)
        {
            row = book.getsheetat(sheetid).createrow(rowindex);
        }
        row.zeroheight = true;
    }

    /// <summary>
    /// 删除一行
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    public void removerow(int rowindex)
    {
        irow row = book.getsheetat(sheetid).getrow(rowindex);
        if (row != null)
        {
            activesheet.removerow(row);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    /// <param name="columnindex">列号</param>
    /// <returns>单元格的值</returns>
    public object readvalue(int rowindex, int columnindex, bool? isdatetime = null)
    {
        try
        {
            icell cell = book.getsheetat(sheetid).getrow(rowindex).getcell(columnindex);
            short df = cell.cellstyle.dataformat;

            //return cell.tostring();
            switch (cell.celltype)
            {
                case celltype.blank:
                    return null;
                case celltype.boolean:
                    return cell.booleancellvalue;
                case celltype.error:
                    throw new exception("cell value error");
                case celltype.formula:
                    {
                        switch (cell.cachedformularesulttype)
                        {
                            case celltype.blank:
                                return "";
                            case celltype.boolean:
                                return cell.booleancellvalue;
                            case celltype.error:
                                throw new exception("cell value error");
                            case celltype.formula:
                                throw new exception("the formula of this cell is too complex！");
                            case celltype.numeric:
                                if (isdatetime == null)
                                {
                                    if (dateutil.iscelldateformatted(cell))
                                    {
                                        return cell.datecellvalue;
                                    }
                                    else
                                    {
                                        return cell.numericcellvalue;
                                    }
                                }
                                else if (isdatetime == true)
                                {
                                    return cell.datecellvalue;
                                }
                                else
                                {
                                    return cell.numericcellvalue;
                                }
                            case celltype.string:
                                return cell.stringcellvalue;
                            case celltype.unknown:
                                return cell.tostring();
                            default:
                                return cell.tostring();
                        }
                    }
                case celltype.numeric:
                    {
                        if (isdatetime == null)
                        {
                            if (dateutil.iscelldateformatted(cell))
                            {
                                return cell.datecellvalue;
                            }
                            else
                            {
                                return cell.numericcellvalue;
                            }
                        }
                        else if (isdatetime == true)
                        {
                            return cell.datecellvalue;
                        }
                        else
                        {
                            return cell.numericcellvalue;
                        }
                    }
                case celltype.string:
                    return cell.stringcellvalue;
                case celltype.unknown:
                    return cell.tostring();
                default:
                    return cell.tostring();
            }
        }
        catch (system.nullreferenceexception)
        {
            return null;
        }
        catch (exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    /// <param name="columnindex">列号</param>
    /// <param name="value">单元格的值</param>
    public void setvalue(int rowindex, int columnindex, object value)
    {
        setvalue(rowindex, columnindex, value, false);
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    /// <param name="columnindex">列号</param>
    /// <param name="value">单元格的值</param>
    /// <param name="isformula">是否是公式</param>
    public void setvalue(int rowindex, int columnindex, object value, bool isformula)
    {
        irow row = book.getsheetat(sheetid).getrow(rowindex);
        if (row == null)
        {
            row = book.getsheetat(sheetid).createrow(rowindex);
        }
        icell cell = row.getcell(columnindex);
        if (cell == null)
        {
            cell = row.createcell(columnindex);
        }
        if (value == null)
        {
            cell.setcellvalue("");
        }
        if (isformula)
        {
            cell.setcellformula(value.tostring());
        }
        else
        {
            if (value is short)
            {
                cell.setcellvalue((short)value);
            }
            else if (value is int)
            {
                cell.setcellvalue((int)value);
            }
            else if (value is long)
            {
                cell.setcellvalue((long)value);
            }
            else if (value is float)
            {
                cell.setcellvalue((float)value);
            }
            else if (value is double)
            {
                cell.setcellvalue((double)value);
            }
            else if (value is bool)
            {
                cell.setcellvalue((bool)value);
            }
            else if (value is datetime)
            {
                cell.setcellvalue((datetime)value);
            }
            else if (value == null)
            {
            }
            else
            {
                cell.setcellvalue(value.tostring());
            }
        }

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行</param>
    /// <param name="endrowindex">结束行</param>
    /// <param name="startcolinex">开始列</param>
    /// <param name="endcolindex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatortype">验证方式</param>
    /// <param name="minvalue">最小值</param>
    /// <param name="maxvalue">最大值</param>
    public void setvaluerange(int startrowindex, int endrowindex, int startcolinex, int endcolindex, npoidatatype type, operatortypes operatortype, string minvalue, string maxvalue)
    {
        setvaluerange(startrowindex, endrowindex, startcolinex, endcolindex, type, operatortype, minvalue, maxvalue, "", "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行</param>
    /// <param name="endrowindex">结束行</param>
    /// <param name="startcolinex">开始列</param>
    /// <param name="endcolindex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatortype">验证方式</param>
    /// <param name="minvalue">最小值</param>
    /// <param name="maxvalue">最大值</param>
    /// <param name="formate">数据格式</param>
    public void setvaluerange(int startrowindex, int endrowindex, int startcolinex, int endcolindex, npoidatatype type, operatortypes operatortype, string minvalue, string maxvalue, string formate)
    {
        setvaluerange(startrowindex, endrowindex, startcolinex, endcolindex, type, operatortype, minvalue, maxvalue, formate, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行</param>
    /// <param name="endrowindex">结束行</param>
    /// <param name="startcolinex">开始列</param>
    /// <param name="endcolindex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatortype">验证方式</param>
    /// <param name="minvalue">最小值</param>
    /// <param name="maxvalue">最大值</param>
    /// <param name="formate">数据格式</param>
    /// <param name="alertmassage">报错信息</param>
    public void setvaluerange(int startrowindex, int endrowindex, int startcolinex, int endcolindex, npoidatatype type, operatortypes operatortype, string minvalue, string maxvalue, string formate, string alertmassage)
    {
        cellrangeaddresslist regions = new cellrangeaddresslist(startrowindex, endrowindex, startcolinex, endcolindex);
        dvconstraint constraint = dvconstraint.createnumericconstraint(validationtype.any, 0, null, null);
        switch (type)
        {
            case npoidatatype.integer:
                constraint = dvconstraint.createnumericconstraint(validationtype.integer, (int)operatortype, minvalue, maxvalue);
                break;
            case npoidatatype.float:
                constraint = dvconstraint.createnumericconstraint(validationtype.decimal, (int)operatortype, minvalue, maxvalue);
                break;
            case npoidatatype.date:
                if (formate == "")
                {
                    formate = "yyyy/mm/dd";
                }
                constraint = dvconstraint.createdateconstraint((int)operatortype, minvalue, maxvalue, formate);
                break;
            case npoidatatype.time:
                constraint = dvconstraint.createtimeconstraint((int)operatortype, minvalue, maxvalue);
                break;
            case npoidatatype.textlength:
                constraint = dvconstraint.createnumericconstraint(validationtype.text_length, (int)operatortype, minvalue, maxvalue);
                break;
            default:
                break;
        }

        hssfdatavalidation datavalidate1 = new hssfdatavalidation(regions, constraint);
        if (!string.isnullorempty(alertmassage))
        {
            datavalidate1.createerrorbox("error", alertmassage);
        }
        activesheet.addvalidationdata(datavalidate1);

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行</param>
    /// <param name="endrowindex">结束行</param>
    /// <param name="startcolinex">开始列</param>
    /// <param name="endcolindex">结束列</param>
    /// <param name="datarange">值系列</param>
    public void setvaluerange(int startrowindex, int endrowindex, int startcolinex, int endcolindex, string[] datarange)
    {

        setvaluerange(startrowindex, endrowindex, startcolinex, endcolindex, datarange, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行</param>
    /// <param name="endrowindex">结束行</param>
    /// <param name="startcolinex">开始列</param>
    /// <param name="endcolindex">结束列</param>
    /// <param name="datarange">值系列</param>
    /// <param name="alertmassage">报错信息</param>
    public void setvaluerange(int startrowindex, int endrowindex, int startcolinex, int endcolindex, string[] datarange, string alertmassage)
    {
        isheetconditionalformatting hscf = activesheet.sheetconditionalformatting;
        cellrangeaddress[] regions = {
                new cellrangeaddress(startrowindex, endrowindex,startcolinex,endcolindex)
            };

        cellrangeaddresslist rangelist = new cellrangeaddresslist();
        rangelist.addcellrangeaddress(new cellrangeaddress(startrowindex, endrowindex, startcolinex, endcolindex));
        dvconstraint dvconstraint = dvconstraint.createexplicitlistconstraint(datarange);
        hssfdatavalidation datavalidation = new hssfdatavalidation(rangelist, dvconstraint);

        if (!string.isnullorempty(alertmassage))
        {
            datavalidation.createerrorbox("error", alertmassage);
        }

        activesheet.addvalidationdata(datavalidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行</param>
    /// <param name="endrowindex">结束行</param>
    /// <param name="startcolinex">开始列</param>
    /// <param name="endcolindex">结束列</param>
    /// <param name="formula">计算公式</param>
    /// <param name="alertmassage">报错信息</param>
    public void setvaluerange(int startrowindex, int endrowindex, int startcolinex, int endcolindex, string formula, string alertmassage)
    {
        isheetconditionalformatting hscf = activesheet.sheetconditionalformatting;
        cellrangeaddress[] regions = {
                new cellrangeaddress(startrowindex, endrowindex,startcolinex,endcolindex)
            };

        cellrangeaddresslist rangelist = new cellrangeaddresslist();
        rangelist.addcellrangeaddress(new cellrangeaddress(startrowindex, endrowindex, startcolinex, endcolindex));
        dvconstraint dvconstraint = dvconstraint.createformulalistconstraint(formula);
        hssfdatavalidation datavalidation = new hssfdatavalidation(rangelist, dvconstraint);

        if (!string.isnullorempty(alertmassage))
        {
            datavalidation.createerrorbox("error", alertmassage);
        }

        activesheet.addvalidationdata(datavalidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行</param>
    /// <param name="endrowindex">结束行</param>
    /// <param name="startcolinex">开始列</param>
    /// <param name="endcolindex">结束列</param>
    /// <param name="formula">计算公式</param>
    public void setvaluerange(int startrowindex, int endrowindex, int startcolinex, int endcolindex, string formula)
    {
        setvaluerange(startrowindex, endcolindex, startrowindex, endcolindex, formula, "");
    }

    /// <summary>
    /// 生成单元格样式
    /// </summary>
    /// <returns>与当前excel相关的单元格样式</returns>
    public icellstyle createcellstyle()
    {
        return book.createcellstyle();
    }

    /// <summary>
    /// 生成字体
    /// </summary>
    /// <returns>与当前excel相关的字体</returns>
    public ifont createfont()
    {
        return book.createfont();
    }

    /// <summary>
    /// 设置单元格样式
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    /// <param name="columnindex">列号</param>
    /// <param name="style">样式</param>
    public void setstyle(int rowindex, int columnindex, icellstyle style)
    {
        irow row = book.getsheetat(sheetid).getrow(rowindex);
        if (row == null)
        {
            row = book.getsheetat(sheetid).createrow(rowindex);
        }
        icell cell = row.getcell(columnindex);
        if (cell == null)
        {
            cell = row.createcell(columnindex);
        }
        cell.cellstyle = style;
    }

    /// <summary>
    /// 合并单元格
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="startrowindex">开始行号</param>
    /// <param name="startcolumnindex">开始列号</param>
    /// <param name="endrowindex">结束行号</param>
    /// <param name="endcolumnindex">结束列号</param>
    public void mergecells(int startrowindex, int startcolumnindex, int endrowindex, int endcolumnindex)
    {
        int index = book.getsheetat(sheetid).addmergedregion(new cellrangeaddress(startrowindex, endrowindex, startcolumnindex, endcolumnindex));
    }

    /// <summary>
    /// 拆分单元格
    /// 注1：只对当前activesheet有效
    /// 注2：只有合并的单元格才能拆分
    /// </summary>
    /// <param name="startrowindex">开始行号</param>
    /// <param name="startcolumnindex">开始列号</param>
    public void unmergecells(int startrowindex, int startcolumnindex)
    {
        int merges = book.getsheetat(sheetid).nummergedregions;
        cellrangeaddress merge;
        for (int i = 0; i < merges; i++)
        {
            merge = book.getsheetat(sheetid).getmergedregion(i);
            if (merge.firstrow == startrowindex && merge.firstcolumn == startcolumnindex)
            {
                book.getsheetat(sheetid).removemergedregion(i);
                break;
            }
        }
    }

    /// <summary>
    /// 保存到文件
    /// 注：有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <param name="filename">保存文件名</param>
    public void save(string filename)
    {
        filestream file = new filestream(filename, filemode.create);
        book.write(file);
        file.close();
    }

    /// <summary>
    /// 保存到流
    /// 注：保存或下载时，有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <returns>内存流</returns>
    public memorystream save()
    {
        memorystream ms = new memorystream();
        book.write(ms);
        return ms;
    }

    /// <summary>
    /// 把excel读成dataset
    /// 注：必须是正规表格式
    /// </summary>
    /// <returns>读出的excel</returns>
    public dataset readasdataset()
    {
        dataset rtn = new dataset();
        for (int i = 0; i < sheetcount; i++)
        {
            isheet sheet = book.getsheetat(i);
            rtn.tables.add(getdatatablebysheet(sheet));
        }
        return rtn;
    }

    private datatable getdatatablebysheet(isheet sheet)
    {
        datatable dt = new datatable(sheet.sheetname);
        int maxcols = 0;
        object value;
        while ((value = readvalue(sheet, 0, maxcols)) != null)
        {
            dt.columns.add(value.tostring());
            maxcols++;
        }

        int row = 1;

        bool emptyrow = false;
        int emptyrowcount = 0;
        while (emptyrowcount < 10)
        {
            emptyrow = true;
            datarow dr = dt.newrow();
            for (int i = 0; i < maxcols; i++)
            {
                value = readvalue(sheet, row, i);
                if (value != null)
                {
                    dr[i] = value;
                    emptyrow = false;
                }
            }
            if (!emptyrow)
            {
                dt.rows.add(dr);
                emptyrowcount = 0;
            }
            else
            {
                emptyrowcount++;
            }
            row++;
        }

        return dt;
    }

    /// <summary>
    /// 根据sheetname导出数据为datatable
    /// </summary>
    /// <param name="sheetname">sheet名称</param>
    /// <returns></returns>
    public datatable getdatatablebysheet(string sheetname)
    {

        isheet sheet = book.getsheet(sheetname);
        if (sheet != null)
        {
            return getdatatablebysheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 根据sheetname导出数据为datatable
    /// </summary>
    /// <param name="sheetindex">sheet编号</param>
    /// <returns></returns>
    public datatable getdatatablebysheet(int sheetindex)
    {

        isheet sheet = book.getsheetat(sheetindex);
        if (sheet != null)
        {
            return getdatatablebysheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 写入表格
    /// </summary>
    /// <param name="data">表格数据</param>
    /// <param name="col">写入的起始列</param>
    /// <param name="row">写入的起始行</param>
    /// <param name="titlecolor">标题颜色</param>
    /// <param name="fullborder">是否需要四周边框</param>
    public void writedatatable(datatable data, int col = 1, int row = 1, short? titlecolor = null, bool fullborder = true)
    {
        if (data == null)
        {
            return;
        }

        var titlestyle = createcellstyle();
        var rowstyle = createcellstyle();
        if (titlecolor != null)
            titlestyle.fillforegroundcolor = titlecolor.value;

        titlestyle.fillpattern = fillpatterntype.solid_foreground;
        if (fullborder)
        {
            titlestyle.borderbottom = borderstyle.thin;
            titlestyle.borderleft = borderstyle.thin;
            titlestyle.borderright = borderstyle.thin;
            titlestyle.bordertop = borderstyle.thin;
            titlestyle.bottombordercolor = npoicolor.black;
            titlestyle.leftbordercolor = npoicolor.black;
            titlestyle.rightbordercolor = npoicolor.black;
            titlestyle.topbordercolor = npoicolor.black;
            rowstyle.borderbottom = borderstyle.thin;
            rowstyle.borderleft = borderstyle.thin;
            rowstyle.borderright = borderstyle.thin;
            rowstyle.bordertop = borderstyle.thin;
            rowstyle.bottombordercolor = npoicolor.black;
            rowstyle.leftbordercolor = npoicolor.black;
            rowstyle.rightbordercolor = npoicolor.black;
            rowstyle.topbordercolor = npoicolor.black;
        }

        int icol = 0, irow = 1;

        foreach (datacolumn dc in data.columns)
        {
            setvalue(row, col + icol, dc.columnname);
            setstyle(row, col + icol, titlestyle);
            icol++;
        }

        rowstyle.fillforegroundcolor = npoicolor.white;

        foreach (datarow dr in data.rows)
        {
            icol = 0;
            foreach (datacolumn dc in data.columns)
            {
                setvalue(row + irow, col + icol, dr[dc]);
                setstyle(row + irow, col + icol, rowstyle);
                icol++;
            }
            irow++;
        }

        for (int i = 0; i < icol; i++)
        {
            this.autocolwidth(i);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前activesheet有效
    /// </summary>
    /// <param name="rowindex">行号</param>
    /// <param name="columnindex">列号</param>
    /// <returns>单元格的值</returns>
    public object readvalue(isheet sheet, int rowindex, int columnindex, bool? isdatetime = null)
    {
        try
        {
            icell cell = sheet.getrow(rowindex).getcell(columnindex);
            short df = cell.cellstyle.dataformat;

            //return cell.tostring();
            switch (cell.celltype)
            {
                case celltype.blank:
                    return null;
                case celltype.boolean:
                    return cell.booleancellvalue;
                case celltype.error:
                    throw new exception("cell value error");
                case celltype.formula:
                    {
                        switch (cell.cachedformularesulttype)
                        {
                            case celltype.blank:
                                return "";
                            case celltype.boolean:
                                return cell.booleancellvalue;
                            case celltype.error:
                                throw new exception("cell value error");
                            case celltype.formula:
                                throw new exception("the formula of this cell is too complex！");
                            case celltype.numeric:
                                if (isdatetime == null)
                                {
                                    if (dateutil.iscelldateformatted(cell))
                                    {
                                        return cell.datecellvalue;
                                    }
                                    else
                                    {
                                        return cell.numericcellvalue;
                                    }
                                }
                                else if (isdatetime == true)
                                {
                                    return cell.datecellvalue;
                                }
                                else
                                {
                                    return cell.numericcellvalue;
                                }
                            case celltype.string:
                                return cell.stringcellvalue;
                            case celltype.unknown:
                                return cell.tostring();
                            default:
                                return cell.tostring();
                        }
                    }
                case celltype.numeric:
                    {
                        if (isdatetime == null)
                        {
                            if (dateutil.iscelldateformatted(cell))
                            {
                                return cell.datecellvalue;
                            }
                            else
                            {
                                return cell.numericcellvalue;
                            }
                        }
                        else if (isdatetime == true)
                        {
                            return cell.datecellvalue;
                        }
                        else
                        {
                            return cell.numericcellvalue;
                        }
                    }
                case celltype.string:
                    return cell.stringcellvalue;
                case celltype.unknown:
                    return cell.tostring();
                default:
                    return cell.tostring();
            }
        }
        catch (system.nullreferenceexception)
        {
            return null;
        }
        catch (exception ex)
        {
            throw ex;
        }
    }

    public int sheetcount
    {
        get
        {
            return book.numberofsheets;
        }
    }

    public string getsheetname(int index)
    {
        return book.getsheetname(index);
    }

    public void addpicture(byte[] data, int row, int col)
    {
        int picindex = book.addpicture(data, picturetype.png);

        idrawing draw = activesheet.createdrawingpatriarch();

        iclientanchor anchor = draw.createanchor(0, 0, 255, 255, col, row, col + 5, col + 5);
        ipicture pic = draw.createpicture(anchor, picindex);
        pic.resize();
    }
}

public enum operatortypes
{
    /// <summary>
    /// 介于最大值与小值之间
    /// </summary>
    between = operatortype.between,
    /// <summary>
    /// 等于最小值
    /// </summary>
    equal = operatortype.equal,
    /// <summary>
    /// 大于或等于最小值
    /// </summary>
    greater_or_equal = operatortype.greater_or_equal,
    /// <summary>
    /// 大于最小值
    /// </summary>
    greater_than = operatortype.greater_than,
    /// <summary>
    /// 忽略
    /// </summary>
    no_comparison = operatortype.ignored,
    /// <summary>
    /// 小于或等于最小值
    /// </summary>
    less_or_equal = operatortype.less_or_equal,
    /// <summary>
    /// 小于最小值
    /// </summary>
    less_than = operatortype.less_than,
    /// <summary>
    /// 不在最小值与最大值之间
    /// </summary>
    not_between = operatortype.not_between,
    /// <summary>
    /// 不等于最小值
    /// </summary>
    not_equal = operatortype.not_equal
}

public enum npoidatatype
{
    /// <summary>
    /// 验证整数
    /// </summary>
    integer,
    /// <summary>
    /// 验证符点数
    /// </summary>
    float,
    /// <summary>
    /// 验证日期
    /// </summary>
    date,
    /// <summary>
    /// 验证时间
    /// </summary>
    time,
    /// <summary>
    /// 验证字符长度
    /// </summary>
    textlength
}

public static class npoicolor
{

    /// <summary>
    /// 红色
    /// </summary>
    public static short red { get { return npoi.hssf.util.hssfcolor.red.index; } }

    /// <summary>
    /// 蓝色
    /// </summary>
    public static short blue { get { return npoi.hssf.util.hssfcolor.blue.index; } }

    /// <summary>
    /// 浅绿色
    /// </summary>
    public static short aqua { get { return npoi.hssf.util.hssfcolor.aqua.index; } }

    /// <summary>
    /// 自动
    /// </summary>
    public static short automatic { get { return npoi.hssf.util.hssfcolor.automatic.index; } }

    /// <summary>
    /// 黑色
    /// </summary>
    public static short black { get { return npoi.hssf.util.hssfcolor.black.index; } }

    /// <summary>
    /// 蓝灰色
    /// </summary>
    public static short blue_grey { get { return npoi.hssf.util.hssfcolor.blue_grey.index; } }

    /// <summary>
    /// 明绿色
    /// </summary>
    public static short bright_green { get { return npoi.hssf.util.hssfcolor.bright_green.index; } }

    /// <summary>
    /// 棕色
    /// </summary>
    public static short brown { get { return npoi.hssf.util.hssfcolor.brown.index; } }

    /// <summary>
    /// 正常
    /// </summary>
    public static short color_normal { get { return npoi.hssf.util.hssfcolor.color_normal; } }

    /// <summary>
    /// 珊瑚色
    /// </summary>
    public static short coral { get { return npoi.hssf.util.hssfcolor.coral.index; } }

    /// <summary>
    /// 亮蓝色
    /// </summary>
    public static short cornflower_blue { get { return npoi.hssf.util.hssfcolor.cornflower_blue.index; } }

    /// <summary>
    /// 深蓝色
    /// </summary>
    public static short dark_blue { get { return npoi.hssf.util.hssfcolor.dark_blue.index; } }

    /// <summary>
    /// 深绿色
    /// </summary>
    public static short dark_green { get { return npoi.hssf.util.hssfcolor.dark_green.index; } }

    /// <summary>
    /// 深红色
    /// </summary>
    public static short dark_red { get { return npoi.hssf.util.hssfcolor.dark_red.index; } }

    /// <summary>
    /// 深茶色
    /// </summary>
    public static short dark_teal { get { return npoi.hssf.util.hssfcolor.dark_teal.index; } }

    /// <summary>
    /// 深黄
    /// </summary>
    public static short dark_yellow { get { return npoi.hssf.util.hssfcolor.dark_yellow.index; } }

    /// <summary>
    /// 金色
    /// </summary>
    public static short gold { get { return npoi.hssf.util.hssfcolor.gold.index; } }

    /// <summary>
    /// 绿色
    /// </summary>
    public static short green { get { return npoi.hssf.util.hssfcolor.green.index; } }

    /// <summary>
    /// 25%灰色
    /// </summary>
    public static short grey_25_percent { get { return npoi.hssf.util.hssfcolor.grey_25_percent.index; } }

    /// <summary>
    /// 40%灰色
    /// </summary>
    public static short grey_40_percent { get { return npoi.hssf.util.hssfcolor.grey_40_percent.index; } }

    /// <summary>
    /// 50%灰色
    /// </summary>
    public static short grey_50_percent { get { return npoi.hssf.util.hssfcolor.grey_50_percent.index; } }

    /// <summary>
    /// 80%灰色
    /// </summary>
    public static short grey_80_percent { get { return npoi.hssf.util.hssfcolor.grey_80_percent.index; } }

    /// <summary>
    /// 靛蓝色
    /// </summary>
    public static short indigo { get { return npoi.hssf.util.hssfcolor.indigo.index; } }

    /// <summary>
    /// 淡紫色
    /// </summary>
    public static short lavender { get { return npoi.hssf.util.hssfcolor.lavender.index; } }

    /// <summary>
    /// 粉黄色
    /// </summary>
    public static short lemon_chiffon { get { return npoi.hssf.util.hssfcolor.lemon_chiffon.index; } }

    /// <summary>
    /// 淡蓝色
    /// </summary>
    public static short light_blue { get { return npoi.hssf.util.hssfcolor.light_blue.index; } }

    /// <summary>
    /// 淡亮蓝色
    /// </summary>
    public static short light_cornflower_blue { get { return npoi.hssf.util.hssfcolor.light_cornflower_blue.index; } }

    /// <summary>
    /// 淡绿色
    /// </summary>
    public static short light_green { get { return npoi.hssf.util.hssfcolor.light_green.index; } }

    /// <summary>
    /// 淡桔黄色
    /// </summary>
    public static short light_orange { get { return npoi.hssf.util.hssfcolor.light_orange.index; } }

    /// <summary>
    /// 淡蓝绿色
    /// </summary>
    public static short light_turquoise { get { return npoi.hssf.util.hssfcolor.light_turquoise.index; } }

    /// <summary>
    /// 淡黄色
    /// </summary>
    public static short light_yellow { get { return npoi.hssf.util.hssfcolor.light_yellow.index; } }

    /// <summary>
    /// 绿黄色
    /// </summary>
    public static short lime { get { return npoi.hssf.util.hssfcolor.lime.index; } }

    /// <summary>
    /// 栗色
    /// </summary>
    public static short maroon { get { return npoi.hssf.util.hssfcolor.maroon.index; } }

    /// <summary>
    /// 橄榄绿色
    /// </summary>
    public static short olive_green { get { return npoi.hssf.util.hssfcolor.olive_green.index; } }

    /// <summary>
    /// 桔色
    /// </summary>
    public static short orange { get { return npoi.hssf.util.hssfcolor.orange.index; } }

    /// <summary>
    /// 白灰蓝色
    /// </summary>
    public static short pale_blue { get { return npoi.hssf.util.hssfcolor.pale_blue.index; } }

    /// <summary>
    /// 粉红色
    /// </summary>
    public static short pink { get { return npoi.hssf.util.hssfcolor.pink.index; } }

    /// <summary>
    /// 紫红色
    /// </summary>
    public static short plum { get { return npoi.hssf.util.hssfcolor.plum.index; } }

    /// <summary>
    /// 玫瑰红色
    /// </summary>
    public static short rose { get { return npoi.hssf.util.hssfcolor.rose.index; } }

    /// <summary>
    /// 高贵蓝
    /// </summary>
    public static short royal_blue { get { return npoi.hssf.util.hssfcolor.royal_blue.index; } }

    /// <summary>
    /// 海绿色
    /// </summary>
    public static short sea_green { get { return npoi.hssf.util.hssfcolor.sea_green.index; } }

    /// <summary>
    /// 天空蓝
    /// </summary>
    public static short sky_blue { get { return npoi.hssf.util.hssfcolor.sky_blue.index; } }

    /// <summary>
    /// 棕褐色
    /// </summary>
    public static short tan { get { return npoi.hssf.util.hssfcolor.tan.index; } }

    /// <summary>
    /// 茶色
    /// </summary>
    public static short teal { get { return npoi.hssf.util.hssfcolor.teal.index; } }

    /// <summary>
    /// 蓝绿色
    /// </summary>
    public static short turquoise { get { return npoi.hssf.util.hssfcolor.turquoise.index; } }

    /// <summary>
    /// 紫色
    /// </summary>
    public static short violet { get { return npoi.hssf.util.hssfcolor.violet.index; } }

    /// <summary>
    /// 白色
    /// </summary>
    public static short white { get { return npoi.hssf.util.hssfcolor.white.index; } }

    /// <summary>
    /// 黄色
    /// </summary>
    public static short yellow { get { return npoi.hssf.util.hssfcolor.yellow.index; } }

}

/// <summary>
/// 针对excel的oledb
/// </summary>
public class oledbexcel
{
    /// <summary>
    /// oledb连接
    /// </summary>
    public oledbconnection connection
    {
        get;
        set;
    }

    /// <summary>
    /// 用oledb对excel进行操作
    /// 注：必须是标准表形式excel内容
    /// </summary>
    /// <param name="excelfile">excel文件</param>
    public oledbexcel(string excelfile)
    {
        string constr = string.empty;
        fileinfo file = new fileinfo(excelfile);
        if (!file.exists) { throw new exception("文件不存在"); }
        string extension = file.extension;
        switch (extension)
        {
            case ".xls":
                constr = "provider=microsoft.jet.oledb.4.0;data source=" + excelfile + ";extended properties='excel 8.0;hdr=yes;imex=1;'";
                break;
            case ".xlsx":
                constr = "provider=microsoft.ace.oledb.12.0;data source=" + excelfile + ";extended properties='excel 12.0;hdr=yes;imex=1;'";
                break;
            default:
                constr = "provider=microsoft.jet.oledb.4.0;data source=" + excelfile + ";extended properties='excel 8.0;hdr=yes;imex=1;'";
                break;
        }

        //链接excel
        connection = new oledbconnection(constr);
    }

    private list<string> tablenames;

    /// <summary>
    /// 获取excel内的sheet名称
    /// </summary>
    public list<string> sheets
    {
        get
        {
            if (tablenames == null)
            {
                try
                {
                    tablenames = new list<string>();
                    //读取excel里面的sheet名
                    connection.open();

                    datatable schematable = connection.getoledbschematable(oledbschemaguid.tables, new object[] { null, null, null, "table" });

                    for (int i = 0; i < schematable.rows.count; i++)
                    {
                        datarow dr = schematable.rows[i];
                        string tbname = dr["table_name"].tostring();
                        if (tbname[tbname.length - 1] == '$')
                        {
                            tablenames.add(tbname);
                        }
                    }
                    connection.close();
                }
                catch (exception ex)
                {
                    if (connection.state != connectionstate.closed)
                    {
                        connection.close();
                    }
                    throw new exception(ex.message, ex);
                }
            }
            return tablenames;
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tablename">sheet名称</param>
    /// <returns>sheet内的所有数据</returns>
    public dataset queryall(string tablename)
    {
        try
        {
            dataset exceldata = new dataset();
            oledbdataadapter adapter = new oledbdataadapter();
            adapter.selectcommand = new oledbcommand();
            adapter.selectcommand.connection = connection;
            adapter.selectcommand.commandtext = string.format("select * from {0}", "[" + tablename + "]");
            adapter.fill(exceldata);
            return exceldata;
        }
        catch (exception ex)
        {
            if (connection.state != connectionstate.closed)
            {
                connection.close();
            }
            throw new exception(ex.message, ex);
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tableindex">sheet序号（从0开始）</param>
    /// <returns>sheet内的所有数据</returns>
    public dataset queryall(int tableindex)
    {
        return queryall(sheets[tableindex]);
    }

    /// <summary>
    /// 利用sql进行查询
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>查询出的数据</returns>
    public dataset query(string sql)
    {
        try
        {
            dataset exceldata = new dataset();
            oledbdataadapter adapter = new oledbdataadapter(sql, connection);
            adapter.fill(exceldata);
            return exceldata;
        }
        catch (exception ex)
        {
            if (connection.state != connectionstate.closed)
            {
                connection.close();
            }
            throw new exception(ex.message, ex);
        }
    }

    /// <summary>
    /// 利用sql进行查询
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="param">查询参数</param>
    /// <returns>查询出的数据</returns>
    public dataset query(string sql, params oledbparameter[] param)
    {
        try
        {
            dataset exceldata = new dataset();
            oledbdataadapter adapter = new oledbdataadapter(sql, connection);
            adapter.selectcommand.parameters.addrange(param);
            adapter.fill(exceldata);
            return exceldata;
        }
        catch (exception ex)
        {
            if (connection.state != connectionstate.closed)
            {
                connection.close();
            }
            throw new exception(ex.message, ex);
        }
    }

    /// <summary>
    /// 利用sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>影响的行数</returns>
    public int executesql(string sql)
    {
        try
        {
            connection.open();
            oledbcommand cmd = connection.createcommand();
            cmd.commandtext = sql;
            int rtn = cmd.executenonquery();
            connection.close();
            return rtn;
        }
        catch (exception ex)
        {
            if (connection.state != connectionstate.closed)
            {
                connection.close();
            }
            throw new exception(ex.message, ex);
        }
    }

    /// <summary>
    /// 利用sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="param">执行参数</param>
    /// <returns>影响的行数</returns>
    public int executesql(string sql, params oledbparameter[] param)
    {
        try
        {
            connection.open();
            oledbcommand cmd = connection.createcommand();
            cmd.commandtext = sql;
            cmd.parameters.addrange(param);
            int rtn = cmd.executenonquery();
            connection.close();
            return rtn;
        }
        catch (exception ex)
        {
            if (connection.state != connectionstate.closed)
            {
                connection.close();
            }
            throw new exception(ex.message, ex);
        }
    }
}

view code

3、后台引用

　　　　[httppost]
        public actionresult import(httppostedfilebase importfile, sgm_shenheform form, int? pagesize, int? currentpageindex, int? replaceflag = 0)
        {
            importfile = request.files["importfile"];
            if (importfile == null)
            {
                return content("<script>alert('未选择文件！');history.go(-1);</script>");
            } 

　　　　　　　　npoiexcel excel = new npoiexcel(importfile.inputstream, importfile.filename);
        
                var item_seri_str = excel.readvalue(i, 2);   //获取excel里的值
            
        }
        
     

4、界面、js

  <div style="margin-top: 10px; margin-bottom: 5px; position: relative; overflow-y: hidden;display:inline">
                        <input type="button" id="btnimport" value="导入" />
                        <input name="importfile" id="importfile" type="file" style="opacity: 0; filter: alpha(opacity=0); width: 80px; font-size: 15px; position: absolute; top: 0; left: 0; margin-left: -15px;" accept=".xls,.xlsx"
                            onchange=" $.messager.progress({title: '请稍候',msg: '正在处理数据...'});import();" />
                    </div>

  function import() {
        debugger;
        var old = $("#f1").attr("action");  //form 表单的id
        $("#f1").attr("action", "@url.action("import")");
        $("#f1").attr("enctype", "multipart/form-data");
        $("#f1").submit();
        $("#f1").attr("action", old);
    }

前台也可以是不用表单形式，放一个 <input type="file" name="upfile" value=" " /> 按钮提交到后台一个方法，方法里接收file

 5、效果

点击导入按钮后弹出选择文件框，选择excel文件后（双击或者打开）直接提交后台