1.概要
无非就是把excel的表格当作数据的表格,
1.1.sheet页面等于表名,例: [heet1$] == 表名
1.2.字段等于F*,例:dataRow["F3"]
2.代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ExcelOledb测试
{
class Program
{
/// <summary>
/// Microsoft Jet 提供程序用于连接到 Excel 工作簿。在以下连接字符串中,Extended Properties 关键字设置 Excel 特定的属性。
/// “HDR=Yes;”指示第一行中包含列名,而不是数据,“IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取。
/// Excel 8.0 针对Excel2000及以上版本,Excel5.0 针对Excel97。
/// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""
/// String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=c:/test.xls;" +"Extended Properties=Excel 8.0;";
/// string ConnnectionString = "Provider='Microsoft.Ace.OLEDB.12.0';Data Source=c:/test.xls;" + "Extended Properties=Excel 8.0;";
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
Console.WriteLine("ExcelOledb测试");
//test1();
test2();
Console.ReadKey();
}
static void test1() {
string ExcelPath = "灌装机数据通讯表.xlsx";
string ConnnectionString = string.Format(@"Provider='Microsoft.Ace.OLEDB.12.0';Data Source={0};" + "Extended Properties=Excel 8.0;", ExcelPath);
OleDbConnection Excelconn = new OleDbConnection(ConnnectionString);
try
{
Excelconn.Open();
OleDbCommand Excelcomm = new OleDbCommand();
//这段代码甚至可以让Connection重定向,根据实际需要选择该写,如果不需要重定向,From TableName就好了
string ExcelCommText = string.Format("select * FROM [Excel 8.0;HDR=yes;DATABASE={0}].[{1}$]", ExcelPath, "MES-PLC_S");
OleDbDataAdapter sda = new OleDbDataAdapter(ExcelCommText, Excelconn);
DataSet ds = new DataSet();
sda.Fill(ds);
DataTable dataTable = ds.Tables[0];
foreach (DataRow dataRow in dataTable.Rows)
{
Console.WriteLine(dataRow[2]);
Console.WriteLine(dataRow["F3"]);
}
}
catch (Exception ex)
{
}
}
static void test2() {
string ExcelPath = "灌装机数据通讯表.xlsx";
string ConnnectionString = string.Format(@"Provider='Microsoft.Ace.OLEDB.12.0';Data Source={0};" + "Extended Properties=Excel 8.0;", ExcelPath);
OleDbConnection Excelconn = new OleDbConnection(ConnnectionString);
Excelconn.Open();
DataTable tb = Excelconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow row in tb.Rows)
{
//遍历弹出各Sheet的名称
Console.WriteLine(row["TABLE_NAME"]);
}
}
}
}
3.运行结果
略
4.参考连接