none
Save() does not work for an Excel spreadsheet with modified cells RRS feed

  • Question

  • Hi all,

    I am trying to read in an Excel document, modify some cell values, and save the document. For this, I add the new cell's value to the shared strings and call Save().

    However, none of the changes, neither the new shared strings part nor the index does get saved.

    Does anybody see where the problem lies?

    Many thanks,
    Kaspar

    --

    using System;
    using System.IO;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Xml;
    using Org.IcarBasel.trainingautomation.Language;
    
    // ...
    
    // Reference: http://msdn.microsoft.com/en-us/library/bb739834.aspx#ManipulateOpenXMLExcelPowerPoint_RetrievetheValueofaCellinaWorksheet
    
                using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(file.FullName, true))
                {
                    // Load main document
                    WorkbookPart workbookPart = excelDoc.WorkbookPart;
                    XmlDocument doc = new XmlDocument();
                    doc.Load(workbookPart.GetStream());
    
                    // Shared strings
                    SharedStringTablePart sharedStringsPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
    
                    // Create namespace manager
                    NameTable nt = new NameTable();
                    XmlNamespaceManager nsManager = new XmlNamespaceManager(nt); // doc.NameTable
                    nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
                    nsManager.AddNamespace("w", excelMlNamespace);
                    nsManager.AddNamespace("v", vmlNamespace);
                    nsManager.AddNamespace("s", sharedStringSchema);
    
                    System.Xml.XmlNodeList sheetsList = doc.SelectNodes("//d:sheets/d:sheet", nsManager);
                    foreach (XmlNode node in sheetsList)
                    {
                        // Get the sheet
                        string sheetId = node.Attributes["r:id"].Value;
                        WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
                        XmlDocument sheetDoc = new XmlDocument();
                        sheetDoc.Load(worksheetPart.GetStream());
    
                        // Get list of all cells
                        XmlNodeList list = sheetDoc.SelectNodes("//d:row/d:c", nsManager);
    
                        // Replace text variables
                        // NOTICE: This calls the first delegate to get a cell's contents (which I debug to work fine) and
                        // the second delegate is used to save the replacement text
                        expandTextNodes(e, settings.CurrentProfile, list,
                            delegate(XmlNode cellNode)
                            {
                                XmlAttribute typeAttr = cellNode.Attributes["t"];
                                string cellType = string.Empty;
                                if (typeAttr != null)
                                {
                                    cellType = typeAttr.Value;
                                }
                                    
                                XmlNode valueNode = cellNode.SelectSingleNode("d:v", nsManager);
                                String cellValue = null;
                                if (valueNode != null)
                                {
                                    cellValue = valueNode.InnerText;
                                }
                                if (cellType == "b")
                                {
                                    if (cellValue == "1")
                                    {
                                        cellValue = "TRUE";
                                    }
                                    else
                                    {
                                        cellValue = "FALSE";
                                    }
                                }  
                                else if (cellType == "s")
                                {
                                    if (workbookPart.SharedStringTablePart != null)
                                    {
                                        XmlDocument stringDoc = new XmlDocument(nt);
                                        stringDoc.Load(workbookPart.SharedStringTablePart.GetStream());
                                        //  Add the string schema to the namespace manager.
                                        nsManager.AddNamespace("s", sharedStringSchema);
                                            
                                        int requestedString = Convert.ToInt32(cellValue);
                                        string strSearch = string.Format("//s:sst/s:si[{0}]", requestedString + 1);
                                        XmlNode stringNode = stringDoc.SelectSingleNode(strSearch, nsManager);
                                        if (stringNode != null)
                                        {
                                            cellValue = stringNode.InnerText;
                                        }
                                    }
                                }
                                return cellValue;
                            },
                            delegate(XmlNode cellNode, string text)
                            // NOTICE: THIS SAVES THE NEW TEXT
                            {
                                // Insert new string into shared strings table
                                int i = InsertSharedStringItem(text, sharedStringsPart); // taken from the article whose URL is given at the top
    
                                XmlNode valueNode = cellNode.SelectSingleNode("d:v", nsManager);
                                if (valueNode == null)
                                {
                                    throw new ApplicationException("Could not replace null value node in Excel document.");
                                }
                                valueNode.InnerText = Convert.ToString(i);
                            });
    
                        // Save (DOES NOT WORK)
                        worksheetPart.Worksheet.Save();
                        workbookPart.Workbook.Save();
                        doc.Save(workbookPart.GetStream(FileMode.Create));
                    }
    
                    // Save (DOES NOT WORK)
                    workbookPart.Workbook.Save();
                    doc.Save(workbookPart.GetStream(FileMode.Create));
                }
            }
    



    Monday, July 6, 2009 3:05 PM

All replies

  • Hi Kaspar,
    The changes you've made are on the DOM tree live in XmlDocument. The worksheetPart and workbookPart actually do not know any of your changes.

    Are you using SDK 2.0 CTP2 or are you using SDV 1.0? If using SDK 2.0 CTP2, you could access worksheetPart's root element with strong typed classes.

    You could find a sample code on inserting a text into a cell here: http://msdn.microsoft.com/en-us/library/cc861607(office.14).aspx
    Hopefully it will be helpful to you.

    Best,
    --L.
    MS Open XML SDK Team
    Tuesday, July 7, 2009 10:18 AM
  • Hi Kaspar,
    The changes you've made are on the DOM tree live in XmlDocument. The worksheetPart and workbookPart actually do not know any of your changes.

    Are you using SDK 2.0 CTP2 or are you using SDV 1.0? If using SDK 2.0 CTP2, you could access worksheetPart's root element with strong typed classes.

    You could find a sample code on inserting a text into a cell here: http://msdn.microsoft.com/en-us/library/cc861607(office.14).aspx
    I am using SDK 2.0 now and things work.

    For the sake of completeness, I attach my code.

    Thanks,
    Kaspar

    // Runs through all text cells and does some replacement on these cells (this part is not shown in the code)
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(file.FullName, true))
                {
                    foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
                    {
                        Worksheet worksheet = part.Worksheet;
                        foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
                        {
                            // Get value; notice that Excel MAY store values indirectly in the shared string table (see below)
                            CellValue cellValue = cell.CellValue;
                            string value = cellValue.InnerText;
    
                            // Notice: For our purposes, it suffices to only modify the cell if it is a string value
                            // (shared or not shared); Boolean values etc. are left unmodified.
    
                            // If the value is a string, processs it right away
                            if (cell.DataType != null && (cell.DataType == CellValues.InlineString) || (cell.DataType == CellValues.String))
                            {
                                expandCellWithSingleTextNode(cell);
                            }
    
                            // If the value is a shared string, look it up and process the resulting parts of the cell.
                            // (Notice here that shared strings may contain formatting, so there can be several parts.)
                            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                            {
                                OpenXmlElement cellNodes = document.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)];
                                expandCellWithMultipleTextNodes(cellNodes);
                            }
                            
                            // Example of how to alter a cell (this does not use the shared string table – the latter should be used if you repeat strings, to save space...):
                            //cell.CellValue = new CellValue("aka");
                            //cell.DataType = new EnumValue<CellValues>(CellValues.String);
                        }
                        worksheet.Save();
                    }
                }
    Monday, July 20, 2009 10:06 AM