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