Maison >développement back-end >Tutoriel C#.Net >ADO.NET lit le code d'implémentation EXCEL ((c#))
ADO.NET lit le code d'implémentation EXCEL ((c#))
- 高洛峰original
- 2017-01-13 16:57:161470parcourir
// Chaîne de connexion
// 连接字符串
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(); Très simple, non ? ! Tout est comme exploiter une base de données, sauf qu'il faut faire attention à :
1. Le fournisseur de données utilise Jet et vous devez spécifier le mot clé Propriétés étendues pour définir les propriétés spécifiques à Excel. Différentes versions d'Excel correspondent à différentes valeurs de propriété : la version Excel valide pour la valeur Propriétés étendues.
Pour les classeurs Microsoft Excel 8.0 (97), 9.0 (2000) et 10.0 (2002), utilisez Excel 8.0.
Pour les classeurs Microsoft Excel 5.0 et 7.0 (95), utilisez Excel 5.0.
Pour les classeurs Microsoft Excel 4.0, utilisez Excel 4.0.
Pour les classeurs Microsoft Excel 3.0, utilisez Excel 3.0.
réf :
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp
2 . Le chemin de la source de données utilise le chemin physique absolu (identique à Access)
3. Comment référencer le nom de la table ?
Une référence valide à un tableau (ou une plage) dans un classeur Excel.
Pour faire référence à une plage de feuilles de calcul entièrement utilisées, spécifiez le nom de la feuille de calcul suivi d'un signe dollar. Par exemple :
select * from [Sheet1$]
Pour référencer une plage d'adresses spécifique sur une feuille de calcul, spécifiez le nom de la feuille de calcul suivi d'un signe dollar et de la plage. Par exemple :
select * from [Sheet1$A1:B10]
Pour référencer une plage spécifiée, utilisez le nom de la plage. Par exemple :
sélectionnez * dans [MyNamedRange]
ref :
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/ html /vbtskcodeexamplereadingexceldataintodataset.asp
Remarque :
peut référencer trois objets dans le classeur Excel :
• La feuille de calcul entière : [Sheet1$], Sheet1 est le nom de la feuille de calcul
• Sur la feuille de calcul nommée plage de cellules : [MyNamedRange] (Pas besoin de spécifier la feuille de calcul, car la plage nommée ne peut être unique que dans l'ensemble du xls)
Méthode de dénomination XLS : Sélectionnez la plage de cellules》Insérer》Nom》Définition
• Sur le worksheet La plage de cellules sans nom : [Sheet1$A1:B10]
(Parmi les différents objets fournis par les bases de données relationnelles (tables, vues, procédures stockées, etc.), la source de données Excel ne fournit que des objets équivalents aux tableaux, qui sont Composés de feuilles de calcul et de plages nommées définies dans le classeur spécifié, les plages nommées sont considérées comme des « tables » et les feuilles de calcul sont considérées comme des « tables système »)
Remarque :
•Doit utiliser [] (crochets), sinon il signalera :
Erreur de syntaxe de la clause FROM
• Doit être suivi de $ (signe dollar), sinon il signalera :
Le moteur de base de données Microsoft Jet ne trouve pas l'objet 'Sheet2'. Veuillez vous assurer que l'objet existe et écrivez correctement son nom et son chemin.
•Si le nom de la feuille de calcul est incorrect ou n'existe pas, il signalera :
« Sheet2$ » n'est pas un nom valide. Assurez-vous qu'il ne contient pas de caractères ou de signes de ponctuation non valides et que le nom n'est pas trop long.
•Dans Comment utiliser ADO pour traiter des données Excel dans Visual Basic ou VBA, il est mentionné que vous pouvez utiliser
~ et '(tilde et guillemet simple) au lieu de [] pour utiliser ADO. NET n'a pas réussi, signalant :
Erreur de syntaxe de la clause FROM
•Lors de la référence au nom de la feuille de calcul ([Sheet1$]), le fournisseur de données pense que la table de données commence à partir de la cellule non vide en haut à gauche sur la grille de feuille de calcul spécifiée démarre. Par exemple, si la feuille de calcul commence à partir de la ligne 3, colonne C, avant la ligne 3, colonne C et que les lignes 1 et 2 sont toutes vides, seules les données à partir de la ligne 3, colonne C seront affichées, la plage maximale de la finale ; le tableau sera utilisé. Se termine par une unité non vide à l'intérieur ;
• Par conséquent, si vous avez besoin de lire la plage avec précision, vous devez utiliser la plage nommée [NamedRange], ou spécifier l'adresse : [Sheet1$A1:C10". ]
4. Comment citer les noms de colonnes ?
•Selon la chaîne de connexion par défaut, le fournisseur de données utilisera la première ligne de la zone valide comme nom de colonne. Si une cellule de cette ligne est vide, elle sera représentée par F1 et F2, où le nombre ordinal. est suivi du nom de la cellule. Les positions sont cohérentes, à partir de 1 ;
• Si vous souhaitez que la première ligne soit affichée sous forme de données au lieu de noms de colonnes, vous pouvez spécifier : HDR=NO dans la propriété Propriétés étendues du fichier. connection string.
La valeur par défaut est : HDR=NO.Le format est le suivant ：
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" Excel 8.0;HDR=NO";"
"data source=" xlsPath;
Remarque : Excel 8.0;HDR=NO nécessite l'utilisation de guillemets doubles (la barre oblique inverse ici est l'échappement en C#)
ms-help://MS.VSCC.v80 /MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm
dans la section "Connexion à Excel" (Remarque : dans mon propre MSDN, l'exemple utilisant deux guillemets doubles est erroné et le test échoue. Le texte original dit :
)
La raison possible de cette situation est que dans la connexion par défaut, le fournisseur de données déduit le type de données des cellules suivantes en fonction de la cellule précédente.
Vous pouvez demander au pilote de toujours lire les colonnes de données « mélangées » sous forme de texte en spécifiant IMEX=1
réf : identique à 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中文网！