Click here to Skip to main content
15,563,248 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi
during amongs years, i've been using the Microsoft.Jet.OLEDB and Microsoft.ACE.OLEDB.12.0 but I have a lot of problems with dot net core 2 problem1 and problem2

some blogs suggest use EPPlus but our boss don't like third party library.
what other alternative do you suggest?

What I have tried:

I've trying

C#
public class ExcelObject
{
     private string excelObject = = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};
                                     Extended Properties=\"Excel {3};HDR=YES\"";
     private string filepath = string.Empty;
     private OleDbConnection con = null;

        public delegate void ProgressWork(float percentage);
        private event ProgressWork Reading;
        private event ProgressWork Writeing;
        private event EventHandler connectionStringChange;

        public event ProgressWork ReadProgress
        {
            add
            {
                Reading += value;
            }
            remove
            {
                Reading -= value;
            }
        }

        public virtual void onReadProgress(float percentage)
        {
            if (Reading != null)
                Reading(percentage);
        }


        public event ProgressWork WriteProgress
        {
            add{ Writeing += value; }
            remove{ Writeing -= value; }
        }

        public virtual void onWriteProgress(float percentage)
        {
            if (Writeing != null)
                Writeing(percentage);
        }


        public event EventHandler ConnectionStringChanged
        {
            add{ connectionStringChange += value; }
            remove { connectionStringChange -= value; }
        }

        public virtual void onConnectionStringChanged()
        {
            if (this.Connection != null && 
                !this.Connection.ConnectionString.Equals(this.ConnectionString))
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
                this.Connection.Dispose();
                this.con = null;

            }
            if (connectionStringChange != null)
            {
                connectionStringChange(this, new EventArgs());
            }
        }
        //ConnectionString
        public string ConnectionString
        {
            get
            {
                if (!(this.filepath == string.Empty))
                {
                   //Check for File Format
                    FileInfo fi = new FileInfo(this.filepath);
                    if (fi.Extension.Equals(".xls"))
                    {
                        // For Excel Below 2007 Format
                        return string.Format(this.excelObject, 
                                   "Jet", "4.0", this.filepath, "8.0");
                    }
                    else if (fi.Extension.Equals(".xlsx"))
                    {
                        // For Excel 2007 File  Format
                        return string.Format(this.excelObject, 
                                   "Ace", "12.0", Me.filepath, "12.0");
                    }
                }
                else
                {
                    return string.Empty;
                }
            }
        }
        //OleDbConnection to the current File
        public OleDbConnection Connection
        {
            get
            {
                if (con == null)
                {
                    OleDbConnection _con = new OleDbConnection { 
                                ConnectionString = this.ConnectionString };
                    this.con = _con;
                }
                return this.con;
            }
        }


        public ExcelObject(string path)
        {

            this.filepath = path;
            this.onConnectionStringChanged();
        }
        // Reads the Schema Information
        public DataTable GetSchema()
        {
            DataTable dtSchema = null;
            if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
            dtSchema = this.Connection.GetOleDbSchemaTable(
                   OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            return dtSchema;
        }
        //Reads table and returns the DataTable
        public DataTable ReadTable(string tableName)
        {
            return this.ReadTable(tableName, "");
        }

        public DataTable ReadTable(string tableName, string criteria)
        {

            try
            {
                DataTable resultTable = null;
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                    onReadProgress(10);
                    
                }
                string cmdText = "Select * from [{0}]";
                if (!string.IsNullOrEmpty(criteria))
                {
                    cmdText += " Where " + criteria;
                }
                OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName));
                cmd.Connection = this.Connection;
                OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
                onReadProgress(30);
                
                DataSet ds = new DataSet();
                onReadProgress(50);
                
                adpt.Fill(ds, tableName);
                onReadProgress(100);
                
                if (ds.Tables.Count == 1)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                MessageBox.Show("Table Cannot be read");
                return null;
            }
        }
        //Generates DropTable statement and executes it.
        public bool DropTable(string tablename)
        {

            try
            {
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                    onWriteProgress(10);
                    
                }
                string cmdText = "Drop Table [{0}]";
                using (OleDbCommand cmd = new OleDbCommand(
                         string.Format(cmdText, tablename), this.Connection))
                {
                    onWriteProgress(30);
                    
                    cmd.ExecuteNonQuery();
                    onWriteProgress(80);
                    
                }
                this.Connection.Close();
                onWriteProgress(100);
                
                return true;
            }
            catch (Exception ex)
            {
                onWriteProgress(0);
                
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        // Creates Create Table Statement and runs it.
        public bool WriteTable(string tableName, Dictionary<string, string> 
                                                             tableDefination)
        {
            try
            {
                using (OleDbCommand cmd = new OleDbCommand(
                this.GenerateCreateTable(tableName, tableDefination), this.Connection))
                {
                    if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
        // Generates Insert Statement and executes it
        public bool AddNewRow(DataRow dr)
        {

            using (OleDbCommand cmd = new OleDbCommand(
                          this.GenerateInsertStatement(dr), this.Connection))
            {
               cmd.ExecuteNonQuery();
            }
            return true;
        }
        // Create Table Generation based on Table Defination
        private string GenerateCreateTable(string tableName, 
                            Dictionary<string, string> tableDefination)
        {

            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("CREATE TABLE [{0}](", tableName);
            firstcol = true;
            foreach (KeyValuePair<string, string> keyvalue in tableDefination)
            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;
                sb.AppendFormat("{0} {1}", keyvalue.Key, keyvalue.Value);
            }

            sb.Append(")");
            return sb.ToString();
        }
        //Generates InsertStatement from a DataRow.
        private string GenerateInsertStatement(DataRow dr)
        {
            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("INSERT INTO [{0}](", dr.Table.TableName);


            foreach (DataColumn dc in dr.Table.Columns)



            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;

                sb.Append(dc.Caption);
            }

            sb.Append(") VALUES(");
            firstcol = true;
            for (int i = 0; i <= dr.Table.Columns.Count - 1; i++)
            {
                if (!object.ReferenceEquals(dr.Table.Columns[i].DataType, typeof(int)))
                {
                    sb.Append("'");
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                    sb.Append("'");
                }
                else
                {
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                }
                if (i != dr.Table.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.Append(")");
            return sb.ToString();
        }


    }
Posted
Updated 6-Dec-18 9:05am
Comments
Mehdi Gholam 6-Dec-18 11:53am    
Change your boss, it would be easier :)
GREG_DORIANcod 6-Dec-18 16:08pm    
:D :D I wish
Richard MacCutchan 6-Dec-18 12:04pm    
Pay for MS Office and you can use the Interop namespaces.
Richard MacCutchan 6-Dec-18 12:06pm    
Interesting that those two problems both refer to not having the correct ACE driver installed, which is easy to resolve. I have used ACE extensively and it happily operates with .XLS and .XLSX files.

1 solution

You don't want a 3rd party library, but what about an Open Source project from which you can incorporate the code ?
GitHub - ExcelDataReader/ExcelDataReader: Lightweight and fast library written in C# for reading Microsoft Excel files[^]
For .NET Core usage read the note at the bottom, this is important !
 
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