C# exporting csv with incorrect column format (Excel)

Question

I am exporting a gridview full of data to a CSV file, one of the columns contains a coupon number that is up to 18 digits long, non floating point. The number needs to be displayed as their full number the csv file in Excel. When I open the csv file in Excel, for example, 720137994699937608 and the others display as:

Then when I resize the column or change the column type to number the coupon number gets displayed as:

You'll notice that excel changed the coupon number into a floating point number and now the numbers within the coupon number have been replaced with zeros at the end. This is bad.

Here is my export code:

protected void btnExport_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=Reports All Prizes.csv");
            Response.Charset = "";
            Response.ContentType = "csv";

            StringBuilder sb = new StringBuilder();

            sb.Append("FirstName" + ',');
            sb.Append("LastName" + ',');
            sb.Append("CouponNumber" + ',');
            string[] splitColumns = sb.ToString().Split(new Char[] { ',' });
            //append new line
            sb.Append("\r\n");

            for (int i = 0; i < gvReportsAllPrizes.Rows.Count; i++)
            {
                foreach (string column in splitColumns)
                {
                    if (column == "FirstName")
                    {
                        //capatilize first letter.
                        string first = Globals.ReplaceHTML(gvReportsAllPrizes.Rows[i].Cells[1].Text.Trim());
                        if (string.IsNullOrEmpty(first))
                        {
                            first = "";
                        }
                        if (!string.IsNullOrEmpty(first))
                        {
                            first = first.ToLower();//convert to lowercase before uppercasing the first letter.
                            first = "\"" + first.First().ToString().ToUpper() + String.Join("", first.Skip(1)) +
                                "\"";
                        }
                        sb.Append(first + ',');
                    }
                    else if (column == "LastName")
                    {
                        //capatilize first letter.
                        string last = Globals.ReplaceHTML(gvReportsAllPrizes.Rows[i].Cells[2].Text.Trim());
                        if (string.IsNullOrEmpty(last))
                        {
                            last = "";
                        }
                        if (!string.IsNullOrEmpty(last))
                        {
                            last = last.ToLower();//convert to lowercase before uppercasing the first letter.
                            last = "\"" + last.First().ToString().ToUpper() + String.Join("", last.Skip(1)) +
                                "\"";
                        }
                        sb.Append(last + ',');
                    }
                    else if (column == "CouponNumber")
                    {
                        string cn = Globals.ReplaceHTML(gvReportsAllPrizes.Rows[i].Cells[8].Text.Trim());
                        if (string.IsNullOrEmpty(cn))
                        {
                            cn = "";
                        }
                        if (!string.IsNullOrEmpty(cn))
                        {
                            sb.Append("\"" + cn + "\"" + ',');
                        }
                        else
                        {
                            sb.Append(cn + ',');
                        }
                    }

                }
                //append new line
                sb.Append("\r\n");
            }
            Response.Output.Write(sb.ToString());
            Response.Flush();
            Response.End();
        }

public static string ReplaceHTML(string strInput)
{
    strInput = strInput.Replace("&quot;", "\"" + "\"");
    strInput = strInput.Replace("&amp;",  "&");
    strInput = strInput.Replace("&nbsp;", string.Empty);
    strInput = strInput.Replace("''", "'");
    return strInput.Replace("&#39;", "'").Trim();
}

Raw txt file csv:

FirstName,LastName,CouponNumber, "first","last","720137994699937608"

Answer 1

You can wrap each value like this:

"=""DATA HERE"""

For example:

Column1,Column2
"=""1231231231231323121321""","=""123"""

Obviously it isn't pretty in the csv file, but it imports into excel as you want.

Answer 2

You can't do anything about it if you're using a CSV file. As others have mentioned, this is an Excel formatting issue. I would suggest using an Excel library of some sort if you want to manage your document's formatting.

Answer 3

Excel's maximum precision is 15 digits. Would it be anathema to display these as strings?

Answer 4

If there's no need for the CSV file you can directly export the gridview to excel. However, if you still need the csv file the easiest way to display the long number in excel column is to convert that column to text prior to importing csv to excel.

Here're the steps.

1. Open excel. Click on open, locate the file. Once you open you will get the following screen.

2. Choose Delimited and click next. Select Other add (,) and then click next.

3. On the next screen highlight the column and press text.

Here's the final result: