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