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

/// <summary>
///Excel读取器
///create by ye_rong
/// </summary>
public class ExcelReader
{
    public OleDbConnection xls_conn;
    public OleDbConnection GetExcelConnection(string file_name)
    {
        xls_conn = new OleDbConnection();
        //"Driver={Microsoft Excel Driver (*.xls)};"
        string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='EXCEL 8.0;HDR=YES;IMEX=1';Data Source=" + file_name;
        try
        {
            xls_conn.ConnectionString = conn_str;
            xls_conn.Open();
        }
        catch
        {
            conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file_name + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
            xls_conn.ConnectionString = conn_str;
            xls_conn.Open();
        }
        return xls_conn;
    }
    /// <summary>
    /// 得到Excel Sheet
    /// </summary>
    /// <param name="file_name">文件名</param>
    /// <param name="sheet">Excel的Sheet，如：Sheet1$</param>
    /// <returns>DataTable</returns>
    public DataTable GetExcelTable(string file_name, string sheet)
    {
        OleDbConnection xls_conn = GetExcelConnection(file_name);
        string sql_xls = "select * from [" + sheet + "]";//Sheet1$
        OleDbCommand cmd = new OleDbCommand(sql_xls, xls_conn);
        OleDbDataAdapter adpt = new OleDbDataAdapter(sql_xls, xls_conn);
        DataSet ds = new DataSet();
        adpt.Fill(ds);
        DataTable dt = ds.Tables[0];
        return dt;
    }
}