C#读取Excel,Access数据库
2013-10-27 18:27 糯米粥 阅读(2063) 评论(1) 编辑 收藏 举报出自:http://blog.csdn.net/limpire/article/details/2599760
使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007
测试文件:D:/97-2003.xls和D:/2007.xlsx,两个文件的内容是一模一样的。
测试环境:SQL Server 2000 / 2005。
- 接口类型
- 语法一览
- 注册表设置
- 单一数据类型列的类型解析
- 混合数据类型列的自然解析
- 混合数据类型列的强制解析——IMEX=1
- 如何解决 NULL 值问题
- SQL Server 2000 中的列顺序问题
- 如何访问隐藏的 Sheet
- 如何访问非常规命名的 Sheet
- 关于Excel导入的HDR=YES; IMEX=1详解
- C#读取Access数据库
接口类型
有两种接口可供选择:Microsoft.Jet.OLEDB.4.0(以下简称 Jet 引擎)和Microsoft.ACE.OLEDB.12.0(以下简称 ACE 引擎)。
Jet 引擎大家都很熟悉,可以访问 Office 97-2003,但不能访问 Office 2007。
ACE 引擎是随 Office 2007 一起发布的数据库连接组件,既可以访问 Office 2007,也可以访问 Office 97-2003。
另外:Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件,而 Microsoft.Jet.OLEDB.4.0 是不可以的。
Microsoft.ACE.OLEDB.12.0 安装文件:
语法一览
使用 Jet 引擎或 ACE 引擎访问,在语法上没有什么的区别。
--> Jet 引擎访问 Excel 97-2003 select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]') select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$]) select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$] select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$] --> ACE 引擎访问 Excel 97-2003 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$]) select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$] select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$] --> ACE 引擎访问 Excel 2007 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet1$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', [Sheet1$]) select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$] select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
Excel 2007 工作簿文件的扩展名是:xlsx
HDR=Yes/No
可选参数,指定 Excel 表的第一行是否列名,缺省为 Yes,可以在注册表中修改缺省的行为。
IMEX=1
可选参数,将 Excel 表中混合 Intermixed 数据类型的列强制解析为文本。
注册表设置
Microsoft.Jet.OLEDB.4.0
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel
Microsoft.ACE.OLEDB.12.0
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Office/12.0/Access Connectivity Engine/Engines/Excel
FirstRowHasNames
设置 HDR 参数的缺省行为,默认为 Yes。
ImportMixedTypes
设置混合列的强制解析类型,默认为文本 Text。
TypeGuessRows
设置用于解析数据类型的取样行数,默认取样前 8 行。如果设置为 0,将分析所有数据行,但不建议这样做,会影响引擎的性能。
注意:Excel 表数据列是单一列数据类型还是混合列数据类型列,是由取样行决定,而不是整列数据决定。
单一数据类型列的类型解析
Sheet1 的内容如下图所示,涵盖了大部分 Excel 的数据类型,其中 longtext 分别有 256 个 A 和 B。
对于单一数据类型列的类型解析,ACE 引擎和 Jet 引擎是一样的,下面测试 Jet 引擎的数据解析:
use tempdb go select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet1$]') select a.name, date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end from syscolumns a inner join systypes b on a.xusertype = b.xusertype where a.id = object_id('#type') /* name date_type -------- ------------- longtext ntext text nvarchar(255) datetime datetime date datetime time datetime money money float float numeric float integer float */ drop table #type
数据类型解析总结
-
文本:长度 <= 255,解析为 nvarchar(255),长度 > 255,解析为 ntext。
-
数值:货币解析为 money,其它均解析为 float。
-
时间:datetime。
混合数据类型列的自然解析
相对于使用 IMEX=1 的强制解析,不使用 IMEX=1,称为自然解析。下图是 Sheet2 的内容:
对于混合数据类型列的自然解析,ACE 引擎和 Jet 有细节上的区别,先看测试:
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]') /* id describe num>str num=str num<str ----- -------- -------- -------- -------- 1 sampling 1 1 NULL 2 sampling 2 2 NULL 3 sampling 3 3 NULL 4 sampling 4 4 A 5 sampling 5 NULL B 6 sampling NULL NULL C 7 sampling NULL NULL D 8 sampling NULL NULL E 9 others 1 2 NULL 10 others NULL NULL < ----- -------- ------- --------- -------- float nvarchar float float nvarchar <-- 解析的数据类型 */ select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/2007.xlsx', 'select * from [Sheet2$]') /* id describe num>str num=str num<str ----- -------- -------- -------- -------- 1 sampling 1 1 1 2 sampling 2 2 2 3 sampling 3 3 3 4 sampling 4 4 A 5 sampling 5 A B 6 sampling NULL B C 7 sampling NULL C D 8 sampling NULL D E 9 others 1 2 3 10 others NULL = < ----- -------- -------- -------- -------- float nvarchar float nvarchar nvarchar <-- 解析的数据类型 */
相同地方
-
取样行里数值型多于文本型,解析为 float 数值。
-
取样行里数值型少于文本型,解析为 nvarchar/ntext 文本。
-
当解析为 float 数值时,文本类型显示为 NULL,这点毫无疑问。
相异地方
-
取样行里数值型等于文本型,Jet 引擎解析为 float 数值,数值优先,ACE 引擎解析为 nvarchar/ntext 文本,文本优先。
-
当解析为 nvarchar/ntext 文本时,Jet 引擎将非文本数据显示为 NULL,ACE 引擎正确显示。
混合数据类型列的强制解析——IMEX=1
使用 IMEX=1 选参之后,只要取样数据里是混合数据类型的列,一律强制解析为 nvarchar/ntext 文本。当然,IMEX=1 对单一数据类型列的解析是不影响的。
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]') select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet2$]') /* id describe num>str num=str num<str ----- -------- -------- -------- -------- 1 sampling 1 1 1 2 sampling 2 2 2 3 sampling 3 3 3 4 sampling 4 4 A 5 sampling 5 A B 6 sampling A B C 7 sampling B C D 8 sampling C D E 9 others 1 2 3 10 others > = < ----- -------- -------- -------- -------- float nvarchar nvarchar nvarchar nvarchar <-- 解析的数据类型 */
最后一列(num<str),Jet 引擎的自然解析和强制解析都解析为 nvarchar(255),但是自然解析将数值显示为 NULL,强制解析却能正确显示,这是不一致的地方。
在数据解析的细节方面,ACE 引擎的表现优于 Jet 引擎。在前面提到的文本优先问题、非文本数据的 NULL 值问题,ACE 引擎的解析更合理。
如何解决 NULL 值问题
前 8 行(取样行)是混合数据类型的列,使用 IMEX=1 选参解决。
前 8 行是文本,8 行之外有非文本的数据,使用 ACE 引擎解决。
前 8 行是数值,8 行之外又非数值的数据:
-
将前 8 行其中一行的单元格式数字设置为文本(如果还不行,可能要手工重写该单元格,以应用文本格式,不记得是 Office 97 还是 2000 存在这个问题了);
-
修改注册表中的 TypeGuessRows(注册表设置),增加取样行数,或设置为 0 全部解析。
目的只有一个,让取样行变成混合数据类型的列,然后使用 IMEX=1 选参解决。
SQL Server 2000 中的列顺序问题
这是 SQL Server 2000 行集函数 OpenRowSet 和 OpenDataSource 本身的问题,与访问接口引擎无关,也与 Excel 版本无关。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在这个问题。
上图是 Sheet3 的内容,连接到 SQL Server 2000 测试看看是什么问题:
--> HDR=Yes select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:/97-2003.xls', [Sheet3$]) select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/97-2003.xls')...[Sheet3$] select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$] /* A B C D E F G H I J --- --- --- --- --- --- --- --- --- --- C10 C9 C8 C7 C6 C5 C4 C3 C2 C1 */ --> HDR=No select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:/97-2003.xls', [Sheet3$]) select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/97-2003.xls')...[Sheet3$] select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$] /* F1 F10 F2 F3 F4 F5 F6 F7 F8 F9 --- --- --- --- --- --- --- --- --- --- J A I H G F E D C B C1 C10 C2 C3 C4 C5 C6 C7 C8 C9 */
返回结果集的列顺序,是按照列名排序,并不是 Excel 表的列顺序。HDR=No 貌似正确,但仔细一看,仍然是按列名排序的。
OpenRowSet(query)
OpenRowSet(query) 可以解决这个列顺序的问题,包括后面的访问隐藏的 Sheet 或非常规命名的 Sheet,都可以用 OpenRowSet(query) 解决。
--> HDR=Yes select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:/97-2003.xls', 'select * from [Sheet3$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/97-2003.xls', 'select * from [Sheet3$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/2007.xlsx', 'select * from [Sheet3$]') /* J I H G F E D C B A --- --- --- --- --- --- --- --- --- --- C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 */ --> HDR=No select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:/97-2003.xls', 'select * from [Sheet3$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/97-2003.xls', 'select * from [Sheet3$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/2007.xlsx', 'select * from [Sheet3$]') /* F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 --- --- --- --- --- --- --- --- --- --- J I H G F E D C B A C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 */
如何访问隐藏的 Sheet
隐藏 Sheet 的访问情况比较复杂,就不写测试过程了,归纳一下:
-
使用 OpenRowSet(query) 肯定可以访问。
-
Excel 2007 任何写法都可以访问(Jet 引擎不能访问 Excel 2007)。
-
打开的 Excel 文件任何写法都可以访问(Jet 引擎不能访问打开的 Excel 文件)。
如何访问非常规命名的 Sheet
新建一个空白的 Sheet,重命名为 4 保存关闭:
-
使用 OpenRowSet(query) 可以正常访问。
-
其它写法,用单引号限定名称 ['4$'] 可以访问。
- OpenRowSet(query) 也可以使用单引号限定访问:'select * from [''4$'']'
引出最后一个问题,访问 Excel 97-2003 空白的 Sheet,会返回一行 NULL 值,访问 Excel 2007 空白的 Sheet,返回空结果集,数据类型均解析为 nvarchar(255)。
关于Excel导入的HDR=YES; IMEX=1详解
出自:http://www.cnblogs.com/goto/archive/2012/04/12/2443670.html参数HDR的值: HDR=Yes,这代表第一行是标题,不做为数据使用 ,
C#读取Access数据
读取Access数据库也是也用JET或者 ACE 引擎。因为都是微软的东东![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?xml version="1.0"?> <configuration> <configSections> </configSections> <connectionStrings> <add name="GoodManage.Properties.Settings.GMConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\GM.accdb" providerName="System.Data.OleDb" /> </connectionStrings> <appSettings> <!--<add key="accessCon" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\db\GM.accdb;Persist Security Info=False"/>--> <add key="accessCon" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\db\GM.accdb;Persist Security Info=False"/> <add key="SQLConnString" value="provider=microsoft.jet.oledb.4.0;data source="/> <add key="dbPath" value="..\..\db\GM.accdb"/> </appSettings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/> </startup> </configuration>
SQLHelper类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.SqlClient; 6 using System.Data; 7 using System.Configuration; 8 using System.Data.OleDb; 9 using System.Web; 10 namespace DAL 11 { 12 public class SQLHelper 13 { 14 //连接字符串 15 16 static string connStr = ConfigurationManager.AppSettings["accessCon"]; 17 //static string connStr = System.Windows.Forms.Application.StartupPath + @"\db\GM.accdb"; 18 //static string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+System.Windows.Forms.Application.StartupPath+@"\db\GM.accdb;Persist Security Info=False"; 19 //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\db\GM.accdb;Persist Security Info=False 20 21 22 /// <summary> 23 /// 返回受影响的行数 24 /// </summary> 25 /// <param name="comText"></param> 26 /// <param name="param"></param> 27 /// <returns></returns> 28 public static int ExecuteNonQuery(string comText, params OleDbParameter[] param) 29 { 30 using (OleDbConnection conn = new OleDbConnection(connStr)) 31 { 32 using (OleDbCommand cmd = new OleDbCommand(comText, conn)) 33 { 34 if (param != null && param.Length != 0) 35 { 36 cmd.Parameters.AddRange(param); 37 } 38 if (conn.State == ConnectionState.Closed) 39 { 40 conn.Open(); 41 } 42 return cmd.ExecuteNonQuery(); 43 } 44 } 45 } 46 /// <summary> 47 /// 返回数据对象 48 /// </summary> 49 /// <param name="comText"></param> 50 /// <param name="param"></param> 51 /// <returns></returns> 52 public static object ExecuteScalar(string comText, params OleDbParameter[] param) 53 { 54 using (OleDbConnection conn = new OleDbConnection(connStr)) 55 { 56 using (OleDbCommand cmd = new OleDbCommand(comText, conn)) 57 { 58 if (param != null && param.Length != 0) 59 { 60 cmd.Parameters.AddRange(param); 61 } 62 if (conn.State == ConnectionState.Closed) 63 { 64 conn.Open(); 65 } 66 return cmd.ExecuteScalar(); 67 } 68 } 69 } 70 /// <summary> 71 /// 返回table 72 /// </summary> 73 /// <param name="cmdText"></param> 74 /// <param name="param"></param> 75 /// <returns></returns> 76 public static DataTable Adapter(string cmdText, params OleDbParameter[] param) 77 { 78 DataTable dt = new DataTable(); 79 //OleDbConnection con = new OleDbConnection(); 80 using (OleDbDataAdapter oda = new OleDbDataAdapter(cmdText, connStr)) 81 { 82 83 if (param != null && param.Length != 0) 84 { 85 oda.SelectCommand.Parameters.AddRange(param); 86 } 87 if (new OleDbConnection().State == ConnectionState.Closed) 88 { 89 new OleDbConnection(connStr).Open(); 90 } 91 oda.Fill(dt); 92 } 93 return dt; 94 95 /* 96 using (SqlDataAdapter sda = new SqlDataAdapter(cmdText, connStr)) 97 { 98 if (param != null && param.Length != 0) 99 { 100 sda.SelectCommand.Parameters.AddRange(param); 101 } 102 sda.Fill(dt); 103 } 104 return dt; 105 * */ 106 } 107 /// <summary> 108 /// 向前读取记录 109 /// </summary> 110 /// <param name="cmdText"></param> 111 /// <param name="param"></param> 112 /// <returns></returns> 113 public static OleDbDataReader ExectueReader(string cmdText, params OleDbParameter[] param) 114 { 115 OleDbConnection conn = new OleDbConnection(connStr); 116 using (OleDbCommand cmd = new OleDbCommand(cmdText, conn)) 117 { 118 if (param != null && param.Length != 0) 119 { 120 cmd.Parameters.AddRange(param); 121 } 122 if (conn.State == ConnectionState.Closed) 123 { 124 conn.Open(); 125 } 126 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 127 } 128 } 129 /// <summary> 130 /// 读取存储过程 131 /// </summary> 132 /// <param name="cmdText"></param> 133 /// <param name="type"></param> 134 /// <param name="param"></param> 135 /// <returns></returns> 136 public static DataTable GetPro(string cmdText, CommandType type, params OleDbParameter[] param) 137 { 138 DataTable dt = new DataTable(); 139 using (OleDbDataAdapter sda = new OleDbDataAdapter(cmdText, connStr)) 140 { 141 new OleDbCommand().CommandType = CommandType.StoredProcedure; 142 if (param != null && param.Length != 0) 143 { 144 sda.SelectCommand.Parameters.AddRange(param); 145 } 146 sda.Fill(dt); 147 } 148 return dt; 149 } 150 } 151 }
主程序
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /// <summary> 2 /// 库存选择 3 /// </summary> 4 /// <param name="sender"></param> 5 /// <param name="e"></param> 6 private void pbChange_Click(object sender, EventArgs e) 7 { 8 //文本文件(*.txt)|*.txt|所有文件(*.*)|*.*” 9 OpenFileDialog ofd = new OpenFileDialog(); 10 DataTable dt = new DataTable(); 11 //定义筛选类型 12 ofd.Filter = "Excel(*.xls;*.xlts)|*.xls;*.jpg;*.et"; 13 ofd.Title = "请选择Excel文件"; 14 ofd.FileName = "9.xls"; 15 //openFileDialog1. 16 if (ofd.ShowDialog() == DialogResult.OK) 17 { 18 dt = getTable(ofd.FileName); 19 if (dt == null) return; 20 21 txtfile.Text = ofd.FileName; 22 //removeEmpty(dt); 23 //dt.AcceptChanges(); 24 25 if (dt.Rows.Count > 0) 26 { 27 dgvkc.DataSource = dt; 28 29 }//txtfile.ReadOnly=true; 30 } 31 32 33 } 34 35 /// <summary> 36 /// 获取Excel 37 /// </summary> 38 /// <param name="fileName">读取的文件路径</param> 39 /// <returns></returns> 40 private DataTable getTable(string fileName) 41 { 42 try 43 { 44 DataSet ds = new DataSet(); 45 OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0;IMEX=1'"); //Excel 8.0;HDR=NO;IMEX=1中的IMEX=0; 46 objConn.Open(); 47 DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); 48 49 string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名,默认值是sheet1 50 51 string queryField = "商品名称,商品价格,商品描述,最低价格,库存数量,备注"; 52 53 string strSql = "select " + queryField + " from [" + tableName + "]"; 54 //string strSql = "select * from [" + tableName + "]"; 55 56 OleDbCommand objCmd = new OleDbCommand(strSql, objConn); 57 OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn); 58 myData.Fill(ds, tableName);//填充数据 59 return ds.Tables[0]; 60 } 61 catch (Exception ex) 62 { 63 //获取文件名 64 string fName = fileName.Substring(fileName.LastIndexOf("\\") + 1); 65 MessageBox.Show(ex.Message); 66 return null; 67 } 68 }