如何使用EPPlus C#在单个单元格中显示多行文本

我正在制作体育赛事数据展示Excel文档，需要将同一赛事的多个播出频道放在对应赛事时间和名称旁的同一单元格内。目前遇到的问题是：已手动设置行高、启用自动换行，也尝试过三种不同的换行方式，但只有打开Excel并点击进入单元格后，单元格内的多行文本才会正常显示。

核心代码 #

// 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);

解决方法 #

1. 明确启用单元格自动换行 #

在频道列的单元格样式中添加WrapText = true，确保Excel识别需要换行显示：

worksheet.Cells[row, 2].Style.WrapText = true;

2. 确保换行符格式正确 #

Excel在Windows环境下仅识别\r\n作为换行符，如果你的ex.Channels中使用的是单独的\n，需要替换为标准Windows换行符：

worksheet.Cells[row, 2].Value = ex.Channels.Replace("\n", Environment.NewLine);

3. 避免自动调整覆盖行高 #

代码末尾的AutoSizeWorksheet可能会覆盖手动设置的行高，建议先执行自动调整，再重新应用行高设置：

AutoSizeWorksheet(worksheet);

// 重新遍历设置行高，确保生效
int currentRow = 3;
foreach (SportDataExport ex in GetSportDataExport(DateTimeString, League).OrderBy(export => DateTime.ParseExact(export.GameTime, "h:mmtt", CultureInfo.InvariantCulture)))
{
    worksheet.Row(currentRow).Height = ex.ChannelCount == 0 ? 31.5 : 31.5 * ex.ChannelCount;
    currentRow++;
}

修改后的循环代码示例 #

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.Replace("\n", Environment.NewLine);
    worksheet.Cells[row, 3].Value = ex.Teams;

    // 设置行高
    worksheet.Row(row).Height = ex.ChannelCount == 0 ? 31.5 : 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, 2].Style.WrapText = true;
    // 边框设置
    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++;
}

AutoSizeWorksheet(worksheet);

// 重新设置行高，避免被AutoSize覆盖
int currentRow = 3;
foreach (SportDataExport ex in GetSportDataExport(DateTimeString, League).OrderBy(export => DateTime.ParseExact(export.GameTime, "h:mmtt", CultureInfo.InvariantCulture)))
{
    worksheet.Row(currentRow).Height = ex.ChannelCount == 0 ? 31.5 : 31.5 * ex.ChannelCount;
    currentRow++;
}

内容的提问来源于stack exchange，提问作者Jamie Head