excel读写技艺-:ADO.NET 怎么着读取 Excel

HDR=NO 表示 Excel
区域不包含标题,也就是说区域的第一行就是数据行,此时使用 F1 表示第一个字段,使用 F2
表示第二个字段,以此类推。

OleDbConnection conn = new
OleDbConnection(“Provider=Microsoft.Jet.OleDb.4.0;Data Source=” +
                        Server.MapPath(“foo.xls”) + “;” +
                        “Extended Properties=’Excel 8.0;HDR=NO;'”);
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.ExecuteNonQuery();

一切就像操作数据库一样,只是需要注意的是:
1。数据提供程序使用Jet,同时需要指定Extended
Properties 关键字设置 Excel
特定的属性,不同版本的Excel对应不同的属性值:用于 Extended Properties
值的有效 Excel 版本。
对于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002)
工作簿,请使用 Excel 8.0。

ylg娱乐官网 1        // 读取Excel数据,填充DataSet
ylg娱乐官网 2        // 连接字符串            
ylg娱乐官网 3        string xlsPath = Server.MapPath(“~/app_data/somefile.xls”);
ylg娱乐官网 4        string connStr = “Provider=Microsoft.Jet.OLEDB.4.0;” +
ylg娱乐官网 5                        “Extended Properties=”Excel 8.0;HDR=No;IMEX=1″;” + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002),并且第一行作为数据返回,且以文本方式读取
ylg娱乐官网 6                        “data source=” + xlsPath;
ylg娱乐官网 7        string sql_F = “SELECT * FROM [{0}]”;
ylg娱乐官网 8
ylg娱乐官网 9        OleDbConnection conn = null;
ylg娱乐官网 10        OleDbDataAdapter da = null;
ylg娱乐官网 11        DataTable tblSchema = null;
ylg娱乐官网 12        IList<string> tblNames = null;
ylg娱乐官网 13
ylg娱乐官网 14        // 初始化连接,并打开
ylg娱乐官网 15        conn = new OleDbConnection(connStr);
ylg娱乐官网 16        conn.Open();
ylg娱乐官网 17
ylg娱乐官网 18        // 获取数据源的表定义元数据                        
ylg娱乐官网 19        //tblSchema = conn.GetSchema(“Tables”);
ylg娱乐官网 20ylg娱乐官网 21        tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] ylg娱乐官网 22{ null, null, null, “TABLE” });
ylg娱乐官网 23
ylg娱乐官网 24        //GridView1.DataSource = tblSchema;
ylg娱乐官网 25        //GridView1.DataBind();
ylg娱乐官网 26
ylg娱乐官网 27        // 关闭连接
ylg娱乐官网 28        //conn.Close();
ylg娱乐官网 29
ylg娱乐官网 30        tblNames = new List<string>();
ylg娱乐官网 31ylg娱乐官网 32        foreach (DataRow row in tblSchema.Rows) ylg娱乐官网 33{
ylg娱乐官网 34            tblNames.Add((string)row[“TABLE_NAME”]); // 读取表名
ylg娱乐官网 35        }
ylg娱乐官网 36
ylg娱乐官网 37        // 初始化适配器
ylg娱乐官网 38        da = new OleDbDataAdapter();
ylg娱乐官网 39        // 准备数据,导入DataSet
ylg娱乐官网 40        DataSet ds = new DataSet();
ylg娱乐官网 41
ylg娱乐官网 42ylg娱乐官网 43        foreach (string tblName in tblNames) ylg娱乐官网 44{
ylg娱乐官网 45            da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
ylg娱乐官网 46ylg娱乐官网 47            try ylg娱乐官网 48{
ylg娱乐官网 49                da.Fill(ds, tblName);
ylg娱乐官网 50            }
ylg娱乐官网 51ylg娱乐官网 52            catch ylg娱乐官网 53{
ylg娱乐官网 54                // 关闭连接
ylg娱乐官网 55ylg娱乐官网 56                if (conn.State == ConnectionState.Open) ylg娱乐官网 57{
ylg娱乐官网 58                    conn.Close();
ylg娱乐官网 59                }
ylg娱乐官网 60                throw;
ylg娱乐官网 61            }
ylg娱乐官网 62        }
ylg娱乐官网 63
ylg娱乐官网 64        // 关闭连接
ylg娱乐官网 65ylg娱乐官网 66        if (conn.State == ConnectionState.Open) ylg娱乐官网 67{
ylg娱乐官网 68ylg娱乐官网,            conn.Close();
ylg娱乐官网 69        }
ylg娱乐官网 70
ylg娱乐官网 71        // 对导入DataSet的每张sheet进行处理        
ylg娱乐官网 72        // 这里仅做显示
ylg娱乐官网 73        GridView1.DataSource = ds.Tables[0];
ylg娱乐官网 74        GridView1.DataBind();
ylg娱乐官网 75
ylg娱乐官网 76        GridView2.DataSource = ds.Tables[1];
ylg娱乐官网 77        GridView2.DataBind();
ylg娱乐官网 78
ylg娱乐官网 79        // more codes
ylg娱乐官网 80        // ylg娱乐官网 81.
ylg娱乐官网 82

