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