Menu
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();
}
422
The character code for a line break in Excel varies depending on the platform. On Windows, the code is 10 and on a Mac it's 13. Note: make sure you have Wrap Text enabled on cells that contain line breaks. In Excel 365, both Win and Mac versions of Excel use CHAR(10) as a line break.
No, it does not require Excel to be installed on the server, as you can read in the docs: EPPlus is a . NET library that reads and writes Excel files using the Office Open XML format (xlsx). EPPlus has no dependencies other than .
EPPlus does not work with the XLS format.
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.
167
.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?
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
