C# OleDbConnection读取加装Excel2003(.xls),Excel2007(.xlsx)文件
作者:C/S框架网|www.cscode.ne  发布日期:2020-03-08 16:47:22
  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



上一篇 下一篇