using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Sale_Comp_KR_Report_Mapping
{
    class ExcelBase
    {
        /// <summary>
        /// 连接Excel数据库
        /// </summary>
        /// <param name="Path">文件路径</param>
        /// <returns>返回连接对象</returns>
        public static OleDbConnection ConnExcel(string Path)
        {
            try
            {
                //HDR=Yes，这代表第一行是标题，不做为数据使用
				//HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES。
				
				//当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
				//当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
				//当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”
            
                //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;\""; //IMEX=1;   
                // string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;HDR=YES;IMEX=2;\""; //IMEX=1;                
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;\""; //IMEX=1;  
                OleDbConnection conn = new OleDbConnection(strConn);                
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 在Excel中查找数据
        /// </summary>
        /// <param name="strConn">连接字符串</param>
        /// <param name="Sql">查询语句</param>
        /// <returns>返回查询到的数据集</returns>
        public static DataSet SelectExcel(OleDbConnection conn, string Sql)
        {
            try
            {
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = Sql;
                myCommand = new OleDbDataAdapter(strExcel, conn);
                ds = new DataSet();
                myCommand.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        /// <summary> 
        /// 更新Excel文档 
        /// </summary> 
        /// <param name="Path">文件名称</param> 
        /// <returns>
        /// -1:发生错误
        /// other：更新的条数
        /// </returns> 
        public static int UpdateExcel(OleDbConnection conn, string Sql)
        {
            try
            {
                string strExcel = "";
                OleDbCommand myCommand = null;
                strExcel = Sql;
                myCommand = new OleDbCommand(strExcel, conn);


                int resultnum = myCommand.ExecuteNonQuery();
                return resultnum;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        /// <summary>
        /// 关闭Excel数据库
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        public static void CloseExcel(OleDbConnection conn)
        {
            conn.Close();
        }

        /// <summary> 
        /// 读取Excel文档 
        /// </summary> 
        /// <param name="Path">文件名称</param> 
        /// <returns>返回一个数据集</returns> 
        public static DataSet ExcelToDS(string Path, string Sql)
        {
            try
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;HDR=YES;IMEX=1;\""; //IMEX=1;                
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = Sql;
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds);
                conn.Close();
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary> 
        /// 更新Excel文档 
        /// </summary> 
        /// <param name="Path">文件名称</param> 
        /// <returns>
        /// -1:发生错误
        /// other：更新的条数
        /// </returns> 
        public static int UpdateToExcel(string Path, string Sql)
        {
            try
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;HDR=FALSE;IMEX=1;\""; //IMEX=1;
                int resultnum = 0;
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbCommand myCommand = null;
                strExcel = Sql;
                myCommand = new OleDbCommand(strExcel, conn);
                resultnum = myCommand.ExecuteNonQuery();
                conn.Close();
                return resultnum;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }
    }
}