public List<MatExcel> InputMaterialByPurPriceAndLeadTimeExcel(HttpPostedFileBase ExcelFile, Guid LinkMainCID)
{
MyNormalUploadFile MF = new MyNormalUploadFile();
//创建上传文件
string ExcelFilePath = MF.NormalUpLoadFileProcess(ExcelFile, "Excel");
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
XSSFWorkbook workbook = new XSSFWorkbook(new FileStream(HttpRuntime.AppDomainAppPath.ToString() + ExcelFilePath, FileMode.Open, FileAccess.Read));
//获取excel的第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
List<MatExcel> ML = new List<MatExcel>();
MatExcel M = new MatExcel();
int Seconds = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
Seconds = Seconds + 1;
IRow row = sheet.GetRow(i);
M = M = new MatExcel();
try
{
M.MatID = new Guid(row.GetCell(0).ToString().Trim());
}
catch
{
M.MatID = Guid.Empty;
}
try
{
M.MatSn = row.GetCell(2).ToString().Trim();
}
catch
{
M.MatSn = string.Empty;
}
try
{
M.MatName = row.GetCell(3).ToString().Trim();
}
catch
{
M.MatName = string.Empty;
}
try
{
M.MatSpecifications = row.GetCell(4).ToString().Trim();
}
catch
{
M.MatSpecifications = string.Empty;
}
try
{
M.MatBrand = row.GetCell(5).ToString().Trim();
}
catch
{
M.MatBrand = string.Empty;
}
try
{
M.MatManufacturerSn = row.GetCell(6).ToString().Trim();
}
catch
{
M.MatManufacturerSn = string.Empty;
}
try
{
M.MatUnit = row.GetCell(7).ToString().Trim();
}
catch
{
M.MatUnit = string.Empty;
}
try
{
M.CostPrice = Convert.ToDecimal(row.GetCell(8).ToString().Trim());
}
catch
{
M.CostPrice = 0;
}
try
{
M.MarketPrice = Convert.ToDecimal(row.GetCell(9).ToString().Trim());
}
catch
{
M.MarketPrice = 0;
}
try
{
M.LeadTime = Convert.ToInt32(row.GetCell(10).ToString().Trim());
}
catch
{
M.LeadTime = 0;
}
try
{
M.SupplierName = row.GetCell(11).ToString().Trim();
}
catch
{
M.SupplierName = string.Empty;
}
M.IsErrorMatInfo = 0;
M.IsErrorMatSn = 0;
if (Seconds > 1 && Seconds <= 4001 && M.MatID != Guid.Empty && !string.IsNullOrEmpty(M.MatName))
{
ML.Add(M);
}
}
return ML;
}
public void InputMaterialByExcel(HttpPostedFileBase ExcelFile, Guid LinkMainCID)
{
MyNormalUploadFile MF = new MyNormalUploadFile();
//创建上传文件
string ExcelFilePath = MF.NormalUpLoadFileProcess(ExcelFile, "Excel");
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
XSSFWorkbook workbook = new XSSFWorkbook(new FileStream(HttpRuntime.AppDomainAppPath.ToString() + ExcelFilePath, FileMode.Open, FileAccess.Read));
//获取excel的第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
List<MatExcel> ML = new List<MatExcel>();
MatExcel M = new MatExcel();
int Seconds = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
Seconds = Seconds + 1;
IRow row = sheet.GetRow(i);
M = M = new MatExcel();
try
{
M.MatSn = row.GetCell(0).ToString().Trim();
}
catch
{
M.MatSn = string.Empty;
}
try
{
M.MatName = row.GetCell(1).ToString().Trim();
}
catch
{
M.MatName = string.Empty;
}
try
{
M.MatSpecifications = row.GetCell(2).ToString().Trim();
}
catch
{
M.MatSpecifications = string.Empty;
}
try
{
M.MatManufacturerSn = row.GetCell(3).ToString().Trim();
}
catch
{
M.MatManufacturerSn = string.Empty;
}
try
{
M.MatBrand = row.GetCell(4).ToString().Trim();
}
catch
{
M.MatBrand = string.Empty;
}
try
{
M.MatBrandType = row.GetCell(5).ToString().Trim();
}
catch
{
M.MatBrandType = string.Empty;
}
try
{
M.MatUnit = row.GetCell(6).ToString().Trim();
}
catch
{
M.MatUnit = string.Empty;
}
try
{
M.CostPrice = Convert.ToDecimal(row.GetCell(7).ToString().Trim());
}
catch
{
M.CostPrice = 0;
}
try
{
M.MarketPrice = Convert.ToDecimal(row.GetCell(8).ToString().Trim());
}
catch
{
M.MarketPrice = 0;
}
try
{
M.LeadTime = Convert.ToInt32(row.GetCell(9).ToString().Trim());
}
catch
{
M.LeadTime = 0;
}
try
{
M.MatSummary = row.GetCell(10).ToString().Trim();
}
catch
{
M.MatSummary = string.Empty;
}
if (Seconds > 1 && Seconds <= 1001 && !string.IsNullOrEmpty(M.MatName) && !string.IsNullOrEmpty(M.MatUnit) && !string.IsNullOrEmpty(M.MatBrand))
{
ML.Add(M);
}
}
////删除持久化内容
try {
db.MatExcelDB.Remove(db.MatExcelDB.Find(LinkMainCID));
db.SaveChanges();
}
catch
{
//Dothing
}
//存储进入持久化Json数据
string JSONStr = JsonConvert.SerializeObject(ML);
MatExcelDB MExcel = new MatExcelDB();
MExcel.LinkMainCID = LinkMainCID;
MExcel.MatListJson = JSONStr;
db.MatExcelDB.Add(MExcel);
db.SaveChanges();
}