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

[deepakg_rao]

If you are using response.write to export the gridview to excel, then add the line
string brstyle = @"<style>br { mso-data-placement:same-cell; }</style>";
Response.Write(brstyle);
just before exporting the gridview. Hope this helps somebody like myself..

[lucas823]

Hi you can check this article on codeproject: http://www.codeproject.com/Articles/...el-for-ASP-NET