Friday, May 20, 2011

Excel to Dataset

using System.Data.OleDb;

private static DataSet ExceltoDataset(string FileName,string strSheet)
    {       
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                         FileName + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";

        DataSet output = new DataSet();

        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            conn.Open();

            DataTable schemaTable = conn.GetOleDbSchemaTable(
              OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            foreach (DataRow schemaRow in schemaTable.Rows)
            {
                string sheet = schemaRow["TABLE_NAME"].ToString();
                if (sheet == strSheet)
                {
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;

                    DataTable outputTable = new DataTable(sheet);
                    output.Tables.Add(outputTable);
                    new OleDbDataAdapter(cmd).Fill(outputTable);
                }
            }
        }
        return output;
    }

No comments:

Post a Comment