Monday, February 06, 2012

Code snippet for querying excel files as DataTable


public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
    {
        string HDR = hasHeaders ? "Yes" : "No";

        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                            FileName +
                                    ";Extended Properties='Excel 12.0;HDR=" +
                         HDR + ";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 = Convert.ToString(schemaRow["TABLE_NAME"]);

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                cmd.CommandType = CommandType.Text;

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