public virtual ExcelWorksheet AddAsExcelSheet(ExcelWorksheets excelWorksheets, TableStyles tableStyle, string headerStyle)
{
var spreadSheet = excelWorksheets.Add(SpreadsheetName);
var dataTable = ConvertToDataTables().First();
foreach (var column in dataTable.Columns)
{
var columnName = column.ToString();
var friendlyName = column.ToString()
.ToFriendlyString(" ", a => a == '_', b => b.CapitalizeFirstLetter(), true);
dataTable.Columns[column.ToString()].ColumnName = friendlyName;
}
spreadSheet.Cells[1, 1].LoadFromDataTable(dataTable, true, tableStyle);
spreadSheet.Cells.AutoFitColumns();
//No rows of close approach data were added and the spreadsheet is empty, no need to include it.
if (!spreadSheet.Cells.Any())
{
excelWorksheets.Delete(spreadSheet);
return(null);
}
return(spreadSheet);
}
/// <summary>
/// Add a new sheet to the collection. Replace if already exists.
/// </summary>
/// <param name="sheets"></param>
/// <param name="name"></param>
/// <returns></returns>
public static ExcelWorksheet AddOrReplace(this ExcelWorksheets sheets, string name)
{
if (sheets.Any(x => StringComparer.OrdinalIgnoreCase.Equals(x.Name, name)))
{
sheets.Delete(name);
}
return(sheets.Add(name));
}
public void DeleteSheets()
{
ExcelWorksheet one = doc.Add("one");
ExcelWorksheet two = doc.Add("two");
ExcelWorksheet three = doc.Add("three");
Assert.AreEqual(3, doc.Count);
doc.Delete(2);
Assert.AreEqual(2, doc.Count);
Assert.AreEqual(one, doc[1]);
Assert.AreEqual(three, doc[2]);
Assert.AreEqual(null, doc["two"]);
doc.Delete(1);
Assert.AreEqual(1, doc.Count);
Assert.AreEqual(three, doc[1]);
Assert.AreEqual(null, doc["one"]);
}
public void Delete(object worksheet)
{
if (TypeUtilities.IsString(worksheet))
{
m_excelWorksheets.Delete(TypeConverter.ToString(worksheet));
}
else if (TypeUtilities.IsNumeric(worksheet))
{
m_excelWorksheets.Delete(TypeConverter.ToInteger(worksheet));
}
else if (worksheet is ExcelWorksheetInstance)
{
m_excelWorksheets.Delete((worksheet as ExcelWorksheetInstance).ExcelWorksheet);
}
else
{
throw new JavaScriptException(this.Engine, "Error", "Could not delete a worksheet -- did not understand the argument type.");
}
}
public override ExcelWorksheet AddAsExcelSheet(ExcelWorksheets excelWorksheets, TableStyles tableStyle, string headerStyle)
{
var spreadSheet = excelWorksheets.Add(SpreadsheetName);
var currentRow = 1;
var allDataTables = ConvertToDataTables();
var subTableCollection = SerializationData.Select(a => (CloseApproachInfoSubTable)a).ToList();
var counter = 0;
foreach (var dataTable in allDataTables)
{
if (dataTable.Rows.Count == 0)
{
continue;
}
foreach (var column in dataTable.Columns)
{
var columnName = column.ToString();
var friendlyName = column.ToString()
.ToFriendlyString(" ", a => a == '_', b => b.CapitalizeFirstLetter(), true);
dataTable.Columns[column.ToString()].ColumnName = friendlyName;
}
spreadSheet.Cells[currentRow, 1].Value = subTableCollection[counter].Name;
spreadSheet.Cells[currentRow, 1].StyleName = headerStyle;
spreadSheet.Cells[currentRow + 1, 1].LoadFromDataTable(dataTable, true, tableStyle);
currentRow += dataTable.Rows.Count + 2;
spreadSheet.Cells.AutoFitColumns();
counter++;
}
//No rows of close approach data were added and the spreadsheet is empty, no need to include it.
if (!spreadSheet.Cells.Any())
{
excelWorksheets.Delete(spreadSheet);
return(null);
}
return(spreadSheet);
}
private void createUnloadActivityWorksheets(KrogerUnloadingInvoiceModel invoice, ExcelWorksheets sheets, ExcelWorksheet blankUnloadActivitySheet)
{
// Create and populate "All Loads" worksheet:
var allLoadsSheet = sheets.Add("All Loads", blankUnloadActivitySheet);
populateUnloadActivitySheet(allLoadsSheet, invoice.AllWorkOrders);
// Create and populate "Street" worksheet:
var streetSheet = sheets.Add("Street", blankUnloadActivitySheet);
populateUnloadActivitySheet(streetSheet, invoice.PrepaidWorkOrders);
// Create and populate "Controlled" worksheet:
var controlledSheet = sheets.Add("Controlled", blankUnloadActivitySheet);
populateUnloadActivitySheet(controlledSheet, invoice.ControlledWorkOrders);
// Get the names of all unique department #'s in the collection of work orders:
var controlledWorkOrdersByRefNo = invoice.ControlledWorkOrdersByReferenceCode;
var allRefNames = controlledWorkOrdersByRefNo
.Select(g => g[0].ReferenceNumber)
.Where(s => !String.IsNullOrEmpty(s))
.ToList();
// Create workSheets for each unique Ref in the collection of invoices:
allRefNames.ForEach(n =>
{
var sheet = sheets.Add(String.Format("Controlled ({0})", n), blankUnloadActivitySheet);
var workOrdersFromThisRef = controlledWorkOrdersByRefNo.Find(l => l[0].ReferenceNumber == n);
populateUnloadActivitySheet(sheet, workOrdersFromThisRef);
});
if (invoice.BreakdownByDepartment)
{
// Create workSheets for each department
foreach (var gr in invoice.AllWorkOrders.GroupBy(x => new { x.LocationDepartmentId, x.DepartmentName }))
{
var departmentSheet = sheets.Add(gr.Key.DepartmentName, blankUnloadActivitySheet);
populateUnloadActivitySheet(departmentSheet, gr.ToList());
}
}
sheets.Delete(blankUnloadActivitySheet);
}
public void Delete(int Index)
{
Worksheets.Delete(Index - 1);
}
public void Remove(string name)
{
_worksheets.Delete(name);
}
