private uint AddTotalRowExcel(uint rowIndex, string[] excelColumnNames, SheetData sheetData)
{
if (this.exportConfig.Where(e => e.Field == "TotalCost").Count() > 0 && this.exportConfig.Count > 1)
{
rowIndex++;
var totalRow = new Row {
RowIndex = rowIndex
}; // add a row at the to name the fields of spreadsheet
OpenXmlSpreadsheetUtilities.AppendTextCell
(excelColumnNames[this.exportConfig.Count - 2] + rowIndex.ToString(), "Sum Total Cost:", totalRow, CellValues.String, OpenXmlSpreadsheetUtilities.BOLD_CELL);
decimal total = 0;
foreach (var item in this.Model)
{
if (item != null)
{
var properties = item.GetType().GetProperties(BindingFlags.FlattenHierarchy | BindingFlags.Public | BindingFlags.Instance);
if (typeof(T).Name.Equals("ItemUsageReportItemModel"))
{
var property = properties.Where(p => p.Name.Equals("TotalCost", StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault();
total += decimal.Parse(this.GetFieldValue(item, property).Trim());
}
}
}
}
return(rowIndex);
}
private uint AddOrderHeaderToExcelExport(SheetData sheetData, string[] excelColumnNames, uint rowIndex)
{
Order headerInfo = (Order)_headerInfo;
List <string> list = new List <string>();
list.Add(string.Format("ORDER INFORMATION (System: {0})", headerInfo.OrderSystem));
list.Add(string.Format("Invoice # {0}", headerInfo.InvoiceNumber));
list.Add(string.Format("Order Date {0}", headerInfo.CreatedDate.ToString("ddd, M-dd-yy")));
list.Add(string.Format("Delivery Date {0}", DateTime.Parse(headerInfo.DeliveryDate).ToString("ddd, M-dd-yy")));
list.Add(string.Format("Subtotal ${0}", headerInfo.OrderTotal));
list.Add(string.Format("Invoice Status {0}", headerInfo.InvoiceStatus));
list.Add(string.Format("Order Status {0}", headerInfo.Status));
list.Add(string.Format("Requested Ship Date {0}", DateTime.Parse(headerInfo.RequestedShipDate).ToString("ddd, M-dd-yy")));
list.Add(string.Format("Delivered {0}", headerInfo.ActualDeliveryTime));
list.Add(string.Format("Items {0} Items / {1} Pieces", headerInfo.ItemCount, headerInfo.Items.Sum(i => i.Quantity)));
list.Add(string.Format("PO Number {0}", headerInfo.PONumber));
if (excelColumnNames.Length > 0)
{
rowIndex = OpenXmlSpreadsheetUtilities.AddHeaderRows
(rowIndex, typeof(T).Name, excelColumnNames, list, sheetData);
}
return(rowIndex);
}
private MemoryStream GenerateExcelExport()
{
MemoryStream stream = OpenXmlSpreadsheetUtilities.MakeSpreadSheet
(SetCustomColumnWidths(typeof(T).Name, new DocumentFormat.OpenXml.Spreadsheet.Worksheet()),
WriteDataTableToExcelWorksheet(),
typeof(T).Name);
return(stream);
}
private Worksheet SetCustomColumnWidths(Worksheet workSheet)
{
OpenXmlSpreadsheetUtilities.SetColumnWidth(workSheet, 2, 20);
OpenXmlSpreadsheetUtilities.SetColumnWidth(workSheet, 3, 20);
OpenXmlSpreadsheetUtilities.SetColumnWidth(workSheet, 4, 20);
OpenXmlSpreadsheetUtilities.SetColumnWidth(workSheet, 5, 20);
OpenXmlSpreadsheetUtilities.SetColumnWidth(workSheet, 8, 20);
return(workSheet);
}
private MemoryStream GenerateExcelReport(List <InventoryValuationModel> data)
{
MemoryStream stream = OpenXmlSpreadsheetUtilities.MakeSpreadSheet
(SetCustomColumnWidths(new DocumentFormat.OpenXml.Spreadsheet.Worksheet()),
WriteDataTableToExcelWorksheet(data),
"InventoryValuationModel");
return(stream);
}
private Worksheet SetCustomColumnWidths(string modelName, Worksheet workSheet)
{
uint colIndex = 0;
int width = 0;
foreach (ExportModelConfiguration config in exportConfig.OrderBy(c => c.Order))
{
colIndex++;
width = 0;
switch (modelName)
{
case ("OrderLine"):
width = OrderLine.SetWidths(config, width);
break;
case ("ShoppingCartItem"):
width = ShoppingCartItem.SetWidths(config, width);
break;
case ("InvoiceItemModel"):
width = InvoiceItemModel.SetWidths(config, width);
break;
case ("Product"):
width = Product.SetWidths(config, width);
break;
case ("Order"):
width = Order.SetWidths(config, width);
break;
case ("ItemUsageReportItemModel"):
width = ItemUsageReportItemModel.SetWidths(config, width);
break;
case ("InvoiceModel"):
width = InvoiceModel.SetWidths(config, width);
break;
case ("ListItemModel"):
width = ListItemModel.SetWidths(config, width);
break;
}
if (width > 0)
{
OpenXmlSpreadsheetUtilities.SetColumnWidth(workSheet, colIndex, width);
}
}
return(workSheet);
}
private static uint AddTitleToExcelExport(SheetData sheetData, string[] excelColumnNames, uint rowIndex)
{
List <string> exports = Configuration.ExportAddTitle;
foreach (string gettitle in exports)
{
if (gettitle.StartsWith(typeof(T).Name) && excelColumnNames.Length > 0)
{
rowIndex = OpenXmlSpreadsheetUtilities.AddTitleRow
(rowIndex, typeof(T).Name, excelColumnNames, gettitle.Substring(gettitle.IndexOf(';') + 1), sheetData);
}
}
return(rowIndex);
}
private uint AddCustomerToExcelExport(SheetData sheetData, string[] excelColumnNames, uint rowIndex)
{
List <string> exports = Configuration.ExportAddCustomer;
foreach (string gettitle in exports)
{
if (gettitle.Equals(typeof(T).Name) && excelColumnNames.Length > 2)
{
rowIndex = OpenXmlSpreadsheetUtilities.AddCustomerRow
(rowIndex, typeof(T).Name, excelColumnNames, _customerRepo.GetCustomerByCustomerNumber(_context.CustomerId, _context.BranchId), sheetData);
}
}
return(rowIndex);
}
private PropertyInfo[] WriteDataFieldsHeaderToExcelWorksheet(uint rowIndex, SheetData sheetData, string[] excelColumnNames)
{
var headerRow = new Row {
RowIndex = rowIndex
}; // add a row at the to name the fields of spreadsheet
sheetData.Append(headerRow);
var properties = typeof(T).GetProperties();
int columnIndex = 0;
foreach (var config in exportConfig.OrderBy(e => e.Order))
{
var propertyName = config.Field.Split('.');
uint styleInd = SetStyleForHeaderCell(typeof(T).Name, config.Field);
if (propertyName.Length == 1)
{
var property = properties.Where(p => p.Name.Equals(config.Field, StringComparison.CurrentCultureIgnoreCase))
.FirstOrDefault();
if (property != null)
{
OpenXmlSpreadsheetUtilities.AppendTextCell
(excelColumnNames[columnIndex] + rowIndex.ToString(), GetPropertyDescription(property)
.Trim(), headerRow, CellValues.String, styleInd);
columnIndex++;
}
}
else
{
var childProp = properties.Where(p => p.Name.Equals(propertyName[0]))
.FirstOrDefault();
var childProperties = childProp.PropertyType.GetProperties();
var subProperty = childProperties.Where(p => p.Name.Equals(propertyName[1]))
.FirstOrDefault();
if (subProperty != null)
{
OpenXmlSpreadsheetUtilities.AppendTextCell
(excelColumnNames[columnIndex] + rowIndex.ToString(), GetPropertyDescription(subProperty)
.Trim(), headerRow);
columnIndex++;
}
}
}
return(properties);
}
private uint AddInvoiceHeaderToExcelExport(SheetData sheetData, string[] excelColumnNames, uint rowIndex)
{
InvoiceModel headerInfo = (InvoiceModel)_headerInfo;
List <string> list = new List <string>();
list.Add("INVOICE INFORMATION");
list.Add(string.Format(" Invoice,# {0}", headerInfo.InvoiceNumber));
list.Add(string.Format(" Invoice Status,{0}", headerInfo.Status));
list.Add(string.Format(" Amount Due,${0}", headerInfo.Amount));
list.Add(string.Format(" Order Date,{0}", headerInfo.OrderDate.Value.ToString("ddd-M-dd-yy")));
list.Add(string.Format(" PO Number,{0}", headerInfo.PONumber));
list.Add(string.Format(" Type,{0}", headerInfo.Type));
list.Add(string.Format(" Due Date,{0}", headerInfo.DueDate.Value.ToString("ddd-M-dd-yy")));
list.Add(string.Format(" Items,{0}", headerInfo.Items.Count));
list.Add(string.Format(" Ship Date,{0}", headerInfo.InvoiceDate.Value.ToString("ddd-M-dd-yy")));
if (excelColumnNames.Length > 0)
{
rowIndex = OpenXmlSpreadsheetUtilities.AddHeaderRows
(rowIndex, typeof(T).Name, excelColumnNames, list, sheetData);
}
return(rowIndex);
}
private uint WriteDataFieldsDataRowsToExcelWorksheet(uint rowIndex, SheetData sheetData, PropertyInfo[] properties, string[] excelColumnNames)
{
int columnIndex;
foreach (var item in this.Model)
{
rowIndex++;
var newExcelRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(newExcelRow);
if (item != null)
{
columnIndex = 0;
foreach (var config in exportConfig.OrderBy(e => e.Order))
{
var propertyName = config.Field.Split('.');
if (propertyName.Length == 1)
{
ExportModelConfiguration thisConfig = new ExportModelConfiguration()
{
// just a shallow copy
Field = config.Field,
Label = config.Label,
Order = config.Order,
Selected = config.Selected
};
SetPriceConfig(properties, item, thisConfig);
var property = properties.Where(p => p.Name.Equals(thisConfig.Field, StringComparison.CurrentCultureIgnoreCase))
.FirstOrDefault();
uint styleInd = SetStyleForCell(typeof(T).Name, thisConfig.Field);
CellValues celltype = SetCellValueTypesForCell(typeof(T).Name, thisConfig.Field);
if (property != null)
{
OpenXmlSpreadsheetUtilities.AppendTextCell
(excelColumnNames[columnIndex] + rowIndex.ToString(),
this.GetFieldValue(item, property)
.Trim(), newExcelRow,
celltype,
styleInd);
}
}
else
{
var childProp = properties.Where(p => p.Name.Equals(propertyName[0]))
.FirstOrDefault();
var childProperties = childProp.PropertyType.GetProperties();
var subProperty = childProperties.Where(p => p.Name.Equals(propertyName[1]))
.FirstOrDefault();
if (subProperty != null)
{
OpenXmlSpreadsheetUtilities.AppendTextCell
(excelColumnNames[columnIndex] + rowIndex.ToString(), this.GetFieldValue(childProp.GetValue(item), subProperty)
.Trim(), newExcelRow);
}
}
columnIndex++;
}
}
}
return(rowIndex);
}
private SheetData WriteDataTableToExcelWorksheet(List <InventoryValuationModel> data)
{
SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
string[] excelColumnNames = new string[12];
excelColumnNames[0] = "Item";
excelColumnNames[1] = "Name";
excelColumnNames[2] = "Brand";
excelColumnNames[3] = "Category";
excelColumnNames[4] = "ContractCategory";
excelColumnNames[5] = "Pack";
excelColumnNames[6] = "Size";
excelColumnNames[7] = "Label";
excelColumnNames[8] = "Each";
excelColumnNames[9] = "Quantity";
excelColumnNames[10] = "Price";
excelColumnNames[11] = "ExtPrice";
rowIndex = OpenXmlSpreadsheetUtilities.AddTitleRow
(rowIndex, "InventoryValuationModel", excelColumnNames, "Inventory Valuation Report", sheetData);
rowIndex = OpenXmlSpreadsheetUtilities.AddCustomerRow
(rowIndex, "InventoryValuationModel", excelColumnNames, _customerRepo.GetCustomerByCustomerNumber(_context.CustomerId, _context.BranchId), sheetData);
var headerRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(headerRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[0] + rowIndex.ToString(), "Item", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[1] + rowIndex.ToString(), "Name", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[2] + rowIndex.ToString(), "Brand", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[3] + rowIndex.ToString(), "Category", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[4] + rowIndex.ToString(), "Contract Category", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[5] + rowIndex.ToString(), "Pack", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[6] + rowIndex.ToString(), "Size", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[7] + rowIndex.ToString(), "Label", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[8] + rowIndex.ToString(), "Each", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[9] + rowIndex.ToString(), "Quantity", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[10] + rowIndex.ToString(), "Price", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[11] + rowIndex.ToString(), "Ext. Price", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
//
// Now, step through each row of data in our DataTable...
foreach (var item in data)
{
rowIndex++;
var newExcelRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(newExcelRow);
if (item != null)
{
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[0] + rowIndex.ToString(), item.ItemId, newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[1] + rowIndex.ToString(), item.Name, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[2] + rowIndex.ToString(), item.Brand, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[3] + rowIndex.ToString(), item.Category, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[4] + rowIndex.ToString(), item.ContractCategory, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[5] + rowIndex.ToString(), item.Pack, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[6] + rowIndex.ToString(), item.Size, newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[7] + rowIndex.ToString(), item.Label, newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[8] + rowIndex.ToString(), item.Each ? "Y" : "N", newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[9] + rowIndex.ToString(), item.Quantity.ToString(), newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[10] + rowIndex.ToString(), Math.Round(item.Price, 2).ToString("F2"), newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[11] + rowIndex.ToString(), Math.Round(item.ExtPrice, 2).ToString("F2"), newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
}
}
rowIndex++;
var newRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[0] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[1] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[2] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[3] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[4] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[5] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[6] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[7] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[8] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[9] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[10] + rowIndex.ToString(), "Total", newRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[11] + rowIndex.ToString(), Math.Round(data.Sum(s => s.ExtPrice), 2).ToString("F2"), newRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
return(sheetData);
}
