public ActionResult GenerateReport(Int32 Event_ID, String Name)
{
ExcelPackage pck = new ExcelPackage();
String Worksheet = Name;
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(Worksheet);
ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
ExcelWorksheetView wv = ws.View;
wv.ZoomScale = 100;
DataTable dt = new DataTable(); // Read records from database here
dt.Columns.Add("Vendor", typeof(string));
dt.Columns.Add("Badge", typeof(string));
dt.Columns.Add("Status", typeof(string));
DataColumn dc = dt.Columns.Add("Invoice", typeof(Int32));
dc.AllowDBNull = true;
dt.Columns.Add("Customer", typeof(string));
List <spBadge_Export_Result> model = db.spBadge_Export(Event_ID).ToList();
Int32 Count = model.Count() + 1;
foreach (var item in model)
{
dt.Rows.Add(item.Vendor, item.Badge, item.Status, item.Invoice, item.Customer);
}
Int32 plustwo = Count + 1;
ws.Cells["A1"].LoadFromDataTable(dt, true);
ws.View.FreezePanes(2, 1);
ws.Cells.AutoFitColumns();
return(File(pck.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", Name + ".xlsx"));
}
/// <summary>
/// Sets the appropiate sheet view.
/// </summary>
/// <param name="reference">The document view properties.</param>
/// <param name="view">Document view.</param>
/// <returns>
/// An <see cref="ExcelWorksheetView"/> reference which contains the appropiate sheet view.
/// </returns>
public static ExcelWorksheetView ToEppSheetView(this ExcelWorksheetView reference, KnownDocumentView view)
{
SentinelHelper.ArgumentNull(reference, nameof(reference));
switch (view)
{
case KnownDocumentView.Design:
reference.PageLayoutView = true;
break;
case KnownDocumentView.Print:
reference.PageBreakView = true;
break;
}
return(reference);
}
public ActionResult GenerateReport(DateTime Start, DateTime End, Int32 Cat_ID, Int32 Venue_ID)
{
ExcelPackage pck = new ExcelPackage();
String Worksheet = Start.ToString("MM/dd/yy") + "-" + End.ToString("MM/dd/yy");
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(Worksheet);
ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
ExcelWorksheetView wv = ws.View;
wv.ZoomScale = 100;
DataTable dt = new DataTable(); // Read records from database here
DataColumn[] cols = { new DataColumn("Event_Date", typeof(String)),
new DataColumn("Category", typeof(String)),
new DataColumn("Event_Name", typeof(String)),
new DataColumn("Venue_Name", typeof(String)),
new DataColumn("Section", typeof(String)),
new DataColumn("Row", typeof(String)),
new DataColumn("Seats", typeof(String)),
new DataColumn("Qty", typeof(Int16)),
new DataColumn("Cost", typeof(Decimal)),
new DataColumn("Price", typeof(Decimal)),
new DataColumn("Profit", typeof(Decimal)),
new DataColumn("TotalCost", typeof(Decimal)),
new DataColumn("TotalPrice", typeof(Decimal)),
new DataColumn("TotalProfit", typeof(Decimal)),
new DataColumn("Vendor", typeof(String)),
new DataColumn("PO_Date", typeof(DateTime)),
new DataColumn("Status", typeof(String)),
new DataColumn("Invoice_ID", typeof(Int32)),
new DataColumn("Invoice_Date", typeof(DateTime)),
new DataColumn("Customer", typeof(String)),
new DataColumn("W/O", typeof(int)) };
dt.Columns.AddRange(cols);
List <spExport_EventStatus1_Result> model = db.spExport_EventStatus1(Start, End, Venue_ID, Cat_ID).ToList();
Int32 Count = model.Count() + 1;
Int32 Qty = 0;
Double Cost = 0;
Double Price = 0;
Double Profit = 0;
foreach (var item in model)
{
Qty += Convert.ToInt32(item.Qty);
Cost += Convert.ToDouble(item.Cost * item.Qty);
Price += Convert.ToDouble(item.Price * item.Qty);
Profit += Convert.ToDouble((item.Price - item.Cost) * item.Qty);
dt.Rows.Add(item.Event_Date, item.Category, item.Event_Name, item.Venue, item.Section, item.Row, item.Seats, item.Qty, item.Cost, item.Price, item.Price - item.Cost, item.Cost * item.Qty, item.Price * item.Qty, (item.Price - item.Cost) * item.Qty, item.Vendor, item.PO_Date, item.Status, item.Invoice_ID, item.Invoice_Date, item.Customer, item.Writeoff);
}
Int32 plustwo = Count + 1;
ws.Cells["A1"].LoadFromDataTable(dt, true);
ws.View.FreezePanes(2, 1);
ws.Cells["H" + plustwo.ToString()].Value = Qty;
ws.Cells["H" + plustwo.ToString()].Style.Font.Bold = true;
ws.Cells["L" + plustwo.ToString()].Value = Cost;
ws.Cells["L" + plustwo.ToString()].Style.Font.Bold = true;
ws.Cells["M" + plustwo.ToString()].Value = Price;
ws.Cells["M" + plustwo.ToString()].Style.Font.Bold = true;
ws.Cells["N" + plustwo.ToString()].Value = Profit;
ws.Cells["N" + plustwo.ToString()].Style.Font.Bold = true;
ws.Column(9).Style.Numberformat.Format = "$#,##0.00";
ws.Column(10).Style.Numberformat.Format = "$#,##0.00";
ws.Column(11).Style.Numberformat.Format = "$#,##0.00";
ws.Column(12).Style.Numberformat.Format = "$#,##0.00";
ws.Column(13).Style.Numberformat.Format = "$#,##0.00";
ws.Column(14).Style.Numberformat.Format = "$#,##0.00";
ws.Column(16).Style.Numberformat.Format = "MM/dd/yyyy";
ws.Column(19).Style.Numberformat.Format = "MM/dd/yyyy";
ws.Cells.AutoFitColumns();
return(File(pck.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "EventStatus" + ".xlsx"));
}