将Excel工作表数据读入DataTable [英] Read Excel Sheet Data into DataTable

问题描述

大家好..



我有Excel表格，工作表名称是Sheet1，Sheet2，Sheet3 ...



我想将Sheet3数据加载到我的DataTable中。



如何做到这一点可以帮助我做任何事情。< br $> b $ b



提前感谢

Hi everyone..

I have an Excel Sheet ,in that work sheet names are Sheet1,Sheet2,Sheet3...

I want to load Sheet3 data in to my DataTable.

How to do this can any one help me to do this .


Thank's in advance

推荐答案

按照这个过程..

follow this process..

public static DataTable exceldata(string filePath)
        {     
            DataTable dtexcel = new DataTable();
               bool hasHeaders = false;
                string HDR = hasHeaders ? "Yes" : "No";
                string strConn;
                if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                else
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                //Looping Total Sheet of Xl File
                /*foreach (DataRow schemaRow in schemaTable.Rows)
                {
                }*/
                //Looping a first Sheet of Xl File
                DataRow schemaRow = schemaTable.Rows[0];
                string sheet = schemaRow["TABLE_NAME"].ToString();
                if (!sheet.EndsWith("_"))
                {
                    string query = "SELECT  * FROM [" + sheet3 + "]";
                    OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                    dtexcel.Locale = CultureInfo.CurrentCulture;
                    daexcel.Fill(dtexcel);
                }
            
            conn.Close();
            return dtexcel;

        }

它的工作原理与数据库相同。

赞

It works same as in case of database.
like

OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(@"~\data\cocustomerdetails.xlsx") + "; Extended Properties=Excel 12.0;");

    OleDbCommand oconn = new OleDbCommand("select * from [Sheet1

，cnn）;
cnn.Open（）;
OleDbDataAdapter adp = new OleDbDataAdapter（oconn）;
DataTable dt = new DataTable（）;
adp.Fill（dt）;

", cnn); cnn.Open(); OleDbDataAdapter adp = new OleDbDataAdapter(oconn); DataTable dt = new DataTable(); adp.Fill(dt);

这篇关于将Excel工作表数据读入DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！