如何在excel epplus c#中进行换行

时间:2022-01-20 08:36:01

I am using Epplus library to convert dataTable in Excel. I am using a textarea in my front end site. In which a line break is also there. But, the problem is when I convert this file to Excel, text shows in one line not with a line break.

我正在使用Epplus库在Excel中转换dataTable。我在我的前端站点上使用一个文本区域。其中也有断线。但是,问题是当我将这个文件转换为Excel时,文本显示在一行中,而不是换行符。

How can I add a line break in Excel Epplus.

如何在Excel Epplus中添加换行符。

I am using the following script:

我正在使用以下脚本:

using (ExcelPackage pck = new ExcelPackage(newFile)) {
  ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
  ws.Cells["A1"].LoadFromDataTable(dataTable, true);
  pck.Save();
}

2 个解决方案

#1


11  

Did you to turn on text wrap on the cells? It is off by default. So something like this:

你是否打开了单元格的文本?默认情况下是关闭的。所以这样的:

<body>
    <form id="form1" runat="server">
    <div>
        <textarea id="TextArea1" style="height: 200px; width: 400px;" runat="server"></textarea>
    </div>
    <div>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_OnClick" Text="Button" />
    </div>
    </form>
</body>

And this:

这:

protected void Button1_OnClick(object sender, EventArgs e)
{
    //Create the table from the textbox
    var dt = new DataTable();
    dt.Columns.Add("Column1");

    var dr = dt.NewRow();
    dr[0] = TextArea1.InnerText;

    dt.Rows.Add(dr);

    var excelDocName = @"c:\temp\temp.xlsx";
    var aFile = new FileInfo(excelDocName);

    if (aFile.Exists)
        aFile.Delete();

    var pck = new ExcelPackage(aFile);
    var ws = pck.Workbook.Worksheets.Add("Content");
    ws.Cells["A1"].LoadFromDataTable(dt, true);
    ws.Cells["A1:A2"].Style.WrapText = true;  //false by default
    pck.Save();

}

And I drop this in the textarea:

我把这个放到文本区:

This is line 1.

This is line 3.

This is line 5.

Which opens with line breaks shown.

它以显示的换行符打开。

#2


1  

.LoadFromDataTable() will preserve linebreaks that exist in the data in dataTable.

. loadfromdatatable()将保存dataTable中的数据中存在的换行符。

Running this minimal example produces a spreadsheet where the string in cell A2 contains the line break:

运行这个最小的示例生成一个电子表格,其中单元A2中的字符串包含行中断:

DataTable dt = new DataTable();
dt.Clear();
dt.Columns.Add("TestCol");
DataRow row = dt.NewRow();
row["TestCol"] = "string that"+Environment.NewLine+"contains a linebreakc";
dt.Rows.Add(row);

using (var package = new ExcelPackage())
{
    ExcelWorksheet ws = package.Workbook.Worksheets.Add("DataTable");
    ws.Cells["A1"].LoadFromDataTable(dt, true);

    package.SaveAs(new FileInfo(@"C:\Temp\test.xlsx"));
}

I'd guess either the data doesnt contain a linebreak or its just a case that you can only see one line when you open the spreadsheet because of the row height?

我猜要么是数据不包含换行符,要么是因为行高的原因,在打开电子表格时只能看到一行?

#1


11  

Did you to turn on text wrap on the cells? It is off by default. So something like this:

你是否打开了单元格的文本?默认情况下是关闭的。所以这样的:

<body>
    <form id="form1" runat="server">
    <div>
        <textarea id="TextArea1" style="height: 200px; width: 400px;" runat="server"></textarea>
    </div>
    <div>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_OnClick" Text="Button" />
    </div>
    </form>
</body>

And this:

这:

protected void Button1_OnClick(object sender, EventArgs e)
{
    //Create the table from the textbox
    var dt = new DataTable();
    dt.Columns.Add("Column1");

    var dr = dt.NewRow();
    dr[0] = TextArea1.InnerText;

    dt.Rows.Add(dr);

    var excelDocName = @"c:\temp\temp.xlsx";
    var aFile = new FileInfo(excelDocName);

    if (aFile.Exists)
        aFile.Delete();

    var pck = new ExcelPackage(aFile);
    var ws = pck.Workbook.Worksheets.Add("Content");
    ws.Cells["A1"].LoadFromDataTable(dt, true);
    ws.Cells["A1:A2"].Style.WrapText = true;  //false by default
    pck.Save();

}

And I drop this in the textarea:

我把这个放到文本区:

This is line 1.

This is line 3.

This is line 5.

Which opens with line breaks shown.

它以显示的换行符打开。

#2


1  

.LoadFromDataTable() will preserve linebreaks that exist in the data in dataTable.

. loadfromdatatable()将保存dataTable中的数据中存在的换行符。

Running this minimal example produces a spreadsheet where the string in cell A2 contains the line break:

运行这个最小的示例生成一个电子表格,其中单元A2中的字符串包含行中断:

DataTable dt = new DataTable();
dt.Clear();
dt.Columns.Add("TestCol");
DataRow row = dt.NewRow();
row["TestCol"] = "string that"+Environment.NewLine+"contains a linebreakc";
dt.Rows.Add(row);

using (var package = new ExcelPackage())
{
    ExcelWorksheet ws = package.Workbook.Worksheets.Add("DataTable");
    ws.Cells["A1"].LoadFromDataTable(dt, true);

    package.SaveAs(new FileInfo(@"C:\Temp\test.xlsx"));
}

I'd guess either the data doesnt contain a linebreak or its just a case that you can only see one line when you open the spreadsheet because of the row height?

我猜要么是数据不包含换行符,要么是因为行高的原因,在打开电子表格时只能看到一行?