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

namespace Com.DataCool.AppCommon
{
    /// <summary>
    /// OLE连接Excel数据文件Helper
    /// </summary>
    public  class ExcelDBHelper
    {
        /// <summary>
        /// 取得Excel文本中的记录，并作成DataTable
        /// </summary>
        /// <param name="strExcelFileName">Excel文件路径</param>
        /// <param name="strSheetName">指定Excel中的那个Sheet</param>
        /// <returns>DataTable</returns>
        public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            string strSheet = GetSheetName(strExcelFileName);
            string strExcel = "select * from [" + strSheet + "$]";
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, strSheetName);
                }
                catch (Exception ex)
                {
                    LogHelper.Error(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return ds.Tables[strSheetName];
        }

        /// <summary>
        /// 取得指定Excel的sheet名
        /// </summary>
        /// <param name="filePath">指定的Excel路径</param>
        /// <returns>string返回Sheet名</returns>
        public static string GetSheetName(string filePath)
        {
            string sheetName = "";
            System.IO.FileStream tmpStream = File.OpenRead(filePath);
            byte[] fileByte = new byte[tmpStream.Length];
            tmpStream.Read(fileByte, 0, fileByte.Length);
            tmpStream.Close();
            byte[] tmpByte = new byte[]
            {
                Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
                Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
                Convert.ToByte(30),Convert.ToByte(16),Convert.ToByte(0),Convert.ToByte(0)
            };
            int index = GetSheetIndex(fileByte, tmpByte);
            if (index > -1)
            {
                index += 16 + 12;
                ArrayList sheetNameList = new ArrayList();
                for (int i = index; i < fileByte.Length - 1; i++)
                {
                    byte temp = fileByte[i];
                    if (temp != Convert.ToByte(0))
                        sheetNameList.Add(temp);
                    else
                        break;
                }
                byte[] sheetNameByte = new byte[sheetNameList.Count];
                for (int i = 0; i < sheetNameList.Count; i++)
                    sheetNameByte[i] = Convert.ToByte(sheetNameList[i]);
                sheetName = System.Text.Encoding.Default.GetString(sheetNameByte);
            }
            return sheetName;
        }

        /// <summary>
        /// 只供方法GetSheetName()使用
        /// </summary>
        /// <returns></returns>
        private static int GetSheetIndex(byte[] FindTarget, byte[] FindItem)
        {
            int index = -1;
            int FindItemLength = FindItem.Length;
            if (FindItemLength < 1) return -1;
            int FindTargetLength = FindTarget.Length;
            if ((FindTargetLength - 1) < FindItemLength) return -1;
            for (int i = FindTargetLength - FindItemLength - 1; i > -1; i--)
            {
                ArrayList tmpList = new ArrayList();
                int find = 0;
                for (int j = 0; j < FindItemLength; j++)
                {
                    if (FindTarget[i + j] == FindItem[j]) find += 1;
                }
                if (find == FindItemLength)
                {
                    index = i;
                    break;
                }
            }
            return index;
        }
    }
}