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

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?

Answers


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

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): http://simpleooxml.codeplex.com/

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

The Code posted by @CDonner throws some exceptions, i have added some of the code that will take care of code, which throws an Exceptions, here it is

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 Application.Model{
public class TempCode
{
    public TempCode()
    {
        UpdateCell("E:/Visual Studio Code/Book1.xlsx", "120", 1, "A");
        UpdateCell("E:/Visual Studio Code/Book1.xlsx", "220", 2, "B");
        UpdateCell("E:/Visual Studio Code/Book1.xlsx", "320", 3, "C");
        UpdateCell("E:/Visual Studio Code/Book1.xlsx", "420", 4, "D");
        UpdateCell("E:/Visual Studio Code/Book1.xlsx", "520", 5, "E");

        ProcessStartInfo startInfo = new ProcessStartInfo("E:/Visual Studio Code/Book1.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, "Sheet2");
            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){
            return null;
        }
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
        return worksheetPart;
    }


    private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
    {
        Row row;
        string cellReference = columnName + rowIndex;
        if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            row = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
        else{
            row = new Row() { RowIndex = rowIndex };
            worksheet.Append(row);
        }

        if (row == null)
            return null;

        if (row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).Count() > 0) {
            return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
        }
        else{
            Cell refCell = null;
            foreach (Cell cell in row.Elements<Cell>()){
                if (string.Compare(cell.CellReference.Value, cellReference, true) > 0){
                    refCell = cell;
                    break;
                }
            }
            Cell newCell = new Cell() {
                CellReference = cellReference, 
                StyleIndex = (UInt32Value)1U

            };
            row.InsertBefore(newCell, refCell);
            worksheet.Save();
            return newCell;
        }
    }
}

}


Need Your Help

Ajax sometimes freezes

php jquery ajax load

This code is supposed to request new chat messages and append them to the content on my website. The problem, though, is that it sometimes freezes and stops requesting new messages.

WeakHashMap - what is its purpose and how should it be used correctly

java caching weak-references weakhashmap

Today I found this blog post which discussed usages of WeakHashMap over cache. It was intrigued by the fact that not the values, but the keys are stored as weak references, and when the reference ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.