Click here to Skip to main content
15,892,161 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi..
We hav upload excel sheet.. but in code behind we are giving Excelsheet name..
actually w shld not giv d sheet name.. How to retrieve sheet name dynamicaly.
Posted

Hi Check the Code
public DataSet GetDataFromExcel(string filePath)
{
    try
    {
    string strConn;
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";
    DataTable dt = new DataTable();
    dt = null;
    using (OleDbConnection oleDB = new OleDbConnection(strConn))
    {
        oleDB.Open();
        dt = oleDB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt == null)
            return null;

        ListItemCollection items = new ListItemCollection();
        int i = 0;

        //if (dt.Rows.Count > 1)
        //return null;  

        for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
        {
            string excelSheetName;
            string lastCharacter = "";

            excelSheetName = dt.Rows[rowIndex]["TABLE_NAME"].ToString();
            excelSheetName = excelSheetName.Replace("'", "");
            lastCharacter = excelSheetName.Substring(excelSheetName.Length - 1, 1);
            if (lastCharacter == "$")
            {
                items.Add(dt.Rows[rowIndex]["TABLE_NAME"].ToString());
            }
        }
        if (items.Count > 1)
            return null;

        string sName;
        string query;

        sName = items[0].ToString();
        sName = sName.Replace("'", "");
        sName = sName.Replace("$", "");

        query = "";
        query = String.Format("select * from [{0}$]", sName);
        OleDbDataAdapter da = new OleDbDataAdapter(query, strConn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    }
    catch (Exception ex)
    {

        throw ex;
    }
}



public static DataSet ImportExceltoDataset(string file)
{
    IExcelDataReader iExcelDataReader = null;

    FileStream oStream = File.Open(file, FileMode.Open, FileAccess.Read);

    iExcelDataReader = ExcelReaderFactory.CreateBinaryReader(oStream);

    iExcelDataReader.IsFirstRowAsColumnNames = true;

    DataSet dsUnUpdated = new DataSet();

    dsUnUpdated = iExcelDataReader.AsDataSet();

    iExcelDataReader.Close();

    return dsUnUpdated;
}
 
Share this answer
 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900