读取excel

突然上头要把现有的excel数据表中数据经过逻辑计算导入到数据库中去，急急忙忙写了如下代码，感觉还行，可能有用，所以提供这里给大家参考，没怎么详细设计，如有问题自行解决了；

连接字符串  connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\";data source="+path;

注：IMEX=1; 通知驱动程序始终将“互混”数据列作为文本读取

Extended Properties 属性指定：HDR=NO 读取数据首行是以列名显示还是直接以数据显示

 

 

 

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

/// <summary>
/// ExcelReader 的摘要说明
/// </summary>
public class ExcelReader
{
    private string connectionstr = "";
    private string comtxt = string.Empty;
    private IDbConnection con = null;
    private IDbCommand com = null;
    private IDataReader dr = null;
    private OleDbDataAdapter da = null;
    private DataTable dt = null;
    private DataSet ds = null;

    public string ConnectionStr
    {
        get
        {
            return connectionstr;
        }
        set
        {
            connectionstr = value;
        }
    }

 public ExcelReader(string constr)
 {
        this.connectionstr = constr;

 }

    #region 读取整张表
    /// <summary>
    /// 读取整张表
    /// </summary>
    /// <param name="sheetname">excel表的名称，即Sheet</param>
    /// <returns>OleDbDataReader</returns>
    public OleDbDataReader GetWholeSheet(string sheetname)
    {
        comtxt = "select * from [" + sheetname + "$]";
        con = new OleDbConnection(connectionstr);
        con.Open();
        com = new OleDbCommand();
        com.Connection = con;
        com.CommandText = comtxt;
        dr = com.ExecuteReader(CommandBehavior.CloseConnection);
        return (OleDbDataReader)dr;
    }
    #endregion

    #region 读取指定范围数据
    /// <summary>
    /// 读取指定范围数据
    /// </summary>
    /// <param name="tablename">指定读取表名</param>
    /// <param name="range">指定读取区域，如a10:f11</param>
    /// <returns>OleDbDataReader</returns>
    private OleDbDataReader GetRangeSheet(string tablename, string range)
    {
        comtxt = "select * from ["+tablename+"$"+range+"]";
        con = new OleDbConnection(connectionstr);
        con.Open();
        com = new OleDbCommand();
        com.Connection = con;
        com.CommandText = comtxt;
        dr = com.ExecuteReader(CommandBehavior.CloseConnection);
        return (OleDbDataReader)dr;
    }
    #endregion

    #region 读取多张sheet表
    /// <summary>
    /// 读取多张sheet表
    /// </summary>
    /// <returns>DataSet</returns>
    private DataSet GetMultiSheets()
    {
        comtxt = "select * from [{0}]";
        OleDbConnection conn = new OleDbConnection(connectionstr);
        conn.Open();
        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        System.Collections.Generic.List<string> li = new System.Collections.Generic.List<string>();
        foreach (DataRow dr in dt.Rows)
        {
            li.Add((string)dr["TABLE_NAME"]);
        }
        da = new OleDbDataAdapter();
        // 准备数据，导入DataSet
        ds = new DataSet();
        foreach (string tablename in li)
        {
            da.SelectCommand = new OleDbCommand(String.Format(comtxt, tablename), conn);
            try
            {
                da.Fill(ds, tablename);
            }
            catch
            {
                // 关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw;
            }
        }
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
        return ds;
    }
    #endregion

    #region 返回指定单元数据
    /// <summary>
    /// 返回指定的单元格的数据，此方法和GetRangeSheet方法原理相同，即range起始相同
    /// </summary>
    /// <param name="tablename"></param>
    /// <param name="range"></param>
    /// <returns></returns>
    public string GetSpecialDate(string tablename, string range)
    {
        comtxt = "select * from [" + tablename + "$" + range + "]";
        con = new OleDbConnection(connectionstr);
        con.Open();
        com = new OleDbCommand();
        com.Connection = con;
        com.CommandText = comtxt;
        dr = com.ExecuteReader(CommandBehavior.SingleRow);
        if (dr.Read())
            return dr.GetString(0);//
        else
            return string.Empty;
    }
    #endregion
}