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