OLEDB Command Execl 2007(注:IMEX=0则可Update 数据)

最新推荐文章于 2021-08-31 20:06:03 发布

super_tiger_

最新推荐文章于 2021-08-31 20:06:03 发布

阅读量1.2k

点赞数

分类专栏： C# execl 文章标签： C# execl

本文链接：https://blog.csdn.net/king19861228/article/details/9178331

版权

C# 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

execl

2 篇文章 0 订阅

订阅专栏

如何在 Visual Basic .NET 中使用 ADO.NET 检索和修改 Excel 工作簿中的记录

察看本文应用于的产品

文章编号	:	316934
最后修改	:	2004年6月29日
修订	:	7.0

概要

本文讨论如何使用 ADO.NET 检索 Microsoft Excel 工作簿中的数据、修改现有工作簿中的数据或将数据添加至新的工作簿中。要通过 ADO.NET 访问 Excel 工作簿，您可以使用 Jet OLE DB 提供程序；本文提供了您所需要的信息，以便您可以在 Excel 充当目标数据源时使用 Jet OLE DB 提供程序。

回到顶端

如何将 Jet OLE DB 提供程序与 Microsoft Excel 工作簿配合使用

Microsoft Jet 数据库引擎可以通过可安装的索引顺序访问方法 (ISAM) 驱动程序，访问格式为其他数据库文件（例如 Excel 工作簿）的数据。要打开 Microsoft Jet 4.0 OLE DB 提供程序所支持的外部格式，请在连接的扩展属性中指定数据库类型。Jet OLE DB 提供程序对于 Microsoft Excel 工作簿支持下列数据库类型：

Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0

注意：对于 Microsoft Excel 5.0 和 7.0 (95) 工作簿，请使用 Excel 5.0 源数据库类型；对于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿，请使用 Excel 8.0 源数据库类型。本文中的示例使用的是格式为 Excel 2000 和 Excel 2002 的 Excel 工作簿。

连接字符串

要使用 Jet OLE DB 提供程序访问 Excel 工作簿，请使用具有下列语法的连接字符串：

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties="Excel 8.0;HDR=YES;"

在连接字符串中，用 Data Source 参数指定工作簿的完整路径和文件名。Extended Properties 参数可包含两种属性：一个属性用于 ISAM 版本，一个属性用于指示表是否包括标题。

使用 Excel 工作簿时，默认情况下，区域中的第一行是标题行（或字段名称）。如果第一个区域不包含标题，您可以在连接字符串的扩展属性中指定 HDR=NO。如果您在连接字符串中指定 HDR=NO，Jet OLE DB 提供程序将自动为您命名字段（F1 表示第一个字段，F2 表示第二个字段，依此类推）。

数据类型

与传统的数据库不同，在 Excel 表中没有指定列的数据类型的直接方式。而是，OLE DB 提供程序通过对一列中的八行进行扫描来猜测该字段的数据类型。您可以通过为连接字符串的扩展属性中的 MAXSCANROWS 设置指定一个一 (1) 至十六 (16) 之间的值，来更改要扫描的行数。

表命名规则您可以通过若干种方式引用 Excel 工作簿中的表（或区域）：

•	使用工作表名称后面跟一个美元符号（例如 [Sheet1$] 或 [My Worksheet$]）。以此方式引用的工作簿包括工作表的整个使用区域。 Select * from [Sheet1$]
•	使用带有定义名称的区域（例如 [MyNamedRange]）： Select * from [MyNamedRange]
•	使用带有特定地址的区域（例如 [Sheet1$A1:B10]）： Select * from [Sheet1$A1:B10]

注意：工作表名称后面带美元符号表示该表存在。如果您要创建一个新表，如本文创建新工作簿和表一节中所讨论的那样，请不要使用美元符号。

回到顶端

如何将 Excel 工作簿用作 ADO.NET 数据源

检索记录

您可以使用 ADO.NET 中的两种方法之一检索数据库中的记录：使用 Dataset 或使用 DataReader。

Dataset 是一个从数据源检索到的记录的缓存。Dataset 中的数据通常要比数据库中的数据精简得多。但是，您可以像使用实际数据一样使用它，并且无须与实际数据库连接。除了数据检索之外，还可以使用 Dataset 在基础数据库上执行更新操作。

或者，您可以使用 DataReader 从数据库中检索只读、只进数据流。当您使用 DataReader 程序时，由于内存中每次仅有一行，因此性能将增强，系统开销将降低。如果您有大量数据需要检索并且不希望更改基础数据库，则 DataReader 是比 Dataset 更好的选择。

