OpenXML 将单元格添加到工作表

我目前正在MSDN站点上研究OpenXML 2.5框架，https://msdn.microsoft.com/en-us/library/office/cc861607.aspx

我尝试将单元格添加到现有工作表的所有方法都会损坏工作簿，因为 MSDN 站点仅概述了创建工作表而不是修改它。

每次我添加一个单元格时，系统都需要一个全新的工作表，并且不允许将单元格添加到现有工作表中。我已经敲了几个小时的头，浏览MSDN并在谷歌上搜索这个，但没有运气。

问题是我需要一个可以接收字符串并更新 excel 文件的类。有没有人能够将单元格添加到现有工作表？我的问题似乎是由于逐个字符串的解决方案造成的。

工作输入 (PowerShell( 仅在为单元格创建新工作表时才有效，

[CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='Medium')]
$cSharpData = (
    [Reflection.Assembly]::LoadWithPartialName("DocumentFormat.OpenXml"),
    [Reflection.Assembly]::LoadWithPartialName("WindowsBase"),
    [Reflection.Assembly]::LoadWithPartialName("System.Linq")
)
[String]$cSharpClass = Get-Content .'method.cs
$cSharpType = Add-Type -ReferencedAssemblies $cSharpData -TypeDefinition $cSharpClass
$testData = Get-WmiObject Win32_QuickFixEngineering
[DoExcelMethod]::CreateXLSX('.'test.xlsx')
$locNo = 1
[DoExcelMethod]::AddSheetData('.'test.xlsx', $testData, 'TestWS', 'A', $locNo)

此指向的文件具有以下内容，

using System;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
public class DoExcelMethod {
    private static int SharedDataItem(string sData, SharedStringTablePart ssPart) {
        if (ssPart.SharedStringTable == null) {
            ssPart.SharedStringTable = new SharedStringTable();
        }
        int cnt = 0;
        foreach (SharedStringItem sspItem in ssPart.SharedStringTable.Elements<SharedStringItem>()) {
            if (sspItem.InnerText == sData) {
                return cnt;
            }
            cnt++;
        }
        ssPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(sData)));
        ssPart.SharedStringTable.Save();
        return cnt;
    }
    private static WorksheetPart InsertWorksheet(string wsName, WorkbookPart wbPart) {
        WorksheetPart newWsPart = wbPart.AddNewPart<WorksheetPart>();
        newWsPart.Worksheet = new Worksheet(new SheetData());
        newWsPart.Worksheet.Save();
        Sheets sheets = wbPart.Workbook.GetFirstChild<Sheets>();
        string relId = wbPart.GetIdOfPart(newWsPart);
        uint sheetId = 1;
        if (sheets.Elements<Sheet>().Count() > 0) {
            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }
        Sheet sheet = new Sheet() { Id = relId, SheetId = sheetId, Name = wsName };
        sheets.Append(sheet);
        wbPart.Workbook.Save();
        return newWsPart;
    }
    private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) {
        Worksheet worksheet = worksheetPart.Worksheet;
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        string cellReference = columnName + rowIndex;
        Row row;
        if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) {
            row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
        } else {
            row = new Row() { RowIndex = rowIndex };
            sheetData.Append(row);
        }
        if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).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 };
            row.InsertBefore(newCell, refCell);
            worksheet.Save();
            return newCell;
        }
    }
    public static void CreateXLSX(string xlsxFile) {
        SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(xlsxFile, SpreadsheetDocumentType.Workbook);
        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();
        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());
        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
        Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Default" };
        sheets.Append(sheet);
        workbookpart.Workbook.Save();
        spreadsheetDocument.Close();
    }
    public static void AddSheetData(string xlsxFile, string psData, string wsName, string psCol, uint psRow) {
        using (SpreadsheetDocument sSheet = SpreadsheetDocument.Open(xlsxFile, true)) {
            SharedStringTablePart ssPart;
            if (sSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0) {
                ssPart = sSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            } else {
                ssPart = sSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }
            int ssIns = SharedDataItem(psData, ssPart);
            WorksheetPart wsPart = InsertWorksheet(wsName, sSheet.WorkbookPart);
            Cell cell = InsertCellInWorksheet(psCol, psRow, wsPart);
            cell.CellValue = new CellValue(ssIns.ToString());
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
            wsPart.Worksheet.Save();
        }
    }
}

因此，尽管可以正常工作，但我无法将单元格放入现有工作表中，任何人都可以提供帮助，因为我快要疯了:(

谢谢大家

private static WorksheetPart GetWorksheetPartBySheetName(WorkbookPart workbookPart, string sheetName)
{
    WorksheetPart worksheetPart = null;
    //find the sheet (note this is case-sensitive)
    IEnumerable<Sheet> sheets = workbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
    if (sheets.Count() > 0)
    {
        string relationshipId = sheets.First().Id.Value;
        worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationshipId);
    }
    return worksheetPart;
}

WorksheetPart wsPart = InsertWorksheet(wsName, sSheet.WorkbookPart);

WorksheetPart wsPart = GetWorksheetPartBySheetName(sSheet.WorkbookPart, wsName);
if (wsPart == null)
    wsPart = InsertWorksheet(wsName, sSheet.WorkbookPart);