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