1

I am able to export data table to excel sheet. But, String values are appearing as numericals in excel sheet.

For Eg: 616031100038 is a string in my datatable but in excel sheet it is appearing as a numerical i.e., 6.16031E+11.

I am using following code. dtSO is the datatable with valid content.

            string attachment = "attachment; filename=exportdata.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/vnd.ms-excel";
            string tab = "";
            foreach (DataColumn dc in dtSO.Columns)
            {
                Response.Write(tab + dc.ColumnName);
                tab = "\t";
            }
            Response.Write("\n");
            int i;
            foreach (DataRow dr in dtSO.Rows)
            {
                tab = "";
                for (i = 0; i < dtSO.Columns.Count; i++)
                {
                    Response.Write(tab + dr[i].ToString());
                    tab = "\t";
                }
                Response.Write("\n");
            }
            Response.End();
        }

Is there any way I can format these cells in Response object?

Asha
  • 19
  • 2
  • since you're creating a tab-delimited file and not a real Excel file, it doesn't carry any formatting or data type information and therefore Excel is well within its rights in assuming your strings are numbers (after all, they _look_ like numbers and are _parseable_ as numbers). And the format you're seeing is just how it displays large numbers by default. You can either a) change the format of the column after you open Excel, b) re-write your code using a library (there are free ones) which will create a real Excel file so you can include metadata about the column... – ADyson Jul 27 '17 at 12:35
  • ... or c) try inserting certain special characters in front of the data as suggested in the link given by Andrew Morton above, although to me that feels like a mucky hack. – ADyson Jul 27 '17 at 12:35
  • Thank you very much for the response. I had to use "=\"" + Variable Name + "\"" and it worked fine. – Asha Jul 28 '17 at 09:02

0 Answers0