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"; }