欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#实现EXCEL表格转DataTable

程序员文章站 2022-06-22 11:06:12
C#代码实现把Excel文件转化为DataTable,根据Excel的文件后缀名不同,用不同的方法来进行实现,下面通过根据Excel文件的两种后缀名(*.xlsx和*.xls)分别来实现。获取文件后缀名的方法是:Path.GetExtension(fileName)方法,通过引用:using Sys ......

    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;
        }