Open XML SDK 2.0 - how to update a cell in a spreadsheet?

Question

I want to update a cell in a spreadsheet that is used by a chart, using the Open XML SDK 2.0 (CTP). All the code samples I have found insert new cells. I am struggling with retrieving the right worksheet.

public static void InsertText(string docName, string text, uint rowIndex,    string columnName) {   // Open the document for editing.   using (SpreadsheetDocument spreadSheet =      SpreadsheetDocument.Open(docName, true))   {     Workbook workBook = spreadSheet.WorkbookPart.Workbook;      WorksheetPart worksheetPart = workBook.WorkbookPart.       WorksheetParts.First();      SheetData sheetData = worksheetPart.Worksheet.       GetFirstChild<SheetData>();      // If the worksheet does not contain a row with the specified     // row index, insert one.     Row row;      if (sheetData.Elements<Row>().Where(       r => r.RowIndex == rowIndex).Count() != 0)       // At this point I am expecting a match for a row that exists       // in sheet1 but I am not getting one 

When I navigate the tree in Visual Studio, I am seeing three sheets, but none of them has any children. What am I missing?

Answer 1

Here is the working code. This is a prototype. For a larger number of changes, one might open the document only once. Also, there are some hard-coded things like sheet name and cell type that would have to be parameterized before this can be called production-ready. http://openxmldeveloper.org/forums/4005/ShowThread.aspx was very helpful.

using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.Xml; using System.IO; using System.Diagnostics;  namespace OpenXMLWindowsApp {     public class OpenXMLWindowsApp     {         public void UpdateSheet()         {             UpdateCell("Chart.xlsx", "20", 2, "B");             UpdateCell("Chart.xlsx", "80", 3, "B");             UpdateCell("Chart.xlsx", "80", 2, "C");             UpdateCell("Chart.xlsx", "20", 3, "C");              ProcessStartInfo startInfo = new ProcessStartInfo("Chart.xlsx");             startInfo.WindowStyle = ProcessWindowStyle.Normal;             Process.Start(startInfo);         }          public static void UpdateCell(string docName, string text,             uint rowIndex, string columnName)         {             // Open the document for editing.             using (SpreadsheetDocument spreadSheet =                       SpreadsheetDocument.Open(docName, true))             {                 WorksheetPart worksheetPart =                        GetWorksheetPartByName(spreadSheet, "Sheet1");                  if (worksheetPart != null)                 {                     Cell cell = GetCell(worksheetPart.Worksheet,                                               columnName, rowIndex);                      cell.CellValue = new CellValue(text);                     cell.DataType =                          new EnumValue<CellValues>(CellValues.Number);                      // Save the worksheet.                     worksheetPart.Worksheet.Save();                 }             }          }          private static WorksheetPart               GetWorksheetPartByName(SpreadsheetDocument document,               string sheetName)         {             IEnumerable<Sheet> sheets =                document.WorkbookPart.Workbook.GetFirstChild<Sheets>().                Elements<Sheet>().Where(s => s.Name == sheetName);              if (sheets.Count() == 0)             {                 // The specified worksheet does not exist.                  return null;             }              string relationshipId = sheets.First().Id.Value;             WorksheetPart worksheetPart = (WorksheetPart)                  document.WorkbookPart.GetPartById(relationshipId);             return worksheetPart;          }          // Given a worksheet, a column name, and a row index,          // gets the cell at the specified column and          private static Cell GetCell(Worksheet worksheet,                    string columnName, uint rowIndex)         {             Row row = GetRow(worksheet, rowIndex);              if (row == null)                 return null;              return row.Elements<Cell>().Where(c => string.Compare                    (c.CellReference.Value, columnName +                     rowIndex, true) == 0).First();         }           // Given a worksheet and a row index, return the row.         private static Row GetRow(Worksheet worksheet, uint rowIndex)         {             return worksheet.GetFirstChild<SheetData>().               Elements<Row>().Where(r => r.RowIndex == rowIndex).First();         }      } } 

Answer 2

I've been working with excel and found this helper library to be of great help (I've created my own helpers for word, would have saved at least 2 weeks if I was aware of this): https://www.nuget.org/packages/SimpleOOXML/

This is what is needed to update cell (writer.PasteText(...)):

MemoryStream stream = SpreadsheetReader.Create(); SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true); WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1"); WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);  writer.PasteText("B2", "Hello World");  //Save to the memory stream SpreadsheetWriter.Save(doc);  byte[] result = stream.ToArray(); FileStream file = new FileStream(@"D:\x1.xlsx", FileMode.Create); file.Write(result, 0, result.Length); file.Close();