locked
Export data table to excel, how to avoid data converting to date. RRS feed

  • Question

  • User442831413 posted

    Hi All,

    I've used the following line of code to export data table in excel file but the problem in exporting  the data appears when data table having values like 1-2 and 1/2 and it converts the value to 2-Jan in both cases after exporting.

    I've to avoid this case in my export feature; any help in this scenario is greatly appreciated. Thanks all.

    public static void ExportToSpreadsheet(DataTable table, string name)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            string ColValue;



            foreach (DataColumn column in table.Columns)
            {
                context.Response.Write(column.ColumnName + "\t");

            }
            context.Response.Write(Environment.NewLine);
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {

                    ColValue = row[i].ToString();
                    ColValue = ColValue.ToString().Replace(",", string.Empty) + "\t";
                    ColValue = ColValue.ToString().Replace(Environment.NewLine, " ");
                    ColValue = ColValue.ToString().Replace("\n", " ");
                    ColValue = ColValue.ToString().Replace("&nbsp;", "");
                    ColValue = ColValue.ToString().Replace("-Select-;", "");
       
                    context.Response.Write(ColValue);
                }

                context.Response.Write(Environment.NewLine);

            }

            context.Response.ContentType = "application/ms-excel";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename = " + name + ".xls");
            context.Response.End();
        }

    Tuesday, December 28, 2010 8:27 AM

All replies

  • User551462331 posted

    try this....

    for (int i = 0; i < table.Columns.Count; i++)
                {

                    if(i==4) // if column 4 contains data like 1/2 or 1-2

                   {

                    ColValue = row[i].ToString();
                    ColValue = ColValue.ToString().Replace(",", string.Empty) + "\t";
                    ColValue = ColValue.ToString().Replace(Environment.NewLine, " ");
                    ColValue = ColValue.ToString().Replace("\n", " ");
                    ColValue = ColValue.ToString().Replace("&nbsp;", "");
                    ColValue = ColValue.ToString().Replace("-Select-;", "");
                    ColValue = "'" + ColValue
                    context.Response.Write(ColValue);
                   }

                }

    here, i just appended single quote to value 1/2 considering column 5 (0 to 4) contains such value...

    hope this helps...

    Tuesday, December 28, 2010 9:19 AM
  • User442831413 posted

    thanks dear for ur reply ... this solution is not good enough that solution was in back of my mind but i m looking 4 some generic solution that helps in my case ... as i have to provide various data tables of different natures and every time i've to number various columns in my IF clause for different data tables ... another reason for not using above solution is my data table has too many columns and it becomes tedious task to use it.

    Any other help or clue is greatly appreciated. Smile


    if somehow we can set the excel file's Format Cell to Text through code then we can achieve the target.

    Tuesday, December 28, 2010 11:41 AM