private void AddOrderLines(ExcelPackage excelFile, OrderExportQuery exportQuery)
{
ExcelWorksheet ordersWorksheet = excelFile.Workbook.Worksheets.Add("Order Lines");
IList <OrderLine> orderLines = GetOrderLines(exportQuery);
Dictionary <string, Func <OrderLine, object> > columns = GetOrderLineColumns();
List <string> keys = columns.Keys.ToList();
for (int index = 0; index < keys.Count; index++)
{
string key = keys[index];
var cell = ordersWorksheet.Cells[1, index + 1];
cell.Value = _stringResourceProvider.GetValue("Excel Order Line Export - " + key, key);
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
for (int i = 0; i < orderLines.Count; i++)
{
var orderLine = orderLines[i];
for (int index = 0; index < keys.Count; index++)
{
string key = keys[index];
var row = i + 2; // +1 for the non-zero-based index and +1 for the header row
var cell = ordersWorksheet.Cells[row, index + 1];
var value = columns[key](orderLine);
cell.Value = value;
if (value is DateTime)
{
cell.Style.Numberformat.Format = "dd/mm/yyyy hh:mm:ss";
}
}
}
ordersWorksheet.Cells.AutoFitColumns();
}
private IList <OrderLine> GetOrderLines(OrderExportQuery exportQuery)
{
return(_session.QueryOver <OrderLine>()
.WithSubquery.WhereProperty(line => line.Order.Id).In(
GetOrderQuery(exportQuery).Select(order => order.Id)
).Cacheable().List());
}
public FileResult ExportOrdersToExcel(OrderExportQuery exportQuery)
{
using (var excelFile = new ExcelPackage())
{
AddOrders(excelFile, exportQuery);
AddOrderLines(excelFile, exportQuery);
byte[] data = excelFile.GetAsByteArray();
return(new FileContentResult(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = "mrcms-ecommerce-order-export.xlsx"
});
}
}
public FileResult ToExcel(OrderExportQuery exportQuery)
{
return(_orderExportService.ExportOrdersToExcel(exportQuery));
}
private QueryOver <Order, Order> GetOrderQuery(OrderExportQuery exportQuery)
{
return(QueryOver.Of <Order>()
.Where(order => order.OrderDate >= exportQuery.From &&
order.OrderDate <= exportQuery.To));
}
private IList <Order> GetOrders(OrderExportQuery exportQuery)
{
var queryOver = GetOrderQuery(exportQuery);
return(queryOver.GetExecutableQueryOver(_session).Cacheable().List());
}
