能否用ADO.NET的技术访问EXCEL中的数据

1. // 连接字符串            
   
2.         string xlsPath = Server.MapPath("~/app_data/somefile.xls"); // 绝对物理路径
   
3.         string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
   
4.                         "Extended Properties=Excel 8.0;" +
   
5.                         "data source=" + xlsPath;
   
6.         // 查询语句
   
7.         string sql = "SELECT * FROM [Sheet1$]";

复制代码

1、数据提供程序使用Jet，同时需要指定Extended Properties 关键字设置 Excel 特定的属性，不同版本的Excel对应不同的属性值
2、数据源路径使用物理绝对路径
3、如何引用表名：
对 Excel 工作簿中表（或范围）的有效引用。
若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如：
select * from [Sheet1$]
若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如：
select * from [Sheet1$A1:B10]
若要引用指定的范围，请使用该范围的名称。例如：
select * from [MyNamedRange]

一、在程序中，用 ADO.NET。代码如下：

　　//连接串
　　string　strConn　=　"Provider=Microsoft.Jet.OLEDB.4.0;Extended　Properties=Excel　8.0;Data　Source="　+　[EXCEL文件，含路径]　+　";";
　　OleDbConnection　conn　=　new　OleDbConnection(strConn);
　　conn.Open();
　　DataTable　dtSchema　=　conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new　object[]　{null,　null,　null,　"TABLE"});
　　DataSet　ds　=　new　DataSet();
　　//一个EXCEL文件可能有多个工作表，遍历之
　　foreach(　DataRow　dr　in　dtSchema.Rows　)
　　{
　　string　table　=　dr["TABLE_NAME"].ToString();
　　string　strExcel　=　"SELECT　*　FROM　["　+　table　+　"]";
　　ds.Tables.Add(table);
　　OleDbDataAdapter　myCommand　=　new　OleDbDataAdapter(strExcel,conn);
　　myCommand.Fill(ds,table);
　　}
　　conn.Close();

　　这样，读取出来的数据就藏在 DataSet里了。

　　采用这种方式，数据库所在机器不必装有 EXCEL。

　　二、在查询分析器里，直接写 SQL语句：

　　如果是导入数据到现有表，则采用

　　INSERT INTO 表 SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

　　,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)的形式

　　如果是导入数据并新增表，则采用

　　SELECT * INTO 表 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

　　,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)的形式。

　　以上语句是将 EXCEL文件里 SHEET1工作表中所有的列都读进来，如果只想导部分列，可以

　　INSERT INTO 表 (a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

　　,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

　　其实可以将 OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

　　,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)当成一个表，例如我就写过这样一个句子：

　　INSERT INTO eval_channel_employee(channel,employee_id)

　　SELECT CASE a.渠道 WHEN 'DIY' THEN 1 WHEN 'RDC' THEN 0 WHEN 'KCM' THEN 2 ELSE 3 END

　　,b.id FROM

　　OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

　　,'Excel 5.0;HDR=YES;DATABASE=c:\temp\name.xls',sheet1$) AS a,pers_employee b

　　WHERE a.员工编码 =b.code

　　不管是哪种方式，哪种途径，系统都会默认将第一行上的内容作为字段名。

　　这二种方式虽然非常好，但操作过程不太方便，经过测试发现在 SQL2005中直接可以实现导入功能。操作过程如下：

　　第一步：登录到 SQL Server Management Studio，

　　第二步：在 “对象资源管理器 ”中右键单击 “管理 ”，在弹出列表中单击 “导入数据 ”

　　第三步：在 “导入向导 ”对话框中单击 “下一步 ”，进入到 “选择数据源 ”对话框，在 “数据源 ”列表中选择 “Microsoft Excel ”，同时选择相应的 Excel 文档，完成后单击 “下一步 ”（一定要勾选该对话框中的 “首行包含列名称 ”，因此它是将 Excel文档中的列标题为数据库表中的列项标题）

　　第四步：指定目标数据库服务，依次单击 “下一步 ”。。。。至到 “完成 ”

　　第五步：重新打到 SQL Server Management Studio，进入到导入的数据库表，可以发现所导入的 Excel文档数据。

现在我们一般使用NPOI技术实现ADO对Excel的操作
首先得添加引用NPOI.dll和Ionic.Zip.dll 两个程序集

1. using System;
   
2. using System.Collections.Generic;
   
3. using System.Linq;
   
4. using System.Text;
   
5. using System.IO;
   
6. using System.Data.SqlClient;
   
7. using NPOI.SS.UserModel;
   
8. using NPOI.HSSF.UserModel;
   
9. using System.Configuration;
   
10. namespace 将Excel数据导入数据库
    
11. {
    
12. class Program
    
13. {
    
14. string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
    
15. static void Main(string[] args)
    
16. {
    
17. //从Excel中将数据导入数据库
    
18. //LoadDataToDBFromExcel();
    
19. //从数据库中将数据导出到Excel
    
20. LoadDataToExcelFromDb();
    
21. Console.ReadKey();
    
22. }
    
23. /// <summary>
    
24. /// 从数据库中读取数据导入到Excel中
    
25. /// </summary>
    
26. private static void LoadDataToExcelFromDb()
    
27. {
    
28. string sql = "select * from etest";
    
29. using(SqlDataReader reader=SqlHelper.ExecuteDataReader(sql,System.Data.CommandType.Text))
    
30. {
    
31. if(reader.HasRows)
    
32. {
    
33. using(Workbook wk=new HSSFWorkbook ())
    
34. {
    
35. 
    
36. using (FileStream fs=new FileStream("my.xls",FileMode.OpenOrCreate))
    
37. {
    
38. int rowindex = 0;
    
39. using(Sheet sheet=wk.CreateSheet("表1")){
    
40. while(reader.Read()){
    
41. Row row = sheet.CreateRow(rowindex);
    
42. for (int index = 0; index < reader.FieldCount; index++)
    
43. {
    
44. row.CreateCell(index).SetCellValue(reader.GetValue(index).ToString());
    
45. }
    
46. //Console.WriteLine("导出成功！");
    
47. rowindex++;
    
48. }
    
49. wk.Write(fs);
    
50. }
    
51. 
    
52. }
    
53. }
    
54. 
    
55. }
    
56. }
    
57. }
    
58. /// <summary>
    
59. /// 从Excel中读取数据导入到数据库
    
60. /// </summary>
    
61. private static void LoadDataToDBFromExcel()
    
62. {
    
63. using(FileStream fs=File.OpenRead("my.xls"))
    
64. {
    
65. using(Workbook wk=new HSSFWorkbook (fs))
    
66. {
    
67. using(Sheet sheet=wk.GetSheetAt(0))
    
68. {
    
69. int r = -1;
    
70. for (int i = 0; i <=sheet.LastRowNum;i++ )
    
71. {
    
72. int id =(int) sheet.GetRow(i).GetCell(1).NumericCellValue;
    
73. string ename = sheet.GetRow(i).GetCell(2).StringCellValue;
    
74. 
    
75. string sql = "insert into etest values(@eid,@ename)";
    
76. SqlParameter[] pms =
    
77. {
    
78. new SqlParameter ("@eid",id),
    
79. new SqlParameter ("@ename",ename)
    
80. };
    
81. r= SqlHelper.ExecuteNonQuery(sql,System.Data.CommandType.Text,pms);
    
82. if (r > 0)
    
83. {
    
84. Console.WriteLine("导入成功!id:{0}",id);
    
85. }
    
86. }
    
87. 
    
88. }
    
89. }
    
90. }
    
91. }
    
92. }
    
93. }
    

复制代码