Excel导入导出数据库01

  1 引入
  2 using System.Data.OleDb;
  3 using System.IO;
  4 using System.Collections;
  5 
  6     /// <summary>
  7     /// Excel导入导出
  8     /// </summary>
  9     public class ExcelToData
 10     {
 11         /// <summary>
 12         /// 无参构造函数
 13         /// </summary>
 14         public ExcelToData()
 15         {
 16             //
 17         }
 18         /// <summary>
 19         /// 带参构造函数,初始化连接Excel字符串
 20         /// </summary>
 21         /// <param name="fileName">文件绝对路径</param>
 22         public ExcelToData(string fileName)
 23         {
 24             this.connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
 25                   "Data Source=" + fileName + ";" +
 26                   "Extended Properties='Excel 8.0;HDR=No;IMEX=1'";//HDR第一行是否为列名；IMEX=1互混数据列作为文本数据读取。
 27         }
 28 
 29         private string connStr;
 30         /// <summary>
 31         /// 连接Excel字符串
 32         /// </summary>
 33         public string ConnStr
 34         {
 35             get { return this.connStr; }
 36             set { this.connStr = value; }
 37         }
 38         private List<string> sheets;
 39         /// <summary>
 40         /// 得到Excel文件所有Sheets(工作表),使用前请先调用FillSheets（）方法
 41         /// </summary>
 42         public List<string> Sheets
 43         {
 44             //set {this.sheets =value ;}
 45             get { return this.sheets; }
 46         }
 47 
 48         /// <summary>
 49         /// 得到Excel文件所有Sheets(工作表)
 50         /// </summary>
 51         /// <returns>void</returns>
 52         public List<string> FillSheets()
 53         {
 54             sheets = new List<string>();
 55             OleDbConnection conn = new OleDbConnection(connStr);
 56             conn.Open();
 57             DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
 58             conn.Close();
 59             if (schemaTable.Rows.Count > 0)
 60             {
 61                 foreach (DataRow dr in schemaTable.Rows)
 62                 {
 63                     if (dr[2].ToString().Substring(0, 1) == "'")
 64                     {
 65                         sheets.Add(dr[2].ToString().Substring(1, dr[2].ToString().Length - 2));
 66                     }
 67                     else
 68                     {
 69                         sheets.Add(dr[2].ToString());
 70                     }
 71                 }
 72             }
 73             return sheets;
 74 
 75         }
 76         /// <summary>
 77         /// 根据指定Excel工作表名的指定单元区域读取数据
 78         /// </summary>
 79         /// <param name="sheetName">工作表名</param>
 80         /// <param name="region">单元区域</param>
 81         /// <returns>DataTable</returns>
 82         public DataTable GetExcelData(string sheetName, string region)
 83         {
 84             string sqlStr = "select * from [" + sheetName + region + "]";
 85             OleDbDataAdapter ExcelDA = new OleDbDataAdapter(sqlStr, connStr);
 86             DataSet ds = new DataSet();
 87             try
 88             {
 89                 ExcelDA.Fill(ds);
 90             }
 91             catch (OleDbException ex)
 92             {
 93                 throw ex;
 94             }
 95             return ds.Tables[0];
 96         }
 97         /// <summary>
 98         /// 根据指定Excel工作表名读取数据
 99         /// </summary>
100         /// <param name="sheetName">工作表名</param>
101         /// <returns></returns>
102         public DataTable GetExcelData(string sheetName)
103         {
104             string sqlStr = "select * from [" + sheetName + "]";
105             OleDbDataAdapter ExcelDA = new OleDbDataAdapter(sqlStr, connStr);
106             DataSet ds = new DataSet();
107             try
108             {
109                 ExcelDA.Fill(ds);
110             }
111             catch (OleDbException ex)
112             {
113                 throw ex;
114             }
115             return ds.Tables[0];
116         }
117         /// <summary>
118         /// 读取Excel文件所有的sheet工作表中数据到DataSet
119         /// </summary>
120         /// <returns></returns>
121         public DataSet GetAllExcelData()
122         {
123             if (sheets == null)
124             {
125                 FillSheets();
126             }
127             string sql = "";
128             DataSet ds = new DataSet();
129             for (int i = 0; i < sheets.Count; i++)
130             {
131                 sql = "select * from [" + sheets[i].ToString() + "]";
132                 OleDbDataAdapter ExcelDA = new OleDbDataAdapter(sql, connStr);
133                 ExcelDA.Fill(ds, sheets[i].ToString());
134             }
135             return ds;
136 
137         }
138 
139         /// <summary>
140         /// 往Excel文件中批插入数据行,插入时确保该Excel文件存在，且工作表（sheetName)存在。
141         /// </summary>
142         /// <param name="Htable">键表示插入的列,注意必须用(F1，F2，..)；值表示插入列值，注意与参数dt中的列要一一对应</param>
143         /// <param name="dt">要向Excel中插入的datatable</param>
144         /// <param name="sheetName">工作表名</param>
145         ///   /// <param name="Flag">true,参数Htable键值为参数dt列的索引号;false,为dt列名</param>
146         /// <param name="BeginRow">Excel插入开始行,如(A1:C1);无表示从工作表（sheetName)末尾行开始插入</param>
147         /// <returns>void</returns>
148         public void InsertExcelData(Hashtable Htable, DataTable dt, string sheetName, bool Flag, params string[] BeginRow)
149         {
150             if (Htable.Count > 0)
151             {
152                 if (dt.Rows.Count > 0)
153                 {
154                     string connStr1 = this.connStr.Replace(";IMEX=1", "");
155                     OleDbConnection conn = new OleDbConnection(connStr1);
156                     conn.Open();
157                     OleDbCommand comm = conn.CreateCommand();
158                     StringBuilder sb1 = new StringBuilder(256);
159                     StringBuilder sb2 = null;
160                     foreach (DictionaryEntry de in Htable)
161                     {
162                         sb1.Append(de.Key.ToString() + ",");
163 
164                     }
165                     sb1.Remove(sb1.Length - 1, 1);
166 
167                     int colStartID = 0;
168 
169                     string[] splitStr = null;
170                     if (BeginRow.Length > 0)
171                     {
172                         splitStr = BeginRow[0].ToString().Split(':');
173                         if (Char.IsNumber(splitStr[1].ToString(), 1))
174                         {
175                             colStartID = int.Parse(splitStr[1].ToString().Substring(1));
176                         }
177                         else
178                         {
179                             colStartID = int.Parse(splitStr[1].ToString().Substring(2));
180                             splitStr[1] = splitStr[1].Substring(0, 2);
181                         }
182 
183                     }
184 
185                     for (int i = 0; i < dt.Rows.Count; i++)
186                     {
187                         sb2 = new StringBuilder(256);
188                         foreach (DictionaryEntry de in Htable)
189                         {
190                             if (Flag)
191                             {
192                                 sb2.Append("'" + dt.Rows[i][int.Parse(de.Value.ToString())].ToString() + "',");
193                             }
194                             else
195                             {
196                                 sb2.Append("'" + dt.Rows[i][de.Value.ToString()].ToString() + "',");
197                             }
198 
199                         }
200                         if (BeginRow.Length > 0)
201                         {
202 
203 
204                             comm.CommandText = "insert into [" + sheetName + splitStr[0].Substring(0, 1) + colStartID.ToString() + ":" + splitStr[1].Substring(0, 1) + colStartID.ToString() + "] (" + sb1.ToString() + ") values(" + sb2.Remove(sb2.Length - 1, 1).ToString() + ")";
205 
206                             colStartID++;
207 
208                         }
209                         else
210                         {
211                             comm.CommandText = "insert into [" + sheetName + "] (" + sb1.ToString() + ") values(" + sb2.Remove(sb2.Length - 1, 1).ToString() + ")";
212                         }
213                         comm.ExecuteNonQuery();
214                     }
215                     conn.Close();
216 
217                 }
218             }
219 
220         }
221         /// <summary>
222         /// 插入一条数据
223         /// </summary>
224         /// <param name="Htable">键表示插入的列,注意必须用(F1，F2，..)；值表示插入列值</param>
225         /// <param name="sheetName">工作表名</param>
226         /// <param name="BeginRow">Excel插入开始行,如(A1,C1);无表示从工作表（sheetName)末尾行开始插入</param>
227         public void InsertExcelData(Hashtable Htable, string sheetName, params string[] BeginRow)
228         {
229 
230             if (Htable.Count > 0)
231             {
232                 string connStr1 = this.connStr.Replace(";IMEX=1", "");
233                 OleDbConnection conn = new OleDbConnection(connStr1);
234 
235                 StringBuilder sb1 = new StringBuilder(256);
236                 StringBuilder sb2 = new StringBuilder(256);
237                 foreach (DictionaryEntry de in Htable)
238                 {
239                     sb1.Append(de.Key.ToString() + ",");
240                     sb2.Append("'" + de.Value.ToString() + "',");
241 
242                 }
243 
244                 conn.Open();
245                 OleDbCommand comm = conn.CreateCommand();
246                 if (BeginRow.Length > 0)
247                 {
248                     comm.CommandText = "insert into [" + sheetName + BeginRow[0].ToString() + "] (" + sb1.Remove(sb1.Length - 1, 1).ToString() + ") values(" + sb2.Remove(sb2.Length - 1, 1).ToString() + ")";
249 
250                 }
251                 else
252                 {
253                     comm.CommandText = "insert into [" + sheetName + "] (" + sb1.Remove(sb1.Length - 1, 1).ToString() + ") values(" + sb2.Remove(sb2.Length - 1, 1).ToString() + ")";
254                 }
255                 comm.ExecuteNonQuery();
256                 conn.Close();
257             }
258         }
259 
260         /// <summary>
261         /// 更新一行数据
262         /// </summary>
263         /// <param name="sheetName">工作表名</param>
264         /// <param name="Htable">要更新的字段，键表示列名，键值表示要更新列值</param>
265         /// <param name="whereStr">更新条件如（F1=?);无，null</param>
266         /// <param name="updateRow">sheetName中要更新的行如（A1:C1)</param>
267         public void UpdateExcelData(string sheetName, Hashtable Htable, string whereStr, params string[] updateRow)
268         {
269             if (Htable.Count > 0)
270             {
271                 string connStr1 = this.connStr.Replace(";IMEX=1", "");
272                 OleDbConnection conn = new OleDbConnection(connStr1);
273                 conn.Open();
274                 OleDbCommand comm = conn.CreateCommand();
275 
276                 StringBuilder sb1 = new StringBuilder(256);
277                 sb1.Append("set ");
278                 foreach (DictionaryEntry de in Htable)
279                 {
280                     sb1.Append(de.Key.ToString() + "='" + de.Value.ToString() + "',");
281 
282                 }
283 
284                 if (whereStr != null)
285                 {
286                     if (updateRow.Length > 0)
287                     {
288                         comm.CommandText = "UPDATE [" + sheetName + updateRow[0].ToString() + "]  " + sb1.Remove(sb1.Length - 1, 1).ToString() + " where " + whereStr;
289                     }
290                     else
291                     {
292                         comm.CommandText = "UPDATE [" + sheetName + "]  " + sb1.Remove(sb1.Length - 1, 1).ToString() + " where " + whereStr;
293 
294                     }
295                 }
296                 else
297                 {
298                     if (updateRow.Length > 0)
299                     {
300                         comm.CommandText = "UPDATE [" + sheetName + updateRow[0].ToString() + "]  " + sb1.Remove(sb1.Length - 1, 1).ToString();
301                     }
302                     else
303                     {
304                         comm.CommandText = "UPDATE [" + sheetName + "]  " + sb1.Remove(sb1.Length - 1, 1).ToString();
305 
306                     }
307                 }
308                 comm.ExecuteNonQuery();
309                 conn.Close();
310 
311 
312             }
313         }
314 
315     }

 1   protected void btnSubmit_Click(object sender, EventArgs e)
 2         {
 3             if (!fileExcel.HasFile)
 4             {
 5                 ExtAspNet.Alert.ShowInTop("请您选择Excel文件！");
 6                 return;//当无文件时,返回
 7             }
 8             string fileName = fileExcel.FileName;
 9             string IsXls = System.IO.Path.GetExtension(fileName).ToString().ToLower();//获得文件的扩展名
10 
11             if (IsXls != ".xls" && IsXls != ".xlsx")
12             {
13                 ExtAspNet.Alert.ShowInTop("只可以选择Excel文件！");
14                 return;//当选择的不是Excel文件时,返回
15             }
16 
17             ExcelToData readExcel = new ExcelToData(fileName);
18             readExcel.FillSheets();
19             DataTable dtExcel = readExcel.GetExcelData(readExcel.Sheets[0].ToString());
20             DataRow[] dr = dtExcel.Select();            //定义一个DataRow数组
21             int rowsnum = dtExcel.Rows.Count;
22 
23             if (rowsnum < 2)
24             {//第一行为标题，所以从2开始
25                 ExtAspNet.Alert.ShowInTop("Excel表为空表,无数据！");
26                 return;
27             }
28             else
29             {
30                 string User_No = String.Empty;
31                 DateTime ScheduleDate;
32                 string ShiftName = String.Empty;
33                 StringBuilder sb;
34                 string strCheckSQL;
35                 StringBuilder sbInfo = new StringBuilder();
36                 for (int i = 1; i < dr.Length; i++)
37                 {
38                     sb = new StringBuilder();
39                     User_No = dr[i][0].ToString().Trim();
40                     ShiftName = dr[i][2].ToString().Trim();
41 
42                     try
43                     {
44                         ScheduleDate = DateTime.Parse(dr[i][1].ToString().Trim ());
45                         
46                     }
47                     catch (Exception ex)
48                     {
49                         sbInfo.AppendLine("人员【" + User_No + "】日期【" + dr[i][1].ToString() + "】格式不正确!");
50                         continue;
51                     }
52 
53                     try
54                     {
55 
56                         sb.Append("INSERT INTO [dbo].[KQ_Schedule]([EmpID],[ScheduleDate],[ShiftID])");
57                         sb.Append(String.Format(" VALUES ('{0}','{1}','{2}') END;", User_No ,ScheduleDate, ShiftName));
58                         int result=dbacess.ExecuteNonQuery(sql, null);
59                         if (result > 0)
60                         {
61                             sbInfo.AppendLine("人员【" + User_No + "】导入成功!");
62                         }
63                         else {
64                             sbInfo.AppendLine("人员【" + User_No + "】导入失败!");
65                         }
66                     }
67                     catch (Exception ex)       //捕捉异常
68                     {
69                         sbInfo.AppendLine("人员【" + User_No + "】导入出错：" + ex.Message);
70                         continue;
71                     }
72                     
73                 }//for i
74                 txtInfo.Text = sbInfo.ToString();
75             }
76             
77         }