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();
}
}
}
Unfortunately, it is not that simple.
Cell comments also have a graphics object which is in a VML drawing part. VML is a cryptic legacy specification and is not in the approved ECMA standard. You can find documentation on it in Microsoft's Open XML documents, but it is not pretty. Hopefully Microsoft will address this in Excel 14 by adding full cell comment support as well as support for controls which are also written to VML.
Having said that, I have not used the Open XML SDK and I cannot say whether or not it is possible to add comments with it. I just thought this might help get you pointed in the right direction.
Best Answer
The other answer seemed more like a meta-answer. I have been struggling with this since using LINQ does work with separated document parts. The following code includes a wrapper function to get the value from a Cell, resolving any possible string lookups.
Edit: Thanks @Nitin-Jadhav for the correction to GetCellValue().