protected void Button2_OnClick(object sender, EventArgs e)
{
//Uses DataClassesDataContext from LinqToSql to obtain connection to data from the database
DataClassesDataContext dtContext = new DataClassesDataContext();
var beginDate = Convert.ToDateTime(Calendar1.SelectedDate.ToShortDateString());
var endDate = Convert.ToDateTime(Calendar2.SelectedDate.ToShortDateString());
//LinqToSql query to grab data for all available records
var query = (from viewApex in dtContext.viewApexes
where viewApex.Order_Date >= beginDate && viewApex.Due_Date <= endDate
orderby viewApex.Account_Number
select new { viewApex.Sold_At, viewApex.Sold_To, viewApex.Account_Number, viewApex.Invoice__, viewApex.Customer_PO__, viewApex.OrderDateText, viewApex.DueDateText, viewApex.Invoice_Total });
//Creates a list to obtain the records from the LinqToSql query
List <List <string> > data = new List <List <string> >();
foreach (dynamic item in query)
{
Console.WriteLine(item);
var Sold_At = (item.Sold_At);
var Sold_To = (item.Sold_To);
var Account_Number = (item.Account_Number);
var Invoice__ = (item.Invoice__);
var Customer_PO__ = (item.Customer_PO__);
var Order_Date = (item.OrderDateText);
var Due_Date = (item.DueDateText);
var Invoice_Total = (item.Invoice_Total);
data.Add(new List <String> {
Sold_At, Sold_To, Account_Number, Invoice__, Customer_PO__, Convert.ToString(Order_Date), Convert.ToString(Due_Date), Convert.ToString(Invoice_Total)
});
}
query.AsEnumerable();
//Creation of the Excel Package
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Output");
//Defines the column names for the Excel output
IEnumerable <String> columnnames = new string[] { "Sold At", "Sold To", "Account Number", "Invoice #", "Customer PO #", "Order Date", "Due Date", "Invoice Total" };
//Utilizes methods from the prvoided SpreadsheetBuilderWithExample.linq (pasted the code into the Class2.cs file)
SpreadsheetBuilder.SaveData(workSheet, columnnames, data);
//Process to save the file into an Excel file
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Excel.xlsx");
MemoryStream stream = new MemoryStream(excel.GetAsByteArray());
Response.OutputStream.Write(stream.ToArray(), 0, stream.ToArray().Length);
Response.Flush();
Response.End();
Response.Close();
}
static void Main(string[] args)
{
Spreadsheet sheet = null;
SpreadsheetBuilder spreadsheetBuilder = new SpreadsheetBuilder();
while (!spreadsheetBuilder.Build(ref sheet))
{
}
spreadsheetBuilder.Read(sheet);
spreadsheetBuilder.Calculate(sheet);
spreadsheetBuilder.Display(sheet);
}
