c# - 使用 OpenXml 将文本写入特定 Excel 单元格时遇到问题

Question

我想要实现的目标如下所示:

我遇到的结果和问题如下所示:

这是我的代码生成的结果文件，应该有预期的内容。

单击"is"按钮后出现窗口提示。

我的运行代码如下:

主要方法:

public static void Main(string[] args)
{
    WriteExcelService writeExcelService = new WriteExcelService();
    Dictionary<string, List<string>> contentList = new Dictionary<string, List<string>>
    {
        { "en-US",new List<string> (new string[] { "Dummy text 01","Dummy text 02"}) },
        { "es-ES",new List<string> (new string[] { "Texto ficticio 01", "Texto ficticio 02"}) }
    };
    string inputFile = @"C:\{username}\Desktop\Valentines_Day.xlsx";
    string sheetName = "Copy";

    writeExcelService.WriteValueToCell(inputFile, sheetName, contentList);
}

WriteValueToCell 方法:

char columnName = 'I';
        uint rowNumber = 1;
        foreach (var keys in contentList.Keys)
        {
            foreach (var value in contentList.Where(v => v.Key == keys).SelectMany(v => v.Value))
            {
                string cellAddress = String.Concat(columnName, rowNumber);
                this.Write(filepath, sheetName, value, cellAddress, rowNumber);
                int tempColumn = (int)columnName;
                columnName = (char)++tempColumn;
            }
            columnName = 'I';
            ++rowNumber;
        }

书写方法:

private void Write(string filepath, string sheetName, string value, string cellAddress,uint rowNumber)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
    {
        //writeExcelService.WriteValueToCell(outputFilePath, sheetName, cellAddress, value.Value);
        // Add a WorkbookPart to the document.
        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        // Add a WorksheetPart to the WorkbookPart.
        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        // Add Sheets to the Workbook.
        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        // Append a new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };
        sheets.Append(sheet);

        // Get the sheetData cell table.
        SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

        // Add a row to the cell table.
        Row row;
        row = new Row() { RowIndex = rowNumber };
        sheetData.Append(row);

        // In the new row, find the column location to insert a cell.
        Cell refCell = null;
        foreach (Cell cell in row.Elements<Cell>())
        {
            if (string.Compare(cell.CellReference.Value, cellAddress, true) > 0)
            {
                refCell = cell;
                break;
            }
        }

        // Add the cell to the cell table.
        Cell newCell = new Cell() { CellReference = cellAddress };
        row.InsertBefore(newCell, refCell);

        // Set the cell value to be a numeric value.
        newCell.CellValue = new CellValue(value);
        newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
    }
}

My problem is:

我的代码执行，但是一旦打开结果文件，它会提示我上面发布的窗口，并且文件是空的。如果我调试代码以逐一插入内容列表，它可以正确写入单元格I2或J2。由于我的代码为每个列表内容创建 SpreadsheetDocument，因此我更改了以下代码中的 SpreadsheetDocument 创建方法:

using (SpreadsheetDocument spreadsheetDocument = File.Exists(filePath) ?
SpreadsheetDocument.Open(filepath, true) : 
SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
   {

   }

但是我遇到了异常

Only one instance of the type is allowed for this parent.

有人可以帮我解决这个问题吗？

提前欣赏一下。

Answer 1

Excel 中的字符串保存在sharedStringTable下。插入字符串时，添加字符串或从 sharedStringTable 引用字符串非常重要。此外，您还需要为单元格提供正确的DataType。在您的代码中，您将所有值作为数字插入:

// Set the cell value to be a numeric value.
newCell.CellValue = new CellValue(value);
newCell.DataType = new EnumValue<CellValues>(CellValues.Number);

要插入字符串，我建议您在创建新单元格后使用以下方法:

private SpreadsheetDocument _spreadSheet;
private WorksheetPart _worksheetPart;
..
..
private void UpdateCell(Cell cell, DataTypes type, string text)
{
    if (type == DataTypes.String)
    {
        cell.DataType = CellValues.SharedString;

        if (!_spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any())
        {
            _spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        var sharedStringTablePart = _spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        if (sharedStringTablePart.SharedStringTable == null)
        {
            sharedStringTablePart.SharedStringTable = new SharedStringTable();
        }
        //Iterate through shared string table to check if the value is already present.
        foreach (SharedStringItem ssItem in sharedStringTablePart.SharedStringTable.Elements<SharedStringItem>())
        {
            if (ssItem.InnerText == text)
            {
                cell.CellValue = new CellValue(ssItem.ElementsBefore().Count().ToString());
                return;
            }
        }
        // The text does not exist in the part. Create the SharedStringItem.
        var item = sharedStringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
        cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString());
    }
    else if (type == DataTypes.Number)
    {
        cell.CellValue = new CellValue(text);
        cell.DataType = CellValues.Number;
    }
    else if (type == DataTypes.DateTime)
    {

        cell.DataType = CellValues.Number;
        cell.StyleIndex = Convert.ToUInt32(_dateStyleIndex);

        DateTime dateTime = DateTime.Parse(text);
        double oaValue = dateTime.ToOADate();
        cell.CellValue = new CellValue(oaValue.ToString(CultureInfo.InvariantCulture));
    }
    _worksheetPart.Worksheet.Save();
}