Pages

Men

rh

7/30/2014

ExcelReader in C#

 using System.Text;
using System.Data;
using System.Data.OleDb;




  #region Data
        String DB_Path = String.Empty;
        String Con_Str = String.Empty;
        OleDbConnection con;
        #endregion
        public ExcelReader(String filePath)
        {
            if (!String.IsNullOrEmpty(filePath))
            {
                DB_Path = filePath;
                Con_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB_Path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
                con = new OleDbConnection(Con_Str);

            }
        }

        public DataSet readFromFile(String QueryString)
        {
            String qry = String.Empty;
            if (String.IsNullOrEmpty(QueryString))
            {
                qry = "SELECT * FROM [sheet1$]";
            }
            else
            {
                qry = QueryString;
            }
            try
            {
                DataSet ds = new DataSet();
                OleDbDataAdapter odp = new OleDbDataAdapter(qry, con);
                using (odp)
                {
                    odp.Fill(ds);
                }
                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet readFromXlFile(String SheetName)
        {
            String qry = String.Empty;
            if (String.IsNullOrEmpty(SheetName))
            {
                qry = "SELECT * FROM [sheet1$]";
            }
            else
            {
                qry = "SELECT * FROM '"+SheetName+"$]'";
            }
            try
            {
                DataSet ds = new DataSet();
                OleDbDataAdapter odp = new OleDbDataAdapter(qry, con);
                using (odp)
                {
                    odp.Fill(ds);
                }
                return ds;
            }
            catch
            {
                return null;
            }
        }

No comments :

Post a Comment