Maintain text formatting during Excel file download

Question

I have a column "Product_Decription" in MySQL database, and its data type is Text. When user updates the column through UI, there may exists breakline. When my function "Download all product description" exports the data to Excel file, the breakline disappears. How can i maintain the text formatting which similar to the database?

Download function as follows:

Dim forDownloadGV As New GridView()
    forDownloadGV.DataSource = ""
    forDownloadGV.DataBind()

    Dim myConnection As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataset As DataSet

    Dim strSQL As String

    myConnection = New MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("for_Read").ConnectionString)

    myConnection.Close()
    myConnection.Open()

    strSQL = "SELECT REPLACE(REPLACE(`Product_Desription`, char(13), '<br/>'), CHAR(10), '<br/>') as 'Description'from `Products`"
    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)

    myDataset = New DataSet()

    myDataAdapter.Fill(myDataset, "Products")

    forDownloadGV.DataSource = myDataset
    forDownloadGV.DataBind()

    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=Products.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Dim sw As New StringWriter()
    Dim hw As New HtmlTextWriter(sw)

    For i As Integer = 0 To forDownloadGV.Rows.Count - 1
        'Apply text style to each Row
        forDownloadGV.Rows(i).Attributes.Add("class", "textmode")
    Next
    forDownloadGV.RenderControl(hw)

    'style to format numbers to string
    Dim style As String = "<style> .textmode{mso-number-format: \@;}</style>"
    Response.Write(style)
    Response.Output.Write(sw.ToString())
    Response.Flush()
    Response.End()

I have tried replacing to but the Excel file when downloaded still does not maintain the original text formatting.

Any help? Should the formatting be done at the Select statement?

Answer 1

if it helps anyone. Below will be the solution.

    Dim forDownloadGV As New GridView()
    forDownloadGV.DataSource = ""
    forDownloadGV.DataBind()

    Dim myConnection As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataset As DataSet

    Dim strSQL As String

    myConnection = New MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("for_Read").ConnectionString)

    myConnection.Close()
    myConnection.Open()

    strSQL = "SELECT REPLACE(REPLACE(`Product_Desription`, char(13), '<br>'), CHAR(10), '<br>') as 'Description'from `Products`"
    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)

    myDataset = New DataSet()

    myDataAdapter.Fill(myDataset, "Products")

    forDownloadGV.DataSource = myDataset
    forDownloadGV.DataBind()

    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=Products.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Dim sw As New StringWriter()
    Dim hw As New HtmlTextWriter(sw)

    hw.AddAttribute("xmlns:x", "urn:schemas-microsoft-com:office:excel") ' optional'
    hw.RenderBeginTag(HtmlTextWriterTag.Html)
    hw.RenderBeginTag(HtmlTextWriterTag.Head)
    hw.RenderBeginTag(HtmlTextWriterTag.Style)
    hw.Write("br {mso-data-placement:same-cell;}")
    hw.RenderEndTag() ' /Style'
    hw.RenderEndTag() ' /Head'
    hw.RenderBeginTag(HtmlTextWriterTag.Body)

    forDownloadGV.RenderControl(hw)

    hw.RenderEndTag() ' /Body'
    hw.RenderEndTag() ' /Html'

    Response.Write(HttpUtility.HtmlDecode(sw.ToString))    ' turns &lt;br/&gt; back into <br/>'
    Response.Flush()
    Response.End()