.net导入excel 以及常见错误的处理方法
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using Microsoft.Office.Interop; 7 using Microsoft.Office.Interop.Excel; 8 9 using System.Windows.Forms; 10 using wuliu.Models; 11 12 13 14 namespace wl10.DAL 15 { 16 public class ExcelHandle 17 { 18 19 20 21 22 23 public List<PRINTSMODEL> GetStudentsFromExcel(string filePath, string sheetName) 24 { 25 26 List<PRINTSMODEL> printS = new List<PRINTSMODEL>(); 27 Microsoft.Office.Interop.Excel.Application excel = 28 new Microsoft.Office.Interop.Excel.Application(); 29 30 31 excel.Workbooks.Open(filePath);//打开Excel 文件 32 33 Worksheet sheet = null; 34 foreach (Worksheet wsheet in excel.ActiveWorkbook.Sheets) 35 { 36 if (wsheet.Name == sheetName) 37 { 38 sheet = wsheet; 39 break; 40 } 41 } 42 //读取单元格数据 43 if (sheet != null) 44 { 45 int row = 2;//当前行号 46 while (true) 47 { 48 49 Range rNo = sheet.Cells[row, 1] as Range; 50 if (rNo.Text.ToString().Trim().Length == 0) 51 { 52 break; 53 } 54 55 PRINTSMODEL PR = new PRINTSMODEL(); 56 //ID 57 Range rContent = sheet.Cells[row, 1] as Range; 58 //PR.ID = Convert.ToInt32(rContent.Text.ToString().Trim()); 59 //寄件人姓名 60 rContent = sheet.Cells[row, 2] as Range; 61 PR.JNAME = rContent.Text.ToString().Trim(); 62 //寄件人联系电话 63 rContent = sheet.Cells[row, 3] as Range; 64 PR.JMOBILE = rContent.Text.ToString().Trim(); 65 //寄件人地址 66 rContent = sheet.Cells[row, 4] as Range; 67 PR.JADDRES = rContent.Text.ToString().Trim(); 68 //收件人姓名 69 rContent = sheet.Cells[row, 5] as Range; 70 PR.SNAME = rContent.Text.ToString().Trim(); 71 //收件人联系电话 72 rContent = sheet.Cells[row, 6] as Range; 73 PR.SMOBILE = rContent.Text.ToString().Trim(); 74 //收件人地址 75 rContent = sheet.Cells[row, 7] as Range; 76 PR.SADDRES = rContent.Text.ToString().Trim(); 77 //货物名称 78 rContent = sheet.Cells[row, 8] as Range; 79 PR.GOODS = rContent.Text.ToString().Trim(); 80 //数量 81 rContent = sheet.Cells[row, 9] as Range; 82 PR.NUMBER = rContent.Text.ToString().Trim(); 83 84 85 printS.Add(PR); 86 row += 1; 87 } 88 } 89 excel.Visible = true; 90 //关闭WorkBook 91 excel.ActiveWorkbook.Close(); 92 //关闭Excel 93 excel.Quit(); 94 95 return printS; 96 } 97 98 99 100 } 101 }
1 using System; 2 using System.IO; 3 using System.Data; 4 using System.Reflection; 5 using System.Diagnostics; 6 using cfg = System.Configuration; 7 using Microsoft.Office.Interop.Excel; 8 9 10 namespace CPI.WFO.Model 11 { 12 /// <summary> 13 /// 输出Excel 14 /// </summary> 15 public class ExcelHelper 16 { 17 18 19 //从datatable中导出到Excel，打开Excel,但不释放其对象 20 public void DataTableToExcelNotRealse(System.Data.DataTable dt, string outputPath, bool deleteOldFile) 21 { 22 //删除原有文件 23 if (deleteOldFile) 24 { 25 if (System.IO.File.Exists(outputPath)) 26 { 27 FileInfo fi = new FileInfo(outputPath); 28 if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1) 29 fi.Attributes = FileAttributes.Normal; 30 try 31 { 32 System.IO.File.Delete(outputPath); 33 } 34 catch (Exception ex) 35 { 36 throw (ex); 37 } 38 } 39 } 40 41 42 //创建Excel的Application对象 43 ApplicationClass excelApp = new ApplicationClass(); 44 excelApp.Visible = true; 45 // 创建一个新的Excel的Workbook 46 Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing); 47 48 try 49 { 50 int sheetIndex = 0;//sheet索引 51 52 //列头字符集和长度 53 string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; 54 int colCharsetLen = colCharset.Length; 55 56 //给Excel某列的数据数组 57 object[,] rawData = new object[dt.Rows.Count + 1, 1]; 58 59 Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add( 60 excelWorkbook.Sheets.get_Item(++sheetIndex), 61 Type.Missing, 1, XlSheetType.xlWorksheet); 62 if (string.IsNullOrEmpty(dt.TableName)) 63 dt.TableName = "table1"; 64 excelSheet.Name = dt.TableName; 65 66 for (int col = 0; col < dt.Columns.Count; col++) 67 { 68 //将datatable中的某一个列的值以objec数组形式拿出来 69 rawData[0, 0] = dt.Columns[col].ColumnName; 70 //rawData[0, 0] = dt.TableName; 71 for (int row = 0; row < dt.Rows.Count; row++) 72 { 73 rawData[row + 1, 0] = dt.Rows[row].ItemArray[col]; 74 } 75 76 //获取Excel中列头名 77 string finalColLetter = string.Empty; 78 if (col + 1 > colCharsetLen) 79 { 80 finalColLetter = colCharset.Substring( 81 (col) / colCharsetLen - 1, 1); 82 } 83 finalColLetter += colCharset.Substring( 84 (col) % colCharsetLen, 1); 85 //设置列区间 86 string excelRange = string.Format("{0}1:{1}{2}", 87 finalColLetter, finalColLetter, dt.Rows.Count + 1); 88 //获取列区间对象 89 Range range = excelSheet.get_Range(excelRange, Type.Missing); 90 //设置列格式 91 switch (dt.Columns[col].DataType.ToString()) 92 { 93 case "System.String": 94 range.NumberFormatLocal = "@"; 95 break; 96 case "System.DateTime": 97 range.NumberFormatLocal = "yyyy-mm-dd"; 98 break; 99 //可以根据自己的需要扩展。 100 default: 101 range.NumberFormatLocal = "G/通用格式"; 102 break; 103 } 104 //对列赋值 105 range.Value2 = rawData; 106 } 107 //将Excel的第一行的字体设置成粗体 108 ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true; 109 110 excelApp.Application.DisplayAlerts = false; 111 //保存Excel文件 112 //excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, 113 // Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, 114 // Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 115 //excelWorkbook.SaveAs(dt, outputPath, true); 116 } 117 catch (Exception e) 118 { 119 throw e; 120 } 121 } 122 123 //从dataSet中导出到Excel，打开Excel，但不释放其对象 124 public void DataSetToExcelNotRealse(DataSet dataSet, string outputPath, bool deleteOldFile) 125 { 126 //删除原有文件 127 if (deleteOldFile) 128 { 129 if (System.IO.File.Exists(outputPath)) 130 { 131 FileInfo fi = new FileInfo(outputPath); 132 if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1) 133 fi.Attributes = FileAttributes.Normal; 134 try 135 { 136 System.IO.File.Delete(outputPath); 137 } 138 catch (Exception ex) 139 { 140 throw (ex); 141 } 142 } 143 } 144 145 //创建Excel的Application对象 146 ApplicationClass excelApp = new ApplicationClass(); 147 excelApp.Visible = true; 148 //创建Excel的Application对象 149 Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing); 150 151 try 152 { 153 int sheetIndex = 0;//sheet索引 154 155 //列头字符集和长度 156 string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; 157 int colCharsetLen = colCharset.Length; 158 159 //遍历数据源DataSet的各个表 160 foreach (System.Data.DataTable dt in dataSet.Tables) 161 { 162 //给Excel某列的数据数组 163 object[,] rawData = new object[dt.Rows.Count + 1, 1]; 164 165 Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add( 166 excelWorkbook.Sheets.get_Item(++sheetIndex), 167 Type.Missing, 1, XlSheetType.xlWorksheet); 168 excelSheet.Name = dt.TableName; 169 170 for (int col = 0; col < dt.Columns.Count; col++) 171 { 172 //将datatable中的某一个列的值以objec数组形式拿出来 173 rawData[0, 0] = dt.Columns[col].ColumnName; 174 for (int row = 0; row < dt.Rows.Count; row++) 175 { 176 rawData[row + 1, 0] = dt.Rows[row].ItemArray[col]; 177 } 178 179 //获取Excel中列头名 180 string finalColLetter = string.Empty; 181 if (col + 1 > colCharsetLen) 182 { 183 finalColLetter = colCharset.Substring( 184 (col) / colCharsetLen - 1, 1); 185 } 186 finalColLetter += colCharset.Substring( 187 (col) % colCharsetLen, 1); 188 //设置列区间 189 string excelRange = string.Format("{0}1:{1}{2}", 190 finalColLetter, finalColLetter, dt.Rows.Count + 1); 191 //获取列区间对象 192 Range range = excelSheet.get_Range(excelRange, Type.Missing); 193 //设置列格式 194 switch (dt.Columns[col].DataType.ToString()) 195 { 196 case "System.String": 197 range.NumberFormatLocal = "@"; 198 break; 199 case "System.DateTime": 200 range.NumberFormatLocal = "yyyy-mm-dd"; 201 break; 202 //可以根据自己的需要扩展。 203 default: 204 range.NumberFormatLocal = "G/通用格式"; 205 break; 206 } 207 //对列赋值 208 range.Value2 = rawData; 209 } 210 //将Excel的第一行的字体设置成粗体 211 ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true; 212 } 213 excelApp.Application.DisplayAlerts = false; 214 215 //保存Excel文件 216 excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, 217 Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, 218 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 219 220 } 221 catch (Exception ex) 222 { 223 throw (ex); 224 } 225 } 226 227 //从dataSet中导出到Excel，释放其对象，但不打开Excel 228 public void DataTableToExcel(System.Data.DataTable dt, string outputPath, bool deleteOldFile) 229 { 230 //删除原有文件 231 if (deleteOldFile) 232 { 233 if (System.IO.File.Exists(outputPath)) 234 { 235 FileInfo fi = new FileInfo(outputPath); 236 if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1) 237 fi.Attributes = FileAttributes.Normal; 238 try 239 { 240 System.IO.File.Delete(outputPath); 241 } 242 catch (Exception ex) 243 { 244 throw (ex); 245 } 246 } 247 } 248 249 DateTime beforeTime; 250 DateTime afterTime; 251 252 beforeTime = DateTime.Now; 253 //创建Excel的Application对象 254 ApplicationClass excelApp = new ApplicationClass(); 255 afterTime = DateTime.Now; 256 // 创建一个新的Excel的Workbook 257 Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing); 258 259 try 260 { 261 262 int sheetIndex = 0; 263 264 //列头字符集和长度 265 string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; 266 int colCharsetLen = colCharset.Length; 267 268 //给Excel某列的数据数组 269 object[,] rawData = new object[dt.Rows.Count + 1, 1]; 270 271 Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add( 272 excelWorkbook.Sheets.get_Item(++sheetIndex), 273 Type.Missing, 1, XlSheetType.xlWorksheet); 274 excelSheet.Name = dt.TableName; 275 276 for (int col = 0; col < dt.Columns.Count; col++) 277 { 278 //将datatable中的某一个列的值以objec数组形式拿出来 279 rawData[0, 0] = dt.Columns[col].ColumnName; 280 for (int row = 0; row < dt.Rows.Count; row++) 281 { 282 rawData[row + 1, 0] = dt.Rows[row].ItemArray[col]; 283 } 284 285 //获取Excel中列头名 286 string finalColLetter = string.Empty; 287 if (col + 1 > colCharsetLen) 288 { 289 finalColLetter = colCharset.Substring( 290 (col) / colCharsetLen - 1, 1); 291 } 292 finalColLetter += colCharset.Substring( 293 (col) % colCharsetLen, 1); 294 //设置列区间 295 string excelRange = string.Format("{0}1:{1}{2}", 296 finalColLetter, finalColLetter, dt.Rows.Count + 1); 297 //获取列区间对象 298 Range range = excelSheet.get_Range(excelRange, Type.Missing); 299 //设置列格式 300 switch (dt.Columns[col].DataType.ToString()) 301 { 302 case "System.String": 303 range.NumberFormatLocal = "@"; 304 break; 305 case "System.DateTime": 306 range.NumberFormatLocal = "yyyy-mm-dd"; 307 break; 308 //可以根据自己的需要扩展。 309 default: 310 range.NumberFormatLocal = "G/通用格式"; 311 break; 312 } 313 //对列赋值 314 range.Value2 = rawData; 315 } 316 //将Excel的第一行的字体设置成粗体 317 ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true; 318 319 //保存Excel文件 320 excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, 321 Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, 322 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 323 excelWorkbook.Close(true, Type.Missing, Type.Missing); 324 325 excelApp.Workbooks.Close(); 326 excelApp.Application.Quit(); 327 excelApp.Quit(); 328 329 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet); 330 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); 331 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); 332 333 excelSheet = null; 334 excelWorkbook = null; 335 excelApp = null; 336 337 GC.Collect(); 338 GC.WaitForPendingFinalizers(); 339 } 340 catch (Exception e) 341 { 342 throw e; 343 } 344 finally 345 { 346 Process[] myProcesses; 347 DateTime startTime; 348 myProcesses = Process.GetProcessesByName("Excel"); 349 350 // 得不到Excel进程ID，暂时只能判断进程启动时间 351 foreach (Process myProcess in myProcesses) 352 { 353 startTime = myProcess.StartTime; 354 355 if (startTime > beforeTime && startTime < afterTime) 356 { 357 myProcess.Kill(); 358 } 359 } 360 } 361 } 362 363 //从dataSet中导出到Excel，释放其对象，但不打开Excel 364 public void DataSetToExcel(DataSet dataSet, string outputPath, bool deleteOldFile) 365 { 366 if (deleteOldFile) 367 { 368 if (System.IO.File.Exists(outputPath)) 369 { 370 FileInfo fi = new FileInfo(outputPath); 371 if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1) 372 fi.Attributes = FileAttributes.Normal; 373 System.IO.File.Delete(outputPath); 374 } 375 } 376 377 DateTime beforeTime; 378 DateTime afterTime; 379 380 beforeTime = DateTime.Now; 381 //创建Excel的Application对象 382 ApplicationClass excelApp = new ApplicationClass(); 383 afterTime = DateTime.Now; 384 // 创建一个新的Excel的Workbook 385 Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing); 386 387 try 388 { 389 390 int sheetIndex = 0; 391 //列头字符集和长度 392 string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; 393 int colCharsetLen = colCharset.Length; 394 395 //遍历数据源DataSet的各个表 396 foreach (System.Data.DataTable dt in dataSet.Tables) 397 { 398 //给Excel某列的数据数组 399 object[,] rawData = new object[dt.Rows.Count + 1, 1]; 400 401 Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add( 402 excelWorkbook.Sheets.get_Item(++sheetIndex), 403 Type.Missing, 1, XlSheetType.xlWorksheet); 404 excelSheet.Name = dt.TableName; 405 406 for (int col = 0; col < dt.Columns.Count; col++) 407 { 408 //将datatable中的某一个列的值以objec数组形式拿出来 409 rawData[0, 0] = dt.Columns[col].ColumnName; 410 for (int row = 0; row < dt.Rows.Count; row++) 411 { 412 rawData[row + 1, 0] = dt.Rows[row].ItemArray[col]; 413 } 414 415 //获取Excel中列头名 416 string finalColLetter = string.Empty; 417 if (col + 1 > colCharsetLen) 418 { 419 finalColLetter = colCharset.Substring( 420 (col) / colCharsetLen - 1, 1); 421 } 422 finalColLetter += colCharset.Substring( 423 (col) % colCharsetLen, 1); 424 //设置列区间 425 string excelRange = string.Format("{0}1:{1}{2}", 426 finalColLetter, finalColLetter, dt.Rows.Count + 1); 427 //获取列区间对象 428 Range range = excelSheet.get_Range(excelRange, Type.Missing); 429 //设置列格式 430 switch (dt.Columns[col].DataType.ToString()) 431 { 432 case "System.String": 433 range.NumberFormatLocal = "@"; 434 break; 435 case "System.DateTime": 436 range.NumberFormatLocal = "yyyy-mm-dd"; 437 break; 438 //可以根据自己的需要扩展。 439 default: 440 range.NumberFormatLocal = "G/通用格式"; 441 break; 442 } 443 //对列赋值 444 range.Value2 = rawData; 445 } 446 //将Excel的第一行的字体设置成粗体 447 ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true; 448 } 449 excelApp.Application.DisplayAlerts = false; 450 451 //保存Excel文件 452 excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, 453 Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, 454 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 455 excelWorkbook.Close(true, Type.Missing, Type.Missing); 456 457 excelApp.Workbooks.Close(); 458 excelApp.Application.Quit(); 459 excelApp.Quit(); 460 461 //System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); 462 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); 463 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); 464 465 //workSheet = null; 466 excelWorkbook = null; 467 excelApp = null; 468 469 GC.Collect(); 470 GC.WaitForPendingFinalizers(); 471 } 472 catch (Exception e) 473 { 474 throw e; 475 } 476 finally 477 { 478 Process[] myProcesses; 479 DateTime startTime; 480 myProcesses = Process.GetProcessesByName("Excel"); 481 482 // 得不到Excel进程ID，暂时只能判断进程启动时间 483 foreach (Process myProcess in myProcesses) 484 { 485 startTime = myProcess.StartTime; 486 487 if (startTime > beforeTime && startTime < afterTime) 488 { 489 myProcess.Kill(); 490 } 491 } 492 } 493 } 494 495 //使用QueryTable来导出数据 496 public void ExportExcelXQT(string constr, string selectCmd) 497 { 498 Application excel; 499 _Workbook xBk; 500 _Worksheet xSt; 501 _QueryTable xQt; 502 503 string Conn = "OLEDB;Provider=SQLOLEDB.1;" + constr; 504 try 505 { 506 excel = new ApplicationClass(); 507 xBk = excel.Workbooks.Add(true); 508 xSt = (_Worksheet)xBk.ActiveSheet; 509 xQt = xSt.QueryTables.Add(Conn, xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]), selectCmd); 510 xQt.Name = "导出数据"; 511 xQt.FieldNames = true; 512 xQt.RowNumbers = false; 513 514 xQt.FillAdjacentFormulas = false; 515 xQt.PreserveFormatting = false; 516 xQt.BackgroundQuery = true; 517 xQt.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells; 518 xQt.AdjustColumnWidth = true; 519 xQt.RefreshPeriod = 0; 520 xQt.PreserveColumnInfo = true; 521 xQt.Refresh(false); 522 xSt.QueryTables.Item(1).Delete(); 523 excel.Visible = true; 524 } 525 catch (Exception ex) 526 { 527 throw (ex); 528 } 529 } 530 531 } 532 }
1 using Microsoft.Reporting.WebForms; 2 using System; 3 using System.Collections.Generic; 4 using System.Data; 5 using System.Data.SqlClient; 6 using System.IO; 7 using System.Linq; 8 using System.Web; 9 using System.Web.UI; 10 using System.Web.UI.WebControls; 11 using wl10.DAL; 12 using wl10.Models; 13 using wuliu.Models; 14 15 namespace wuliu 16 { 17 public partial class batchPrint : System.Web.UI.Page 18 { 19 private int PageSize = 100; 20 protected void Page_Load(object sender, EventArgs e) 21 { 22 23 24 25 if (!Page.IsPostBack) 26 { 27 if (HttpContext.Current.Request.Cookies["user"] == null) 28 { 29 Response.Write("<script>alert('请登录！');window.location.href='Sign.aspx';</script>");//弹框本页面打开 30 } 31 else 32 { 33 HttpCookie cookies = Request.Cookies["user"]; 34 string name = Request.Cookies["user"].Value; 35 this.CurrentUser.Text = name; 36 ViewState["PageIndex"] = 1; //默认为第一页 37 if (!IsPostBack) 38 { 39 //HttpCookie companynames = Request.Cookies["MyCook"]; 40 //string companyname = companynames.Values["companyname"]; 41 ViewState["companyname"] = "admin"; 42 string datebase = "PRINTS"; 43 DataSet ds = SQLHelper.GetList_Page( "", datebase); 44 45 46 47 ReportDataSource data = new ReportDataSource("DataSet1", ds.Tables[0]); 48 ReportViewer1.LocalReport.ReportPath = "Report2.rdlc"; 49 this.ReportViewer1.LocalReport.DataSources.Clear(); 50 this.ReportViewer1.LocalReport.DataSources.Add(data); 51 52 53 54 55 56 57 } 58 } 59 } 60 61 } 62 protected void TextBox1_TextChanged(object sender, EventArgs e) 63 { 64 65 } 66 67 protected void Button1_Click(object sender, EventArgs e) 68 { 69 70 PrintClientInfo pinfo = SQLHelper.PrintOrderClientInfo(TextBox1.Text); 71 72 73 } 74 75 protected void btnimport_Click(object sender, EventArgs e) 76 { 77 string path = Path.PostedFile.FileName; //获取文件名和扩展名 78 79 ExcelHandle excelHandle = new ExcelHandle(); 80 PRINTSMODEL prmodel = new PRINTSMODEL(); 81 List<PRINTSMODEL> PRINTS = excelHandle.GetStudentsFromExcel(path, "Sheet1"); 82 if (PRINTS.Count > 0) 83 { 84 string messages = ""; 85 int count = prmodel.AddPRINTS(PRINTS, ref messages); 86 if (count == PRINTS.Count)//全部导入成功 87 { 88 Response.Write("<script>alert('导入成功');</script>");//弹框本页面打开 89 } 90 } 91 Response.Redirect("batchPrint.aspx"); 92 } 93 94 protected void Button2_Click(object sender, EventArgs e) 95 { 96 DataSet ds; 97 using (SqlConnection cn = new SqlConnection(SQLHelper.ConnectionString)) 98 { 99 string strSQL = "delete from PRINTS"; //要执行的SQL语句 100 101 try 102 { 103 104 cn.Open(); // 打开数据库连接 105 106 SqlDataAdapter da = new SqlDataAdapter(strSQL, cn); //创建DataAdapter数据适配器实例 107 ds = new DataSet();//创建DataSet实例 108 da.Fill(ds, "table");//使用DataAdapter的Fill方法(填充)，调用SELECT命令 109 cn.Close(); 110 111 } 112 catch (Exception ex) 113 { 114 //Page.ClientScript.RegisterClientScriptBlock(this.GetType(), Guid.NewGuid().ToString(), string.Format("<script>alert('数据库操作有误!')</script>")); 115 116 //Console.WriteLine(ex.Message); 117 } 118 finally 119 { 120 cn.Close(); // 关闭数据库连接 121 Response.Redirect("batchPrint.aspx"); 122 } 123 } 124 } 125 } 126 }
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Data.SqlClient; 5 using System.Linq; 6 using System.Text; 7 using System.Web; 8 using wuliu.Models; 9 10 namespace wuliu.DAL 11 { 12 public class printsDLL 13 { 14 public bool AddPRINTS(PRINTSMODEL PRIS) 15 { 16 17 //创建Sql语句 18 StringBuilder sb = new StringBuilder(); 19 string sql = ""; 20 21 sql = @"INSERT [PRINTS](JNAME,JMOBILE,JADDRES, 22 SNAME,SMOBILE,SADDRES, 23 GOODS,NUMBER) 24 VALUES (@JNAME,@JMOBILE,@JADDRES,@SNAME,@SMOBILE,@SADDRES,@GOODS,@NUMBER) "; 25 //sql = @"update PRINTS set JNAME=@JNAME,JMOBILE=@JMOBILE,JADDRES=@JADDRES,SNAME=@SNAME,SMOBILE=@SMOBILE,SADDRES=@SADDRES,GOODS=@GOODS,NUMBER=@NUMBER"; 26 27 SqlParameter[] paras = 28 { 29 new SqlParameter("@JNAME",PRIS.JNAME ), 30 new SqlParameter("@JMOBILE",PRIS.JMOBILE ), 31 new SqlParameter("@JADDRES",PRIS.JADDRES ), 32 new SqlParameter("@SNAME",PRIS.SNAME ), 33 new SqlParameter("@SMOBILE",PRIS.SMOBILE ), 34 new SqlParameter("@SADDRES",PRIS.SADDRES ), 35 new SqlParameter("@GOODS",PRIS.GOODS ), 36 new SqlParameter("@NUMBER",PRIS.NUMBER ) 37 }; 38 39 40 return SQLHelper.ExecuteNonQueryis(SQLHelper.ConnectionString, 41 CommandType.Text, 42 sql, 43 paras) > 0; 44 } 45 46 } 47 }
以上是.net导入excel 的代码
注：以上只能在本地vs中使用如果发布到iis上则需要配置一些东东
1.下载office，你的电脑中必须装office软件
2.把iis上你的网站中的身份验证设置成匿名身份验证
3.打开组件服务，把我的电脑->属性->com安全->访问权限和启动激活权限中的->编辑权限和编辑默认值分别添加上你的匿名和Everyone用户并赋予所有权限
4.打开组件服务，计算机->我的电脑->DCOM配置 找到Microsoft Excel Application 右键属性中 安全选项卡 启动和激活权限、访问权限、配置权限分别添加上你的匿名和Everyone用户并赋予所有权限，标识 选项卡选择交互式用户。
5.打开组件服务，计算机->我的电脑->Distributed Transaction Coordinator 中的DTC 安全选项卡设置允许远程客户端 允许远程管理 然后应用即可重启DTC。
至此所有配置已经完成。应该不会出现错误的。你就可以通过iis来访问了并且导入可以成功运行。
