public FileResult downloadExcel_SettleData(ParmGetSettleData q)
{
ExcelPackage excel = null;
MemoryStream fs = null;
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("獎金核算");
ExcelWorksheet sheet = excel.Workbook.Worksheets["獎金核算"];
sheet.View.TabSelected = true;
#region 取得獎金核算資料
var item = db0.Settle.Find(q.main_id);
var itemDetails = item.SettleDetail.ToList();
#endregion
#region Excel Handle
int detail_row = 3;
#region 內容
#region 標題
sheet.Cells[1, 1].Value = string.Format("{0}年{1}月 獎金核算", item.y, item.m);
sheet.Cells[1, 1, 1, 10].Merge = true;
setFontColorAndBg_Blue(sheet, 1, 1);
sheet.Cells[2, 1].Value = "[會員編號]";
sheet.Cells[2, 2].Value = "[姓名]";
sheet.Cells[2, 3].Value = "[級別]";
sheet.Cells[2, 4].Value = "[kv]";
sheet.Cells[2, 5].Value = "[共享圈總kv]";
sheet.Cells[2, 6].Value = "[累積回饋獎金]";
sheet.Cells[2, 7].Value = "[回饋獎金]";
sheet.Cells[2, 8].Value = "[經營獎金]";
sheet.Cells[2, 9].Value = "[營運紅利]";
sheet.Cells[2, 10].Value = "[管理紅利]";
setFontColor_Label(sheet, 2, 1, 10);
#endregion
foreach (var detail in itemDetails)
{
sheet.Cells[detail_row, 1].Value = detail.sales_no;
sheet.Cells[detail_row, 2].Value = detail.sales_name;
sheet.Cells[detail_row, 3].Value = CodeSheet.GetStateVal(detail.rank, CodeSheet.sales_rank);
sheet.Cells[detail_row, 4].Value = detail.kv_p_sum;//個人kv總計
sheet.Cells[detail_row, 5].Value = detail.kv_g_sum;
sheet.Cells[detail_row, 6].Value = detail.b;
sheet.Cells[detail_row, 7].Value = detail.a;
sheet.Cells[detail_row, 8].Value = detail.bound;
sheet.Cells[detail_row, 9].Value = detail.center_bonus;
sheet.Cells[detail_row, 10].Value = detail.office_bonus;
detail_row++;
}
#endregion
#region excel排版
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
for (int j = startColumn; j <= endColumn; j++)
{
//sheet.Column(j).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//靠左對齊
//sheet.Column(j).Width = 30;//固定寬度寫法
sheet.Column(j).AutoFit();//依內容fit寬度
}//End for
#endregion
//sheet.Cells.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的
#endregion
string filename = item.y + "年" + item.m + "月獎金核算" + "[" + DateTime.Now.ToString("yyyyMMddHHmm") + "].xlsx";
excel.Save();
fs.Position = 0;
return(File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
catch (Exception ex)
{
Console.Write(ex.Message);
return(null);
}
finally
{
db0.Dispose();
}
}
public FileResult downloadExcel_ProductRecord(ParmGetProductRecord parm)
{
ExcelPackage excel = null;
MemoryStream fs = null;
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("ProductRecordData");
ExcelWorksheet sheet = excel.Workbook.Worksheets["ProductRecordData"];
sheet.View.TabSelected = true;
#region 取得產品銷售明細
string date_range = "(All)";
var items = from x in db0.RecordDetail
orderby x.ProductRecord.record_sn descending
select(new R02_RecordDetail()
{
product_record_id = x.product_record_id,
record_deatil_id = x.record_deatil_id,
born_id = x.born_id,
record_sn = x.ProductRecord.record_sn,
customer_name = x.ProductRecord.Customer.customer_name,
sell_day = x.sell_day,
product_type = x.product_type,
product_name = x.product_name,
qty = x.qty,
price = x.price,
subtotal = x.subtotal,
user_id = x.i_InsertUserID
});
if (parm.product_type != null)
{
items = items.Where(x => x.product_type == parm.product_type);
}
if (parm.product_name != null)
{
items = items.Where(x => x.product_name.Contains(parm.product_name));
}
if (parm.word != null)
{
items = items.Where(x => x.record_sn.Contains(parm.word) ||
x.customer_name.Contains(parm.word));
}
if (parm.start_date != null && parm.end_date != null)
{
DateTime end = ((DateTime)parm.end_date).AddDays(1);
items = items.Where(x => x.sell_day >= parm.start_date && x.sell_day < end);
date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")";
}
var getPrintVal = items.ToList();
foreach (var item in getPrintVal)
{
string User_Name = db0.AspNetUsers.FirstOrDefault(x => x.Id == item.user_id).user_name_c;
item.user_name = User_Name;
}
#endregion
#region Excel Handle
int detail_row = 3;
#region 標題
sheet.Cells[1, 1].Value = "R02產品銷售明細報表" + date_range;
sheet.Cells[1, 1, 1, 9].Merge = true;
sheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[2, 1].Value = "[銷售單號]";
sheet.Cells[2, 2].Value = "[銷售日期]";
sheet.Cells[2, 3].Value = "[客戶名稱]";
sheet.Cells[2, 4].Value = "[產品分類]";
sheet.Cells[2, 5].Value = "[產品名稱]";
sheet.Cells[2, 6].Value = "[數量]";
sheet.Cells[2, 7].Value = "[售價]";
sheet.Cells[2, 8].Value = "[小計]";
sheet.Cells[2, 9].Value = "[經手人]";
setFontColor_blue(sheet, 2, 1, 9);
#endregion
#region 內容
foreach (var item in getPrintVal)
{
sheet.Cells[detail_row, 1].Value = item.record_sn;
sheet.Cells[detail_row, 2].Value = item.sell_day.ToString("yyyy/MM/dd");
sheet.Cells[detail_row, 3].Value = item.customer_name;
sheet.Cells[detail_row, 4].Value = CodeSheet.GetProductTypeVal(item.product_type);
sheet.Cells[detail_row, 5].Value = item.product_name;
sheet.Cells[detail_row, 6].Value = item.qty;
sheet.Cells[detail_row, 7].Value = item.price;
sheet.Cells[detail_row, 8].Value = item.subtotal;
sheet.Cells[detail_row, 9].Value = item.user_name;
detail_row++;
}
sheet.Cells[detail_row, 7].Value = "[合計]";
sheet.Cells[detail_row, 8].Value = getPrintVal.Sum(x => x.subtotal);
setFontColor_blue(sheet, detail_row, 7, 7);
#endregion
#region excel排版
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
for (int j = startColumn; j <= endColumn; j++)
{
//sheet.Column(j).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//靠左對齊
//sheet.Column(j).Width = 30;//固定寬度寫法
sheet.Column(j).AutoFit();//依內容fit寬度
}//End for
#endregion
//sheet.Cells.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的
#endregion
string filename = "R02產品銷售明細報表" + "[" + DateTime.Now.ToString("yyyyMMddHHmm") + "].xlsx";
excel.Save();
fs.Position = 0;
return(File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
catch (Exception ex)
{
Console.Write(ex.Message);
return(null);
}
finally
{
db0.Dispose();
}
}
// GET: ExcelReport
public FileResult downloadExcel_SalesRankData()
{
ExcelPackage excel = null;
MemoryStream fs = null;
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("SalesRankData");
ExcelWorksheet sheet = excel.Workbook.Worksheets["SalesRankData"];
sheet.View.TabSelected = true;
#region 取得客戶拜訪紀錄
var items = (from x in db0.Sales
orderby x.sales_no
select(new m_Sales()
{
sales_no = x.sales_no,
sales_name = x.sales_name,
join_date = x.join_date,
tel = x.tel,
mobile = x.mobile,
zip = x.zip,
address = x.address,
rank = x.rank,
sub_count = x.SalesSub.Count()
}));
var getPrintVal = items.ToList();
#endregion
#region Excel Handle
int detail_row = 3;
#region 標題
sheet.Cells[1, 1].Value = "符合經理人資格之會員名單";
sheet.Cells[1, 1, 1, 9].Merge = true;
sheet.Cells[2, 1].Value = "[會員編號]";
sheet.Cells[2, 2].Value = "[會員名稱]";
sheet.Cells[2, 3].Value = "[加入日期]";
sheet.Cells[2, 4].Value = "[電話]";
sheet.Cells[2, 5].Value = "[手機]";
sheet.Cells[2, 6].Value = "[郵遞區號]";
sheet.Cells[2, 7].Value = "[地址]";
sheet.Cells[2, 8].Value = "[會員階級]";
sheet.Cells[2, 9].Value = "[直推會員數]";
setFontColor_Label(sheet, 2, 1, 9);
setFontColor_blue(sheet, 1, 1);
#endregion
#region 內容
foreach (var item in getPrintVal)
{
sheet.Cells[detail_row, 1].Value = item.sales_no;
sheet.Cells[detail_row, 2].Value = item.sales_name;
sheet.Cells[detail_row, 3].Value = item.join_date.ToString("yyyy/MM/dd");
sheet.Cells[detail_row, 4].Value = item.tel;
sheet.Cells[detail_row, 5].Value = item.mobile;
sheet.Cells[detail_row, 6].Value = item.zip;
sheet.Cells[detail_row, 7].Value = item.address;
sheet.Cells[detail_row, 8].Value = CodeSheet.GetStateVal(item.rank, CodeSheet.sales_rank);
sheet.Cells[detail_row, 9].Value = item.sub_count;
detail_row++;
}
#endregion
#region excel排版
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
for (int j = startColumn; j <= endColumn; j++)
{
//sheet.Column(j).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//靠左對齊
//sheet.Column(j).Width = 30;//固定寬度寫法
sheet.Column(j).AutoFit();//依內容fit寬度
}//End for
#endregion
//sheet.Cells.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的
#endregion
string filename = "會員推薦人數" + "[" + DateTime.Now.ToString("yyyyMMddHHmm") + "].xlsx";
excel.Save();
fs.Position = 0;
return(File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
catch (Exception ex)
{
Console.Write(ex.Message);
return(null);
}
finally
{
db0.Dispose();
}
}
private void makePurchase(List <m_Purchase> data, IXLWorksheet sheet)
{
sheet.Cell(1, 1).Value = "銷售明細總表";
sheet.Range(1, 1, 1, 14).Merge();
setFontColorAndBg(sheet, 1, 1, XLColor.White, XLColor.Blue);
int row_index = 2;
foreach (var i in data)
{
#region 主標題
sheet.Cell(row_index, 1).Value = "[訂單編號]";
sheet.Cell(row_index, 2).Value = "[購買人]";
sheet.Cell(row_index, 3).Value = "[付款方式]";
sheet.Cell(row_index, 4).Value = "[付款狀態]";
sheet.Cell(row_index, 5).Value = "[出貨狀態]";
sheet.Cell(row_index, 6).Value = "[下單日期]";
sheet.Cell(row_index, 7).Value = "[運費]";
sheet.Cell(row_index, 8).Value = "[手續費]";
sheet.Cell(row_index, 9).Value = "[總計金額(含運費)]";
sheet.Cell(row_index, 10).Value = "[收件人]";
sheet.Cell(row_index, 11).Value = "[收件人電話]";
sheet.Cell(row_index, 12).Value = "[收件人手機]";
sheet.Cell(row_index, 13).Value = "[收件人地址]";
sheet.Cell(row_index, 14).Value = "[收件備註]";
setFontColor_Label(sheet, row_index, row_index, 1, 14, XLColor.Blue);
row_index++;
#endregion
sheet.Cell(row_index, 1).Value = i.purchase_no;
sheet.Cell(row_index, 2).Value = i.customer_name;
sheet.Cell(row_index, 3).Value = CodeSheet.GetStateVal(i.pay_type, i_CodeName.Value, CodeSheet.IPayTypeData);
sheet.Cell(row_index, 4).Value = CodeSheet.GetStateVal(i.pay_state, i_CodeName.Value, CodeSheet.IPayStateData);
sheet.Cell(row_index, 5).Value = CodeSheet.GetStateVal(i.ship_state, i_CodeName.Value, CodeSheet.IShipStateData);
sheet.Cell(row_index, 6).Value = i.order_date.ToString("yyyy/MM/dd HH:mm");
sheet.Cell(row_index, 7).Value = i.ship_fee;
sheet.Cell(row_index, 8).Value = i.bank_charges;
sheet.Cell(row_index, 9).Value = i.total;
sheet.Cell(row_index, 10).Value = i.receive_name;
sheet.Cell(row_index, 11).Value = i.receive_tel;
sheet.Cell(row_index, 12).Value = i.receive_mobile;
sheet.Cell(row_index, 13).Value = i.receive_zip + "-" + i.receive_address;
sheet.Cell(row_index, 14).Value = i.receive_memo;
row_index++;
#region 次標題
sheet.Cell(row_index, 3).Value = "產品購買清單";
sheet.Range(row_index, 3, row_index, 9).Merge();
setFontColorAndBg(sheet, row_index, 3, XLColor.White, XLColor.DeepSkyBlue);
row_index++;
sheet.Cell(row_index, 3).Value = "[項次]";
sheet.Cell(row_index, 4).Value = "[產品料號]";
sheet.Cell(row_index, 5).Value = "[產品名稱]";
sheet.Cell(row_index, 6).Value = "[產品包裝]";
sheet.Cell(row_index, 7).Value = "[單價]";
sheet.Cell(row_index, 8).Value = "[數量]";
sheet.Cell(row_index, 9).Value = "[小計]";
setFontColor_Label(sheet, row_index, row_index, 3, 9, XLColor.Blue);
row_index++;
#endregion
int index = 1;
foreach (var detail in i.Deatil)
{
sheet.Cell(row_index, 3).Value = index;
sheet.Cell(row_index, 4).Value = detail.p_d_sn;
sheet.Cell(row_index, 5).Value = detail.p_name;
sheet.Cell(row_index, 6).Value = detail.p_d_pack_name;
sheet.Cell(row_index, 7).Value = detail.price;
sheet.Cell(row_index, 8).Value = detail.qty;
sheet.Cell(row_index, 9).Value = detail.sub_total;
row_index++;
}
row_index++;
}
sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
}
