将数据导入到已存在的excel文件中

简介:

 

 
  1. CRUD数据访问类基类  
  2.  
  3. using System;  
  4. using System.Collections;  
  5. using System.Collections.Generic;  
  6. using System.Text;  
  7. using System.Data;  
  8. using System.Data.OleDb;  
  9.  
  10.  
  11. namespace myexcel{  
  12.     public class DbExcel  
  13.     {  
  14.           
  15.         /// <summary> 
  16.         /// 获取读取连接字符串  
  17.         /// </summary> 
  18.         /// <param name="phyfilepath"></param> 
  19.         /// <returns></returns> 
  20.         private static string GetOptionConnstr(string phyfilepath)  
  21.         {  
  22.             string endstr = phyfilepath.Substring(phyfilepath.IndexOf('.') + 1);  
  23.             if (endstr.ToLower() == "xlsx")  
  24.             {  
  25.                 return "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=\"Excel 12.0;HDR=no;\";Data Source=" + phyfilepath;  
  26.             }  
  27.  
  28.             return "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=no;\";Data Source=" + phyfilepath;  
  29.         }  
  30.  
  31.  
  32.         /// <summary> 
  33.         /// 获取操作连接字符串  
  34.         /// </summary> 
  35.         /// <param name="phyfilepath"></param> 
  36.         /// <returns></returns> 
  37.         private static string GetReadConnStr(string phyfilepath)  
  38.         {  
  39.             string endstr = phyfilepath.Substring(phyfilepath.IndexOf('.') + 1);  
  40.             if (endstr.ToLower() == "xlsx")  
  41.             {  
  42.                 return "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1\";Data Source="+phyfilepath;   
  43.             }  
  44.      
  45.                 return "Provider=Microsoft.Jet." +  
  46.                 "OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source="+phyfilepath;                  
  47.         }  
  48.  
  49.  
  50.         public static DataTable GetExcelDataTable(string phyfilepath)  
  51.         {  
  52.             OleDbConnection conn = null;  
  53.             string sheetName = "Sheet1";  
  54.             DataTable dataTable = null;  
  55.               
  56.             try  
  57.             {  
  58.                 using (conn = new OleDbConnection(GetReadConnStr(phyfilepath)))  
  59.                 {  
  60.                     conn.Open();  
  61.                     DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  62.  
  63.                         foreach (DataRow dr in sheetNames.Rows)  
  64.                         {  
  65.                             sheetName = dr[2].ToString().Trim();  
  66.                             break;  
  67.                         }  
  68.                     OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);  
  69.                     DataSet ds = new DataSet();  
  70.                     oada.Fill(ds, "InitData");  
  71.  
  72.                     dataTable = ds.Tables["InitData"];  
  73.                 }  
  74.             }  
  75.             catch (Exception ex)  
  76.             {  
  77.                 throw new BaseDBException(ex.Message);  
  78.             }  
  79.             finally  
  80.             {  
  81.                 conn.Close();  
  82.             }  
  83.  
  84.             return dataTable;  
  85.         }  
  86.  
  87.         public static DataTable GetExcelSheetTable(string phyfilepath)  
  88.         {  
  89.             OleDbConnection conn = null;  
  90.             string sheetName = "Sheet1$";  
  91.             DataTable dataTable = null;  
  92.  
  93.             try  
  94.             {  
  95.                 using (conn = new OleDbConnection(GetReadConnStr(phyfilepath)))  
  96.                 {  
  97.                     conn.Open();  
  98.                     OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);  
  99.                     DataSet ds = new DataSet();  
  100.                     oada.Fill(ds, "InitData");  
  101.  
  102.                     dataTable = ds.Tables["InitData"];  
  103.                 }  
  104.             }  
  105.             catch (Exception ex)  
  106.             {  
  107.                 throw new BaseDBException(ex.Message);  
  108.                 //return null;  
  109.                 
  110.             }  
  111.             finally  
  112.             {  
  113.                 conn.Close();  
  114.             }  
  115.  
  116.             return dataTable;  
  117.         }  
  118.  
  119.         public static DataTable GetExcelSheetTable(string phyfilepath,string SheetName)  
  120.         {  
  121.             OleDbConnection conn = null;  
  122.             string sheetName = SheetName;  
  123.             DataTable dataTable = null;  
  124.  
  125.             try  
  126.             {  
  127.                 using (conn = new OleDbConnection(GetReadConnStr(phyfilepath)))  
  128.                 {  
  129.                     conn.Open();  
  130.                     DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  131.  
  132.  
  133.                     OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);  
  134.                     DataSet ds = new DataSet();  
  135.                     oada.Fill(ds, "InitData");  
  136.  
  137.                     dataTable = ds.Tables["InitData"];  
  138.                 }  
  139.             }  
  140.             catch (Exception ex)  
  141.             {  
  142.                 throw new BaseDBException(ex.Message);  
  143.             }  
  144.             finally  
  145.             {  
  146.                 conn.Close();  
  147.             }  
  148.  
  149.             return dataTable;  
  150.         }  
  151.           
  152.         public static void ExcelColDataUpdate(string phyfilepath, string sheetName,string setvalue,string where)  
  153.         {  
  154.             OleDbConnection conn = null;  
  155.             try  
  156.             {  
  157.                 using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath)))  
  158.                 {  
  159.                     conn.Open();  
  160.                     OleDbCommand cmd = new OleDbCommand();  
  161.                     cmd.CommandType = CommandType.Text;  
  162.  
  163.                     cmd.CommandText = "UPDATE ["+sheetName+"$] "+setvalue+" "+where;  
  164.                     cmd.Connection = conn;  
  165.                     cmd.ExecuteNonQuery();  
  166.  
  167.                 }  
  168.             }  
  169.             catch (Exception ex)  
  170.             {  
  171.                 throw new BaseDBException(ex.Message);  
  172.             }  
  173.             finally  
  174.             {  
  175.                 conn.Close();  
  176.             }  
  177.           
  178.           
  179.         }  
  180.  
  181.         public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnNames, string values)  
  182.         {  
  183.             OleDbConnection conn = null;  
  184.             try  
  185.             {  
  186.                 using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath)))  
  187.                 {  
  188.                     conn.Open();  
  189.                     OleDbCommand cmd = new OleDbCommand();  
  190.                     cmd.CommandType = CommandType.Text;  
  191.  
  192.                     cmd.CommandText = "insert into [" + sheetName + "$] (" + columnNames + ") values(" + values + ")";  
  193.                     cmd.Connection = conn;  
  194.                     cmd.ExecuteNonQuery();  
  195.                       
  196.                 }  
  197.             }  
  198.             catch (Exception ex)  
  199.             {  
  200.                 throw new BaseDBException(ex.Message);  
  201.             }  
  202.             finally  
  203.             {  
  204.                 conn.Close();  
  205.             }  
  206.           
  207.         }  
  208.  
  209.         public static void ExcelVoucherDataInsert(string filePath, string sheetName, DataTable dt)  
  210.         {  
  211.             StringBuilder sb = new StringBuilder();  
  212.             if (dt == null || dt.Rows.Count == 0) return;  
  213.             try  
  214.             {  
  215.                 using (OleDbConnection conn = new OleDbConnection(GetOptionConnstr(filePath)))  
  216.                 {  
  217.                     conn.Open();  
  218.                     foreach (DataRow row in dt.Rows)  
  219.                     {  
  220.                         OleDbCommand cmd = new OleDbCommand();  
  221.                         cmd.CommandType = CommandType.Text;  
  222.                         cmd.CommandText = "insert into [" + sheetName + "$] values('" + row["FName"].ToString() + "','" + row["FNo"].ToString() + "','" + row["FIName"].ToString() + "')";  
  223.                         cmd.Connection = conn;  
  224.                         cmd.ExecuteNonQuery();  
  225.                     }  
  226.                 }  
  227.             }  
  228.             catch (Exception ex)  
  229.             {  
  230.                 throw new BaseDBException(ex.Message);  
  231.             }  
  232.         }  
  233.  
  234.         public static void  ExcelVoucherDataInsert(string filePath, string sheetName, string itemClass , string number , string name)  
  235.         {  
  236.             try  
  237.             {  
  238.                 using (OleDbConnection conn = new OleDbConnection(GetOptionConnstr(filePath)))  
  239.                 {  
  240.                     conn.Open();  
  241.                     OleDbCommand cmd = new OleDbCommand();  
  242.                     cmd.CommandType = CommandType.Text;  
  243.  
  244.                     cmd.CommandText = "insert into [" + sheetName + "$] values('" + itemClass + "','" + number + "','" + name + "')";  
  245.                     cmd.Connection = conn;  
  246.                     cmd.ExecuteNonQuery();  
  247.                 }  
  248.             }  
  249.             catch(Exception ex)  
  250.             {  
  251.                 throw new BaseDBException(ex.Message);  
  252.             }  
  253.         }  
  254.           
  255.  
  256.         public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnName, string[] values)  
  257.         {  
  258.             OleDbConnection conn = null;  
  259.             try  
  260.             {  
  261.                 using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath)))  
  262.                 {  
  263.                     conn.Open();  
  264.                     OleDbCommand cmd = new OleDbCommand();  
  265.                     cmd.CommandType = CommandType.Text;  
  266.  
  267.                     foreach (string str in values)  
  268.                     {  
  269.                         cmd.CommandText = "insert into [" + sheetName + "$] (" + columnName + ") values(' " + str + "')";  
  270.                         cmd.Connection = conn;  
  271.                         cmd.ExecuteNonQuery();  
  272.                     }  
  273.                 }  
  274.             }  
  275.             catch (Exception ex)  
  276.             {  
  277.                 throw new BaseDBException(ex.Message);  
  278.             }  
  279.             finally  
  280.             {  
  281.                 conn.Close();  
  282.             }  
  283.         }  
  284.       
  285.     }  
  286.  

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

 
  1. 代码  
  2.  
  3. protected void DoOleSql(string sql, string database)  
  4.  
  5.   {     
  6.  
  7.   OleDbConnection conn = new OleDbConnection();  
  8.  
  9.   conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("\\") + database + "; Extended Properties='Excel 8.0;HDR=no;IMEX=0'";  
  10.  
  11.   try  
  12.  
  13.   {//打开连接  
  14.  
  15.   conn.Open();  
  16.  
  17.   }  
  18.  
  19.   catch (Exception e)  
  20.  
  21.   {  
  22.  
  23.   Response.Write(e.ToString());  
  24.  
  25.   }  
  26.  
  27.   OleDbCommand olecommand = new OleDbCommand(sql, conn);     
  28.  
  29.   try  
  30.  
  31.   {//执行语句  
  32.  
  33.   olecommand.ExecuteNonQuery();  
  34.  
  35.   }  
  36.  
  37.   catch (Exception eee)  
  38.  
  39.   {  
  40.  
  41.   Response.Write(eee.ToString());  
  42.  
  43.   conn.Close();  
  44.  
  45.   }  
  46.  
  47.   finally  
  48.  
  49.   {  
  50.  
  51.   conn.Close();//关闭数据库  
  52.  
  53.   }  
  54.  
  55.   conn.Close();  
  56.  

注: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文件引用的“范围”有错误。



本文转自linzheng 51CTO博客,原文链接:http://blog.51cto.com/linzheng/1080854

相关文章
|
1天前
|
Java Maven
Java 怎样从 excel 中读取文件、写入文件
Java 怎样从 excel 中读取文件、写入文件
7 0
|
6天前
|
Java 测试技术 Apache
《手把手教你》系列技巧篇(六十八)-java+ selenium自动化测试 - 读写excel文件 - 下篇(详细教程)
【6月更文挑战第9天】本文介绍了如何使用Java处理Excel文件中的不同数据类型,包括日期、数字、布尔值和标签(常规字符串)。文章提供了两个示例,分别使用JXL库和Apache POI库来读取Excel文件。
9 1
|
6天前
|
XML Java 测试技术
《手把手教你》系列技巧篇(六十七)-java+ selenium自动化测试 - 读写excel文件 - 中篇(详细教程)
【6月更文挑战第8天】本文介绍了Java中操作Excel的工具,包括POI和JXL。POI支持处理Office 2003及以下的OLE2格式(.xls)和2007以上的OOXML格式(.xlsx)。而JXL只能处理2003版本的Excel文件。文章详细讲解了如何下载和使用JXL库,并给出了一个简单的Java代码示例,展示如何读取2003版Excel文件中的数据。在实际项目中,由于JXL对新版本Excel的支持限制,通常推荐使用POI。
22 5
|
7天前
|
C#
【C#】C#读写Excel文件
【C#】C#读写Excel文件
9 1
|
8天前
|
Java 测试技术 Apache
《手把手教你》系列技巧篇(六十六)-java+ selenium自动化测试 - 读写excel文件 - 上篇(详细教程)
【6月更文挑战第7天】本文介绍了在Java自动化测试中如何操作Excel数据。文章提到了当测试数据存储在Excel文件时,可以使用Apache的POI库来读写Excel。POI提供了对OLE2(.xls)和OOXML(.xlsx)格式的支持,比JXL库功能更全面。文章还详细讲解了如何下载和添加POI库到项目中,以及准备测试用的Excel文件。最后,给出了一个简单的Java代码示例,演示如何读取Excel文件的内容。
13 1
|
9天前
|
前端开发
react框架对Excel文件进行上传和导出
react框架对Excel文件进行上传和导出
|
11天前
|
前端开发
React实现一个excel文件导出
React实现一个excel文件导出
14 0
|
26天前
|
前端开发 Java
基于Java爬取微博数据(二) 正文长文本+导出数据Excel
【5月更文挑战第12天】基于Java爬取微博数据,正文长文本+导出数据Excel
|
1月前
|
Java
java导出复杂excel
java导出复杂excel
分享:2秒快速查询40万手机号码归属地,批量手机号码归属地查询可以导出excel表格,WPS表格查询手机号码归属地怎么操作,批量手机号码归属地批量查询软件,批量号码查询按省份和城市分类,按运移动号码电信号码联通号码分类整理
本文介绍了如何批量快速查询手机号码归属地并进行分类。首先,通过提供的百度网盘或腾讯云盘链接下载免费查询软件。其次,开启软件,启用复制粘贴功能,直接粘贴号码列表并选择高速查询。软件能在极短时间内(如1.76秒内)完成40多万个号码的查询,结果包括归属地、运营商、邮箱和区号,且数据准确。之后,可直接导出数据至表格,若数据超过100万,可按省份、城市及运营商分类导出。文章还附带了操作动画演示,展示全程流畅的处理大量手机号码归属地查询的过程。
分享:2秒快速查询40万手机号码归属地,批量手机号码归属地查询可以导出excel表格,WPS表格查询手机号码归属地怎么操作,批量手机号码归属地批量查询软件,批量号码查询按省份和城市分类,按运移动号码电信号码联通号码分类整理