添加和更新记录使用 ADO.NET，您可以通过三种方式之一在工作簿中插入和更新记录：

•

直接运行一个命令，每次插入或更新一个记录。为此，您可以在自己的连接上创建一个 OLEDbCommand 对象，并将其 CommandText 属性设置成一个插入记录的有效命令

INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC')

或一个可更新记录的命令，

UPDATE [Sheet1$] SET F2 = 'XYZ' WHERE F1 = '111'

然后调用 ExecuteNonQuery 方法。

•

对使用 Excel 工作簿中的一个表/查询填充的一个 DataSet 做一些更改，然后调用 DataAdapter 的 Update 方法，以将这些更改从 DataSet 解析回工作簿。不过，要使用 Update 方法执行更新解析，您必须为 DataAdapter 的 InsertCommand 设置参数化命令：

INSERT INTO [Sheet1$] (F1, F2) values (?, ?)

并为 UpdateCommand 设置参数化命令：

UPDATE [Sheet1$] SET F2 = ? WHERE F1 = ?

必须要用 INSERT 和 UPDATE 的参数化命令，因为 OleDbDataAdapter 不提供 Excel 工作簿的主键/索引信息；没有主键/索引字段，CommandBuilder 就无法自动为您生成命令。

•

如果 Jet OLE DB 提供程序能够使用另外的数据源，请将该数据源中的数据导出到 Excel 工作簿中。可通过 Jet OLE DB 提供程序以这种方式使用的数据源包括：文本文件、Microsoft Access 数据库，当然也包括其他 Excel 工作簿。使用单个 INSERT INTO 命令，您可以将其他表/查询中的数据导出到您的工作簿中：

INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"

INSERT INTO 要求目标表（或工作表）已存在，并且数据已附加到目标表中。

您还可以使用 SELECT..INTO 将您的表/查询导出到工作簿中：

SELECT * INTO [Excel 8.0;Database=C:\Book1.xls].[Sheet1] FROM [MyTable]

当您使用 SELECT..INTO 时，如果目标表或工作簿不存在，将为您创建。如果在发出 SELECT..INTO 命令之前表已存在，您将收到错误信息。

本文的稍后的示例代码部分对这些在工作簿中添加和更新记录的方法中的每一种都进行了说明。

删除记录

虽然 Jet OLE DB 提供程序允许您在 Excel 工作簿中插入和更新记录，但是不允许进行 DELETE（删除）操作。如果您尝试对一个或多个记录执行 DELETE 操作，您将收到以下错误信息：

Deleting data in a linked table is not supported by this ISAM.

这是将 Excel 工作簿作为数据库进行处理时所固有的限制。

创建工作簿和表

要在 Excel 工作簿中创建表，请运行 CREATE TABLE 命令：

CREATE TABLE Sheet1 (F1 char(255), F2 char(255))

当您运行此命令时，将使用您在命令中指定的表名称创建新的工作表。如果不存在要连接的工作簿，也会创建该工作簿。

本文的示例代码部分说明了如何使用 CREATE TABLE 命令创建新的工作簿和表。

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="InertExecl.aspx.cs" Inherits="InertExecl" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Button ID="Button1" runat="server" Text="Button" οnclick="Button1_Click" />
    
    <asp:GridView ID="GridView2" runat="server" AllowPaging="True" PageSize="30">
    </asp:GridView>
        2222<br />
    
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" PageSize="30">
    </asp:GridView>
    </div>
    </form>
</body>
</html>







using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;


