1 /// <summary>  2         ///     EXCEL批量导入  3         /// </summary>  4         /// <param name="filePath">文件路径</param>  5         /// <param name="shop">店铺</param>  6         /// <returns></returns>  7         public string BatchUploadProduct(string filePath, Web_Shop shop)  8         {  9             #region 创建一个用于记录错误的新Excel 10  11             var workbook = new HSSFWorkbook(); 12             ISheet sheet0 = workbook.CreateSheet("sheet1"); 13             IRow rows = sheet0.CreateRow(0); 14             rows.CreateCell(0).SetCellValue("分类ID"); 15             rows.CreateCell(1).SetCellValue("商品名称"); 16             rows.CreateCell(2).SetCellValue("售价"); 17             rows.CreateCell(3).SetCellValue("简单描述"); 18             rows.CreateCell(4).SetCellValue("重量"); 19             rows.CreateCell(5).SetCellValue("单位"); 20             rows.CreateCell(6).SetCellValue("现购库存"); 21             rows.CreateCell(7).SetCellValue("供应类型"); 22             rows.CreateCell(8).SetCellValue("收获时间"); 23             rows.CreateCell(9).SetCellValue("订购库存"); 24             rows.CreateCell(10).SetCellValue("错误消息"); 25  26             #endregion 27  28             try 29             { 30                 using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 31                 { 32                     var book = new HSSFWorkbook(stream); //读出来用户上传的Excel 33                     ISheet sheet = book.GetSheetAt(0); //拿到Excel中的第一个sheet 34                     int rowCount = sheet.LastRowNum; //拿到sheet的行数 35                     int errorCount = 0; 36                     for (int i = sheet.FirstRowNum + 1; i < rowCount; i++) 37                     { 38                         IRow row = sheet.GetRow(i); 39                         //row2.CreateCell(10).SetCellValue(row.GetCell(10).ToString()); 40  41                         #region 中间变量 42  43                         string errMsg = string.Empty; 44                         bool error = false; 45                         int categoryId; 46                         string proName; 47                         decimal marketPrice; 48                         string shortContent; 49                         int weight; 50                         string unit; 51                         int storage; //现购库存 52                         int canSupply; //可订购数量 53                         int supplyType; //供应类型 54                         DateTime harvestTime; //收获时间  55  56                         #endregion 57  58                         var pro = new Web_Product(); 59  60                         #region 检测数据合法性 61  62                         if (!int.TryParse(row.GetCell(0).ToString(), out categoryId)) 63                         { 64                             errMsg = "商品类型错误"; 65                             error = true; 66                         } 67                         proName = row.GetCell(1).ToString(); 68                         if (string.IsNullOrEmpty(proName)) 69                         { 70                             errMsg = errMsg + ",商品名称不能为空"; 71                             error = true; 72                         } 73  74                         if (!decimal.TryParse(row.GetCell(2).ToString(), out marketPrice)) 75                         { 76                             errMsg = errMsg + ",商品价格错误"; 77                             error = true; 78                         } 79                         shortContent = row.GetCell(3).ToString(); 80                         if (string.IsNullOrEmpty(shortContent)) 81                         { 82                             errMsg = errMsg + ",商品描述不能为空"; 83                             error = true; 84                         } 85                         if (!int.TryParse(row.GetCell(4).ToString(), out weight)) 86                         { 87                             errMsg = errMsg + ",商品重量错误"; 88                             error = true; 89                         } 90                         unit = row.GetCell(5).ToString(); 91                         if (string.IsNullOrEmpty(unit)) 92                         { 93                             errMsg = errMsg + ",商品单位错误"; 94                             error = true; 95                         } 96  97                         if (!int.TryParse(row.GetCell(6).ToString(), out storage)) 98                         { 99                             errMsg = errMsg + ",商品现购库存错误";100                             error = true;101                         }102                         if (!int.TryParse(row.GetCell(7).ToString(), out supplyType))103                         {104                             errMsg = errMsg + ",商品供应类型错误";105                             error = true;106                         }107                         if (!DateTime.TryParse(row.GetCell(8).DateCellValue.ToString("yyyy-MM-dd"), out harvestTime))108                         {109                             errMsg = errMsg + ",商品收获时间错误";110                             error = true;111                         }112                         if (!int.TryParse(row.GetCell(9).ToString(), out canSupply))113                         {114                             errMsg = errMsg + ",商品订购库存错误";115                             error = true;116                         }117 118                         #endregion119 120                         #region 如果有错误，就把这一行给添加到新的Excel中121 122                         if (error)123                         {124                             IRow row2 = sheet0.CreateRow(sheet0.LastRowNum + 1);125                             row2.CreateCell(0).SetCellValue(row.GetCell(0).ToString());126                             row2.CreateCell(1).SetCellValue(row.GetCell(1).ToString());127                             row2.CreateCell(2).SetCellValue(row.GetCell(2).ToString());128                             row2.CreateCell(3).SetCellValue(row.GetCell(3).ToString());129                             row2.CreateCell(4).SetCellValue(row.GetCell(4).ToString());130                             row2.CreateCell(5).SetCellValue(row.GetCell(5).ToString());131                             row2.CreateCell(6).SetCellValue(row.GetCell(6).ToString());132                             row2.CreateCell(7).SetCellValue(row.GetCell(7).ToString());133                             row2.CreateCell(8).SetCellValue(row.GetCell(8).ToString());134                             row2.CreateCell(9).SetCellValue(row.GetCell(9).ToString());135                             row2.CreateCell(10).SetCellValue(errMsg);136                             errorCount += 1;137                         }138 139                         #endregion140 141                         #region 如果没有错误，把产品添加到数据库142 143                         if (!error)144                         {145                             pro.Name = proName.TagReplace();146                             pro.MarketPrice = marketPrice;147                             pro.ShortContent = shortContent.TagReplace();148                             pro.Weight = weight;149                             pro.Unit = unit.TagReplace();150                             pro.Storage = storage;151                             pro.CanSupply = canSupply;152                             pro.CategoryID = categoryId;153                             pro.IsValidate = 0; //默认这个产品是没有经过验证的，不让它上架154                             if (supplyType != 100) //只要不等于100，默认都给200155                             {156                                 supplyType = 200;157                             }158                             pro.SupplyType = supplyType;159                             pro.ShopID = shop.ID;160                             pro.HarvestTime = harvestTime;161                             AddProduct(pro);162                         }163 164                         #endregion165                     }166                     string savePath = VirtualPaths.ErrorExcelSavePath + "/" + GetProductrNumberByDate() + ".xls";167                         //生成错误Excel的文件168                     if (errorCount > 0) //如果总的错误个数大于0，就吧错误的Excel写到文件中，否则不写。169                     {170                         using (var fs = new FileStream(savePath, FileMode.Create))171                         {172                             workbook.Write(fs); //保存173                         }174                         return savePath; //返回路径供下载175                     }176                     return "ok"; //表示上传成功，没有错误177                 }178             }179             catch180             {181                 return "上传失败，请重试"; //遇到的未知的错误182             }183         }