namespace HW.Utility
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Runtime.CompilerServices;
    using System.Text;

    public sealed class ExcelOle : IDisposable
    {
        public const int EXCEL_MAXROW = 0x10000;
        private string excelPath;
        private string specifyTableName;

        public event RenderDataFromDataRowHandler OnFillDataRowRender;

        public ExcelOle() : this("")
        {
        }

        public ExcelOle(string excelPath)
        {
            this.excelPath = excelPath;
        }

        private string CleanStringForSql(object obj)
        {
            return this.CleanStringForSql(obj.ToString());
        }

        private string CleanStringForSql(string str)
        {
            return str.Replace("'", "''");
        }

        public void Dispose()
        {
            this.excelPath = null;
        }

        public void FillDataByList(DataTable dt)
        {
            List<string> list = new List<string>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                list.Add(dt.Columns[i].ColumnName);
            }
            this.FillDataByList(dt, list.ToArray());
        }

        public void FillDataByList(DataTable dt, string[] columns)
        {
            if ((columns == null) || (columns.Length == 0))
            {
                this.FillDataByList(dt);
            }
            if (dt.Rows.Count > 0x10000)
            {
                while (dt.Rows.Count >= 0x10000)
                {
                    dt.Rows.RemoveAt(dt.Rows.Count - 1);
                }
            }
            ArrayList alSql = new ArrayList();
            int length = columns.Length;
            foreach (DataRow row in dt.Rows)
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("INSERT INTO {0} ({1}) VALUES (");
                for (int i = 0; i < length; i++)
                {
                    string str = "";
                    string columnName = columns[i];
                    if (this.OnFillDataRowRender != null)
                    {
                        str = this.OnFillDataRowRender(row, row[columnName], columnName, row.Table.Columns[columnName].DataType);
                    }
                    else if ((row[columnName] == null) || (row[columnName] is DBNull))
                    {
                        str = "";
                    }
                    else
                    {
                        str = row[columns[i]].ToString();
                    }
                    builder.Append("'" + str + "',");
                }
                builder.Remove(builder.Length - 1, 1);
                builder.Append(");");
                alSql.Add(builder.ToString());
            }
            this.RunSqlArray(alSql);
        }

        private void RunSqlArray(ArrayList alSql)
        {
            using (OleDbConnection connection = new OleDbConnection(this.ExcelConnString))
            {
                DataTable schemaTable;
                connection.Open();
                string specifyTableName = "";
                if (string.IsNullOrEmpty(this.specifyTableName))
                {
                    specifyTableName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
                }
                else
                {
                    specifyTableName = this.specifyTableName;
                }
                specifyTableName = "[" + specifyTableName + "]";
                string str3 = "";
                OleDbCommand command = null;
                command = new OleDbCommand(string.Format("SELECT TOP 1 * FROM {0}", specifyTableName), connection);
                using (OleDbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
                {
                    schemaTable = reader.GetSchemaTable();
                }
                foreach (DataRow row in schemaTable.Rows)
                {
                    str3 = str3 + "[" + row["ColumnName"].ToString() + "],";
                }
                str3 = str3.TrimEnd(new char[] { ',' });
                foreach (string str4 in alSql)
                {
                    new OleDbCommand(string.Format(str4, specifyTableName, str3), connection).ExecuteNonQuery();
                }
            }
        }

        public string SetSpecifyTableName(string specifyTableName)
        {
            this.specifyTableName = specifyTableName + "$";
            return this.specifyTableName;
        }

        private string ExcelConnString
        {
            get
            {
                return string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source ={0};Extended Properties='Excel 8.0;'", this.ExcelPath);
            }
        }

        public string ExcelPath
        {
            get
            {
                if (!(!string.IsNullOrEmpty(this.excelPath) && File.Exists(this.excelPath)))
                {
                    throw new Exception("Excel为空");
                }
                return this.excelPath;
            }
            set
            {
                this.excelPath = value;
            }
        }
    }
}