How do I add single quote to an EXCEL cell using ASP.NET C# code/program that reads data from an SQL Table and producing an EXCEL report from it?

Question

How do I add an additional single quote character to an EXCEL/CSV cell when I have an ASP.NET C# code/program that reads data from an SQL Table and producing an EXCEL report from it? So that the number produced is not scientific value , it would be exact (its an additional single quote character to a cell that will have data). I need to add the single quote before the data, but when added, the single quote itself will not show, rather, the exact number will show.

The problem is in the image below, from the SQL Result it is an exact number, but when the code is used it is rounded to scientific value. Image: http://photoadder.com/show-image.php?id=0ca30b7f4ba55fe359c889af62a278a4 (click to enlarge)

When I typed the single quote to the excel my self this is the result. Image:http://photoadder.com/show-image.php?id=48366a8093afc8555bcf7c990fc3648e

Here are parts of my code

public DataTable GetData()
{
   var dataTable = new DataTable();

   SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
   conn.Open();
   string query = "select * from table abc";

   SqlCommand cmd = new SqlCommand(query, conn);

   DataTable t1 = new DataTable();
   using (SqlDataAdapter a = new SqlDataAdapter(cmd))
   {
       a.Fill(t1);
   }
   return t1;
}
#endregion


protected void btnGenerateReport_Click(object sender, EventArgs e)
{
    System.Diagnostics.Debug.WriteLine("PLaccount system on click");

    var dataTable = GetData();

    StringBuilder builder = new StringBuilder();
    List<string> columnNames = new List<string>();
    List<string> rows = new List<string>();

    foreach (DataColumn column in dataTable.Columns)
    {
        columnNames.Add(column.ColumnName);
        if (column.ColumnName.ToString() == "PL_ACCOUNT_NUMBER")
        {

            System.Diagnostics.Debug.WriteLine("PLaccount system match");

        }
    }

    builder.Append(string.Join(",", columnNames.ToArray())).Append("\n");

    foreach (DataRow row in dataTable.Rows)
    {
        List<string> currentRow = new List<string>();

        foreach (DataColumn column in dataTable.Columns)
        {
            object item = row[column];

            currentRow.Add(item.ToString());
        }

        rows.Add(string.Join(",", currentRow.ToArray()));
    }

    builder.Append(string.Join("\n", rows.ToArray()));

    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AddHeader("Content-Disposition", "attachment;filename=myfilename.csv");
    Response.Write(builder.ToString());
    Response.End();

Answer 1

This is a long-standing issue with the way Excel imports CSV files. Because a CSV file doesn't include any information on the data types, Excel makes some guesses based on the string values. This is especially vexing when you have string values that look like numbers but aren't supposed to be interpreted that way... like phone numbers with leading 0s that need to retain those 0s.

Most applications that import CSVs recognize some for of string quoting, such as enclosing strings that could be misinterpreted as something else (including strings with embedded commas) in a pair of double-quotes. Excel handles this during the first part of the import process, so you can use it to put commas in string values. Unfortunately it throws away the double-quotes before it starts interpreting the values.

(I could cheerfully throttle the guys that wrote the Excel CSV importer for that.)

Fortunately, you can use the single-quote character at the start of a string value to encourage Excel to treat it as a string instead of trying to interpret it as a number. This works the same in a CSV as it does in Excel.

Here's a (partial) line from your CSV as it is:

701,888888001074709,XXXXXXXXXXXXXXXXXXXX837659,2/27/2013 12:00:00 AM,...

Assuming that you want just the second value to be treated as a string, you need to change your output method to produce the following:

701,'888888001074709,XXXXXXXXXXXXXXXXXXXX837659,2/27/2013 12:00:00 AM,...

Excel will import the data correctly, but will then hide the single-quote character just the same as if you'd typed data in a cell that way. References to the cells will get the characters after the single-quote, etc.

Now that I've explained why you need to, there are a couple of things you should figure out about how to do it.

If you're trying to write a generic DataTable exporter, without having to specify the columns that need to be quoted, you'll need to do one of:

1. Examine each column's data type and pre-pend a single-quote to every string column (the fast and easy but aesthetically displeasing option)

2. For each string column's value try parsing as a number and only prepend a single-quote if it works out.

3. Pass in an optional list of columns (by name or index) to quote.

Of the three, providing an optional list of columns to treat specially is my preference.

If you're not trying for a generic solution, try this:

    foreach (DataRow row in dataTable.Rows)
    {
        List<string> currentRow = new List<string>();
        foreach (DataColumn column in dataTable.Columns)
        {
            object item = row[column];

            if (column.Ordinal == 1)
                currentRow.Add("'" + item.ToString());
            else
                currentRow.Add(item.ToString());
        }
        rows.Add(string.Join(",", currentRow.ToArray()));
    }