Search code examples
c#excelepplus

EPPLUS is duplicating strings on insert

I am trying to insert text into RichText, when the index of the inserted string is at the end of an element the next element gets duplicated!

Here is an example:

worksheet.Cells[rownum + 100, column].RichText.Add("first ");
worksheet.Cells[rownum + 100, column].RichText.Add(" second");
worksheet.Cells[rownum + 100, column].RichText.Text = worksheet.Cells[rownum + 100, column].RichText.Text.Insert(6, "Inserted");

Result: "first Insertedsecondsecond"

Is this normal behavior? because I am expecting to get:

"first Inserted second"

Solution

  • I created this to simulate your issue.

    static void Main(string[] args)
{
    using (OfficeOpenXml.ExcelPackage ep = new OfficeOpenXml.ExcelPackage())
    {
        var ws = ep.Workbook.Worksheets.Add("sheet 1");
        ws.Cells[1, 1].IsRichText = true;
        ws.Cells[1, 1].RichText.Add("first ");
        ws.Cells[1, 1].RichText.Add(" second");
        ws.Cells[1, 1].RichText.Text = ws.Cells[1, 1].RichText.Text.Insert(6, "Inserted");

        Console.WriteLine(ws.Cells[1, 1].Text); // shows your bug
    }
}

    and this gives an array of 2 items on ws.Cells[1, 1].RichText

    where the first one gives your desired value. enter image description here

    this does not fix it...

    ws.Cells[1, 1].RichText.Add("first ");
ws.Cells[1, 1].RichText.Add(" second");
ws.Cells[1, 1].RichText.Text = ws.Cells[1, 1].RichText.Text.Insert(6, "Inserted");
ws.Cells[1, 1].RichText.RemoveAt(ws.Cells[1, 1].RichText.Count - 1);
Console.WriteLine(ws.Cells[1, 1].Text);

    The problem is in the richtextcollection having a second item. which should not be there.

    ws.Cells[1, 1].RichText.Remove(ws.Cells[1, 1].RichText.Last());

    even throws an Exception!

    The only solution I can come up with is to clear the array of RichTextCollection first.

    string curText = ws.Cells[1, 1].RichText.Text;
ws.Cells[1, 1].RichText.Clear(); // remove previous nodes
ws.Cells[1, 1].RichText.Text = curText.Insert(6, "Inserted");

    Full sample code:

    static void Main(string[] args)
{
    using (OfficeOpenXml.ExcelPackage ep = new OfficeOpenXml.ExcelPackage())
    {
        var ws = ep.Workbook.Worksheets.Add("sheet 1");
        ws.Cells[1, 1].IsRichText = true;
        ws.Cells[1, 1].RichText.Add("first ");
        ws.Cells[1, 1].RichText.Add(" second");
        ws.Cells[1, 1].RichText.Add(" third");
        string curText = ws.Cells[1, 1].RichText.Text;
        ws.Cells[1, 1].RichText.Clear();
        ws.Cells[1, 1].RichText.Text = curText.Insert(6, "Inserted");

        Console.WriteLine(ws.Cells[1, 1].Text);
    }
}