// Origin: analyseExcel_DailyInfoTable
public async Task <List <Excel_DataMessage> > UploadDailySalesByExcelAsync(string filename, List <Excel_DataMessage> messageList)
{
try
{
var user = UserManager.FindById(User.Identity.GetUserId());
string folder = HttpContext.Server.MapPath("~/Content/xlsx/");
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + folder + filename + ";Extended Properties='Excel 12.0; HDR=1; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", "Sheet1$"), conn); //("select * from [Sheet1$]", conn);
odda.Fill(ds, "[Sheet1$]");
conn.Close();
DataTable dt = ds.Tables[0];
int i = 0;
bool result_flag = true;
foreach (DataRow dr in dt.Rows)
{
i++;
try
{
// 判断是否存在店铺
string storename = dr["店铺名称"].ToString();
var exist_store = _offlineDB.Off_Store.SingleOrDefault(m => m.StoreName == storename && m.Off_System_Id == user.DefaultSystemId);
if (exist_store == null)
{
messageList.Add(new Excel_DataMessage(i, "店铺不存在", true));
result_flag = false;
continue;
}
// 判断是否有促销员，如有促销员，判断存在销售员
string sellername = dr["促销员"].ToString();
Off_Seller exist_seller = null;
if (sellername != "")
{
exist_seller = _offlineDB.Off_Seller.SingleOrDefault(m => m.Name == sellername);
if (exist_seller == null)
{
messageList.Add(new Excel_DataMessage(i, "销售员不存在", true));
result_flag = false;
continue;
}
}
// 判断是否含已有数据
DateTime info_date = Convert.ToDateTime(dr["日期"]);
var exist_dailyinfo = _offlineDB.Off_SalesInfo_Daily.SingleOrDefault(m => m.Date == info_date && m.StoreId == exist_store.Id && m.isMultiple == false);
if (exist_dailyinfo != null)
{
messageList.Add(new Excel_DataMessage(i, "当日数据已存在", true));
result_flag = false;
continue;
}
else
{
int? attendance = null;
string attendance_info = dr["考勤"].ToString();
switch (attendance_info)
{
case "全勤":
attendance = 0;
break;
case "迟到":
attendance = 1;
break;
case "早退":
attendance = 2;
break;
case "旷工":
attendance = 3;
break;
default:
attendance = null;
break;
}
Off_SalesInfo_Daily dailyinfo = new Off_SalesInfo_Daily()
{
StoreId = exist_store.Id,
Date = info_date,
Item_Brown = ExcelOperation.ConvertInt(dr, "红糖姜茶"),
Item_Black = ExcelOperation.ConvertInt(dr, "黑糖姜茶"),
Item_Lemon = ExcelOperation.ConvertInt(dr, "柠檬姜茶"),
Item_Honey = ExcelOperation.ConvertInt(dr, "蜂蜜姜茶"),
Item_Dates = ExcelOperation.ConvertInt(dr, "红枣姜茶"),
Off_Seller = exist_seller,
Attendance = attendance,
Salary = ExcelOperation.ConvertDecimal(dr, "工资"),
Bonus = ExcelOperation.ConvertDecimal(dr, "奖金"),
Debit = ExcelOperation.ConvertDecimal(dr, "扣款"),
isMultiple = ExcelOperation.ConvertBoolean(dr, "多人"),
remarks = dr["备注"].ToString(),
UploadTime = DateTime.Now,
UploadUser = User.Identity.Name
};
_offlineDB.Off_SalesInfo_Daily.Add(dailyinfo);
messageList.Add(new Excel_DataMessage(i, "数据类型验证成功", false));
}
}
catch (Exception e)
{
result_flag = false;
messageList.Add(new Excel_DataMessage(i, "表格格式错误," + e.ToString(), true));
}
}
if (result_flag)
{
await _offlineDB.SaveChangesAsync();
messageList.Add(new Excel_DataMessage(0, "保存成功", false));
}
else
{
messageList.Add(new Excel_DataMessage(0, "数据行发生错误，未保存", true));
}
}
catch (Exception e)
{
messageList.Add(new Excel_DataMessage(-1, "表格格式错误" + e.ToString(), true));
}
return(messageList);
}
private List <Excel_DataMessage> analyseExcel_GenericTable(string filename, List <Excel_DataMessage> messageList)
{
try
{
string folder = HttpContext.Server.MapPath("~/Content/xlsx/");
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + folder + filename + ";Extended Properties='Excel 12.0; HDR=1; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", "Sheet1$"), conn); //("select * from [Sheet1$]", conn);
odda.Fill(ds, "[Sheet1$]");
conn.Close();
DataTable dt = ds.Tables[0];
int i = 0;
bool result_flag = true;
foreach (DataRow dr in dt.Rows)
{
i++;
try
{
// 判断是否含有数据
string storename = dr["店铺名称"].ToString();
DateTime date = Convert.ToDateTime(ExcelOperation.ConvertDateTime(dr, "月份"));
string item_code = dr["商品编码"].ToString();
var exist_item = erpdb.product_generic_data.SingleOrDefault(m => m.storename == storename && m.date == date && m.item_code == item_code);
if (exist_item != null)
{
// 更新数据
exist_item.storename = storename;
exist_item.date = date;
exist_item.item_code = item_code;
exist_item.simple_name = dr["商品简称"].ToString();
exist_item.uv = ExcelOperation.ConvertInt(dr, "访客数");
exist_item.pv = ExcelOperation.ConvertInt(dr, "浏览量");
exist_item.order_count = ExcelOperation.ConvertInt(dr, "订单数");
exist_item.order_amount = ExcelOperation.ConvertDecimal(dr, "销售金额");
exist_item.product_unit = ExcelOperation.ConvertInt(dr, "下单件数");
exist_item.convertion = ExcelOperation.ConvertInt(dr, "转化率");
messageList.Add(new Excel_DataMessage(i, "数据修改成功", false));
}
else
{
// 添加数据
product_generic_data details = new product_generic_data()
{
storename = storename,
date = date,
item_code = item_code,
simple_name = dr["商品简称"].ToString(),
uv = ExcelOperation.ConvertInt(dr, "访客数"),
pv = ExcelOperation.ConvertInt(dr, "浏览量"),
order_count = ExcelOperation.ConvertInt(dr, "订单数"),
order_amount = ExcelOperation.ConvertDecimal(dr, "销售金额"),
product_unit = ExcelOperation.ConvertInt(dr, "下单件数"),
convertion = ExcelOperation.ConvertInt(dr, "转化率")
};
erpdb.product_generic_data.Add(details);
messageList.Add(new Excel_DataMessage(i, "数据添加成功", false));
}
}
catch (Exception e)
{
result_flag = false;
messageList.Add(new Excel_DataMessage(i, "格式错误或列名不存在," + e.InnerException, true));
}
}
if (result_flag)
{
erpdb.SaveChanges();
messageList.Add(new Excel_DataMessage(0, "数据存储成功", false));
}
else
{
messageList.Add(new Excel_DataMessage(0, "数据行发生错误，未保存", true));
}
}
catch (Exception e)
{
messageList.Add(new Excel_DataMessage(-1, "表格格式错误" + e.ToString(), true));
}
return(messageList);
}