ADO.NET 讀取EXCEL的實作代碼((c#))
- 高洛峰原創
- 2017-01-13 16:57:161475瀏覽
// 連接字串
// 连接字符串
string xlsPath = Server.MapPath("~/app_data/somefile.xls"); // 绝对物理路径
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=Excel 8.0;" +
"data source=" + xlsPath;
// 查询语句
string sql = "SELECT * FROM [Sheet1$]";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds); // 填充DataSet
// 在这里对DataSet中的数据进行操作
// 输出，绑定数据
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();很簡單吧？ ！一切就像操作資料庫一樣，只是要注意的是：
1。資料提供者使用Jet，同時需要指定Extended Properties 關鍵字設定 Excel 特定的屬性，不同版本的Excel對應不同的屬性值：用於 Extended Properties 值的有效 Excel 版本。
對於 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿，請使用 Excel 8.0。
對於 Microsoft Excel 5.0 和 7.0 (95) 工作簿，請使用 Excel 5.0。
對於 Microsoft Excel 4.0 工作簿，請使用 Excel 4.0。
對於 Microsoft Excel 3.0 工作簿，請使用 Excel 3.0。
ref：
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp
2。資料來源路徑使用實體絕對路徑（同Access）
3。如何引用表名？
對 Excel 工作簿中表格（或範圍）的有效引用。
若要引用完全使用的工作表的範圍，請指定後面跟著有美元符號的工作表名稱。例如：
select * from [Sheet1$]
若要引用工作表上的特定位址範圍，請指定後面跟有美元符號和該範圍的工作表名稱。例如：
select * from [Sheet1$A1:B10]
若要引用指定的範圍，請使用該範圍的名稱。例如：
select * from [MyNamedRange]
ref：
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereading/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingaspdataintodataset.html/vbts5可以引用Excel 工作簿中的三種物件：
• 整個工作表：[Sheet1$] ，Sheet1 就是工作表的名稱
• 工作表上的命名儲存格區域：[MyNamedRange] （不需要指定工作表，因為整個xls中命名區域只能唯一）
XLS命名方法：選取儲存格範圍》插入》名稱》定義
• 工作表上的未命名儲存格區域：[Sheet1$A1:B10]
（在關聯式資料庫提供的各種對像中（表、視圖、預存程序等），Excel 資料來源僅提供相當於表的對象，它由指定工作簿中的工作表和定義的命名區域組成。 ，而工作表被視為「系統表」）
注意：
FROM 子句語法錯誤
•必須跟$（美元符號），否則報：
Microsoft Jet 資料庫引擎找不到物件'Sheet2′。請確定物件是否存在，並正確地寫出它的名稱和路徑。
•如果工作表名稱不對，或不存在，將報：
‘Sheet2$' 不是一個有效名稱。請確認它不包含無效的字元或標點，且名稱不太長。
•在 如何在 Visual Basic 或 VBA 中使用 ADO 來處理 Excel 資料 中提到可以使用
~ 和 ‘(波浪線和單引號）代替[]，使用ADO。 NET測試沒有成功，封包：
FROM 子句語法錯誤
•當引用工作顯示名稱（[Sheet1$]）時，資料提供者認為資料表從指定工作表上最左上方的非空白儲存格開始。例如，工作表從第3 行，C 列開始，第3行，C列之前以及第1、2行全為空，則只會顯示從第3行，C列開始的資料；以最後表最大範圍內的非空單元結束；
•因此，如需要精確讀取範圍，應使用命名區域[NamedRange]，或指定位址：[Sheet1$A1:C10]
4。如何引用列名？
•如果希望第一行作為資料顯示，而非列名，可以在連接字串的Extended Properties 屬性指定：HDR=NO
預設值為：HDR=NO 格式如下：
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"data source=" + xlsPath;
注意： Excel 8.0;HDR=NO 需要使用雙數引號（這裡的反斜扛，是C#中的轉義）
ref：
中《連接Excel》節（說明：在我自己的MSDN中，它的例子使用了兩個雙引號是錯的，測試沒有通過，原文這樣說的：
注意，Extended Properties 所需的雙引號必須還要加雙引號。 。 。 。 。 。 。
5。為什麼有效單元格資料不顯示出來？
出現這種情況的可能原因是，預設連接中，資料提供者根據前面單元格推斷後續單元個的資料類型。
ref：同4
PS：在baidu这个问题的时候，有网友说，将每个单元都加上引号，这固然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到”治本药方“
more ref：
如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599
应
用程序经常需要与Excel进行数据交互，以上阐述了基于ADO.NET
读取Excel的基本方法与技巧。现在要介绍是如何动态的读取Excel数据，这里的动态指的是事先不知道Excel文件的是什么样的结构，或者无法预
测，比如一张.xls文件有多少张sheet，而且每张sheet的结构可能都不一样等等。
其实我们可以通过获取Excel的“架构信息”来动态
的构造查询语句。这里的“架构信息”与数据库领域的“数据库架构信息”意义相同（也称“元数据”），对于整个数据库，这些“元数据”通常包括数据库或可通
过数据库中的数据源、表和视图得到的目录以及所存在的约束等；而对于数据库中的表，架构信息包括主键、列和自动编号字段等。
在上文中提到
在关系数据库提供的各种对象中（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”）
这里我们将Excel也当作一个“数据库”来对待，然后利用OleDbConnection.GetOleDbSchemaTable 方法
要获取所需的架构信息，该方法获取的架构信息与ANSI SQl-92是兼容的：
注
意：对于那些不熟悉 OLE DB 架构行集的人而言，它们基本上是由 ANSI SQL-92
定义的数据库构造的标准化架构。每个架构行集具有为指定构造提供定义元数据的一组列（称作 .NET
文档中的“限制列”）。这样，如果请求架构信息（例如，列的架构信息或排序规则的架构信息），则您会明确知道可以得到哪种类型的数据。如果希望了解更多信
息，请访问 Appendix B:Schema Rowsets。
ref：http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true
以下是读取Excel文件内“表”定义元数据，并显示出来的的程序片断：
// 读取Excel数据，填充DataSet
// 连接字符串
string xlsPath = Server.MapPath("~/app_data/somefile.xls");
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";" + // 指定扩展属性为
Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
"data source=" + xlsPath;
string sql_F = "SELECT * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable tblSchema = null;
IList<string> tblNames = null;
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
//tblSchema = conn.GetSchema("Tables");
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
GridView1.DataSource = tblSchema;
GridView1.DataBind();
// 关闭连接
conn.Close();
GetOleDbSchemaTable 方法的详细说明可以参考：
http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx
接着是一段利用“架构信息”动态读取Excel内部定义的表单或者命名区域的程序片断：
// 读取Excel数据，填充DataSet
// 连接字符串
string xlsPath = Server.MapPath("~/app_data/somefile.xls");
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";" + // 指定扩展属性为
Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
"data source=" + xlsPath;
string sql_F = "SELECT * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable tblSchema = null;
IList<string> tblNames = null;
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
//tblSchema = conn.GetSchema("Tables");
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//GridView1.DataSource = tblSchema;
//GridView1.DataBind();
// 关闭连接
//conn.Close();
tblNames = new List<string>();
foreach (DataRow row in tblSchema.Rows) {
tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
}
// 初始化适配器
da = new OleDbDataAdapter();
// 准备数据，导入DataSet
DataSet ds = new DataSet();
foreach (string tblName in tblNames) {
da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
try {
da.Fill(ds, tblName);
}
catch {
// 关闭连接
if (conn.State == ConnectionState.Open) {
conn.Close();
}
throw;
}
}
// 关闭连接
if (conn.State == ConnectionState.Open) {
conn.Close();
}
// 对导入DataSet的每张sheet进行处理
// 这里仅做显示
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
GridView2.DataSource = ds.Tables[1];
GridView2.DataBind();这里我们就不需要对SELEC 语句进行“硬编码”，可以根据需要动态的构造FROM 字句的“表名”。
不仅可以，获取表明，还可以获取每张表内的字段名、字段类型等信息：
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, null });
在ADO.nET
1.x 时候只有OleDb提供了GetOleDbSchemaTable
方法，而SqlClient或者OrcaleClient没有对应的方法，因为对应数据库已经提供了类似功能的存储过程或者系统表供应用程序访问，比如对
于Sql Server：
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
而在ADO.NET 2.0中每个xxxConnenction都实现了基类System.Data.Common.DbConnection的 GetSchemal 方法
来获取数据源的架构信息。
http://msdn2.microsoft.com/zh-cn/library/system.data.common.dbconnection.getschema.aspx
//---------------------------------------------------------------------------
//IMEX 正確寫法
string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +";Extended Properties='Excel 8.0;IMEX=1;'"; //HDR=NO;IMEX=1
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string strconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"D:/last.xls" + ";Extended Properties=Excel 8.0;"; //HDR=no";//IMEX=1
OleDbConnection conn = new OleDbConnection(strconn);
DataSet myset = new DataSet();
try
{
conn.Open();
string mysql = "select * from [Sheet1$] ";//where chs <> ''
OleDbDataAdapter aper = new OleDbDataAdapter(mysql, conn);
myset.Tables.Clear();
aper.Fill(myset, "book");
conn.Close();
GridView1.DataSource = myset.Tables["book"];
GridView1.DataBind();
}
catch (Exception ex)
{
conn.Close();
this.lb_msg.Text = ex.Message;
return;
// return ex.Message;
}
}
}更多ADO.NET 读取EXCEL的实现代码((c#))相关文章请关注PHP中文网！