主要分为两部份
1.Excel操作类
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 }
2.操作页面，添加一个button控件用来确定上传，一个FileUpload控件，一个TextArea控件来显示导入产生的信息
后台代码：
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 }
本文转自欢醉博客园博客，原文链接http://www.cnblogs.com/zhangs1986/archive/2013/01/19/2867623.html如需转载请自行联系原作者
欢醉
