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