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

namespace JuMi.BLL.Business
{
    public class ExcelDemo
    {
        /// <summary>
        /// Excel数据导入Datable
        /// </summary>
        /// <param name="fileUrl">文件路径</param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static DataTable GetExcelToDatatable(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();
                }
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取excel的第一个sheet名称
                string sheetName1 = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
                //查询sheet数据
                string strSql1 = "select * from [" + sheetName1 + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql1, conn);
                DataSet ds = new DataSet(); da.Fill(ds, table);
                dt = ds.Tables["" + table + ""];
                return dt;

            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }




    }
}