在 Extended Properties 中,Excel 8.0
表示为版本号,Microsoft Jet 4.0 OLE DB Provider 支持 Excel
3.0,4.0,5.0,8.0 数据库类型。也就是说它不能连接 Excel 2007 的文件,对于 Excel 2007
文件,可以将其保存为 97-2003 的格式。

  • ASP.NET 非“自动化”处理
    Excel-连接
  • ASP.NET 非“自动化”处理 Excel-连接 Excel
    2007
  • ASP.NET 非“自动化”处理 Excel-操作
  • ASP.NET 非“自动化”处理
    Excel-数据类型

注意:
在网上有很多同志说是加上HDR或IMEX这两个属性值后会报“找不到可安装的ISAM
”的错误。这时解决方法很简单:
就是把连接串写成如下形式:

ref:

Provider=Microsoft.Jet.OleDb.4.0;Data Source=Excel文件位置;Extended
Properties=’Excel 8.0;HDR=NO;’

cmd.Dispose();
conn.Close();
conn.Dispose();

“IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取
ref:同4

这里我们将Excel也当作一个“数据库”来对待,然后利用OleDbConnection.GetOleDbSchemaTable
方法
要获取所需的架构信息,该方法获取的架构信息与ANSI SQl-92是兼容的:


但如果我们把第五行的单元格边框设置一下,或者把第四行写点数据(写了再删除也可以),此时就相当于执行了数据填充,第四行就可以被更新了。

4。如何引用列名? •根据默认连接字符串中,数据提供程序会将有效区域内的第一行作为列名,如果此行某单元格为空则用F1、F2表示,其中序数,跟单元格的位置一致,从1开始;
•如果希望第一行作为数据显示,而非列名,可以在连接串的 Extended
Properties 属性指定:HDR=NO
默认值为:HDR=NO 格式如下:

注意:
•必须使用[](方括号),否将报:
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]

  • ASP.NET 非“自动化”处理 Excel-连接
  • ASP.NET 非“自动化”处理 Excel-连接 Excel
    2007
  • ASP.NET 非“自动化”处理
    Excel-操作
  • ASP.NET 非“自动化”处理
    Excel-数据类型

使用 SQL 来操作 Excel 没有太多特殊的,但最特殊的一点是 SQL 语句 FROM
后面的表名,如何使用呢?

ref:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm

《连接Excel》节(说明:在我自己的MSDN中,它的例子使用了两个双引号是错的,测试没有通过,原文这样说的:

接着是一段利用“架构信息”动态读取Excel内部定义的表单或者命名区域的程序片断:

使用这种像数据库的方式操作 Excel
还有许多细节技术,将在后面的文章中一一介绍。


注意,Extended Properties 所需的双引号必须还要加双引号。

不仅可以,获取表明,还可以获取每张表内的字段名、字段类型等信息:

Excel 也属性文件式数据库,注意使用后释放资源。

方法三,在 SQL 语句中指定区域。

对于 Microsoft Excel 5.0 和 7.0 (95) 工作簿,请使用 Excel 5.0。

ylg娱乐官网 83用于 Extended Properties 值的有效 Excel 版本。 
ylg娱乐官网 84对于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿,请使用 Excel 8.0。 
ylg娱乐官网 85
ylg娱乐官网 86对于 Microsoft Excel 5.0 和 7.0 (95) 工作簿,请使用 Excel 5.0。 
ylg娱乐官网 87
ylg娱乐官网 88对于 Microsoft Excel 4.0 工作簿,请使用 Excel 4.0。 
ylg娱乐官网 89
ylg娱乐官网 90对于 Microsoft Excel 3.0 工作簿,请使用 Excel 3.0。 
ylg娱乐官网 91
ylg娱乐官网 92

连接字符串如下:

如果起始单元格和结束单元格相同,则表示只处理该单元格。

经常需要在数据库与Execl之间互导数据。net时代,ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel,网上已经有很多类似的资源,最典型也是最简单的可能如下:(asp.net环境)

ref:

注意

在 Excel 的表名后加美元符号,再加起始单元格编号,冒号,结束单元格编号。

Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended
Properties=Excel 8.0;HDR=1; IMEX=1;

GetOleDbSchemaTable 方法的详细说明可以参考:

  • ASP.NET 非“自动化”处理 Excel-连接
  • ASP.NET 非“自动化”处理 Excel-连接 Excel
    2007
  • ASP.NET 非“自动化”处理
    Excel-操作
  • ASP.NET 非“自动化”处理
    Excel-数据类型

示例:

select * from [Sheet1$A1:B10]
若要引用指定的范围,请使用该范围的名称。例如:

注意: Excel 8.0;HDR=NO  需要使用双引号(这里的反斜扛,是C#中的转义)

发表评论

电子邮件地址不会被公开。 必填项已用*标注