using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;

/// <summary>
/// Summary description for dbconnection
/// </summary>
public class Excelhelper
{

    #region Variable Declaration
    DataSet ds;
    OleDbDataAdapter adptr;
    OleDbConnection con = null;
    String connString = null;

    #endregion
    public Excelhelper()
    {

    }
    /// <summary>
    /// connection path for excelsheet
    /// </summary>
    /// <param name="filename"></param>
    public Excelhelper(string filename)
    {
        connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0 xml;IMEX=1;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text'";
        //connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;MAXSCANROWS=0;\"";
        //connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0;";
        //this.connString = connString;
        //connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "datasource = " + filename + ";Extended Properties=Excel 12.0;";
        con = new OleDbConnection(connString);
    }
    /// <summary>
    /// methord for getconnectinon excel file 
    /// </summary>
    /// <returns></returns>
    //public OleDbConnection getconnection()
    //{ 

    //connString= ConfigurationManager.AppSettings["Provider"] + "Data Source=" + ConfigurationManager.AppSettings["DataSource"];

    //  //connString=" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Users\\anupc\\Desktop\\ExcelProject\\App_Data\\PricingDatabase.mdb;Persist Security Info=True";

    //    con=new OleDbConnection( connString );
    //    con.Open();
    //    return con;
    //}
    //public void closeconnection()
    //{
    //    connString = ConfigurationManager.AppSettings["Provider"] + "Data Source=" + ConfigurationManager.AppSettings["DataSource"];

    //    con = new OleDbConnection(connString);
    //    con.Close();

    //}
    /// <summary>
    /// pic data from excelsheet
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public DataSet GetDataFromSheet(string query)
    {
        try
        {
            if (con.State != ConnectionState.Open)
            con.Open();
            adptr = new OleDbDataAdapter(query, con);
            ds = new DataSet();
            adptr.Fill(ds);
            return ds;
        }
        catch
        {
            throw new Exception();
        }
        finally
        {
            con.Close();
        }
    }

    /// <summary>
    /// Public Method Use to Get Excel Sheet Names
    /// </summary>
    /// <returns>DataTable</returns>
    public ArrayList GetExcelSheetNames(string UploadFileName)
    {
        try
        {
            con = new OleDbConnection(connString);
            if (con.State != ConnectionState.Open)
                con.Open();
            DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            String[] excelSheets = new String[dt.Rows.Count];
            int increment = 0;
            ArrayList arrList = new ArrayList();
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[increment] = row["TABLE_NAME"].ToString();
                arrList.Add(excelSheets[increment]);
                increment++;
            }

            return arrList;

        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
            return null;
        }
        finally { con.Close(); }
    }

}