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_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();
}
}
