使用OpenXml向特定的Excel单元写入文本时出现问题 [英] Having issue with writing text to specific Excel Cell using OpenXml

问题描述

我想实现的内容如下所示：

以下结果和问题如下所示：

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

This is the result file my code has generated and there supposed to have expected content.

点击是按钮后的窗口提示。

Window prompt after the 'Yes' button has clicked.

我的运行代码如下：

主要方法：

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 创建方法： / p>

My code executes but once the result file is opened, It prompts window as I posted above, and the file is empty.If I debug the code to insert list of contents one by one, it can be written correctly to Cells I2 or J2. Since my code creates SpreadsheetDocument for each list content, therefore I have changed the SpreadsheetDocument creation approach in the code below:

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.

任何人都可以帮助我吗？

Anyone can help me on this?

提前欣赏。

推荐答案

我已经弄清楚了自己的解决方案。我已经通过了字符串内容列表，并将它们全部写入相应的单元格，然后关闭了 SpreadSheetDocument 。以这种方式 SpreadSheetDocument 可以创建一次。工作代码如下：

I have figured out the solution myself. I have passed the list of string contents and write them all to corresponding Cells then closed the SpreadSheetDocument. In this way SpreadSheetDocument can be created once. Working code is below:

public void WriteValueToCell(string filepath, string sheetName, Dictionary<string, List<string>> contentList)
{
    // 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, true))
    {
        // Add a WorkbookPart to the document.
        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        //Add a WorkbookStylesPart to the workbookpart
        WorkbookStylesPart stylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
        stylesPart.Stylesheet = new Stylesheet();

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

        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);
                // 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.String);

                int tempColumn = (int)columnName;
                columnName = (char)++tempColumn;
            }
            columnName = 'I';
            ++rowNumber;
        }
    }
}

主要方法：

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

这篇关于使用OpenXml向特定的Excel单元写入文本时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！