Does Epplus support set sheet data/cell value by office open xml?

Question

My question :

If there is a sheet data format with office open xml like below :

        <sheetData>
            <row r="1" spans="1:3" x14ac:dyDescent="0.25">
                <c r="A1" t="s">
                    <v>0</v>
                </c>
                <c r="B1" t="s">
                    <v>1</v>
                </c>
                <c r="C1" t="s">
                    <v>2</v>
                </c>
            </row>
            <row r="2" spans="1:3" x14ac:dyDescent="0.25">
                <c r="A2">
                    <v>1</v>
                </c>
                <c r="B2">
                    <v>2</v>
                </c>
                <c r="C2">
                    <v>3</v>
                </c>
            </row>
            <row r="3" spans="1:3" x14ac:dyDescent="0.25">
                <c r="A3">
                    <v>4</v>
                </c>
                <c r="B3">
                    <v>5</v>
                </c>
                <c r="C3">
                    <v>6</v>
                </c>
            </row>
            <row r="4" spans="1:3" x14ac:dyDescent="0.25">
                <c r="A4">
                    <v>7</v>
                </c>
                <c r="B4">
                    <v>8</v>
                </c>
                <c r="C4">
                    <v>9</v>
                </c>
            </row>
        </sheetData>

how can I set this xml for epplus to change the cells value?

What I tried :

Searching epplus and only Cells[IndexName].Value = "xx" api.

What I expect for :

some epplus method like :

ws.SetSheetDataXml = xml;

score 1 · Accepted Answer · answered Dec 23 '19 at 22:17

You can always get to and manipulate the XML for the Worksheet like this:

var fi = new FileInfo(@"c:\temp\SomeExcelFile.xlsx");

using (var pck = new ExcelPackage(fi))
{
    var workbook = pck.Workbook;
    var ws = workbook.Worksheets.Add("Sheet1");
    ws.Cells.LoadFromDataTable(datatable, true);
    var xdoc = ws.WorksheetXml;

    ...
}

Obviously, you will have to be careful what you do with it so Epplus does not get upset when you try to save it. Generally, messing with the XML directly is a last resort for thing that Epplus cannot do. Here are a few examples:

How can I turn background error checking off for the Excel app object in EPPlus?

How to stop other conditional formatting from disappearing when hackmodding databars into solid fills?

X-Axis label formatting issue while exporting Chart to Excel using EPPlus

Does Epplus support set sheet data/cell value by office open xml?

My question :

What I tried :

What I expect for :

1 Answers1