How to edit the content in an existing cell?

[hxhgxy]

I opened an excel file and try to edit the content in an existing cell in this way:

row = sheetData.Descendants<Row>().
     Where(r => r.RowIndex == 10).FirstOrDefault();

cell = row.Descendants<Cell>().
     Where(c => c.CellReference == "C10").FirstOrDefault();
cell.CellValue = new CellValue("INCI");

the file will be damaged.

I can create new cell in this way:

private Cell ConstructCell(string value, string cellAddress, CellValues dataType, uint styleIndex = 0)
{
    return new Cell()
        {
	        CellValue = new CellValue(value),
	        CellReference = cellAddress,
	        DataType = new EnumValue<CellValues>(dataType),
	        StyleIndex = styleIndex
        };
}

How can I input content into the existing cell?

what is the differnce between CellValues.SharedString and CellValues.String?

Thanks a lot!

[tomjebo]

@hxhgxy typically this kind of "how-to" question can be asked on StackOverflow.com with a tag of openxml-sdk:
https://stackoverflow.com/questions/tagged/openxml-sdk

Otherwise, I don't immediately see what's wrong with your code but can try it.

[tomjebo]

@hxhgxy I ran your code and Excel didn't like it. When repairing it, Excel used the shared string table instead of a literal value.

Here's the XML that it will use:

            <c r="C10" t="s">
                <v>1</v>
            </c>

Here's the shared string table that results:

<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="2" uniqueCount="2">
    <si>
        <t>df</t>
    </si>
    <si>
        <t>INCI</t>
    </si>
</sst>

(ignore the "df" data)

To add shared strings to the table and correctly add the value, you can use our Open XML SDK Snippets in the Visual Studio Extensions. When you have that extension installed, you can type "excel" in your code and you'll see options for snippets of excel code. You'll also be able to add a new item to the project (right click on the project and select "Add new item") that is called "Open XML SDK Excel Utils". This new .cs file will contain several useful functions that manipulate worksheets, cells, string tables, etc...

I hope that helps.

[hxhgxy]

@hxhgxy I ran your code and Excel didn't like it. When repairing it, Excel used the shared string table instead of a literal value.

Here's the XML that it will use:

            <c r="C10" t="s">
                <v>1</v>
            </c>

Here's the shared string table that results:

<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="2" uniqueCount="2">
    <si>
        <t>df</t>
    </si>
    <si>
        <t>INCI</t>
    </si>
</sst>

(ignore the "df" data)

To add shared strings to the table and correctly add the value, you can use our Open XML SDK Snippets in the Visual Studio Extensions. When you have that extension installed, you can type "excel" in your code and you'll see options for snippets of excel code. You'll also be able to add a new item to the project (right click on the project and select "Add new item") that is called "Open XML SDK Excel Utils". This new .cs file will contain several useful functions that manipulate worksheets, cells, string tables, etc...

I hope that helps.

thank you very much!