C#连接Excel读取与写入数据库SQL （ 下 ）
接上期
dataset简而言之可以理解为 虚拟的 数据库或是Excel文件。而dataset里的datatable 可以理解为数据库中的table活着Excel里的sheet（Excel里面不是可以新建很多表吗）。
这样说应该很容易懂了，相当于dataset只是暂时存放下数据，微软官方解释是存在内存中。至于为啥要找个“中介”来存数据，这个估计是为了和SQL匹配。
好了，接下来说下这次的重点。
在把Excel的数据存到dataset后，我们要把dataset的数据存入SQL才算完事。
废话不多说先上后面的代码：（总的代码）
using System.IO; using System.Data; using System.Configuration; using System.ServiceProcess; using System.Data.SqlClient; using System.Data.OleDb; using System.Timers; using Log4AES; using System; namespace DataCollection_model_HD { public partial class Service1 : ServiceBase { public Service1() { InitializeComponent(); InitTimer(); } #region 各种配置的全局定义 //定义一个dataset 用于暂时存放excel中的数据,后续要存入datatable DataSet ds = new DataSet(); Timer TimModel = new Timer(); public static string LogPath = ConfigurationManager.AppSettings["LogPath"].ToString(); public static string WPath = ConfigurationManager.AppSettings["WorkingPath"].ToString(); public static string APath = ConfigurationManager.AppSettings["ArchivePath"].ToString(); //数据库登录 //注意Integrated Security不写（false）表示必须要用pwd登录，true表示不用密码也能进入数据库 public static string ConnStr = ConfigurationManager.AppSettings["ConnStr"].ToString(); //用于记录log的时候，机台名字 public static string machineName = "test"; #endregion #region 定时器的初始化，及其事务 //这个按钮用于模拟服务(定时器）启动 public void InitTimer() { //DFL的定时器 TimModel.Interval = 15 * 1000; //定时器的事务 TimModel.Elapsed += new ElapsedEventHandler(ElapsedEventDFL); TimModel.Enabled = true; TimModel.AutoReset = true; } private void ElapsedEventDFL(object source, ElapsedEventArgs e) { if (GetFiles("test")) { //多次读取数据，存在多个文件时但其中某个文件在使用的bug ds.Tables.Clear(); Log4App.WriteLine(" ---- End the collect ! ----", LogPath, machineName, System.Threading.Thread.CurrentThread.ManagedThreadId.ToString(), Log4AES.Type.Information); } else { DataToSql("test"); BackupData("test"); Log4App.WriteLine(" ---- End the collect ! ----", LogPath, machineName, System.Threading.Thread.CurrentThread.ManagedThreadId.ToString(), Log4AES.Type.Information); } } #endregion //log初始化设置 Log4Application Log4App = new Log4Application(); /*用于移动源文件到指定文件夹，也就是备份源数据文件 copy all file in folder Working to Achieve*/ public void BackupData(string equipName) { //需要存放（备份）的文件夹路径（Achieve） string ArchivePath = APath + equipName + " Equipment Temp. monitoring by third tool\\Archive"; //读取数据源文件的文件夹路径（Working） string WorkingPath = WPath + equipName + " Equipment Temp. monitoring by third tool\\Working"; //初始化system.IO的配置（路径) DirectoryInfo directoryInfo = new DirectoryInfo(WorkingPath); //用文件流来获取文件夹中所有文件，存放到 FileInfo[] files = directoryInfo.GetFiles(); //循环的把所有机台数据备份到Achieve文件夹 try { foreach (FileInfo file in files) // Directory.GetFiles(srcFolder) { //使用IO中的Moveto函数进行移动文件操作 file.MoveTo(Path.Combine(ArchivePath, file.Name)); } } catch (Exception ex) { } } //判断Excel是否在被人使用 public bool IsUsed(String fileName) { bool result = false; try { FileStream fs = File.OpenWrite(fileName); fs.Close(); } catch { result = true; } return result; } //将xls文件投入datatable ， 返回一个datatable为 ds.table[0] public bool GetFiles(string equipName) { bool flag = false; //choose all sheet? or all data in sheet? string strExcel = "select * from [Sheet1$]"; //初始化system.IO的配置（路径) DirectoryInfo directoryInfo1 = new DirectoryInfo(WPath + equipName + " Equipment Temp. monitoring by third tool\\Working"); //用文件流来获取文件夹中所有文件，存放到 FileInfo[] files1 = directoryInfo1.GetFiles(); foreach (FileInfo file in files1) // Directory.GetFiles(srcFolder) { // 连接到excel 数据源, xlsx要用ACE string strConn = ("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source= " + file.FullName + "; Extended Properties='Excel 12.0';"); OleDbConnection OledbConn = new OleDbConnection(strConn); if (IsUsed(file.FullName)) { flag = IsUsed(file.FullName); continue; } try { OledbConn.Open(); // 存入datatable，Excel表示哪一个sheet，conn表示连接哪一个Excel文件（jet、ACE） OleDbDataAdapter dAdapter = new OleDbDataAdapter(strExcel, strConn); dAdapter.Fill(ds); OledbConn.Dispose(); OledbConn.Close(); } catch (Exception ex) { } } return flag; } // 将datatable中的数据存入SQL server public void DataToSql(string equipName) { //初始化配置 sqlserver的服务器名用户等 SqlConnection Conn = new SqlConnection(ConnStr); Conn.Open(); //配置SQLBulkCopy方法，真正用于复制数据到数据库的方法 SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnStr, SqlBulkCopyOptions.UseInternalTransaction) { DestinationTableName = "ModelTest_HD" }; try { foreach (DataColumn item in ds.Tables[0].Columns) { //只复制所选的相关列 bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName); } //开始复制到sql,每次在数据库中添加 bulkCopy.WriteToServer(ds.Tables[0]); bulkCopy.Close(); //copy完了，要清空ds的内容，不然会引起循环写入上一个内容 ds.Tables.Clear(); } catch (Exception ex) { } finally { //关闭数据库通道 Conn.Close(); } } protected override void OnStart(string[] args) { //启动服务时做的事情 } protected override void OnStop() { //停止服务时做的事情 } } }
认真看注释可以看出本程序的逻辑就是：
1、读取到Excel数据
2、存Excel数据到SQL server
3、备份Excel文件到另一个文件夹
其中一些功能大家可以看一看，注释也写的很清楚。对于初学者 configurationmanager的内容是在 app.config中设置的，这里直接去配置就行（类似html）
不 懂可以评论问楼主。
接下来就是重要的SQLBulkCopy了：
foreach (DataColumn item in ds.Tables[0].Columns)
{
//只复制所选的相关列
bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
注意这一段代码，表示只复制数据库与Excel表中 “列名”一致的数据，如果不一致就不复制。（注意数据的格式，int还char 这些必须弄清楚）
然后bulkCopy.WriteToServer(ds.Tables[0])这里，就是把ds.tables的数据复制到SQLserver ，Tables[0]表示ds第一张表（其实我们也只有一张表，至于怎么在dataset中新建table自己可以查查资料）
最后的最后，注意释放这些dataset，或者table。然后通道也记得close一下。
祝大家学习快乐。
版权声明：本文为tay1987原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。