How to prevent the new line in text box being separate to new row after export to excel in asp.net?

Question

I have one form develop by ASP.NET, for the Notes entry part, i design by using <asp:TextBox ID="requestornote" TextMode="MultiLine" Rows="5" runat="server"></asp:TextBox>

Below was sample form entry output table:

ID       Product        Notes
1        Salt           29NOV  Cleaning Process
                        30NOV  Packing
                        1 DEC  Out from Factory
2        Sugar          30 NOV Packing
                        3 DEC  Sell

Above data was store in SQL Server and when I plug in the export code by using C#, the structure was gone with the ENTER(new line) in Notes column.

Output for current excel:

ID       Product        Notes
1        Salt           29NOV  
Cleaning Process
30NOV  
Packing
1 DEC  
Out from Factory
2        Sugar          30 NOV 
Packing
3 DEC  
Sell

C# code

    protected void Completed_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Completed.xls"));
        Response.ContentType = "application/ms-excel";
        DataTable dt = GetDatafromDatabase_Completed();
        string str = string.Empty;
        foreach (DataColumn dtcol in dt.Columns)
        {
            Response.Write(str + dtcol.ColumnName);
            str = "\t";
        }
        Response.Write("\n");
        foreach (DataRow dr in dt.Rows)
        {
            str = "";
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                Response.Write(str + Convert.ToString(dr[j]));
                str = "\t";
            }
            Response.Write("\n");
        }
        Response.End();
    }

Anyone have ideas?

Answer 1

Since you are directly constructing Excel document by writing to Response, by default \n line feed is considered as new row i.e it goes to next row.

The best and recommended way to construct Excel documents is by using any third party libraries like OpenXML or ClosedXML which are pretty popular free libraries. But for some reason, if you not prefer to use any libraries and continue writing to Response object directly, then I recommend you to construct the Excel using HTML table constructs. In this way you have more control in your hand. For Example,

Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Completed.xls"));
            Response.ContentType = "application/ms-excel";
            Response.Write("<table border='1'>");
            Response.Write("<tr>"); // Header row starts
            Response.Write("<td>Column 1</td> <td>Column2</td>");
            Response.Write("</tr>"); // header Row ends
            Response.Write("<tr>");  // First data row starts
            Response.Write("<td> Row1 Column1 Data 1</td>");
            Response.Write("<td> Row1 Column2 Data 1</td>");
            Response.Write("</tr>"); // First data row ends

            Response.Write("<tr>");  // Second data row starts
            Response.Write("<td> Row2 Column1 Data 1</td>");
            Response.Write("<td> Row2 Column2 Data 1");
            Response.Write("<br>"); // Makes it look like new line in same cell (Alt + Enter in excel)
            Response.Write("Row2 Column2 Data 2");
            Response.Write("<br>"); // Makes it look like new line in same cell (Alt + Enter in excel)
            Response.Write("Row2 Column2 Data 3</td>");
            Response.Write("</tr>"); // Second data row ends

            Response.Write("<tr>"); // Third data row Starts
            Response.Write("<td> Row3 Column1 Data 1</td>");
            Response.Write("<td> Row3 Column2 Data 1</td>");
            Response.Write("</tr>"); // Third data row Starts
            Response.Write("</table>");
            Response.End();

this will result in the excel just like shown in the below image.

In the excel shown above, Cells A3 to A5 are merged automatically. And Cells B3,B4,B5 are still individual cells.

If this satisfies your requirement, then here is the description.

• There is one root <table></table> element.
• As usual, each row is represented by <tr></tr>
• and again, each columns are represented by <td></td>
• Use <br> to add element in next line in same cell. Actually, it merges the rows if (n-1)th column.

So in your case, there is no need of using \n or \t anymore. And your code becomes like this.

protected void Completed_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Completed.xls"));
        Response.ContentType = "application/ms-excel";
        DataTable dt = GetDatafromDatabase_Completed();
        string str = string.Empty;
        Response.Write("<table border='1'>");
        Response.Write("<tr>");
        foreach (DataColumn dtcol in dt.Columns)
        {
            Response.Write("<td>");
            Response.Write(str + dtcol.ColumnName);
            Response.Write("</td>");
        }
        Response.Write("</tr>");
        foreach (DataRow dr in dt.Rows)
        {
            Response.Write("<tr>");
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                Response.Write("<td>");
                String lineFeedToBreakConvertedData = Convert.ToString(dr[j]).Replace("\n", "<br>");
                Response.Write(str + lineFeedToBreakConvertedData);
                Response.Write("</td>");
            }
            Response.Write("</tr>");
        }
        Response.Write("</table>");
        Response.End();
    }

Hope this answers your question.