将Excel文件导出至DataTable(第一行作为表头)

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/MajorMayer/article/details/50467921

将Excel文件导出至DataTable(第一行作为表头)

// <summary>
    /// 将Excel文件导出至DataTable(第一行作为表头)
    /// </summary>
    /// <param name="ExcelFilePath">Excel文件路径</param>
    /// <param name="TableName">数据表名,如果数据表名错误,默认为第一个数据表名</param>
    public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
    {
        if (!File.Exists(ExcelFilePath))
        {
            throw new Exception("Excel文件不存在!");
        }

        //如果数据表名不存在,则数据表名为Excel文件的第一个数据表
        ArrayList TableList = new ArrayList();
        TableList = GetExcelTables(ExcelFilePath);

        if (TableName.IndexOf(TableName) < 0)
        {
            TableName = TableList[0].ToString().Trim();
        }

        DataTable table = new DataTable();
        OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");
        OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
        OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

        try
        {
            if (dbcon.State == ConnectionState.Closed)
            {
                dbcon.Open();
            }
            adapter.Fill(table);
        }
        catch (Exception exp)
        {
            throw exp;
        }
        finally
        {
            if (dbcon.State == ConnectionState.Open)
            {
                dbcon.Close();
            }
        }
        return table;
    }

用法:
InputFromExcel("Excel文件路径","数据表名,如果数据表名错误,默认为第一个数据表名")

<pre name="code" class="csharp">    /// <summary>
    /// 获取Excel文件数据表列表
    /// </summary>
    public static ArrayList GetExcelTables(string ExcelFileName)
    {
        DataTable dt = new DataTable();
        ArrayList TablesList = new ArrayList();
        if (File.Exists(ExcelFileName))
        {
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
            {
                try
                {
                    conn.Open();
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                }
                catch (Exception exp)
                {
                    throw exp;
                }

                //获取数据表个数
                int tablecount = dt.Rows.Count;
                for (int i = 0; i < tablecount; i++)
                {
                    string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                    if (TablesList.IndexOf(tablename) < 0)
                    {
                        TablesList.Add(tablename);
                    }
                }
            }
        }
        return TablesList;
    }



没有更多推荐了,返回首页