C# 导入
@*前台
1 /// <summary> 2 /// 导出Excel 3 /// </summary> 4 public void ExcelExprot() 5 { 6 //创建Excel文件的对象 7 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); 8 //添加一个sheet 9 NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); 10 11 List<Thebookfor> list = GetAll(); 12 13 //给sheet1添加第一行的头部标题 14 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); 15 row1.CreateCell(0).SetCellValue("序号"); 16 row1.CreateCell(1).SetCellValue("书名"); 17 row1.CreateCell(2).SetCellValue("ISBN"); 18 row1.CreateCell(3).SetCellValue("出版者"); 19 row1.CreateCell(4).SetCellValue("中图分类法"); 20 row1.CreateCell(5).SetCellValue("申请时间"); 21 //将数据逐步写入sheet1各个行 22 for (int i = 0; i < list.Count; i++) 23 { 24 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); 25 rowtemp.CreateCell(0).SetCellValue(list[i].ThebookforID.ToString()); 26 rowtemp.CreateCell(1).SetCellValue(list[i].BookName.ToString()); 27 rowtemp.CreateCell(2).SetCellValue(list[i].ISBN.ToString()); 28 rowtemp.CreateCell(3).SetCellValue(list[i].bookmaker.ToString()); 29 rowtemp.CreateCell(4).SetCellValue(list[i].BookclassifyID.ToString()); 30 rowtemp.CreateCell(5).SetCellValue(list[i].timeofmaking.ToString()); 31 } 32 33 // 写入到客户端 34 MemoryStream ms = new MemoryStream(); 35 book.Write(ms); 36 ms.Seek(0, SeekOrigin.Begin); 37 38 ms.Flush(); 39 ms.Position = 0; 40 //编辑完后 通过response输出 41 42 Response.Clear(); 43 Response.Buffer = true; 44 Response.Charset = "UTF8"; 45 Response.ContentEncoding = System.Text.Encoding.UTF8; 46 Response.ContentType = "application/msexcel"; 47 Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("第一批电脑派位生名册.xls")); 48 Response.BinaryWrite(ms.ToArray()); 49 Response.Flush(); 50 Response.End(); 51 52 } 53 54 private List<Thebookfor> GetAll() 55 { 56 string sql = "select * from Thebookfor"; 57 DataTable datas = DBhelper.QuerySql(sql); 58 string t = Newtonsoft.Json.JsonConvert.SerializeObject(datas); 59 List<Thebookfor> ex = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Thebookfor>>(t); 60 return ex; 61 }
*@
1 <form action="/Home/TestExcel" enctype="multipart/form-data" method="post"> 2 <text>选择上传文件:(工作表名为“Sheet1”,“电脑号”在A1单元格。)</text> 3 <input name="file" type="file" id="file" /> 4 <input type="submit" name="Upload" value="批量导入第一批电脑派位名册" /> 5 </form> 6 7 8 9 10 11 12 1 /// <summary> 13 2 /// Excel导入 14 3 /// </summary> 15 4 /// <param name="filePath"></param> 16 5 /// <returns></returns> 17 6 [HttpPost] 18 7 public ActionResult TestExcel(FormCollection form) 19 8 { 20 9 HttpPostedFileBase file = Request.Files[0]; 21 10 string path = Server.MapPath("\\Models"); 22 11 path += "\\" + file.FileName; 23 12 file.SaveAs(path); 24 13 25 14 ImportExcelFile(path); 26 15 return View(); 27 16 } 28 17 29 18 30 19 /// <summary> 31 20 /// Excel导入 32 21 /// </summary> 33 22 /// <param name="filePath"></param> 34 23 /// <returns></returns> 35 24 public DataTable ImportExcelFile(string filePath) 36 25 { 37 26 HSSFWorkbook hssfworkbook; 38 27 #region//初始化信息 39 28 try 40 29 { 41 30 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 42 31 { 43 32 hssfworkbook = new HSSFWorkbook(file); 44 33 } 45 34 } 46 35 catch (Exception e) 47 36 { 48 37 throw e; 49 38 } 50 39 #endregion 51 40 52 41 ISheet sheet = hssfworkbook.GetSheetAt(3); 53 42 DataTable table = new DataTable(); 54 43 IRow headerRow = sheet.GetRow(0);//第一行为标题行 55 44 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells 56 45 int rowCount = sheet.LastRowNum - 2; 57 46 58 47 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 59 48 { 60 49 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); 61 50 table.Columns.Add(column); 62 51 } 63 52 for (int i = (sheet.FirstRowNum + 4); i <= rowCount; i++) 64 53 { 65 54 IRow row = sheet.GetRow(i); 66 55 DataRow dataRow = table.NewRow(); 67 56 68 57 if (row != null) 69 58 { 70 59 for (int j = row.FirstCellNum; j < cellCount; j++) 71 60 { 72 61 if (row.GetCell(j) != null) 73 62 dataRow[j] = GetCellValue(row.GetCell(j)); 74 63 } 75 64 } 76 65 table.Rows.Add(dataRow); 77 66 } 78 67 using (SqlBulkCopy abc = new SqlBulkCopy(SqlConnectionFactory.Connection)) 79 68 { 80 69 abc.BatchSize = table.Rows.Count; 81 70 abc.BulkCopyTimeout = 11; 82 71 abc.DestinationTableName = "ExcelTable"; 83 72 for (int i = 0; i < table.Columns.Count; i++) 84 73 { 85 74 abc.ColumnMappings.Add(table.Columns[i].ColumnName, i); 86 75 } 87 76 abc.WriteToServer(table); 88 77 } 89 78 return table; 90 79 } 91 80 /// <summary> 92 81 /// 根据Excel列类型获取列的值 93 82 /// </summary> 94 83 /// <param name="cell">Excel列</param> 95 84 /// <returns></returns> 96 85 private static string GetCellValue(ICell cell) 97 86 { 98 87 if (cell == null) 99 88 return string.Empty; 100 89 switch (cell.CellType) 101 90 { 102 91 case CellType.Blank: 103 92 return string.Empty; 104 93 case CellType.Boolean: 105 94 return cell.BooleanCellValue.ToString(); 106 95 case CellType.Error: 107 96 return cell.ErrorCellValue.ToString(); 108 97 case CellType.Numeric: 109 98 case CellType.Unknown: 110 99 default: 111 100 return cell.ToString(); 112 101 case CellType.String: 113 102 return cell.StringCellValue; 114 103 case CellType.Formula: 115 104 try 116 105 { 117 106 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); 118 107 e.EvaluateInCell(cell); 119 108 return cell.ToString(); 120 109 } 121 110 catch 122 111 { 123 112 return cell.NumericCellValue.ToString(); 124 113 } 125 114 } 126 115 }