/// <summary>
/// 銷售明細總表
/// </summary>
/// <param name="no"></param>
/// <returns></returns>
public FileResult downloadExcel_PurchaseDataAll(ParmGetAllPurchase 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["銷售明細總表"];
string date_range = "All";
sheet.View.TabSelected = true;
#region 取得客戶銷售紀錄
var items = db0.Purchase.AsQueryable();
if (q.start_date != null && q.end_date != null)
{
DateTime end = ((DateTime)q.end_date).AddDays(1);
items = items.Where(x => x.set_date >= q.start_date && x.set_date < end);
date_range = "(" + ((DateTime)q.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)q.end_date).ToString("yyyy/MM/dd") + ")";
}
if (q.keyword != null)
{
items = items.Where(x => x.purchase_no
.StartsWith(q.keyword) || x.sales_name.StartsWith(q.keyword));
}
if (q.source != null)
{
items = items.Where(x => x.source == q.source);
}
if (q.state != null)
{
items = items.Where(x => x.state == q.state);
}
var print = items.ToList();
#endregion
#region Excel Handle
int detail_row = 2;
#region 內容
#region 總標題
sheet.Cells[1, 1].Value = "銷售明細總表" + date_range;
sheet.Cells[1, 1, 1, 10].Merge = true;
setFontColorAndBg_Blue(sheet, 1, 1);
#endregion
foreach (var item in print)
{
#region 主標題
sheet.Cells[detail_row, 1].Value = "[訂單編號]";
sheet.Cells[detail_row, 2].Value = "[會員名稱]";
sheet.Cells[detail_row, 3].Value = "[購買日期]";
sheet.Cells[detail_row, 4].Value = "[總計金額(含運費)]";
sheet.Cells[detail_row, 5].Value = "[運費]";
sheet.Cells[detail_row, 6].Value = "[收件人]";
sheet.Cells[detail_row, 7].Value = "[電話]";
sheet.Cells[detail_row, 8].Value = "[手機]";
sheet.Cells[detail_row, 9].Value = "[地址]";
sheet.Cells[detail_row, 10].Value = "[備註]";
setFontColor_Label(sheet, detail_row, 1, 10);
detail_row++;
#endregion
sheet.Cells[detail_row, 1].Value = item.purchase_no;
sheet.Cells[detail_row, 2].Value = item.sales_name;
sheet.Cells[detail_row, 3].Value = item.set_date.ToString("yyyy/MM/dd");
sheet.Cells[detail_row, 4].Value = item.total;
sheet.Cells[detail_row, 5].Value = item.shipping_fee;
sheet.Cells[detail_row, 6].Value = item.receive_person;
sheet.Cells[detail_row, 7].Value = item.receive_tel;
sheet.Cells[detail_row, 8].Value = item.receive_mobile;
sheet.Cells[detail_row, 9].Value = item.receive_zip + " " + item.receive_address;
sheet.Cells[detail_row, 10].Value = item.receive_memo;
detail_row++;
#region 次標題
sheet.Cells[detail_row, 3].Value = "產品購買清單";
sheet.Cells[detail_row, 3, detail_row, 8].Merge = true;
setFontColorAndBg_DeepSkyBlue(sheet, detail_row, 3);
detail_row++;
sheet.Cells[detail_row, 3].Value = "[項次]";
sheet.Cells[detail_row, 4].Value = "[品號]";
sheet.Cells[detail_row, 5].Value = "[品名]";
sheet.Cells[detail_row, 6].Value = "[單價]";
sheet.Cells[detail_row, 7].Value = "[數量]";
sheet.Cells[detail_row, 8].Value = "[小計]";
setFontColor_Label(sheet, detail_row, 3, 8);
detail_row++;
#endregion
foreach (var detail in item.PurchaseDetail)
{
sheet.Cells[detail_row, 3].Value = detail.item_no;
sheet.Cells[detail_row, 4].Value = detail.product_no;
sheet.Cells[detail_row, 5].Value = detail.product_name;
sheet.Cells[detail_row, 6].Value = detail.price;
sheet.Cells[detail_row, 7].Value = detail.qty;
sheet.Cells[detail_row, 8].Value = detail.sub_total;
detail_row++;
}
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 = "銷售明細總表" + date_range + "[" + 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();
}
}
/// <summary>
/// 銷售明細總表
/// </summary>
/// <param name="no"></param>
/// <returns></returns>
public FileResult downloadExcel_PurchaseDataAll(ParmGetAllPurchase 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["銷售明細總表"];
string date_range = "All";
sheet.View.TabSelected = true;
#region 取得客戶銷售紀錄
var items = db0.Purchase.AsQueryable();
if (q.start_date != null && q.end_date != null)
{
DateTime end = ((DateTime)q.end_date).AddDays(1);
items = items.Where(x => x.set_date >= q.start_date && x.set_date < end);
date_range = "(" + ((DateTime)q.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)q.end_date).ToString("yyyy/MM/dd") + ")";
}
if (q.keyword != null)
{
items = items.Where(x => x.purchase_no
.StartsWith(q.keyword) || x.sales_name.StartsWith(q.keyword));
}
if (q.source != null)
{
items = items.Where(x => x.source == q.source);
}
if (q.state != null)
{
items = items.Where(x => x.state == q.state);
}
var print = items.ToList();
#endregion
#region Excel Handle
int detail_row = 2;
#region 內容
#region 總標題
sheet.Cells[1, 1].Value = "銷售明細總表" + date_range;
sheet.Cells[1, 1, 1, 10].Merge = true;
setFontColorAndBg_Blue(sheet, 1, 1);
#endregion
foreach (var item in print)
{
#region 主標題
sheet.Cells[detail_row, 1].Value = "[訂單編號]";
sheet.Cells[detail_row, 2].Value = "[會員名稱]";
sheet.Cells[detail_row, 3].Value = "[購買日期]";
sheet.Cells[detail_row, 4].Value = "[總計金額(含運費)]";
sheet.Cells[detail_row, 5].Value = "[運費]";
sheet.Cells[detail_row, 6].Value = "[收件人]";
sheet.Cells[detail_row, 7].Value = "[電話]";
sheet.Cells[detail_row, 8].Value = "[手機]";
sheet.Cells[detail_row, 9].Value = "[地址]";
sheet.Cells[detail_row, 10].Value = "[備註]";
setFontColor_Label(sheet, detail_row, 1, 10);
detail_row++;
#endregion
sheet.Cells[detail_row, 1].Value = item.purchase_no;
sheet.Cells[detail_row, 2].Value = item.sales_name;
sheet.Cells[detail_row, 3].Value = item.set_date.ToString("yyyy/MM/dd");
sheet.Cells[detail_row, 4].Value = item.total;
sheet.Cells[detail_row, 5].Value = item.shipping_fee;
sheet.Cells[detail_row, 6].Value = item.receive_person;
sheet.Cells[detail_row, 7].Value = item.receive_tel;
sheet.Cells[detail_row, 8].Value = item.receive_mobile;
sheet.Cells[detail_row, 9].Value = item.receive_zip + " " + item.receive_address;
sheet.Cells[detail_row, 10].Value = item.receive_memo;
detail_row++;
#region 次標題
sheet.Cells[detail_row, 3].Value = "產品購買清單";
sheet.Cells[detail_row, 3, detail_row, 8].Merge = true;
setFontColorAndBg_DeepSkyBlue(sheet, detail_row, 3);
detail_row++;
sheet.Cells[detail_row, 3].Value = "[項次]";
sheet.Cells[detail_row, 4].Value = "[品號]";
sheet.Cells[detail_row, 5].Value = "[品名]";
sheet.Cells[detail_row, 6].Value = "[單價]";
sheet.Cells[detail_row, 7].Value = "[數量]";
sheet.Cells[detail_row, 8].Value = "[小計]";
setFontColor_Label(sheet, detail_row, 3, 8);
detail_row++;
#endregion
foreach (var detail in item.PurchaseDetail)
{
sheet.Cells[detail_row, 3].Value = detail.item_no;
sheet.Cells[detail_row, 4].Value = detail.product_no;
sheet.Cells[detail_row, 5].Value = detail.product_name;
sheet.Cells[detail_row, 6].Value = detail.price;
sheet.Cells[detail_row, 7].Value = detail.qty;
sheet.Cells[detail_row, 8].Value = detail.sub_total;
detail_row++;
}
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 = "銷售明細總表" + date_range + "[" + 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();
}
}