c# Excel line feed within cell

Question

I have been trying in vain to automate a line feed into an excel cell. I start with a SQL query that I call from asp.Gridview (Visual Studios c#) that outputs an excel file. I can follow the string in quetion all the way into excel with breakpoints and I see \r\n. Once in excel, nothing happens.

I have been working on this issue for 6 days. I have tried every possible carriage return and line feed I could think of. Interesting enough c# converts char(10) back into \n. I have also tried @"\u000a" and among others. The word wrap is working fine. basically I have a list of dates in a cell. My client would like each date to be on a separate line feed within the cell. When I simply push the results into excel I'll get multiple dates on the same line and then a word wrap and more dates. The closest I have gotten is to treat the script like a formula and add '=' to the begining with " around each date then char(10). The issue with that is its a formual and word wrap doesn't engage. I can double click on the row to auto-fit and everything looks great (except its a formula and an extrenal client might not know how to extract the specific date they need from the list). This is a dynamic report based on real time data accessed through a web page. The client can extract the results from the page via an excel file. I have successfully done this for 13 other previous reports. This one is unique because of the line feed.

Here is my c# code that I use within the RowDataBound Event

e.Row.Cells[cellcnt].Text = e.Row.Cells[cellcnt].Text.Replace("\r\n", Convert.ToString((char)10));

Any thoughts?

Answer 1

The escape sequence \n' is a linefeed which maps to decimal 10 so ((char)10) == '\n'` is the same thing. All your code is doing is replacing the more traditional CRLF with just a LF.

I'm a little confused on the code you posted. That looks like code that you're using to manipulate the UI GridView and not the Excel data itself. Changing things in the UI isn't going to impact the Excel file at all. To change the data in the Excel file you'll need to transform the data while writing the Excel file.

Here's an example of how you might open an Excel spreadsheet, enumerate the rows and modify the data in the second column to replace spaces with a newline such that Excel shows it on two separate lines.

var app = new Excel.Application();
Excel.Workbook workbook = null;

try
{
    workbook = app.Workbooks.Open(path);

    Excel.Worksheet sheet = workbook.ActiveSheet;
    foreach (Excel.Range row in sheet.UsedRange)
    {
        var oldValue = row.Cells[1, 2].Value;
        var newValue = (oldValue?.ToString() ?? "").Replace(" ", "\n");
        row.Cells[1, 2].Value = newValue;
    };

    workbook.Save();
} finally
{
    workbook.Close();
    app.Quit();
};

I'm using Excel interop here but the flow is basically the same. I'm not sure how you're generating the Excel file but at some point you'll be extracting the data from the UI (assuming you don't already have it stored in memory somewhere) and it is at that point that you'll want to convert the text to using newlines (\n).

Answer 2

I'm populating a gridview and exporting to excel with Response.Write:

    private void ExportToExcel(string strFileName, GridView gv)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
        Response.ContentType = "application/excel";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gv.RenderControl(htw);
        for (int count = 0; count < gv.Rows.Count; count++)
        {
            for (int colcount = 0; colcount < gv.Rows[count].Cells.Count; colcount++)
            {
                string a = gv.Rows[count].Cells[colcount].Text;
                string b = gv.Rows[count].Cells[colcount].Text;
            }
        }
        bool AttrSelectedExprt = Convert.ToBoolean((String)ViewState["AttrSelectedExprt"]);
        if (AttrSelectedExprt)
        {
            string headerTable = @"<Table><tr><td colspan=&quot;6&quot;><font face=&quot;Lato&quot;, size=&quot;3&quot;><b>" + HeadingLabel.Text + "</font></td></tr><tr><td colspan=&quot;6&quot;><font face=&quot;Lato&quot;, size=&quot;3&quot;><b>" + AttrName + " <font color=&quot;Blue&quot;>" + AttrTotal + "</font></b></font></td></tr></Table>";
            Response.Write(headerTable);
        }
        else
        {
            string headerTable = @"<Table><tr><td colspan=&quot;6&quot;><font face=&quot;Lato&quot;, size=&quot;3&quot;><b>" + HeadingLabel.Text + "</font></td></tr></Table>";
            Response.Write(headerTable);
        }
        Response.Write(sw.ToString());
        Response.End();
    }

I added string a & b to check the contents of the cell and they still contain \r\n.

Answer 3

Thank you very much, you helped me in an indirect way. I simply added . . . .

        for (int count = 0; count < gv.Rows.Count; count++)  
        {  
            for (int colcount = 0; colcount < gv.Rows[count].Cells.Count; colcount++)  
            {  
                if (gv.Rows[count].Cells[colcount].Text.Contains("\r\n"))  
                {  
                    gv.Rows[count].Cells[colcount].Text = @"<Table><tr><td>" + gv.Rows[count].Cells[colcount].Text.Replace("\r\n", "<br/>") + "</td></tr></Table>";  
                }  
           }  
        }  

. . . . to that ExportToExcel code I previously sent you. It adds a line feed within the desidered cell!!!

PREVIOUS RESULTS

NEW RESULTS

Its a legacy code that was built before I started working over 8 yrs ago.

Answer 4

I'm sorry, yes I have resolved my issue. How do I go about closing this query?

Answer 5

The issue was solved indirectly from a response. I'd hate to accept the answer if it was incomplete. The answer gave me a new direction were I was able to resolve my issue.