Click here to Skip to main content
12,954,280 members (91,882 online)
Rate this:
 
Please Sign up or sign in to vote.
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 31-Jan-13 20:30pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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;
}
  Permalink  

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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,524
CHill60 3,490
Maciej Los 3,123
ppolymorphe 2,030
Jochen Arndt 1,975


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 1 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100