How can I give line break in excel epplus c#
Asked Answered
L

3

22

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();
}
Ly answered 19/4, 2015 at 15:53 Comment(0)
B
26

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.

Bingaman answered 20/4, 2015 at 0:28 Comment(0)
S
4

.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?

Sodomite answered 19/4, 2015 at 16:45 Comment(2)
+1 This works but it does not automatically format the cells so that the text is displayed properly.(just caveat emptor)Michealmicheil
Chad is right, for visual purposes this solution does not work well.Thermel
E
0

You can also try this solution:

string textOriginal = "First line text.| Second line text.";
string textToWrite = textOriginal.Replace("|", ("" + ((char)13) + ((char)10)));

myWorksheet.Cells["a1"].Value = textToWrite;
Elysha answered 30/3, 2022 at 23:11 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.