ADO.NET は EXCEL 実装コード ((c#)) を読み取ります
- 高洛峰オリジナル
- 2017-01-13 16:57:161443ブラウズ
// 接続文字列
// 连接字符串
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 を使用するため、Excel 固有のプロパティを設定するには、拡張プロパティ キーワードを指定する必要があります。Excel のさまざまなバージョンは、拡張プロパティ値の有効な 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 を使用してください。
参照:
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/vbtskcodeexamplereadingexceldataintodataset.asp
説明:
Excel ワークブックでは 3 種類のオブジェクトを参照できます:
• ワークシート全体: [Sheet1$]、Sheet1 はワークシートの名前です
• ワークシート上の名前付きセル範囲: [MyNamedRange] (ワークシート。名前付き範囲はさまざまなオブジェクト (テーブル、ビュー、ストアド プロシージャなど) 全体でのみ一意であるため、Excel データ ソースは、指定されたワークブック内のワークシートと、指定されたワークシートで構成されるテーブルと同等のもののみを提供します。定義された名前付き範囲。名前付き範囲は「テーブル」とみなされ、ワークシートは「システム テーブル」とみなされます)
注:
•[] (角括弧) を使用する必要があります。そうでない場合は、次のように報告されます。句の構文エラー
Microsoft Jet データベース エンジンはオブジェクト 'Sheet2' を見つけることができません。オブジェクトが存在することを確認し、その名前とパスを正しく記述してください。
•ワークシート名が間違っているか、存在しない場合は、次のようにレポートされます:
「Sheet2$」は有効な名前ではありません。無効な文字や句読点が含まれていないこと、および名前が長すぎないことを確認してください。
•ADO を使用して Visual Basic または VBA で Excel データを処理する方法では、ADO を使用するために [] の代わりに
~ と ' (チルダと一重引用符) を使用できることが記載されています。 NET テストは失敗し、次のメッセージが表示されました:
FROM 句構文エラー
•ワークシート名 ([Sheet1$]) を参照する場合、データ プロバイダーは、データ テーブルが指定されたワークシートの左上の空でないセルから始まると認識します。たとえば、ワークシートが行 3、列 C から開始され、行 3、列 C がすべて空の場合、行 3、列 C から始まるデータのみが表示されます。 table は空ではない単位で終了します
• したがって、範囲を正確に読み取る必要がある場合は、名前付き範囲 [NamedRange] を使用するか、アドレスを指定する必要があります: [Sheet1$A1:C10]
4.列名を引用するにはどうすればよいですか?
•デフォルトの接続文字列に従って、データプロバイダーは有効な領域の最初の行を列名として使用します。この行のセルが空の場合、順序番号が一貫している F1 または F2 で表されます。 1 から始まるセルの位置;
•最初の行を列名ではなくデータとして表示する場合は、接続文字列の拡張プロパティ プロパティで HDR=NO を指定できます。形式は次のとおりです:
"Extended Properties="Excel 8.0;HDR=NO";" =" + xlsPath;
注: Excel 8.0;HDR=NO では二重引用符を使用する必要があります (ここでのバックスラッシュは C# のエスケープです)。
参照:
(注: 私自身の MSDN では、例では 2 つの二重引用符が使用されています。元のテキストは次のようになります:
拡張プロパティに必要な二重引用符も二重引用符で囲む必要があることに注意してください
)
この場合、すべての列名は F で始まり、その後にインデックスが続きます。 F1、F2、F3 から始まります。 。 。 。 。 。 。
5.有効なセルデータが表示されないのはなぜですか?
拡張プロパティで IMEX=1 を指定できます
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中文网！