Inconsistent behavior rendering line breaks with RichText.

[James-Jackson-South]

Hi there,

I'm evaluating EPPlus with a view to instructing my employer to purchase a license and I have stumbled upon some interesting behaviour when rendering rich text.

Version Numbers EPPlus 7.0.10 targeting .NET 8 Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit

I'm using the following code to try to get Excel to render the line breaks as expected. The different approaches to render the break are my attempts to work-around the issue and demonstrate that no version of a line break renders correctly.

using OfficeOpenXml;

namespace EPRichTextDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello, World!");

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using ExcelPackage package = new();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

            ExcelRange cell = worksheet.Cells[1, 1];
            cell.IsRichText = true;
            cell.RichText.Add("1. I am the first line. No additional formatting required.");
            cell.RichText.Add("\r\n2. This line is manually preceded by return and newline characters.");
            cell.RichText.Add("3. This line uses the 'New Paragraph' overload.", true);
            cell.RichText.Add("\n4. This line is manually preceded by a newline character.");

            package.SaveAs(new FileInfo("EPRichTextDemo.xlsx"));

        }
    }
}

Initial load of the worksheet shows formatted text in the line breaks in the formula window but not the cell.

Selecting the formula window with no additional interaction updates the cell to show formatted text.

Resizing the cell shows the cell now matches the formular window.

Saving and reloading the worksheet persists the correctly formatted cell.

Additionally, the behaviour of the IsNewParagraph parameter is surprising. I would have expected the newline \n character to be prefixed to the text not postfixed given that we are indicating that the body of text should be treated as a separate paragraph from preceding text given that it is being added to a collection.

I've attached a solution containing code demonstrating the issue. Please let me know if you have additional questions. EPRichTextDemo.zip

[AdrianEPPlus]

Hello!

I've looked at your issue and you can set cell.Style.WrapText to true and then set cell.EntireColumn.Width to format the cell for your needs.

Regarding the paragraph, it seem we made an error in our XML comment. When passing true, it will add new paragraph after the text. This is the intended behavior. The XML comment will be updated to reflect this in an upcoming release.

[James-Jackson-South]

Thanks @AdrianEPPlus for your swift reply.

Maybe I'm holding it wrong but with the additional explicit styling I'm still seeing surprising behaviour.

using OfficeOpenXml;

namespace EPRichTextDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello, World!");

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using ExcelPackage package = new();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

            ExcelRange cell = worksheet.Cells[1, 1];

            // Explicitly style cells
            cell.Style.WrapText = true;
            cell.EntireColumn.Width = 1000;

            cell.IsRichText = true;
            cell.RichText.Add("1. I am the first line. No additional formatting required.");
            cell.RichText.Add("\r\n2. This line is manually preceded by return and newline characters.");
            cell.RichText.Add("3. This line uses the 'New Paragraph' overload.", true);
            cell.RichText.Add("\n4. This line is manually preceded by a newline character.");

            package.SaveAs(new FileInfo("EPRichTextDemo.xlsx"));

        }
    }
}

First load. Manually styled width is ignored.

Manual column width resize. Height and alignment are incorrect.

Clicking into the formula input with no additional input. Height and alignment are repaired.

[AdrianEPPlus]

Hello!

You can use cell.EntireRow.Height to set the height of a row. And to align the content of a cell you can use cell.Style.HorizontalAlignment and cell.Style.VerticalAlignment respectively.

[James-Jackson-South]

The explicit width style is being ignored though. Why is that not respected and why would defaults for other styles not be set appropriately??

[AdrianEPPlus]

Hello!

I can't reproduce the issue you are having with the code you have shared. Only difference is that I removed cell.RichText = true as it is set when you add a richtext. Setting the property when the richtext collection is empty could make it throw an exception. Width should be saved when you save the workbook. Are you sure you are opening the correct workbook?

[James-Jackson-South]

Thanks again for your reply.

I'm definitely reading/writing to the correct file. It's in the bin folder of my application and I am taking care to delete it between debugging runs.

I followed your lead and removed the explicit cell.RichText = true; statement and there is now a width applied to the cell and the alignment seems correct.

No exceptions were thrown during debugging when the statement was explicitly set.

What are the units used for cell dimensions? It's not px as the output is not representative of set values if so.

cell.EntireColumn.Width = 1000;

cell.EntireColumn.Width = 300;

[AdrianEPPlus]

Hello!

Glad to see width is now working! Unsure why removing that statement makes it work even if it didn't throw any exceptions on your end.

As for the unit width is using is an OOXML standard, you can read more about it here in the first table under width: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.column?view=openxml-3.0.1