public FileResult downloadExcel_CustomerAgent(ParmReportR04 parm) { ExcelPackage excel = null; MemoryStream fs = null; //char test = Convert.ToChar(64 + 9); var db0 = getDB0(); try { fs = new MemoryStream(); excel = new ExcelPackage(fs); excel.Workbook.Worksheets.Add("CustomerAgentData"); ExcelWorksheet sheet = excel.Workbook.Worksheets["CustomerAgentData"]; sheet.View.TabSelected = true; #region 取得客戶進貨數量 string date_range = "(All)"; var items = from x in db0.StockDetail join y in db0.StockDetailQty on x.stock_detail_id equals y.stock_detail_id orderby x.Stock.y, x.Stock.m, x.product_id, y.customer_id select (new CustomerAgent() { stock_detail_id = x.stock_detail_id, stock_detail_qty_id = y.stock_detail_qty_id, agent_id = x.Stock.agent_id, agent_name = x.Stock.Agent.agent_name, product_id = x.product_id, product_name = x.Product.product_name, customer_id = y.customer_id, customer_name = y.Customer.customer_name, qty = y.qty, y = x.Stock.y, m = x.Stock.m, customer_type = y.Customer.customer_type, channel_type = y.Customer.channel_type, evaluate = y.Customer.evaluate, store_type = y.Customer.store_type, store_level = y.Customer.store_level, area_id = y.Customer.area_id, area_name = y.Customer.Area.area_name }); //列印月份用 List<int> months = new List<int>(); if (parm.start_date != null && parm.end_date != null) { DateTime start = (DateTime)parm.start_date; DateTime end = (DateTime)parm.end_date; if (start.Year == end.Year) {//同年 items = items.Where(x => x.y == start.Year && x.m >= start.Month && x.m <= end.Month); months = startToEndMonth(start.Month, end.Month); } else {//不同年 List<int> start_m = startMonth(start.Month); List<int> end_m = endMonth(end.Month); months.AddRange(start_m); months.AddRange(end_m); items = items.Where(x => (x.y == start.Year && start_m.Contains(x.m)) || (x.y == end.Year && end_m.Contains(x.m))); } date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")"; } if (parm.product_name != null) { items = items.Where(x => x.product_name.Contains(parm.product_name)); } if (parm.customer_name != null) { items = items.Where(x => x.customer_name.Contains(parm.customer_name)); } if (parm.customer_type != null) { items = items.Where(x => x.customer_type == parm.customer_type); } if (parm.channel_type != null) { items = items.Where(x => x.channel_type == parm.channel_type); } if (parm.evaluate != null) { items = items.Where(x => x.evaluate == parm.evaluate); } if (parm.store_type != null) { items = items.Where(x => x.store_type == parm.store_type); } if (parm.store_level != null) { items = items.Where(x => x.store_level == parm.store_level); } if (parm.area != null) { items = items.Where(x => x.area_id == parm.area); } //if (parm.months_p != null) //{ // items = items.Where(x => parm.months_p.Contains(x.m)); //} if (parm.ids != null) { items = items.Where(x => parm.ids.Contains(x.product_id)); } var getTempVal = items.ToList(); #endregion #region 整理報表列印格式 //取得每月進貨加總 var getSumMonth = from x in getTempVal group x by new { x.product_id, x.product_name, x.customer_id, x.customer_name, x.m } into g select (new CustomerAgent() { product_id = g.Key.product_id, product_name = g.Key.product_name, customer_id = g.Key.customer_id, customer_name = g.Key.customer_name, m = g.Key.m, qty = g.Sum(z => z.qty) }); //取得不重複客戶資料 var getPrintVal = (from x in getTempVal group x by new { x.product_id, x.product_name, x.customer_id, x.customer_name, x.customer_type, x.channel_type, x.evaluate, x.store_type, x.store_level, x.area_name } into g orderby g.Key.product_id, g.Key.customer_id select (new ExcleCustomerAgent() { product_id = g.Key.product_id, product_name = g.Key.product_name, customer_id = g.Key.customer_id, customer_name = g.Key.customer_name, customer_type = g.Key.customer_type, channel_type = g.Key.channel_type, evaluate = g.Key.evaluate, store_type = g.Key.store_type, store_level = g.Key.store_level, area_name = g.Key.area_name })).ToList(); foreach (var itemA in getPrintVal) { foreach (var itemB in getSumMonth) { if (itemA.customer_id == itemB.customer_id && itemA.product_id == itemB.product_id) { itemA.sum_qtys += itemB.qty; switch (itemB.m) { case 1: itemA.qty_1 = itemB.qty; break; case 2: itemA.qty_2 = itemB.qty; break; case 3: itemA.qty_3 = itemB.qty; break; case 4: itemA.qty_4 = itemB.qty; break; case 5: itemA.qty_5 = itemB.qty; break; case 6: itemA.qty_6 = itemB.qty; break; case 7: itemA.qty_7 = itemB.qty; break; case 8: itemA.qty_8 = itemB.qty; break; case 9: itemA.qty_9 = itemB.qty; break; case 10: itemA.qty_10 = itemB.qty; break; case 11: itemA.qty_11 = itemB.qty; break; case 12: itemA.qty_12 = itemB.qty; break; default: break; } } } } #endregion #region Excel Handle int detail_row = 4; #region 標題 sheet.Cells[1, 1].Value = "R05客戶進貨統計表(客戶-多經銷商)" + date_range; //sheet.Cells[1, 1, 1, 8].Merge = true; sheet.Cells[2, 1].Value = "[產品名稱]"; sheet.Cells[2, 2].Value = "[客戶名稱]"; sheet.Cells[2, 3].Value = "[區域\n群組]"; sheet.Cells[2, 4].Value = "[客戶\n類別]"; sheet.Cells[2, 5].Value = "[通路\n級別]"; sheet.Cells[2, 6].Value = "[客戶\n銷售等級]"; sheet.Cells[2, 7].Value = "[客戶\n型態]"; sheet.Cells[2, 8].Value = "[型態\n等級]"; //setMerge_label(sheet, 2, 3, 1, 8);//上下合併儲存格 setWrapText(sheet, 2, 3, 8);// \n換行設定 const int month_start = 9; int month_end = month_start + months.Count() - 1; int temp_index = month_start; foreach (var i in months) { sheet.Cells[3, temp_index].Value = "[" + i + "月份]"; temp_index++; } sheet.Cells[3, temp_index].Value = "[加總]"; sheet.Cells[2, month_start].Value = date_range + "產品進貨數量(" + months[0] + "~" + months[months.Count() - 1] + "月)"; //sheet.Cells[2, month_start, 2, month_end].Merge = true; setFontColor_LabelBord(sheet, 2, 1, month_end);//儲存格框線+藍字 setFontColor_LabelBord(sheet, 3, 1, month_end); setFontColor_blue(sheet, 1, 1); setFontColor_red(sheet, 3, month_end + 1);//紅字 sheet.Cells[3, month_end + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; #endregion #region 內容 decimal[] row_sum = new decimal[12];//底部加總計算 decimal[] row_subtotal = new decimal[12];//每個產品小計計算 string subtotal_product_name = string.Empty; foreach (var item in getPrintVal) { if (item.sum_qtys != 0)//如果1~12月都沒有進貨,就不顯示 { #region 小計判斷 if (detail_row == 4) { subtotal_product_name = item.product_name;//第一筆資料 } else if (subtotal_product_name != item.product_name)//產品變換時,做一次小計 { #region 小計 #region 小計欄位,合併及文字顏色 sheet.Cells[detail_row, 8].Value = "[小計]"; setFontColor_red(sheet, detail_row, 8); sheet.Cells[detail_row, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //sheet.Cells[detail_row, 1, detail_row, 8].Merge = true; #endregion temp_index = month_start; foreach (var i in months) { sheet.Cells[detail_row, temp_index].Value = row_subtotal[i - 1]; sheet.Cells[detail_row, temp_index].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[detail_row, temp_index].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); temp_index++; } #endregion #region 不同產品區分版面 setBroder_red(sheet, detail_row + 1, 1, month_end); detail_row += 1; #endregion detail_row++; row_subtotal = new decimal[12];//小計歸零 subtotal_product_name = item.product_name;//紀錄新產品 } #endregion sheet.Cells[detail_row, 1].Value = item.product_name; sheet.Cells[detail_row, 2].Value = item.customer_name; sheet.Cells[detail_row, 3].Value = item.area_name; sheet.Cells[detail_row, 4].Value = CodeSheet.GetCustomerTypeVal(item.customer_type); sheet.Cells[detail_row, 5].Value = CodeSheet.GetChannelTypeVal(item.channel_type); sheet.Cells[detail_row, 6].Value = CodeSheet.GetEvaluateVal(item.evaluate); sheet.Cells[detail_row, 7].Value = CodeSheet.GetStoreTypeVal(item.store_type); sheet.Cells[detail_row, 8].Value = CodeSheet.GetStoreLevelVal(item.store_level); temp_index = month_start; foreach (var i in months) { #region getQtyVal decimal temp_qyt = 0; switch (i) { case 1: temp_qyt = item.qty_1; break; case 2: temp_qyt = item.qty_2; break; case 3: temp_qyt = item.qty_3; break; case 4: temp_qyt = item.qty_4; break; case 5: temp_qyt = item.qty_5; break; case 6: temp_qyt = item.qty_6; break; case 7: temp_qyt = item.qty_7; break; case 8: temp_qyt = item.qty_8; break; case 9: temp_qyt = item.qty_9; break; case 10: temp_qyt = item.qty_10; break; case 11: temp_qyt = item.qty_11; break; case 12: temp_qyt = item.qty_12; break; default: break; } #endregion sheet.Cells[detail_row, temp_index].Value = temp_qyt; temp_index++; } sheet.Cells[detail_row, month_end + 1].Formula = string.Format("=SUM(I{0}:{1}{0})", detail_row, Convert.ToChar(64 + month_end)); #region 小計加總計算 row_subtotal[0] += item.qty_1; row_subtotal[1] += item.qty_2; row_subtotal[2] += item.qty_3; row_subtotal[3] += item.qty_4; row_subtotal[4] += item.qty_5; row_subtotal[5] += item.qty_6; row_subtotal[6] += item.qty_7; row_subtotal[7] += item.qty_8; row_subtotal[8] += item.qty_9; row_subtotal[9] += item.qty_10; row_subtotal[10] += item.qty_11; row_subtotal[11] += item.qty_12; #endregion #region 底部加總計算 row_sum[0] += item.qty_1; row_sum[1] += item.qty_2; row_sum[2] += item.qty_3; row_sum[3] += item.qty_4; row_sum[4] += item.qty_5; row_sum[5] += item.qty_6; row_sum[6] += item.qty_7; row_sum[7] += item.qty_8; row_sum[8] += item.qty_9; row_sum[9] += item.qty_10; row_sum[10] += item.qty_11; row_sum[11] += item.qty_12; #endregion detail_row++; } } #region 最後一次小計 #region 小計欄位,合併及文字顏色 sheet.Cells[detail_row, 8].Value = "[小計]"; setFontColor_red(sheet, detail_row, 8); sheet.Cells[detail_row, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //sheet.Cells[detail_row, 1, detail_row, 8].Merge = true; #endregion temp_index = month_start; foreach (var i in months) { sheet.Cells[detail_row, temp_index].Value = row_subtotal[i - 1]; sheet.Cells[detail_row, temp_index].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[detail_row, temp_index].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); temp_index++; } #region 不同產品區分版面 setBroder_red(sheet, detail_row + 1, 1, month_end); detail_row += 1; #endregion detail_row++; #endregion #region 底部加總 #region 加總欄位,合併及文字顏色 sheet.Cells[detail_row, 1].Value = "[加總]"; setFontColor_red(sheet, detail_row, 1); sheet.Cells[detail_row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //sheet.Cells[detail_row, 1, detail_row, 8].Merge = true; #endregion temp_index = month_start; foreach (var i in months) { sheet.Cells[detail_row, temp_index].Value = row_sum[i - 1]; sheet.Cells[detail_row, temp_index].Style.Border.Top.Style = ExcelBorderStyle.Double; sheet.Cells[detail_row, temp_index].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); temp_index++; } #endregion #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.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的 #endregion string filename = "R05客戶進貨統計表(客戶-多經銷商)" + "[" + 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_CustomerProduct(ParmReportR04 parm) { ExcelPackage excel = null; MemoryStream fs = null; var db0 = getDB0(); try { fs = new MemoryStream(); excel = new ExcelPackage(fs); excel.Workbook.Worksheets.Add("CustomerProductData"); ExcelWorksheet sheet = excel.Workbook.Worksheets["CustomerProductData"]; sheet.View.TabSelected = true; #region 取得客戶進貨數量 string date_range = "(All)"; var items = from x in db0.StockDetail join y in db0.StockDetailQty on x.stock_detail_id equals y.stock_detail_id orderby y.Customer.customer_name where parm.ids.Contains(x.product_id) select (new CustomerProduct() { agent_id = x.Stock.agent_id, agent_name = x.Stock.Agent.agent_name, product_id = x.product_id, product_name = x.Product.product_name, customer_id = y.customer_id, customer_name = y.Customer.customer_name, channel_type = y.Customer.channel_type, customer_type = y.Customer.customer_type, evaluate = y.Customer.evaluate, store_type = y.Customer.store_type, store_level = y.Customer.store_level, area_id = y.Customer.area_id, area_name = y.Customer.Area.area_name, qty = y.qty, y = x.Stock.y, m = x.Stock.m }); if (parm.start_date != null && parm.end_date != null) { DateTime start = (DateTime)parm.start_date; DateTime end = (DateTime)parm.end_date; if (start.Year == end.Year) {//同年 items = items.Where(x => x.y == start.Year && x.m >= start.Month && x.m <= end.Month); } else {//不同年 List<int> start_m = startMonth(start.Month); List<int> end_m = endMonth(end.Month); items = items.Where(x => (x.y == start.Year && start_m.Contains(x.m)) || (x.y == end.Year && end_m.Contains(x.m))); } date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")"; } if (parm.customer_name != null) { items = items.Where(x => x.customer_name.Contains(parm.customer_name)); } if (parm.product_name != null) { items = items.Where(x => x.product_name.Contains(parm.product_name)); } if (parm.customer_type != null) { items = items.Where(x => x.customer_type == parm.customer_type); } if (parm.channel_type != null) { items = items.Where(x => x.channel_type == parm.channel_type); } if (parm.evaluate != null) { items = items.Where(x => x.evaluate == parm.evaluate); } if (parm.store_type != null) { items = items.Where(x => x.store_type == parm.store_type); } if (parm.store_level != null) { items = items.Where(x => x.store_level == parm.store_level); } if (parm.area != null) { items = items.Where(x => x.area_id == parm.area); } var getTempVal = items.ToList(); foreach (var item in getTempVal) { if (item.qty > 0) { item.distributed = true; } } #endregion #region 整理報表列印格式 //取得進貨加總 var getSum = from x in getTempVal group x by new { x.product_id, x.product_name, x.customer_id, x.customer_name, } into g select (new CustomerProduct() { product_id = g.Key.product_id, product_name = g.Key.product_name, customer_id = g.Key.customer_id, customer_name = g.Key.customer_name, qty = g.Sum(z => z.qty) }); //取得不重複客戶資料 var getPrintVal = (from x in getTempVal group x by new { x.customer_id, x.customer_name, x.customer_type, x.channel_type, x.evaluate, x.store_type, x.store_level, x.area_name } into g orderby g.Key.customer_id select (new ExcelCustomerProduct() { customer_id = g.Key.customer_id, customer_name = g.Key.customer_name, customer_type = g.Key.customer_type, channel_type = g.Key.channel_type, evaluate = g.Key.evaluate, store_type = g.Key.store_type, store_level = g.Key.store_level, area_name = g.Key.area_name })).ToList(); foreach (var itemA in getPrintVal) { itemA.p_qtys = new List<PQList>(); #region 設定產品分部統計版型變數 foreach (var id in parm.ids) { itemA.p_qtys.Add(new PQList() { p_id = id, qty = 0, stock_qty = 0 }); } #endregion decimal sum_qty = 0;//加總判斷,如果加總為零就不顯示 foreach (var itemB in getSum) { if (itemA.customer_id == itemB.customer_id) { if (parm.ids.Contains(itemB.product_id)) { //itemA.p_qtys.Add(itemB.qty); //改為統計產品分布,不是進貨數量 if (itemB.qty != 0) { var getPQList = itemA.p_qtys.Where(x => x.p_id == itemB.product_id).First(); getPQList.qty = 1; getPQList.stock_qty = itemB.qty; } sum_qty += itemB.qty; } } } if (sum_qty == 0) { itemA.is_hide = true; } } #endregion #region Excel Handle int detail_row = 5; #region 標題 sheet.Cells[1, 1].Value = "R03產品分佈統計表(客戶-產品)" + date_range; //sheet.Cells[1, 1, 1, 7].Merge = true; sheet.Cells[2, 1].Value = "[客戶名稱]"; sheet.Cells[2, 2].Value = "[區域\n群組]"; sheet.Cells[2, 3].Value = "[客戶\n類別]"; sheet.Cells[2, 4].Value = "[通路\n級別]"; sheet.Cells[2, 5].Value = "[客戶\n銷售等級]"; sheet.Cells[2, 6].Value = "[客戶\n型態]"; sheet.Cells[2, 7].Value = "[型態\n等級]"; setWrapText(sheet, 2, 2, 7);//換行設定 const int product_column = 8;//設定產品列起始列 int name_index = product_column; foreach (var i in parm.names) { sheet.Cells[3, name_index].Value = "[" + i + "]"; //sheet.Cells[3, name_index, 3, name_index + 1].Merge = true; sheet.Cells[4, name_index].Value = "分布"; sheet.Cells[4, name_index + 1].Value = "進貨量"; name_index += 2; } sheet.Cells[2, product_column].Value = "產品分布"; //sheet.Cells[2, product_column, 2, name_index - 1].Merge = true; //setMerge_label(sheet, 2, 4, 1, 7);//合併上下儲存格 客戶名稱~型態等級 setFontColor_LabelBord(sheet, 2, 1, name_index - 1);//儲存格畫線+文字藍色 setFontColor_LabelBord(sheet, 3, 1, name_index - 1); setFontColor_LabelBord(sheet, 4, 1, name_index - 1); setFontColor_blue(sheet, 1, 1); #endregion #region 內容 decimal[] row_sum = new decimal[parm.ids.Count()];//計算底部加總_分布 decimal[] row_stock_sum = new decimal[parm.ids.Count()];//計算底部加總_進貨量 foreach (var item in getPrintVal) { if (!item.is_hide)//沒進貨量就不顯示 { sheet.Cells[detail_row, 1].Value = item.customer_name; sheet.Cells[detail_row, 2].Value = item.area_name; sheet.Cells[detail_row, 3].Value = CodeSheet.GetCustomerTypeVal(item.customer_type); sheet.Cells[detail_row, 4].Value = CodeSheet.GetChannelTypeVal(item.channel_type); sheet.Cells[detail_row, 5].Value = CodeSheet.GetEvaluateVal(item.evaluate); sheet.Cells[detail_row, 6].Value = CodeSheet.GetStoreTypeVal(item.store_type); sheet.Cells[detail_row, 7].Value = CodeSheet.GetStoreLevelVal(item.store_level); int qty_index = product_column; foreach (var i in item.p_qtys) { sheet.Cells[detail_row, qty_index].Value = i.qty;//分布 sheet.Cells[detail_row, qty_index + 1].Value = i.stock_qty;//進貨量 row_sum[(qty_index - product_column) / 2] += i.qty;//分布加總 row_stock_sum[(qty_index - product_column) / 2] += i.stock_qty;//進貨量加總 qty_index += 2; } detail_row++; } } #region 底部加總 sheet.Cells[detail_row, 1].Value = "[分布統計加總]"; setFontColor_red(sheet, detail_row, 1); sheet.Cells[detail_row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //sheet.Cells[detail_row, 1, detail_row, 7].Merge = true; for (var i = 0; i < parm.ids.Count(); i++) { //分布 sheet.Cells[detail_row, (i * 2) + product_column].Value = row_sum[i]; sheet.Cells[detail_row, (i * 2) + product_column].Style.Border.Top.Style = ExcelBorderStyle.Double; sheet.Cells[detail_row, (i * 2) + product_column].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); //進貨量 sheet.Cells[detail_row, (i * 2) + product_column + 1].Value = row_stock_sum[i]; sheet.Cells[detail_row, (i * 2) + product_column + 1].Style.Border.Top.Style = ExcelBorderStyle.Double; sheet.Cells[detail_row, (i * 2) + product_column + 1].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); } #endregion #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 = "R03產品分佈統計表(客戶-產品)" + "[" + 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_ProductCustomer(ParmReportR04 parm) { ExcelPackage excel = null; MemoryStream fs = null; var db0 = getDB0(); try { fs = new MemoryStream(); excel = new ExcelPackage(fs); excel.Workbook.Worksheets.Add("ProductCustomerData"); ExcelWorksheet sheet = excel.Workbook.Worksheets["ProductCustomerData"]; sheet.View.TabSelected = true; #region 取得客戶進貨數量 string date_range = "(All)"; var items = from x in db0.StockDetail join y in db0.StockDetailQty on x.stock_detail_id equals y.stock_detail_id orderby x.Product.product_name select (new CustomerProduct() { product_id = x.product_id, product_name = x.Product.product_name, customer_id = y.customer_id, customer_name = y.Customer.customer_name, channel_type = y.Customer.channel_type, customer_type = y.Customer.customer_type, evaluate = y.Customer.evaluate, store_type = y.Customer.store_type, store_level = y.Customer.store_level, area_id = y.Customer.area_id, area_name = y.Customer.Area.area_name, qty = y.qty, y = x.Stock.y, m = x.Stock.m }); if (parm.start_date != null && parm.end_date != null) { DateTime start = (DateTime)parm.start_date; DateTime end = (DateTime)parm.end_date; if (start.Year == end.Year) {//同年 items = items.Where(x => x.y == start.Year && x.m >= start.Month && x.m <= end.Month); } else {//不同年 List<int> start_m = startMonth(start.Month); List<int> end_m = endMonth(end.Month); items = items.Where(x => (x.y == start.Year && start_m.Contains(x.m)) || (x.y == end.Year && end_m.Contains(x.m))); } date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")"; } if (parm.customer_name != null) { items = items.Where(x => x.customer_name.Contains(parm.customer_name)); } if (parm.product_name != null) { items = items.Where(x => x.product_name.Contains(parm.product_name)); } if (parm.customer_type != null) { items = items.Where(x => x.customer_type == parm.customer_type); } if (parm.channel_type != null) { items = items.Where(x => x.channel_type == parm.channel_type); } if (parm.evaluate != null) { items = items.Where(x => x.evaluate == parm.evaluate); } if (parm.store_type != null) { items = items.Where(x => x.store_type == parm.store_type); } if (parm.store_level != null) { items = items.Where(x => x.store_level == parm.store_level); } if (parm.area != null) { items = items.Where(x => x.area_id == parm.area); } if (parm.ids != null) { items = items.Where(x => parm.ids.Contains(x.product_id)); } var getTempVal = items.ToList(); var getPrintVal = (from x in getTempVal group x by new { x.product_id, x.product_name, x.customer_id, x.customer_name, x.customer_type, x.channel_type, x.evaluate, x.store_type, x.store_level, x.area_name } into g select (new CustomerProduct() { product_id = g.Key.product_id, product_name = g.Key.product_name, customer_id = g.Key.customer_id, customer_name = g.Key.customer_name, customer_type = g.Key.customer_type, channel_type = g.Key.channel_type, evaluate = g.Key.evaluate, store_type = g.Key.store_type, store_level = g.Key.store_level, area_name = g.Key.area_name, qty = g.Sum(z => z.qty) })).ToList(); foreach (var item in getPrintVal) { if (item.qty > 0) { item.distributed = true; } } #endregion #region Excel Handle int detail_row = 3; #region 標題 sheet.Cells[1, 1].Value = "R04產品分佈統計表(產品-客戶)" + date_range; //sheet.Cells[1, 1, 1, 5].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 = "[客戶型態]"; sheet.Cells[2, 10].Value = "[型態等級]"; setFontColor_Label(sheet, 2, 1, 10); setFontColor_blue(sheet, 1, 1); #endregion #region 內容 string subtotal_product_name = string.Empty; int subtotal_distributed = 0;//分布 小計 decimal subtotal_qty = 0;//進貨量 小計 foreach (var item in getPrintVal) { if (item.distributed)//沒分布就不顯示 { #region 小計判斷 if (detail_row == 3) { subtotal_product_name = item.product_name;//第一筆資料 } else if (subtotal_product_name != item.product_name)//產品變換時,做一次小計 { #region 小計 #region 小計欄位,合併及文字顏色 sheet.Cells[detail_row, 3].Value = "[小計]"; setFontColor_red(sheet, detail_row, 3); sheet.Cells[detail_row, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //sheet.Cells[detail_row, 1, detail_row, 2].Merge = true; #endregion //產品分布 sheet.Cells[detail_row, 4].Value = subtotal_distributed; sheet.Cells[detail_row, 4].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[detail_row, 4].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); //進貨量 sheet.Cells[detail_row, 5].Value = subtotal_qty; sheet.Cells[detail_row, 5].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[detail_row, 5].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); #endregion #region 不同產品區分版面 setBroder_red(sheet, detail_row + 1, 1, 10); detail_row += 1; #endregion detail_row++; subtotal_distributed = 0;//小計歸零 subtotal_qty = 0;//小計歸零 subtotal_product_name = item.product_name;//紀錄新產品 } #endregion sheet.Cells[detail_row, 1].Value = item.product_name; sheet.Cells[detail_row, 2].Value = item.customer_name; sheet.Cells[detail_row, 3].Value = item.area_name; sheet.Cells[detail_row, 4].Value = item.distributed ? "Yes" : "No"; if (item.distributed) { setFontColor_red(sheet, detail_row, 4); } sheet.Cells[detail_row, 5].Value = item.qty; sheet.Cells[detail_row, 6].Value = CodeSheet.GetCustomerTypeVal(item.customer_type); sheet.Cells[detail_row, 7].Value = CodeSheet.GetChannelTypeVal(item.channel_type); sheet.Cells[detail_row, 8].Value = CodeSheet.GetEvaluateVal(item.evaluate); sheet.Cells[detail_row, 9].Value = CodeSheet.GetStoreTypeVal(item.store_type); sheet.Cells[detail_row, 10].Value = CodeSheet.GetStoreLevelVal(item.store_level); #region 小計加總計算 subtotal_distributed++; subtotal_qty += item.qty; #endregion detail_row++; } } #region 最後一次小計 #region 小計欄位,合併及文字顏色 sheet.Cells[detail_row, 3].Value = "[小計]"; setFontColor_red(sheet, detail_row, 3); sheet.Cells[detail_row, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //sheet.Cells[detail_row, 1, detail_row, 2].Merge = true; #endregion //產品分布 sheet.Cells[detail_row, 4].Value = subtotal_distributed; sheet.Cells[detail_row, 4].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[detail_row, 4].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); //進貨量 sheet.Cells[detail_row, 5].Value = subtotal_qty; sheet.Cells[detail_row, 5].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[detail_row, 5].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red); #region 不同產品區分版面 setBroder_red(sheet, detail_row + 1, 1, 10); detail_row += 1; #endregion detail_row++; #endregion #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 = "R04產品分佈統計表(產品-客戶)" + "[" + 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(); } }