Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#4.0
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 21:30pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

To solve the problem you can use this link
 

http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order[^]
 
Best of luck!!
  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)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 1 Feb 2013
Copyright © CodeProject, 1999-2014
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