How can I give line break in excel epplus c#

c# asp.net epplus

20,058

Solution 1

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();
    pck.Dispose();

}

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.

Solution 2

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

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

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?

20,058

Author by

test twes

Updated on July 09, 2022

Comments

test twes almost 2 years
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.

How can I add a line break in 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();
}
```