ExcelConvertToDataTable

#region 将指定Excel文件中的数据转换成DataTable对象
        /// <summary>
        /// 将指定Excel文件中的数据转换成DataTable对象，供应用程序进一步处理
        /// Excel文件必须是标准的.xsl文件（由记事本打开且为乱码.xsl就是标准的）
        /// </summary>
        /// <param name="filePath">导入的文件</param>
        public DataTable ExcelToDataTable(string filePath)
        {
            System.Data.DataTable rs = new System.Data.DataTable();
            bool canOpen = false;

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + filePath + ";" +
            "Mode=Share Deny Read|Share Deny Write;Extended Properties=\"Excel 8.0;\"");

            try//尝试数据连接是否可用
            {
                conn.Open();
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
                canOpen = true;
            }
            if (canOpen)
            {
                try//如果数据连接可以打开则尝试读入数据
                {
                    OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
                    OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
                    myData.Fill(rs);
                    conn.Close();
                }
                catch//如果数据连接可以打开但是读入数据失败，则从文件中提取出工作表的名称，再读入数据
                {
                    string sheetName = GetSheetName(filePath);
                    if (sheetName.Length > 0)
                    {
                        try
                        {
                            OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", conn);
                            OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
                            myData.Fill(rs);
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                }
            }
            else
            {
                rs = null;
            }
            return rs;
        }

        /// <summary>
        /// 将指定Excel文件中读取第一张工作表的名称
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private string GetSheetName(string filePath)
        {
            string sheetName = "";

            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 = Encoding.Default.GetString(sheetNameByte);
            }
            return sheetName;
        }

        /// <summary>
        /// 只供方法GetSheetName()使用
        /// </summary>
        /// <returns></returns>
        private 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;
        }
        #endregion