C# OleDbConnection读取加装Excel2003(.xls),Excel2007(.xlsx)文件

  C# OleDbConnection读取加装Excel2003(.xls),Excel2007(.xlsx)文件

OleDbConnection连接参数：

Extended Properties：Excel 2007或以上版本=Excel 12.0

Extended Properties：Excel 2007或以上版本=Excel 8.0

HDR：Excel文件第一条记录作为列头，对应DataTable的字段名称。

C#读取加装Excel 2007(.xlsx)文件：

C# Code:

/// <summary>
/// 根据excle的路径把第一个sheel中的内容放入datatable
/// </summary>
/// <param name="execlFile"></param>
/// <returns></returns>
public DataTable ImportExcelXlsx(string execlFile)
{
  //
  //连接字符串
  //
  //Office 07及以上版本,读取XLSX文件
   string connstring = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + execlFile + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
  
  //Office 07以下版本,读取xls文件
  //string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + execlFile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
  
  using (OleDbConnection conn = new OleDbConnection(connstring))
  {
    conn.Open();
    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
     string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
     string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
    //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串
     OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
    DataSet set = new DataSet();
    ada.Fill(set);
    return set.Tables[0];
  }
}

//来源:C/S框架网(www.csframework.com) QQ:23404761

C#读取加装Excel 2003或以下版本(.xls)文件：

C# Code:

/// <summary>
/// 根据excle的路径把第一个sheel中的内容放入datatable
/// </summary>
/// <param name="execlFile"></param>
/// <returns></returns>
public DataTable ImportExcelXls(string execlFile)
{
  //
  //连接字符串
  //
  //Office 07及以上版本,读取XLSX文件
  //string connstring = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + execlFile + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
  
  //Office 07以下版本,读取xls文件
   string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + execlFile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
  
  using (OleDbConnection conn = new OleDbConnection(connstring))
  {
    conn.Open();
    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
     string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
     string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
    //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串
     OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
    DataSet set = new DataSet();
    ada.Fill(set);
    return set.Tables[0];
  }
}

//来源:C/S框架网(www.csframework.com) QQ:23404761

测试：

C# Code:

//测试xls
string file = @"C:\Users\Administrator\Downloads\keywordlist_20200308.xls";
DataTable dt = new KeywordBaiduUpload().ImportExcelXls(file);
Console.WriteLine("记录数：" + dt.Rows.Count);

//测试xlsx
string file1 = @"C:\Users\Administrator\Downloads\keywordlist_20200308.xlsx";
DataTable dt1 = new KeywordBaiduUpload().ImportExcelXlsx(file1);
Console.WriteLine("记录数：" + dt.Rows.Count);
Console.ReadLine();

//来源:C/S框架网(www.csframework.com) QQ:23404761