Wednesday, February 19, 2014

Read excel’s multiple sheet in asp.net with C#

I have create a program which need a concept for read multiple sheet at a time. but this is complex.  I have try to solved this problem. I have implement this code. This function is useful for you.



    private DataTable ReadExcelToTable(string path)
    {
        //Connection String (Excel)
        string connstring_Excel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
        using (OleDbConnection conn = new OleDbConnection(connstring_Excel))
        {
            conn.Open();
            //Get All Sheets Name
            DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            //Get the First Sheet Name
            string firstSheetName = sheetsName.Rows[0][2].ToString();

            //Query String
            string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);
            OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring_Excel);
            DataSet set = new DataSet();
            ada.Fill(set);
            conn.Close();
            DataTable tempdt = new DataTable();
            tempdt = set.Tables[0];
            return tempdt;
        }

    }


No comments:

Post a Comment

Generate All Database Backup From Sql Server Using Query

DECLARE   @name   VARCHAR ( 50 )   -- database name DECLARE   @path   VARCHAR ( 256 )   -- path for backup files DECLARE   @fileName  ...