public partial class InertExecl : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Administrator\\Desktop\\Test_2WROW_OLEDB_DATA.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
        OleDbConnection Oconn = new OleDbConnection(connString);
        Oconn.Open();
        OleDbDataAdapter Oda = new OleDbDataAdapter("select * from [sheet1$]", Oconn);
        DataTable dt = new DataTable();
        Oda.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        Oda.Dispose();
        connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\EXECL_TEST\ll.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=0'";
        Oconn = new OleDbConnection(connString);
        OleDbCommand comm = new OleDbCommand();
        Oconn.Open();


        System.Data.DataTable table = Oconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        string tableColumns_arr = "";
        for (int i = 0; i < table.Rows.Count ; i++)
        {
            Response.Write("<br />TableName:" + table.Rows[i]["Table_Name"].ToString());


            DataTable tableColumns = Oconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, table.Rows[i]["Table_Name"].ToString(), null });
            tableColumns.DefaultView.Sort = "Column_Name ASC";
            DataTable tc = tableColumns.DefaultView.ToTable();
            for (int ii = 0; ii < tc.Rows.Count; ii++)
            {
                Response.Write("<br />ColumnName:" + tc.Rows[ii]["Column_Name"].ToString());
                if (i == 0)
                {
                    tableColumns_arr += "[" + tc.Rows[ii]["Column_Name"].ToString() + "],";
                }
            }
        }
	//Delete Table
     	comm.CommandText = "DROP TABLE [sheet1$];";
        comm.Connection = Oconn;
        comm.ExecuteNonQuery();


        try
        {
            Oda = new OleDbDataAdapter("select * from [sheet1$]", Oconn);
            dt = new DataTable();
            Oda.Fill(dt);
            GridView2.DataSource = dt;
            GridView2.DataBind();


            tableColumns_arr = tableColumns_arr.Remove(tableColumns_arr.Length - 1);
            Response.Write("<br /> INSERT INTO [sheet1$] (" + tableColumns_arr + ") values ('1','2','3','4');");
            for (int i = 0; i < 100; i++)
            {
                //"INSERT [sheet1$] (F1,FieldName2) values ('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "')"
                //"update [sheet1$] set [FieldName1]='kkkkk' where [FieldName1]=1"
                //("insert into [Sheet1$] values('{0}','{1}','{2}')", "陈太汉", "陈晓玲", "520");


                
                
                comm.CommandText = "INSERT INTO [sheet1$] ([F1],[F2],[F3],[F4]) values ('1','2','3','4');";
                comm.Connection = Oconn;
                comm.ExecuteNonQuery();


            }
            comm.Dispose();
            Oconn.Close();
            Oconn.Dispose();
        }
        catch (Exception ex)
        {
            Response.Write("<br />Error:" + ex.Message);
            Oconn.Close();
            Oconn.Dispose();
        }
        finally
        {
            comm.Dispose();
            Oconn.Close();
            Oconn.Dispose();
        }






    }






    protected DataTable Get_DT(DataTable dt)
    {


        for (int i = 0; i < dt.Rows.Count - 1; i++)
        {
            for (int ii = 0; ii < dt.Columns.Count - 1; ii++)
            {
                if (!get_num_warning(dt.Rows[i][1]))
                {
                    dt.Rows[i][dt.Columns.Count - 1] = "false";
                }
                else
                {
                    dt.Rows[i][dt.Columns.Count - 1] = "true";
                }
            }
        }
        return dt;
    }


    protected bool get_num_warning(object key)
    {
        try
        {
            Double k = Convert.ToDouble(key);
            return true;
        }
        catch
        {
            return false;
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。当我们设置IMEX＝1时将强制混合数据转换为文本，但仅仅这种设置并不可靠，
        //IMEX＝1只确保在某列前8行数据至少有一个是文本项的时候才起作用，它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字，
        //那么它仍然以数字类型作为该列的数据类型，随后行里的含有文本的数据仍然变空。
        //另一个改进的措施是IMEX＝1与注册表值TypeGuessRows配合使用，TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型，默认为“8”。
        //可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题，
        //即使我们把IMEX设为“1”， TypeGuessRows设得再大，例如1000，假设数据表有1001行，某列前1000行全为纯数字，该列的第1001行又是一个文本，ISAM驱动的这种机制还是让这列的数据变成空。
        //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES


        DataTable dt = (DataTable)ViewState["dt"];
        string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\EXECL_TEST\TEST.xlsx;Extended Properties='Excel 12.0;HDR=Yes;IMEX=2'";
        OleDbConnection Oconn = new OleDbConnection(connString);
        Oconn.Open();
        for (int i = 0; i < 100; i++)
        {
            OleDbCommand comm = new OleDbCommand("insert [sheet1$] ([C1],[C2])values('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "')", Oconn);
            comm.ExecuteNonQuery();
            comm = null;
            comm.Dispose();
        }
        Oconn.Close();
        Oconn.Dispose();
    }


}

super_tiger_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
OLEDB Command Execl 2007(注:IMEX=0则可Update 数据)

如何在 Visual Basic .NET 中使用 ADO.NET 检索和修改 Excel 工作簿中的记录察看本文应用于的产品文章编号:316934最后修改:2004年6月29日修订:7.0概要本文讨论如何使用 ADO.NET 检索 Microsoft Exc
复制链接

扫一扫