/// <summary>
/// EXCEL批量导入
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="shop">店铺</param>
/// <returns></returns>
public string BatchUploadProduct(string filePath, Web_Shop shop)
{
#region 创建一个用于记录错误的新Excel var workbook = new HSSFWorkbook();
ISheet sheet0 = workbook.CreateSheet("sheet1");
IRow rows = sheet0.CreateRow();
rows.CreateCell().SetCellValue("分类ID");
rows.CreateCell().SetCellValue("商品名称");
rows.CreateCell().SetCellValue("售价");
rows.CreateCell().SetCellValue("简单描述");
rows.CreateCell().SetCellValue("重量");
rows.CreateCell().SetCellValue("单位");
rows.CreateCell().SetCellValue("现购库存");
rows.CreateCell().SetCellValue("供应类型");
rows.CreateCell().SetCellValue("收获时间");
rows.CreateCell().SetCellValue("订购库存");
rows.CreateCell().SetCellValue("错误消息"); #endregion try
{
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var book = new HSSFWorkbook(stream); //读出来用户上传的Excel
ISheet sheet = book.GetSheetAt(); //拿到Excel中的第一个sheet
int rowCount = sheet.LastRowNum; //拿到sheet的行数
int errorCount = ;
for (int i = sheet.FirstRowNum + ; i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
//row2.CreateCell(10).SetCellValue(row.GetCell(10).ToString()); #region 中间变量 string errMsg = string.Empty;
bool error = false;
int categoryId;
string proName;
decimal marketPrice;
string shortContent;
int weight;
string unit;
int storage; //现购库存
int canSupply; //可订购数量
int supplyType; //供应类型
DateTime harvestTime; //收获时间 #endregion var pro = new Web_Product(); #region 检测数据合法性 if (!int.TryParse(row.GetCell().ToString(), out categoryId))
{
errMsg = "商品类型错误";
error = true;
}
proName = row.GetCell().ToString();
if (string.IsNullOrEmpty(proName))
{
errMsg = errMsg + ",商品名称不能为空";
error = true;
} if (!decimal.TryParse(row.GetCell().ToString(), out marketPrice))
{
errMsg = errMsg + ",商品价格错误";
error = true;
}
shortContent = row.GetCell().ToString();
if (string.IsNullOrEmpty(shortContent))
{
errMsg = errMsg + ",商品描述不能为空";
error = true;
}
if (!int.TryParse(row.GetCell().ToString(), out weight))
{
errMsg = errMsg + ",商品重量错误";
error = true;
}
unit = row.GetCell().ToString();
if (string.IsNullOrEmpty(unit))
{
errMsg = errMsg + ",商品单位错误";
error = true;
} if (!int.TryParse(row.GetCell().ToString(), out storage))
{
errMsg = errMsg + ",商品现购库存错误";
error = true;
}
if (!int.TryParse(row.GetCell().ToString(), out supplyType))
{
errMsg = errMsg + ",商品供应类型错误";
error = true;
}
if (!DateTime.TryParse(row.GetCell().DateCellValue.ToString("yyyy-MM-dd"), out harvestTime))
{
errMsg = errMsg + ",商品收获时间错误";
error = true;
}
if (!int.TryParse(row.GetCell().ToString(), out canSupply))
{
errMsg = errMsg + ",商品订购库存错误";
error = true;
} #endregion #region 如果有错误，就把这一行给添加到新的Excel中 if (error)
{
IRow row2 = sheet0.CreateRow(sheet0.LastRowNum + );
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(row.GetCell().ToString());
row2.CreateCell().SetCellValue(errMsg);
errorCount += ;
} #endregion #region 如果没有错误，把产品添加到数据库 if (!error)
{
pro.Name = proName.TagReplace();
pro.MarketPrice = marketPrice;
pro.ShortContent = shortContent.TagReplace();
pro.Weight = weight;
pro.Unit = unit.TagReplace();
pro.Storage = storage;
pro.CanSupply = canSupply;
pro.CategoryID = categoryId;
pro.IsValidate = ; //默认这个产品是没有经过验证的，不让它上架
if (supplyType != ) //只要不等于100，默认都给200
{
supplyType = ;
}
pro.SupplyType = supplyType;
pro.ShopID = shop.ID;
pro.HarvestTime = harvestTime;
AddProduct(pro);
} #endregion
}
string savePath = VirtualPaths.ErrorExcelSavePath + "/" + GetProductrNumberByDate() + ".xls";
//生成错误Excel的文件
if (errorCount > ) //如果总的错误个数大于0，就吧错误的Excel写到文件中，否则不写。
{
using (var fs = new FileStream(savePath, FileMode.Create))
{
workbook.Write(fs); //保存
}
return savePath; //返回路径供下载
}
return "ok"; //表示上传成功，没有错误
}
}
catch
{
return "上传失败，请重试"; //遇到的未知的错误
}
}
DAL层