public ActionResult DownloadOrderExcel(string OrderID)
{
string UserID = (Session["Member"] as Member).UserID;
var orders = db.OrderHeader.Where(m => m.UserID == UserID && m.OrderID == OrderID).FirstOrDefault();
var orderDetails = db.OrderDetail.Where(m => m.OrderID == OrderID && m.Delete_Flag != true).ToList();
ExcelPackage ep = new ExcelPackage();
ExcelWorksheet sheet = ep.Workbook.Worksheets.Add("FirstSheet");
string rng = "J" + (orderDetails.Count + 1 + 1); //excel col //array+1 orders+1
ExcelTableCollection tblcollection = sheet.Tables;
ExcelTable table = tblcollection.Add(sheet.Cells["A1:" + rng], "Order");
var tOH = typeof(OrderHeaderMetaData);
var tOD = typeof(OrderDetailMetaData);
int col = 1; //欄:直的，因為要從第1欄開始，所以初始為1
table.Columns[0].Name = tOH.GetProperty("OrderID").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[1].Name = tOH.GetProperty("Receiver").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[2].Name = tOH.GetProperty("Email").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[3].Name = tOH.GetProperty("Address").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[4].Name = tOD.GetProperty("ProductID").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[5].Name = tOD.GetProperty("ProductName").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[6].Name = tOD.GetProperty("UserID").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[7].Name = tOD.GetProperty("ProductQty").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[8].Name = tOD.GetProperty("TotalPrice").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[9].Name = tOD.GetProperty("Create_Date").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
int row = 2;
foreach (OrderDetail item in orderDetails)
{
col = 1;
sheet.Cells[row, col++].Value = orders.OrderID;
sheet.Cells[row, col++].Value = orders.Receiver;
sheet.Cells[row, col++].Value = orders.Email;
sheet.Cells[row, col++].Value = orders.Address;
sheet.Cells[row, col++].Value = item.ProductID;
sheet.Cells[row, col++].Value = item.ProductName;
sheet.Cells[row, col++].Value = item.UserID;
sheet.Cells[row, col++].Value = item.ProductQty;
sheet.Cells[row, col++].Value = item.TotalPrice;
sheet.Cells[row, col++].Value = item.Create_Date.ToString();
row++;
}
MemoryStream ms = new MemoryStream();
ep.SaveAs(ms);
ep.Dispose();
ms.Position = 0;
string filename = OrderID + ".xlsx";
return(File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
public ActionResult DownloadProductExcel2()
{
var products = db.Product.ToList();
string rng = "D" + (products.Count + 1); //excel col
ExcelPackage ep = new ExcelPackage();
ExcelWorksheet sheet = ep.Workbook.Worksheets.Add("FirstSheet");
ExcelTableCollection tblcollection = sheet.Tables;
ExcelTable table = tblcollection.Add(sheet.Cells["A1:" + rng], "Product");
//get product attribute
var t = typeof(ProductMetaData);
int col = 1;
table.Columns[0].Name = t.GetProperty("ProductID").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[1].Name = t.GetProperty("ProductName").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[2].Name = t.GetProperty("ProductExplain").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[3].Name = t.GetProperty("ProductPrice").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.ShowFilter = true;
table.ShowTotal = true;
int row = 2;
foreach (Product item in products)
{
col = 1;
sheet.Cells[row, col++].Value = item.ProductID;
sheet.Cells[row, col++].Value = item.ProductName;
sheet.Cells[row, col++].Value = item.ProductExplain;
sheet.Cells[row, col++].Value = item.ProductPrice;
row++;
}
MemoryStream ms = new MemoryStream();
ep.SaveAs(ms);
ep.Dispose();
ms.Position = 0;
string filename = "AllProductList.xlsx";
return(File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
public static void WriteToFile(List <Page> pages, string FullPath)
{
ExcelPackage Ep = new ExcelPackage();
foreach (var page in pages)
{
Ep.Workbook.Worksheets.Add(page.Name);
var ws = Ep.Workbook.Worksheets.LastOrDefault();
foreach (var table in page.Tables)
{
if (table.Name != null)
{
ws.Cells[table.PosY, table.PosX, table.PosY, table.PosX + table.Columns.Count - 1].Merge = true;
ws.Cells[table.PosY, table.PosX].Value = table.Name;
}
if (table.Columns.Sum(c => c.Values.Count()) > 0)
{
foreach (var c in table.Columns)
{
ws.Cells[table.ColPosY, table.Columns.IndexOf(c) + table.PosX].Value = c.Name.Replace("__", "_");
var i = table.ColPosY;
switch (c.ColumnType.Name)
{
case "DateTime":
ws.Cells[table.ColPosY + 1, table.Columns.IndexOf(c) + table.PosX, table.ColPosY + 1 + c.Values.Count, table.Columns.IndexOf(c) + table.PosX].Style.Numberformat.Format = "yyyy MMM dd";
ws.Column(table.PosX + table.Columns.IndexOf(c)).Width = 15;
foreach (var v in c.Values)
{
ws.Cells[++i, table.Columns.IndexOf(c) + table.PosX].Value = Convert.ToDateTime(v);
}
break;
case "Decimal":
ws.Cells[table.ColPosY + 1, table.Columns.IndexOf(c) + table.PosX, table.ColPosY + 1 + c.Values.Count, table.Columns.IndexOf(c) + table.PosX].Style.Numberformat.Format = "0.".PadRight(c.Digits + 2, '0');
ws.Column(table.PosX + table.Columns.IndexOf(c)).Width = Math.Max(10, c.Values.Max(v => v.ToString().Length));
try {
foreach (var v in c.Values)
{
ws.Cells[++i, table.Columns.IndexOf(c) + table.PosX].Value = (v?.ToString() != "") ? (decimal?)Convert.ToDecimal(v) : null;
}
} catch (Exception) {
foreach (var v in c.Values)
{
ws.Cells[++i, table.Columns.IndexOf(c) + table.PosX].Value = v;
}
}
break;
default:
ws.Column(table.PosX + table.Columns.IndexOf(c)).Width = Math.Max(10, c.Values.Where(v => v != null).Max(v => v.ToString().Length + 1));
foreach (var v in c.Values)
{
ws.Cells[++i, table.Columns.IndexOf(c) + table.PosX].Value = v;
}
break;
}
}
using (ExcelRange Rng = ws.Cells[table.PosY, table.PosX, table.PosY + table.Columns[0].Values.Count, table.PosX + table.Columns.Count - 1]) {
ExcelTableCollection tblcollection = ws.Tables;
ExcelTable ootable = tblcollection.Add(Rng, "tblSalesman");
ootable.ShowFilter = true;
ootable.ShowHeader = true;
}
}
}
}
byte[] bin = Ep.GetAsByteArray();
if (!Directory.Exists(Path.GetDirectoryName(FullPath)))
{
var name = Path.GetFileName(FullPath);
FullPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), name);
}
File.WriteAllBytes(FullPath, bin);
}
public ActionResult Download_ALL_Order()
{
string UserID = Session["Member"].ToString();
var q = (from od in db.OrderDetail
join os in db.OrderHeader on od.OrderID equals os.OrderID
where od.OrderID == os.OrderID && od.UserID.Equals(UserID) && od.Delete_Flag != true
select new
{
OrderId = os.OrderID,
Receiver = os.Receiver,
Email = os.Email,
Address = os.Address,
ProductID = od.ProductID,
ProductName = od.Product.ProductName,
UserID = od.UserID,
ProductQty = od.ProductQty,
Price = os.Price,
Create_Date = od.Create_Date
}).ToList();
ExcelPackage ep = new ExcelPackage();
ExcelWorksheet sheet = ep.Workbook.Worksheets.Add("FirstSheet");
//string rng = "J" + (orderDetails.Count + 1 + 1); //excel col //array+1 orders+1
string rng = "J" + (q.Count + 1 + 1); //excel col //array+1 orders+1
ExcelTableCollection tblcollection = sheet.Tables;
ExcelTable table = tblcollection.Add(sheet.Cells["A1:" + rng], "Order");
var tOH = typeof(OrderHeaderMetaData);
var tOD = typeof(OrderDetailMetaData);
var tPD = typeof(ProductMetaData);
int col = 1; //欄:直的，因為要從第1欄開始，所以初始為1
table.Columns[0].Name = tOH.GetProperty("OrderID").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[1].Name = tOH.GetProperty("Receiver").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[2].Name = tOH.GetProperty("Email").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[3].Name = tOH.GetProperty("Address").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[4].Name = tOD.GetProperty("ProductID").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[5].Name = tPD.GetProperty("ProductName").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[6].Name = tOD.GetProperty("UserID").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[7].Name = tOD.GetProperty("ProductQty").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[8].Name = tOH.GetProperty("Price").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
table.Columns[9].Name = tOD.GetProperty("Create_Date").GetCustomAttribute <DisplayNameAttribute>().DisplayName;
int row = 2;
foreach (var item in q)
{
col = 1;
sheet.Cells[row, col++].Value = item.OrderId;
sheet.Cells[row, col++].Value = item.Receiver;
sheet.Cells[row, col++].Value = item.Email;
sheet.Cells[row, col++].Value = item.Address;
sheet.Cells[row, col++].Value = item.ProductID;
sheet.Cells[row, col++].Value = item.ProductName;
sheet.Cells[row, col++].Value = item.UserID;
sheet.Cells[row, col++].Value = item.ProductQty;
sheet.Cells[row, col++].Value = item.Price;
sheet.Cells[row, col++].Value = item.Create_Date.ToString();
row++;
}
MemoryStream ms = new MemoryStream();
ep.SaveAs(ms);
ep.Dispose();
ms.Position = 0;
string filename = UserID + ".xlsx";
return(File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
private byte[] ReturnExcell(List <Person> lst, List <string> columns)
{
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sample1");
var startFomRow = 10;
SetCaption(ws, startFomRow - 2, 1, startFomRow - 1, columns.Count);
// var title = ws.Cells[startFomRow - 2, 1, startFomRow - 1, columns.Count];
//title.Merge = true;
//title.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//title.Value = "No chart to display";
//title.Style.Font.Size = 16;
//title.Style.Fill.PatternType = ExcelFillStyle.Solid;
//title.Style.Fill.BackgroundColor.SetColor(Color.Gray);
//https://tedgustaf.com/blog/2012/create-excel-20072010-spreadsheets-with-c-and-epplus/
// Columns name
for (int i = 0; i < columns.Count; i++)
{
var row = startFomRow;
var col = i + 1;
ws.Cells[row, col].Value = columns[i];
ws.Cells[row, col].Style.Font.Bold = true;
ws.Cells[row, col].Style.Font.Size = 18;
foreach (var item in lst)
{
var type = item.GetType();
var propInfo = type.GetProperties().Where(x => x.Name == columns[i]).FirstOrDefault();
var val = propInfo.GetValue(item);
var rng = ws.Cells[++row, col];
rng.Value = val;
Regex regex = new Regex(@"\d+");
Match match = regex.Match(@"\d+(\.\d{1,2})?");
if (match.Success)
{
rng.Style.Numberformat.Format = "#,##0.00";
}
}
}
using (ExcelRange Rng = ws.Cells[startFomRow, 1, lst.Count + startFomRow, columns.Count])
{
ExcelTableCollection tblcollection = ws.Tables;
ExcelTable table = tblcollection.Add(Rng, "data");
table.ShowHeader = true;
table.ShowFilter = true;
// table.TableStyle = TableStyles.Medium2;
ws.View.ShowGridLines = false;
Rng.AutoFitColumns();
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//var modelCells = ws.Cells[startFomRow, 1, lst.Count + startFomRow, columns.Count];
//var border = Rng.Style.Border.Top.Style = Rng.Style.Border.Left.Style = Rng.Style.Border.Right.Style = Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
}
return(pck.GetAsByteArray());
// ws.View.ShowGridLines = true;
//ws.Cells["A1"].Value = "Sample 1";
//ws.Cells["A1"].Style.Font.Bold = true;
//var shape = ws.Drawings.AddShape("Shape1", eShapeStyle. );
//shape.SetPosition(50, 200);
//shape.SetSize(200, 100);
//shape.Text = "Sample 1 text text text";
}