Excel批量插入表头教程

Excel批量插入表头教程

 　　情景：在此以制作工资条为例。每个月人力资源部需要将每个员工的工资详情统计出如图一所示表格，再批量添加表头后，制作成图二所示样式，然后打印成纸质剪裁发放，或者邮件发送每人的电子版工资详情。

　　操作步骤

　　1、选择表头内容的单元格，右键单击选中区域，选择复制命令。

　　2、算算共需要多少表头(11-2即9行)，在下方复制相应的行数。

　　3、如下图所示，对应工资行和表头行根据下图规律在后方编号。

　　4、在编号的一列，随便右键单击一个单元格，选择排序→升序。

　　5、删除有编号的L列，工资条制作完成。

ado.net 如何读取 excel

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

　　// 连接字符串

　　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：

　　2。数据源路径使用物理绝对路径(同access)

　　3。如何引用表名?

　　对 Excel 工作簿中表(或范围)的有效引用。

　　若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如：

　　select * from [Sheet1$]

　　若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如：

　　select * from [Sheet1$A1:B10]

　　若要引用指定的范围，请使用该范围的名称。例如：

　　select * from [MyNamedRange]

　　ref： 可以引用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。如何引用列名?

　　*根据默认连接字符串中，数据提供程序会将有效区域内的第一行作为列名，如果此行某单元格为空则用F1、F2表示，其中序数，跟单元格的位置一致，从1开始;

　　*如果希望第一行作为数据显示，而非列名，可以在连接串的 Extended Properties 属性指定：HDR=NO

　　默认值为：HDR=NO 格式如下：

　　string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

　　"Extended Properties="Excel 8.0;HDR=NO";" +

　　"data source=" + xlsPath;

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

　　ref：ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm 中 《连接Excel》节(说明：在我自己的MSDN中，它的例子使用了两个双引号是错的，测试没有通过，原文这样说的：

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

　　)

　　在这种情况下，所有的列名都是以F开头，然后跟索引，从F1开始，F2，F3。。。。。。。

　　5。为什么有效单元格数据不显示出来?

　　出现这种情况的可能原因是，默认连接中，数据提供程序根据前面单元格推断后续单元个的数据类型。

　　可以通过 Extended Properties 中指定 IMEX=1

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

　　ref：同4

　　PS：在baidu这个问题的时候，有网友说，将每个单元都加上引号，这固然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到治本药方

　　more ref：

　　如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据

　　应用程序经常需要与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。

　　以下是读取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 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 方法的详细说明可以参考：

　　接着是一段利用架构信息动态读取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 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();

　　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();

　　// more codes

　　// .

　　这里我们就不需要对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 方法

　　来获取数据源的架构信息。

ADO.NET操作Excel的限制

 ADO.NET操作Excel的限制：

　　1.不能设置单元格格式，插入新行会自动继承上一行的格式 .

　　? 2.您不能使用 ADO.NET 在单元格中插入公式。

　　?

　　3.Jet OLE DB 提供程序无法为 Excel 工作簿中的表提供主键/索引信息。因此，您不能使用 CommandBuilder 在 Excel 工作簿中自动更新和插入记录。

　　// 连接字符串

　　string xlsPath = @"d:PathFileName.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

　　需要注意的是：

　　1.数据提供程序使用Jet,同时需要指定Extended Properties 关键字设置 Excel 特定的属性，不同版本的Excel对应不同的属性值：用于 Extended Properties 值的有效 Excel 版本。

　　对于 Microsoft Excel 8.0 （97）、9.0 （2000） 和 10.0 （2002） 工作簿，请使用 Excel 8.0.

　m.tulaoshi.com　对于 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.如何引用表名？

　　若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如：

　　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 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为"表",而工作表被视为"系统表"）

　　当引用工作表名（[Sheet1$]）时，数据提供程序认为数据表从指定工作表上最左上方的非空单元格开始。比如，工作表从第 3 行，C 列开始，第3行，C列之前以及第1、2行全为空，则只会显示从第3行，C列开始的数据；以最后表最大范围内的非空单元结束；因此，如需要精确读取范围，应该使用命名区域 [NamedRange],或者指定地址：[Sheet1$A1:C10]图老师

　　4.如何引用列名？

　　根据默认连接字符串中，数据提供程序会将有效区域内的第一行作为列名，如果此行某单元格为空则用F1、F2表示，其中序数，跟单元格的位置一致，从1开始；

　　如果希望第一行作为数据显示，而非列名，可以在连接串的 Extended Properties 属性指定：HDR=NO

　　格式如下：

　　string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

　　"Extended Properties="Excel 8.0;HDR=NO";" + "data source=" + xlsPath;

　　在这种情况下，所有的列名都是以F开头，然后跟索引，从F1开始，F2,F3

　　注意： Excel 8.0;HDR=NO 需要使用双引号（这里的反斜扛，是C#中的转义）

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

　　5.为什么有效单元格数据不显示出来？

　　出现这种情况的可能原因是，默认连接中，数据提供程序根据前面单元格推断后续单元个的数据类型。

　　可以通过 Extended Properties 中指定 IMEX=1

　　"IMEX=1;"通知驱动程序始终将"互混"数据列作为文本读取

　　格式如下：

　　string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

　　"Extended Properties="Excel 8.0;HDR=NO;IMEX=1";" + "data source=" + xlsPath;

