/// <summary>
/// Update xl/workbook.xml file
/// </summary>
private string[] UpdateWorkbook()
{
if (!(DeleteWorksheets != null && DeleteWorksheets.Any() ||
(AddWorksheets != null && AddWorksheets.Any())))
{
// Nothing to update
return(null);
}
List <string> sheetNames = new List <string>();
using (Stream stream = Archive.GetEntry("xl/workbook.xml").Open())
{
XDocument document = XDocument.Load(stream);
if (document == null)
{
throw new Exception("Unable to load workbook.xml");
}
bool update = false;
RenameAndRebildWorksheetProperties((from sheet in document.Descendants()
where sheet.Name.LocalName == "sheet"
select sheet).ToArray());
if (update)
{
// Re number sheet ids
XNamespace r = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
int id = 1;
foreach (XElement sheetElement in (from sheet in document.Descendants()
where sheet.Name.LocalName == "sheet"
select sheet))
{
sheetElement.SetAttributeValue(r + "id", string.Format("rId{0}", id++));
sheetNames.Add(sheetElement.Attribute("name").Value);
}
//Set the stream to the start
stream.Position = 0;
// Clear the stream
stream.SetLength(0);
// Open the stream so we can override all content of the sheet
StreamWriter streamWriter = new StreamWriter(stream);
document.Save(streamWriter);
streamWriter.Flush();
}
}
return(sheetNames.ToArray());
}
/// <summary>
/// Update [Content_Types].xml file
/// </summary>
private void UpdateContentTypes(bool ensureStrings)
{
if (!(ensureStrings ||
(DeleteWorksheets != null && DeleteWorksheets.Any()) ||
(AddWorksheets != null && AddWorksheets.Any())))
{
// Nothing to update
return;
}
using (Stream stream = Archive.GetEntry("[Content_Types].xml").Open())
{
XDocument document = XDocument.Load(stream);
if (document == null)
{
//TODO error
}
bool update = false;
List <XElement> overrideElements = document.Descendants().Where(d => d.Name.LocalName == "Override").ToList();
//Ensure SharedStrings
if (ensureStrings)
{
XElement overrideElement = (from element in overrideElements
from attribute in element.Attributes()
where attribute.Name == "PartName" && attribute.Value.Equals("/xl/sharedStrings.xml", StringComparison.OrdinalIgnoreCase)
select element).FirstOrDefault();
if (overrideElement == null)
{
overrideElement = new XElement(document.Root.GetDefaultNamespace() + "Override");
overrideElement.Add(new XAttribute("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"));
overrideElement.Add(new XAttribute("PartName", "/xl/sharedStrings.xml"));
document.Root.Add(overrideElement);
update = true;
}
}
if (DeleteWorksheets != null && DeleteWorksheets.Any())
{
foreach (var item in DeleteWorksheets)
{
// the file name is different for each xml file
string fileName = string.Format("/xl/worksheets/sheet{0}.xml", item);
XElement overrideElement = (from element in overrideElements
from attribute in element.Attributes()
where attribute.Name == "PartName" && attribute.Value == fileName
select element).FirstOrDefault();
if (overrideElement != null)
{
overrideElement.Remove();
update = true;
}
}
}
if (AddWorksheets != null && AddWorksheets.Any())
{
foreach (var item in AddWorksheets)
{
// the file name is different for each xml file
string fileName = string.Format("/xl/worksheets/sheet{0}.xml", item.SheetId);
XElement overrideElement = new XElement(document.Root.GetDefaultNamespace() + "Override");
overrideElement.Add(new XAttribute("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"));
overrideElement.Add(new XAttribute("PartName", fileName));
document.Root.Add(overrideElement);
update = true;
}
}
if (update)
{
// Set the stream to the start
stream.Position = 0;
// Clear the stream
stream.SetLength(0);
// Open the stream so we can override all content of the sheet
StreamWriter streamWriter = new StreamWriter(stream, Encoding.UTF8);
document.Save(streamWriter);
streamWriter.Flush();
}
}
}
/// <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>
/// Update xl/_rels/workbook.xml.rels file
/// </summary>
private void UpdateRelations(bool ensureStrings)
{
if (!(ensureStrings ||
(DeleteWorksheets != null && DeleteWorksheets.Any()) ||
(AddWorksheets != null && AddWorksheets.Any())))
{
// Nothing to update
return;
}
using (Stream stream = Archive.GetEntry("xl/_rels/workbook.xml.rels").Open())
{
XDocument document = XDocument.Load(stream);
if (document == null)
{
//TODO error
}
bool update = false;
List <XElement> relationshipElements = document.Descendants().Where(d => d.Name.LocalName == "Relationship").ToList();
int id = relationshipElements.Count;
if (ensureStrings)
{
//Ensure SharedStrings
XElement relationshipElement = (from element in relationshipElements
from attribute in element.Attributes()
where attribute.Name == "Target" && attribute.Value.Equals("sharedStrings.xml", StringComparison.OrdinalIgnoreCase)
select element).FirstOrDefault();
if (relationshipElement == null)
{
relationshipElement = new XElement(document.Root.GetDefaultNamespace() + "Relationship");
relationshipElement.Add(new XAttribute("Target", "sharedStrings.xml"));
relationshipElement.Add(new XAttribute("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"));
relationshipElement.Add(new XAttribute("Id", string.Format("rId{0}", ++id)));
document.Root.Add(relationshipElement);
update = true;
}
}
// Remove all references to sheets from this file as they are not requried
if ((DeleteWorksheets != null && DeleteWorksheets.Any()) ||
(AddWorksheets != null && AddWorksheets.Any()))
{
XElement[] worksheetElements = (from element in relationshipElements
from attribute in element.Attributes()
where attribute.Name == "Type" && attribute.Value == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
select element).ToArray();
for (int i = worksheetElements.Length - 1; i > 0; i--)
{
worksheetElements[i].Remove();
update = true;
}
}
if (update)
{
// Set the stream to the start
stream.Position = 0;
// Clear the stream
stream.SetLength(0);
// Open the stream so we can override all content of the sheet
StreamWriter streamWriter = new StreamWriter(stream, Encoding.UTF8);
document.Save(streamWriter);
streamWriter.Flush();
}
}
}
