C#_简单Excel导入

引用程序集

Microsoft.Office.Core

Microsoft.Office.Interop.Excel

 

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data.SqlClient;
  4 using Excel = Microsoft.Office.Interop.Excel;
  5 
  6 private void btnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
  7 {
  8         System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();  //打开文件对话框
  9          fd.Filter = @"Excel文件 (*.xls; *.xlsx)|*.xls; *.xlsx|
 10                           All Files (*.*)|*.*";                            //过滤exl文件
 11 
 12             if (fd.ShowDialog() == DialogResult.OK)
 13             {
 14                 string fileName = fd.FileName;//文件名
 15                 try
 16                 {
 18                     LoadExcelData(fileName);
 19                    // LoadData();
 20                 }
 21                 catch (Exception ex)
 22                 {
 23                     throw;
 24                 }
 25                 finally
 26                 {
 27                    
 28                 }
 29                 
 30             }
 31 }
 32 
 33 private void LoadExcelData(string fileName)
 34         {
 35             
 36             Excel.Application excel = new Excel.ApplicationClass();
 37             try
 38             {
 39                 if (excel == null)
 40                 {
 41                     MsgDialog.Show("无法打开Excle文件！", this);
 42                 }
 43                 else
 44                 {
 45                     excel.Visible = false;
 46                     excel.UserControl = true;
 47                     Excel.Workbook wb = excel.Application.Workbooks.Open(fileName, missing, missing, missing, missing,
 48                         missing,
 49                         missing, missing, missing, true, missing, missing, missing, missing, missing);
 50                     Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);

 51                     int columnsint = ws.UsedRange.Cells.Columns.Count; //得到列数
 52                     int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数   
 53 
 54                     StringBuilder sqllist = new StringBuilder();
 55                     string sqltext = "";
 56 
 57                     string column1, column2, column3;
 58                     bool repeat = false;
 59                     
 60                     for (int i = 2; i <= rowsint; i++)
 61                     {
 62 
 63                         //this.barButtonItem5.Caption = "正在扫描第" + i + "条记录，共" + rowsint + "条";
 64                        
 65                         sqltext = "";
 67                         if (((Excel.Range)ws.Cells[i, 1]).Value2 == null)
 68                         {
 69                             column1 = "";
 70                         }
 71                         else
 72                         {
 73                             column1 = ((Excel.Range)ws.Cells[i, 1]).Value2.ToString();
 74                         }
 75 
 76                         if (((Excel.Range)ws.Cells[i, 2]).Value2 == null)
 77                         {
 78                             column2= "";
 79                         }
 80                         else
 81                         {
 82                             column2= ((Excel.Range)ws.Cells[i, 2]).Value2.ToString();
 83                         }
 84 
 85                         if (((Excel.Range)ws.Cells[i, 3]).Value2 == null)
 86                         {
 87                             column3= "";
 88                         }
 89                         else
 90                         {
 91                             column3= ((Excel.Range)ws.Cells[i, 3]).Value2.ToString();
 92                         }
 93                        
 94                         string chRepSql = @"select * from table where column1 ='" + column1 + "' and column2 ='" + column2 + "' and column3 ='" 
　　　　　　　　　　　　　　　　　　　　　　　　　　　+ column3 + "';
 95                         int countR = DbHelperSQL.Query(chRepSql).Tables[0].Rows.Count;
 96                         if (countR > 0)
 97                         {
 98                             repeat = true;
 99                         }
100                         if (repeat)
101                         {
102                             sqltext =
103                                 " update  table  set  column2 ='" + column2 + "' and column3 ='" + column3 + "'  where column1 ='" + column1 
104                                 + "\r\n";
105                         }
106                         else
107                         {
108                             sqltext =
109                                 " insert into table(column1, column2, column3)" +
110                                 "VALUES('" + column1 + "','" + column2+ "','" + column3 + "')" + "\r\n";
111                         }
112                         sqllist.Append(sqltext);
113                     }
114 
115                     if (sqllist.ToString() == "")
116                         return;
117                     string msg;
118                     SqlTransaction trans = null;                    
119                     try
120                     {
121                         trans = _connection.BeginTransaction();
122                         SqlCommand command = new SqlCommand();
123                         command.Connection = _connection;
124                         command.Transaction = trans;
125                         command.CommandText = sqllist.ToString();
126 
127                         command.ExecuteNonQuery();
128                         trans.Commit();
129                     }
130                     catch (Exception ex)
131                     {
132                         trans.Rollback();
133                         msg = "保存过程中出错！错误信息如下：\r\n " + ex.ToString();
134                         XtraMessageBox.Show(msg);
136                     }
137                     msg = "导入成功！";
138                     MsgDialog.Show(msg, this);
139                 }
140             }
141             catch (Exception ex)
142             {
143               string  msg = "保存过程中出错！错误信息如下：\r\n " + ex.ToString();
144                XtraMessageBox.Show(msg);
145             }
146             finally
147             {
148                 excel.Quit();
149                 excel = null;
150               
151             }
152         }

总结：把exl导入数据库时,把SQL语句组成的字符串一次导入，只连接一次数据库，避免反复连接数据库，速度更快。

posted @ 2016-06-03 15:08  ETLeung  阅读(2597)  评论(0)  编辑  收藏  举报