How to visually display multiline text in a single cell using EPPlus C#

Question

I am currently using a lot of data to create and display a visually pleasing document for my workplace displaying a list of sports/sport data.

I have a column that I am using to display channels of these sports. However, some sports have multiple channels and I have decided to put them in the same cell next to the name of the sport and time of the sport. The image attached should display what I am getting vs what I want.Correct Image Incorrect Image

I will not provide all of my code because it is sloppy, and excessive. However the concept in the code block below should be sufficient

// Create a new worksheet
var worksheet = package.Workbook.Worksheets.Add($"{DateTimeString} Lineup");

// Change all cells to font Calibri 24px
worksheet.Cells.Style.Font.Name = "Calibri";
worksheet.Cells.Style.Font.Size = 24;

// Merge cells A1:C1
worksheet.Cells["A1:C1"].Merge = true;
worksheet.Cells["A1:C1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["A1:C1"].Style.Font.Bold = true;
worksheet.Cells["A1:C1"].Style.Font.UnderLine = true;
worksheet.Cells["A1:C1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);


// Set A1:C1 to "Sunday NFL Games"
worksheet.Cells["A1"].Value = $"{dayOfWeek} {League.LeagueNameShort} Games";

// Set A2 to "Time", B2 to "Channel", and C2 to "Games" + DateTime.Now.ToString("MM/dd/yyyy")
worksheet.Cells["A2"].Value = "Time";
worksheet.Cells["A2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["A2"].Style.Font.Bold = true;
worksheet.Cells["A2"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells["B2"].Value = "Channel";
worksheet.Cells["B2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["B2"].Style.Font.Bold = true;
worksheet.Cells["B2"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells["C2"].Value = "Games " + date.ToString("MM/dd/yyyy");
worksheet.Cells["C2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["C2"].Style.Font.Bold = true;
worksheet.Cells["C2"].Style.Border.BorderAround(ExcelBorderStyle.Thin);

//Find Games
int row = 3;

foreach (SportDataExport ex in GetSportDataExport(DateTimeString, League).OrderBy(export => DateTime.ParseExact(export.GameTime, "h:mmtt", CultureInfo.InvariantCulture)))
{
    //Set Cell Values
    worksheet.Cells[row, 1].Value = ex.GameTime;
    worksheet.Cells[row, 2].Value = ex.Channels;
    worksheet.Cells[row, 3].Value = ex.Teams;

    if(ex.ChannelCount == 0)
    {
        worksheet.Row(row).Height = 31.5;
    }
    else
    {
        worksheet.Row(row).Height = 31.5 * ex.ChannelCount;
    }

    //Style Cells
    worksheet.Cells[row, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
    worksheet.Cells[row, 2].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
    worksheet.Cells[row, 3].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
    worksheet.Cells[row, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
    worksheet.Cells[row, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
    worksheet.Cells[row, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin);

    row = row + 1;
}

AutoSizeWorksheet(worksheet);
// Save the Excel package
FileInfo fileInfo = new FileInfo(filePath);
package.SaveAs(fileInfo);

I have tried manually setting the row height, setting "WordWrap" and 3 different methods of NewLines. The cell is fixed once the user opens the excel sheet, clicks INTO the cell and either presses enter or exits the cell by selecting another one.

Victor · Accepted Answer · 2023-12-10 06:20:27Z

4

It is necessary to set the WrapText property for the required column to true:

worksheet.Cells["B2"].Style.WrapText = true;

And then you can use the Environment.NewLine to break the text in lines.

Here it is how this looks like in test example:

edited Dec 10, 2023 at 6:20

answered Dec 10, 2023 at 6:09

Victor

9,0695 gold badges19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jamie Head Over a year ago

The issue with this is that I am also using AutoFit which I believe is messing with the cells aswell. see image <- This is what the sheet looks like when WrapText is set to true on the cell

Jamie Head Over a year ago

And if I expand the column, it then looks like This

Jamie Head Over a year ago

EDIT: This actually did solve my issue. I just needed to change the entire column's WrapText to true AFTER autofitting the text.

Collectives™ on Stack Overflow

How to visually display multiline text in a single cell using EPPlus C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related