// 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(); } }
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(); } }
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();//自動調整寬度 }