当前位置： 移动技术网 > IT编程>开发语言>c# > C#实现EXCEL表格转DataTable

C#实现EXCEL表格转DataTable

2020年01月03日  | 移动技术网IT编程  | 我要评论

    c#代码实现把excel文件转化为datatable，根据excel的文件后缀名不同，用不同的方法来进行实现，下面通过根据excel文件的两种后缀名（*.xlsx和*.xls）分别来实现。获取文件后缀名的方法是：path.getextension(filename)方法，通过引用：using system.io;实现代码如下：（其中以下代码中出现的filename都是带盘符的绝对路径）

  • 根据excel文件的后缀名不同调用的主方法

     private datatable filetodatatable(string filename)
    {
        datatable dt = new datatable();
        string extendname = path.getextension(filename);//获取文件的后缀名
        switch (extendname.tolower())
        {
            case ".xls":
                dt = xlstodatatable(filename);
                break;
            case ".xlsx":
                dt = xlsxtodatatable(filename);
                break;
            default:
                break;
        }
        return dt;
    }

     

  • xlstodatatable()

     private datatable xlstodatatable(string filename)
    {
        datatable datatable = new datatable();
        stream stream = null;
        try
        {
            stream = file.openread(filename);
            hssfworkbook hssfworkbook = new hssfworkbook(stream);
            hssfsheet hssfsheet = (hssfsheet)hssfworkbook.getsheetat(hssfworkbook.activesheetindex);
            hssfrow hssfrow = (hssfrow)hssfsheet.getrow(0);
            int lastcellnum = (int)hssfrow.lastcellnum;
            for (int i = (int)hssfrow.firstcellnum; i < lastcellnum; i++)
            {
                datacolumn column = new datacolumn(hssfrow.getcell(i).stringcellvalue);
                datatable.columns.add(column);
            }
            datatable.tablename = hssfsheet.sheetname;
            int lastrownum = hssfsheet.lastrownum;
            //列名后,从table第二行开始进行填充数据
            for (int i = hssfsheet.firstrownum + 1; i < hssfsheet.lastrownum; i++)//
            {
                hssfrow hssfrow2 = (hssfrow)hssfsheet.getrow(i);
                datarow datarow = datatable.newrow();
                for (int j = (int)hssfrow2.firstcellnum; j < lastcellnum; j++)//
                {
                    datarow[j] = hssfrow2.getcell(j);//
                }
                datatable.rows.add(datarow);
            }
            stream.close();
        }
        catch (exception ex)
        {
            scriptmanager.registerstartupscript(page, gettype(), "alertform", "alert(' xls to datatable: " + ex.message + "');", true);
        }
        finally
        {
            if (stream != null)
            {
                stream.close();
            }
        }
        return datatable;
    }

     

  • xlsxtodatatable() 
    public datatable xlsxtodatatable(string vfilepath)
    {
        datatable datatable = new datatable();
        try
        {
            sldocument sldocument = new sldocument(vfilepath);
            datatable.tablename = sldocument.getsheetnames()[0];
            slworksheetstatistics worksheetstatistics = sldocument.getworksheetstatistics();
            int startcolumnindex = worksheetstatistics.startcolumnindex;
            int endcolumnindex = worksheetstatistics.endcolumnindex;
            int startrowindex = worksheetstatistics.startrowindex;
            int endrowindex = worksheetstatistics.endrowindex;
            for (int i = startcolumnindex; i <= endcolumnindex; i++)
            {
                slrsttype cellvalueasrsttype = sldocument.getcellvalueasrsttype(1, i);
                datatable.columns.add(new datacolumn(cellvalueasrsttype.gettext(), typeof(string)));
            }
            for (int j = startrowindex + 1; j <= endrowindex; j++)
            {
                datarow datarow = datatable.newrow();
                for (int i = startcolumnindex; i <= endcolumnindex; i++)
                {
                    datarow[i - 1] = sldocument.getcellvalueasstring(j, i);
                }
                datatable.rows.add(datarow);
            }
        }
        catch (exception ex)
        {
            throw new exception("xlsx to datatable: \n" + ex.message);
        }
        return datatable;
    }

     

     

     

     

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网