Unexpected date format when writing to a StringBuilder

Question

I am building a StringBuilder with a column of date type and appends their some dates with my format YYYY-MM-DD.

Later, I conver it to byte[] and then creates an .csv file from it:

public ActionResult DownloadUsersDates()
{
    string data = Encoding.UTF8.GetBytes(GetAllDates());
    var res = Encoding.UTF8.GetPreamble().Concat(data).ToArray();
    return File(res, "text/csv", "DatesList.csv");
}

public string GetAllDates()
{
    var sb = new StringBuilder();
    const string separater = ",";
    const string columnEscaper = "\"";

    sb.Append("Date");

    //Doing some code...

    foreach (var item in myItems)
    {
         sb.Append(columnEscaper);
         sb.Append((item.BirthDate.ToString("yyyy-MM-dd"))); //returns dates of format: `YYYY-MM-DD`
         sb.Append(columnEscaper);
         sb.Append(separater);
         sb.Append("\r");
    }
}

The problem is that it changes my format and saves it as another format: DD/MM/YYYY.

I understand that I need somehow to change the column Format Cell. How can I do it?

Thanks in advance.

Answer 1

The problem is that it changes my format and saves it as another format: DD/MM/YYYY.

I believe this is incorrect. If you open the file in a text editor (not Excel) it will be yyyy-MM-dd.

Let's take this the following *.csv file:

This is how it renders in Excel 2016 in Australia.

Excel recognized that the string is a date and formats the cells according to your local settings. What you see will be different to what someone with differently setup Windows would see.

Let's change the column's formatting.

Answer 2

You can try this. I hope it is same as your need.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;


namespace ExportToCSV.Controllers
{    
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {                        
            byte[] data = Encoding.UTF8.GetBytes(GetAllDates());
            var res = Encoding.UTF8.GetPreamble().Concat(data).ToArray();
            return File(res, "text/csv", "DatesList.csv");
        }

        public string GetAllDates()
        {
            var sb = new StringBuilder();
            sb.AppendLine("Date");

            foreach (var item in Clients.clients)
            { 
                sb.Append("'");              
                sb.Append((item.Dob.ToString("yyyy-MM-dd"))); //returns dates of format: `YYYY-MM-DD`             
                sb.Append("',");
                sb.Append("\n");
            }
            return sb.ToString();
        }

    }

    public class Clients
    {
        public string FirstName { set; get; }
        public string LastName { set; get; }
        public string Email { set; get; }
        public DateTime Dob { set; get; }
        public Clients(string firstname, string lastname, DateTime dob, string email)
        {
            this.FirstName = firstname;
            this.LastName = lastname;
            this.Dob = dob;
            this.Email = email;
        }

        public static List<Clients> clients = new List<Clients>
            {
                 new Clients ( "Adam",  "Bielecki",  DateTime.Parse("22-05-1986"),  "adamb@example.com" ),
                 new Clients (  "George1", "Smith",  DateTime.Parse("10-10-1990"),  "george@example.com" ),
                 new Clients (  "George2", "Smith",  DateTime.Parse("10-05-1992"),  "george@example.com" ),
                 new Clients (  "George3", "Smith",  DateTime.Parse("08-12-1998"),  "george@example.com" )
            };
    }
}