Excel批量导入商品，遇到导入失败记录到另一个Excel中供下载查看
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 #endregion 119 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 #endregion 140 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，默认都给200 155 { 156 supplyType = 200; 157 } 158 pro.SupplyType = supplyType; 159 pro.ShopID = shop.ID; 160 pro.HarvestTime = harvestTime; 161 AddProduct(pro); 162 } 163 164 #endregion 165 } 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 catch 180 { 181 return "上传失败，请重试"; //遇到的未知的错误 182 } 183 }
西安.NET俱乐部群
