using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace JY.Infrastructure.Common
{
    public  class ExcelToSQL
    {
        //DBUnti _db = new DBUnti();


        public bool ExcelToSql(ref string strErr)
        {
            try
            {
                OpenFileDialog fd = new OpenFileDialog();
                fd.Filter = "导入SQL数据库|*.xlsx;*.xls";//打开文件对话框筛选器
                if (fd.ShowDialog() == DialogResult.OK)
                {
                   bool b= TransferData(fd.FileName, "tb_hxconfigbase", ref strErr); //数据库表中名称
                    if (b)
                    {
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                strErr = ex.Message;
            }
            return false;
        }


        /// <summary>
        /// Excel导入到Mysql
        /// </summary>
        /// <param name="strErr"></param>
        /// <returns></returns>
        public bool ExcelToStandardSQL(ref string strErr)
        {
            try
            {
                strErr = "";
                OpenFileDialog fd = new OpenFileDialog();
                fd.Filter = "导入SQL数据库|*.xlsx;*.xls";//打开文件对话框筛选器
                if (fd.ShowDialog() == DialogResult.OK)
                {
                    DataTable dt = GetExcelDatatable(fd.FileName, "mapTable");
                    bool b = OpDataBase.InsetMySqlData(dt,ref strErr);
                    if (strErr=="")
                    {
                        return true;
                    }
                    //TransferData(fd.FileName, "tb_hxconfigbase", _db.connstr,ref strErr); //数据库表中名称
                }
                strErr = "取消导入";
            }
            catch (Exception ex)
            {
                strErr = ex.Message;
            }
            return false;
        }


        /// <summary>
        /// Excel导入到SQLSERVER
        /// </summary>
        /// <param name="excelFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="strErr"></param>
        /// <returns></returns>
        public bool TransferData(string excelFile, string sheetName,  ref string strErr)
        {
            strErr = "";
            DataSet ds = new DataSet();
            try
            {
                string strConn = "";
                strConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                strConn = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand;
                strExcel = string.Format("select * from [{0}$]", sheetName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, sheetName);
               bool b= OpDataBase.InsetSqlData(ds, sheetName,ref strErr);
                if (strErr=="")
                {
                    return true;
                }
                #region 屏蔽
                ////列出ds内存表内所有数据，通过For循环把ModelType项数据添加到List集合
                //List<string> Mlist = new List<string>();
                //for (int i=0;i<ds.Tables[0].Rows.Count;i++)
                //{
                //    Mlist.Add(ds.Tables[0].Rows[i][0].ToString());
                //}
                //HashSet<string> typeSet = new HashSet<string>(Mlist);//去除List集合重复项
                //foreach(var item in typeSet)
                //{
                //    _sqldb.AddModelType(item, ref strErr);
                //}
                #endregion

                //如果目标表不存在则创建,excel文件的第一行为列标题,从第二行开始全部都是数据记录
            }
            catch (Exception ex)
            {
                strErr = ex.Message;
            }
            return false;
        }

        public DataTable GetExcelDatatable(string fileUrl, string table)
        {
            //office2007之前 仅支持.xls
            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }
}