黑马程序员技术交流社区

标题: 一个关于ADO.NET的问题 [打印本页]
作者: zerochen46    时间: 2013-4-11 21:41
标题: 一个关于ADO.NET的问题
本帖最后由 zerochen46 于 2013-4-12 18:48 编辑

能否用ADO.NET的技术访问EXCEL中的数据
作者: 陈志盟    时间: 2013-4-11 22:01
  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]
作者: 邓建军    时间: 2013-4-12 17:01
一、在程序中，用 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文档数据。
作者: 曾玉锋    时间: 2013-4-12 18:15
现在我们一般使用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. {

  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. }

  52. }
  53. }

  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;

  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. }

  88. }
  89. }
  90. }
  91. }
  92. }
  93. }
复制代码




欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) 黑马程序员IT技术论坛 X3.2