Open XML SDK 2.0 - 如何更新电子表格中的单元格？

Question

使用Open XML SDK 2.0（CTP）更新电子表格中被图表使用的单元格。我找到的所有代码示例都是插入新单元格。我在尝试检索正确的工作表时遇到了困难。

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

我在 Visual Studio 中导航树时，看到三个表格，但没有一个表格有子项。我错过了什么吗？

- cdonner

---

我正在取得进展。有一件事让我感到困惑，所有的示例都假定 WorksheetParts.First() 会获取“Sheet1”工作表。但实际上并非如此，它只是返回 workbook.xml 中的第一个元素。等我把代码调通了再发出来。 - cdonner

尝试使用 WorksheetParts<Sheet>.First()。它将获取第一个 Sheet 类型的元素。 - lipeiran

Answer 1

这是工作代码。这是一个原型。对于更多的更改，可能只需打开文档一次。此外，还有一些硬编码的东西，比如表格名称和单元格类型，需要在调用生产就绪之前参数化。

http://openxmldeveloper.org/forums/4005/ShowThread.aspx非常有帮助。

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

- cdonner

---

感谢提供可用的代码...我很容易地将其适应到我的情况中。你说得对，大多数示例都会创建新的工作簿/工作表和单元格。我只想更新一些现有的单元格。 - Edward Leno

当我使用您（非常好的）示例代码时，我得到了预期的结果，但是当我在Excel 2010中打开XLSX文件（未在2007中测试）时，我收到一个警告说某些内容不正确（Excel发现不可读内容）并提供修复。我想知道这是否与该代码没有先将文本插入字符串表有关。我应该如何修改此代码以消除警告？ - Philipp Schmid

请参见：https://dev59.com/GGw05IYBdhLWcg3wszwl（写入DateTime导致内容无法读取） - Michael Paulukonis

由于我添加了一个字符串值，我将 EnumValue<CellValues>(CellValues.Number) 更改为 EnumValue<CellValues>(CellValues.String) - 然后“不可读内容”消息消失了。奇怪的是，我添加的内容一直都显示着。 - Michael Paulukonis

这个可行，谢谢。我建议在 GetRow() 和 GetCell() 中使用 FirstOrDefault() 而不是 First()，然后在返回值之前添加一个 NULL 检查。如果你在不存在的行或单元格上使用 First()，将会得到一个“sequence contains no elements”的错误。 - Kevin Adams

Answer 2

我一直在使用Excel，并发现这个辅助库非常有用（我已经为Word创建了自己的辅助程序，如果我知道这个库，至少可以节省2周时间）：https://www.nuget.org/packages/SimpleOOXML/ 这是更新单元格所需的内容(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();

- pajics

---

微软花费了很多心血来创建非常有用的方法，例如PasteText，但却没有为其编写甚至简单的使用文章。至少我们有MSDN，但仍然停留在ooxml 2.5上，应该有更多关于这些宝石的内容！=) - Paul C

谢谢，这确实让我的生活更轻松了！ - GeorgDangl

Answer 3

@CDonner发布的代码会抛出一些异常，我添加了一些代码来处理这些会抛出异常的代码，下面是它：

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

}

- Ashwin

---

检查 string.Compare(cell.CellReference.Value, cellReference, true) > 0 是否正确，因为它会将 "AA9" 和 "B9" 进行比较，这在 Excel 列优先级方面是错误的。 - avestnik

我使用了整个代码，但它没有将任何内容保存到工作表中。有什么想法吗？ - Ab Bennett

Answer 4

虽然这是SDK 2.5，但在这里找到了非常有用的代码。

需要对文本值进行轻微修改以将它们添加到SharedStringTablePart中。

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create one.
    if (shareStringPart.SharedStringTable == null)
    {
        shareStringPart.SharedStringTable = new SharedStringTable();
    }

    int i = 0;

    // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            return i;
        }

        i++;
    }

    // The text does not exist in the part. Create the SharedStringItem and return its index.
    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
    shareStringPart.SharedStringTable.Save();

    return i;
}

并像这样使用：

SharedStringTablePart shareStringPart = GetSharedStringTablePart(excelDoc);

// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(cellValue, shareStringPart);

// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

- Janis S.

---

GetSharedStringTablePart 丢失，您可以通过 spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First(); 获取该值（如果存在），或使用 spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>(); 创建它。更多信息请参见 https://learn.microsoft.com/en-us/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet - BurnsBA

Answer 5

我对 @AZ 的代码进行了一些更改。

首先，在 GetCell 函数中选择当前行存在问题。只需更改：

if (worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)

改为：

if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)

在这个部分中：

if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)

如果您使用的列超过Z列（例如AA列），则可能无法正常工作。为了解决这个问题，我使用列号来确定插入单元格的位置。

为此，我创建了一个名为ColumnIndex的函数，用于将列字母转换为数字：

private static int ColumnIndex(string reference)
    {
        int ci = 0;
        reference = reference.ToUpper();
        for (int ix = 0; ix < reference.Length && reference[ix] >= 'A'; ix++)
            ci = (ci * 26) + ((int)reference[ix] - 64);
        return ci;
    }

所以我为此更改了字符串比较函数：

string columnNew = new String(cellReference.Where(c => c != '-' && (c < '0' || c > '9')).ToArray());
            foreach (Cell cell in row.Elements<Cell>())
            {
                string columnBase = new String(cell.CellReference.Value.Where(c => c != '-' && (c < '0' || c > '9')).ToArray());

                if (ColumnIndex(columnBase) > ColumnIndex(columnNew))
                {
                    refCell = cell;
                    break;
                }
            }

最好的祝福。

- zerala

Answer 6

var sheetData = new SheetData();
var row = UpdateCell("A","Hello World", 5);
sheetData.Append(row);
worksheet.Append(sheetData);

private static Row UpdateCell(string columnName, string value, int rowIndex)
{
       Row row = new Row { RowIndex = (uint)rowIndex };
       Cell  c1 = new TextCell(columnName, value, rowIndex);
       row.Append(c1);
       return row;            
}


public class TextCell : Cell
{
    public TextCell(string header, string text, int index)
    {
        this.DataType = CellValues.InlineString;
        this.CellReference = header + index;
        //Add text to the text cell.
        this.InlineString = new InlineString { Text = new Text { Text = text } };
    }
}

- ravi kalkote

---

这给了我一个“System.InvalidOperationException: '非复合元素没有子元素。”的错误。 - windowsgm