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