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         }
DAL层

 

posted @ 2014-10-21 15:17  人生无赖  阅读(605)  评论(0)  编辑  收藏  举报