　　ref:同4

　　PS:在baidu这个问题的时候，有网友说，将每个单元都加上引号，这固然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到"治本药方：

　　more ref:

　　如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据

　　应用程序经常需要与Excel进行数据交互，以上阐述了基于ADO.NET 读取Excel的基本方法与技巧。现在要介绍是如何动态的读取Excel数据，这里的动态指的是事先不知道Excel文件的是什么样的结构，或者无法预测，比如一张。xls文件有多少张sheet,而且每张sheet的结构可能都不一样等等。

　　其实我们可以通过获取Excel的"架构信息"来动态的构造查询语句。这里的"架构信息"与数据库领域的"数据库架构信息"意义相同（也称"元数据"），对于整个数据库，这些"元数据"通常包括数据库或可通过数据库中的数据源、表和视图得到的目录以及所存在的约束等；而对于数据库中的表，架构信息包括主键、列和自动编号字段等。

　　在上文中提到

　　在关系数据库提供的各种对象中（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为"表",而工作表被视为"系统表"）

　　这里我们将Excel也当作一个"数据库"来对待，然后利用OleDbConnection.GetOleDbSchemaTable 方法

　　要获取所需的架构信息，该方法获取的架构信息与ANSI SQl-92是兼容的：ref:http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true

　　以下是读取Excel文件内"表"定义元数据，并显示出来的的程序片断：

　　// 读取Excel数据，填充DataSet

　　string sql_F = "SELECT * FROM [{0}]";

　　OleDbConnection conn = null;

　　OleDbDataAdapter da = null;

　　DataTable tblSchema = null;

　　IListstring tblNames = null;

　　conn = new OleDbConnection（connStr）；// 初始化连接

　　conn.Open（）； // 打开

　　tblSchema = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }）；// 获取数据源的表定义元数据

　　GetOleDbSchemaTable

　　tblNames = new Liststring（）；

　　foreach （DataRow row in tblSchema.Rows） {

　　tblNames.Add（（string）row["TABLE_NAME"]）； // 读取表名

　　}

　　da = new OleDbDataAdapter（）；// 初始化适配器

　　DataSet ds = new DataSet（）； // 准备数据，导入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进行处理 （省略）

　　这里我们就不需要对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 方法

　　来获取数据源的架构信息。

　　Asp.net/c#+OleDb操作excel文件 [ BugEyes 发表于 2007-10-13 15:58:00 ]

　　推荐

　　1、环境配置：加入using System.OleDb

　　2、编写连接与操作excel文件的通用函数

　　protected void DoOleSql（string sql, string database）

　　{

　　OleDbConnection conn = new OleDbConnection（）；

　　conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath（""） + database + "; Extended Properties='Excel 8.0;HDR=no;IMEX=0'";

　　try

　　{//打开连接

　　conn.Open（）；

　　}

　　catch （Exception e）

　　{

　　Response.Write（e.ToString（））；

　　}

　　OleDbCommand olecommand = new OleDbCommand（sql, conn）；

　　try

　　{//执行语句

　　olecommand.ExecuteNonQuery（）；

　　}

　　catch （Exception eee）

　　{

　　Response.Write（eee.ToString（））；

　　conn.Close（）；

　　}

　　finally

　　{

　　conn.Close（）；//关闭数据库

　　}

　　conn.Close（）；

　　}

　　注：1）使用 Excel 工作簿时，默认情况下，区域中的第一行是标题行（或字段名称）。如果第一个区域不包含标题，您可以在连接字符串的扩展属性中指定 HDR=NO.如果您在连接字符串中指定 HDR=NO,Jet OLE DB 提供程序将自动为您命名字段（F1 表示第一个字段，F2 表示第二个字段，依此类推）；2）IMEX=1将所有读入数据看作字符，其他值（0、2）请查阅相关帮助文档；3）如果出现"找不到可安装的isam"错误，一般是连接字符串错误

　　3、从excel文件读取数据

　　string sql = "select * from [sheet1$]";

　　DoOleSql（sql,"test.xls"）；

　　4、更新excel文件中的数据

　　string sql = "update [sheet1$] set FieldName1='333' where FieldName2='b3'";

　　DoOleSql（sql,"test.xls"）；

　　5、向excel文件插入数据

　　string sql = "insert into [sheet1$]（FieldName1,FieldName2,） values（'a','b',）";

　　DoOleSql（sql,"test.xls"）；

　　6、删除excel文件中的数据：不提倡使用这种方法

　　7、对于非标准结构的excel表格，可以指定excel中sheet的范围

　　1）读取数据：string sql = "select * from [sheet1$A3:F20]";

　　2）更新数据：string sql = "update [sheet1$A9:F15] set FieldName='333' where AnotherFieldName='b3'";

　　3）插入数据：string sql = "insert into [sheet1$A9:F15]（FieldName1,FieldName2,） values（'a','b',）";

　　4）删除数据：不提倡

　　注：1）代码根据需要可以自行修改；2）如果出现"操作必须使用一个可更新的查询"错误，可能sql语句中对excel文件中的"字段"引用有错误，或对excel文件不具有"修改"权限；3）如果出现"不能扩充选定范围"错误，可能是对excel文件引用的"范围"有错误。

Excel结合Word实现表格格式转换

 　　Excel结合Word实现表格格式转换