Multiline in a Excel Cell, When exporting data from datatable to Excel

[hereiskishore]

Hi All,

I am trying to export datatable to excel. In the datatable in one of the column I am concatinating two strings. Ex: str1 and str2. When I export to excel. str1 should come in first line and str2 in second line in the same cell. Manually Alt+Enter works fine in Excel. I want to do it programatically. I tried vbCrLf. It is moving to next line. I want new line in the same Cell.

[Cimperiali]

use vbLf or CHAR(10) , instead of vbcrlf. It will make it, as long as the cell allows to wrap text

[hereiskishore]

I am not using interop. Can you post some sample code. Below is the code I am using for exporting datatable to Excel. In this case how can I wrap the cell.

Dim attachment As String = "attachment; filename=xyzReport" & Date.Now.Year & Date.Now.Month & Date.Now.Day & Date.Now.Ticks & ".xls"
Response.ClearContent()
Response.AddHeader("content-disposition", attachment)
Response.ContentType = "application/vnd.ms-excel"
Dim tab As String = ""
For Each dc As DataColumn In dtExcel.Columns
Response.Write(tab + dc.ColumnName)
tab = vbTab
Next
Response.Write(vbLf)
Dim j As Integer
For Each dr As DataRow In dtExcel.Rows
tab = ""
For j = 0 To dtExcel.Columns.Count - 1
Response.Write(tab + dr(j).ToString())
tab = vbTab
Next
Response.Write(vbLf)
Next
Response.[End]()

[Cimperiali]

***EDIT**** Moved from vb Net Forum

I am not able to see the two colums you're concatenating
in any case:
you're making it with tabs... try with a table, it might suite you a bit better.
Ie: in an App_code Class

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Text
Public Class DtToExcel

Public Shared Function GenerateReport(ByVal dt As DataTable, ByVal Sv As System.Web.HttpServerUtility) As String

Dim sb As New StringBuilder()
sb.AppendLine("<table><tr>")

For Each dc As DataColumn In dt.Columns
sb.AppendLine("<td>" + Sv.HtmlEncode(dc.ColumnName) + "</td>")

Next
sb.AppendLine("</tr>")

For Each dr As DataRow In dt.Rows

sb.AppendLine("<tr>")
For Each dc As DataColumn In dt.Columns

If dr(dc) IsNot Nothing AndAlso dr(dc) IsNot DBNull.Value Then
sb.AppendLine("<td>" + Sv.HtmlEncode(dr(dc).ToString()) & "</td>")
Else

sb.AppendLine("<td></td>")
End If
Next
sb.AppendLine("</tr>")

Next


sb.AppendLine("</table>")
Return sb.ToString()

End Function

End Class


in a web form

Private Sub showReport()
'

Response.AddHeader("Content-Disposition", "attachment;filename=Report_" & Now.Ticks & ".xls")
Response.Buffer = True
Response.Charset = String.Empty
Response.ContentType = "application/vnd.ms-excel"

'a method that give me back the datatable I need here
'you should get your own dt...
Dim dt As System.Data.DataTable = Manager.GetDt("Customers")

Response.Write(DtToExcel.GenerateReport(dt, Server))
Response.Flush()
Response.End()

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
showReport()
End Sub

[Cimperiali]

and then, to get what you want, you can add the proper style to the <br /> you want to behave as alt+Enter:

If dr(dc) IsNot Nothing AndAlso dr(dc) IsNot DBNull.Value Then

sb.AppendLine("<td>" & Sv.HtmlEncode(dr(dc).ToString()) & "<br style='mso-data-placement:same-cell;' />Second Line in single cell</td>")
Else

sb.AppendLine("<td></td>")
End If