Unable to correctly handle the situation where different parts of text within a single cell have different styles

[uganh]

EPPlus usage

Noncommercial use

Environment

Windows

Epplus version

7.4.0

Spreadsheet application

Excel

Description

hero_improve.xlsx

I have some strange files that need to be processed, where different parts of text in some cells have different styles.

In hero_improve.xlsx/xl/sharedStrings.xml, it appears as a si element mixed with multiple t and r elements.

Reference code lines:

EPPlus/src/EPPlus/ExcelWorkbook.cs

Line 340 in 90de83f

XmlReaderHelper.ReadUntil(xr, "t", "r", "rPh", "phoneticPr");

It seems there is no way to get the full content of the cell.

using (var package = new ExcelPackage("hero_improve.xlsx"))
{
	ExcelWorkbook workbook = package.Workbook;
	ExcelWorksheet worksheet = workbook.Worksheets[0];

	var cell = worksheet.Cells["C17"];

	// Assert failed: `cell.Text` and `cell.Value` are both empty string
	Debug.Assert(cell.Text == "{\"any_orange_frag\" : 700, \"any_purple_frag\" : 800,\"any_blue_frag\" : 1200, \"any_green_frag\":2500}");
}

[JanKallman]

You have an empty element in the beginning of all your si-elements in the shared strings table.
For example:

<si>
<t/>
<r>
<rPr>
<sz val="9.75"/>
<color rgb="FF000000"/>
<rFont val="Calibri"/>
<family val="2"/>
</rPr>
<t>{"any_</t>
</r>
<r>
<rPr>
<sz val="10.5"/>
<color rgb="FF000000"/>
<rFont val="Calibri"/>
<family val="2"/>
</rPr>
<t xml:space="preserve">purple_frag":200, "any_blue_frag" : 200, "any_green_frag" : 500}</t>
</r>
</si>
<si>
<t/>
<r>
<rPr>
<sz val="9.75"/>
<color rgb="FF000000"/>
<rFont val="Calibri"/>
<family val="2"/>
</rPr>
<t>{"any</t>
</r>
<r>
<rPr>
<sz val="10.5"/>
<color rgb="FF000000"/>
<rFont val="Calibri"/>
<family val="2"/>
</rPr>
<t>_orange_frag</t>
</r>
<r>
<rPr>
<sz val="9.75"/>
<color rgb="FF000000"/>
<rFont val="Calibri"/>
<family val="2"/>
</rPr>
<t xml:space="preserve">" : 700</t>
</r>
<r>
<rPr>
<sz val="10.5"/>
<color rgb="FF000000"/>
<rFont val="Calibri"/>
<family val="2"/>
</rPr>
<t xml:space="preserve">, "any_purple_frag" : 800,"any_blue_frag" : 1200, "any_green_frag":2500</t>
</r>
<r>
<rPr>
<sz val="9.75"/>
<color rgb="FF000000"/>
<rFont val="Calibri"/>
<family val="2"/>
</rPr>
<t>}</t>
</r>
</si>

Resaving this file in Excel causes this element to be removed.
EPPlus currently reads either the t-element (Text) or the list or r-elements (Rich Text).
It seems the standard supports having a t-element and then a list of r element, but I have never seen this before.
We will look at a fix for this in a coming version. For now, if possible, remove the empty t-element.