/// <summary> /// If sheets have been added or deleted, sheets need to be renamed /// </summary> private void RenameAndRebildWorksheetProperties(XElement[] sheets) { if (!((this.DeleteWorksheets != null && this.DeleteWorksheets.Any()) || (this.AddWorksheets != null && this.AddWorksheets.Any()))) { // Nothing to update return; } XNamespace r = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"; List<WorksheetProperties> sheetProperties = (from sheet in sheets select new WorksheetProperties () { SheetId = int.Parse(sheet.Attribute("sheetId").Value), Name = sheet.Attribute("name").Value, CurrentIndex = int.Parse(sheet.Attribute(r + "id").Value) }).ToList(); // Remove deleted worksheets to sheetProperties if (this.DeleteWorksheets != null && this.DeleteWorksheets.Any()) { foreach (var item in this.DeleteWorksheets) { WorksheetProperties sheetToDelete = (from sp in sheetProperties where sp.SheetId == item select sp).FirstOrDefault(); if (sheetToDelete != null) { sheetProperties.Remove(sheetToDelete); } } } // Add new worksheets to sheetProperties if (this.AddWorksheets != null && this.AddWorksheets.Any()) { // Add the sheets in reverse, this will add them correctly with less work foreach (var item in this.AddWorksheets.Reverse<WorksheetAddSettings>()) { WorksheetProperties previousSheet = (from sp in sheetProperties where sp.SheetId == item.InsertAfterSheetId select sp).FirstOrDefault(); if (previousSheet == null) { throw new Exception(string.Format("Sheet name {0} cannot be added because the insertAfterSheetNumber or insertAfterSheetName is now invalid", item.Name)); } WorksheetProperties newWorksheet = new WorksheetProperties(); newWorksheet.SheetId = item.SheetId; newWorksheet.Name = item.Name; newWorksheet.CurrentIndex = 0;// TODO Something?? sheetProperties.Insert(sheetProperties.IndexOf(previousSheet), newWorksheet); } } int index = 1; foreach (WorksheetProperties worksheet in sheetProperties) { if (worksheet.CurrentIndex != index) { ZipArchiveEntry entry = this.Archive.GetEntry(Worksheet.GetFileName(worksheet.CurrentIndex)); if (entry == null) { // TODO better message throw new Exception("Worksheets could not be rebuilt"); } } index++; } }
/// <summary> /// If sheets have been added or deleted, sheets need to be renamed /// </summary> private void RenameAndRebildWorksheetProperties(XElement[] sheets) { if (!((DeleteWorksheets != null && DeleteWorksheets.Any()) || (AddWorksheets != null && AddWorksheets.Any()))) { // Nothing to update return; } XNamespace r = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"; List <WorksheetProperties> sheetProperties = (from sheet in sheets select new WorksheetProperties () { SheetId = int.Parse(sheet.Attribute("sheetId").Value), Name = sheet.Attribute("name").Value, CurrentIndex = int.Parse(sheet.Attribute(r + "id").Value) }).ToList(); // Remove deleted worksheets to sheetProperties if (DeleteWorksheets != null && DeleteWorksheets.Any()) { foreach (var item in DeleteWorksheets) { WorksheetProperties sheetToDelete = (from sp in sheetProperties where sp.SheetId == item select sp).FirstOrDefault(); if (sheetToDelete != null) { sheetProperties.Remove(sheetToDelete); } } } // Add new worksheets to sheetProperties if (AddWorksheets != null && AddWorksheets.Any()) { // Add the sheets in reverse, this will add them correctly with less work foreach (var item in AddWorksheets.Reverse <WorksheetAddSettings>()) { WorksheetProperties previousSheet = (from sp in sheetProperties where sp.SheetId == item.InsertAfterSheetId select sp).FirstOrDefault(); if (previousSheet == null) { throw new Exception(string.Format("Sheet name {0} cannot be added because the insertAfterSheetNumber or insertAfterSheetName is now invalid", item.Name)); } WorksheetProperties newWorksheet = new WorksheetProperties() { SheetId = item.SheetId, Name = item.Name, CurrentIndex = 0// TODO Something?? }; sheetProperties.Insert(sheetProperties.IndexOf(previousSheet), newWorksheet); } } int index = 1; foreach (WorksheetProperties worksheet in sheetProperties) { if (worksheet.CurrentIndex != index) { ZipArchiveEntry entry = Archive.GetEntry(Worksheet.GetFileName(worksheet.CurrentIndex)); if (entry == null) { // TODO better message throw new Exception("Worksheets could not be rebuilt"); } } index++; } }
/// <summary> /// Constructs an Excel application /// </summary> public Excel() { _activeSheet = new ActiveSheet(); Globals.ThisAddIn.Application.WorkbookActivate += (wb) => _workbookOpened(); _worksheetProperties = new WorksheetProperties(this); }