C# data output format

Question

Good afternoon all, As always, thank you in advance for your help. I have made a windows form in VS 2019 and have a datetimePicker item. The form data is saved in CSV format and then used in Tableau. The date is stored as ddMMyyyy when saved which is a huge problem. For example, If saved data on 5th Aug 2020, I want it to save it as YYYYMMDD (2020-08-05) but it saves it as 05-08-2020. When I open the saved file, it will show the date as May 08 2020. I simply want the date format to save as YYYY-MM-DD.. private void dateTimePicker1_ValueChanged( object sender, EventArgs e) { dateTimePicker1.Format = DateTimePickerFormat.Custom; dateTimePicker1.CustomFormat = "yyyyMMdd" ; } Private void textbox8_Textchanged( object sender, EventArgs e) xlworksheet.Cells[1, 1] = textBox1.Text; xlworksheet.Cells[1, 2] = dateTimePicker1.Value;

Amit Gupta · Answer

@Shane Created a small peice a code for you that solves your issue (as stated in earlier post to create a format for the library you are using (i.e Interop.excel). // namespace used using System; using Excel = Microsoft.Office.Interop.Excel; using Microsoft.Office.Interop.Excel; using System.IO; void SaveAsCSV() { Application xlap = new Application(); // if it set to false then after exiting your application a process of Microsoft.Excel keeps running // in the background that consumes system resources xlap.UserControl = true ; Workbook xlworkbook = xlap.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet xlworksheet = (Worksheet)xlworkbook.Worksheets[1]; SetDateFormat(xlworksheet, 1, 2); xlworksheet.Cells[1, 1] = "first value" ; xlworksheet.Cells[1, 2] = DateTime.Now; var dir = AppContext.BaseDirectory; var file = Path.Combine(dir, "excel_" + DateTime.Now.ToString( "yyyyMMddHHmmss" )) + ".csv" ; xlworkbook.SaveAs(file, XlFileFormat.xlCSV); xlworkbook.Close( false ); xlap.Quit(); } void SetDateFormat(Worksheet ws, int row, int col, string format = "yyyy-MM-dd" ) { ((Range)ws.Cells[row, col]).EntireColumn.NumberFormat = format; } Output: BTW, it's a csv file (basically a text file seperated by a comma), which really doesn't require any library to save it, infact Interop.excel is having some cons like (needs excel to have installed in the target computer alongwith slow performance issue), you can save it using any TextWriter in C#. Update: If you are looking in excel then the format might be different because it converts the format accoring to system environment date. Don't just open the csv file in excel, instead import the csv file and you will get your desired output and ofcourse doesn't required any library/function to convert the format Do let me know, if it resolves your issue!

Amit Gupta · Answer

Try setting formatting like Range rg = (Excel.Range)worksheetobject.Cells[1,2]; rg.EntireColumn.NumberFormat = "yyyy-MM-dd";https://stackoverflow.com/a/3310837

Amit Gupta · Answer

Which library you are using for xlworksheet? If all stuffs doesn't work then you neeed to change the format in that cell or column. Do recheck that and let us know, if it resolves the issue

Ben Thompson · Answer

If you just want the string representation, you can use what Sachin mentioned. However, the dateTimePicker1.Value is already a DateTime object so the cast isn't necessary. DateTimePicker picker = new DateTimePicker(); string date = picker.Value.ToString("yyyy-MM-dd"); The upper-case "MM" indicates month. The microsoft docs have more info on custom datetime strings. https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings Since you're using a custom string to unpack this into a new DateTime object, it'll need to be parsed, and you'll need to instruct the parser exactly what values in the string represent which parts of the datetime: DateTime dt = DateTime.ParseExact(date, "yyyyMMdd" , CultureInfo.InvariantCulture);

Sachin Singh · Answer

You can use ToString() like below xlworksheet.Cells[ 1 , 2 ] = ((dateTime)dateTimePicker 1 .Value).ToString( "yyyy-mm-dd" );

Shane Nicholson · Answer

Good afternoon Ben, So I tested what you mentioned and I am getting the file as MONTH/DAY/YEAR now... I changed the format to yyyyMMdd and removed it completely and still get the same outcome. There is no way in my code that specifies the date output to be MMddyyyy, its so strange. Please keep your suggestions coming.. Thank you

Ben Thompson · Answer

You have this Event Handler in the code you pasted: private void textBox11_TextChanged( object sender, EventArgs e) { textBox11.Text = DateTime.Now.ToString( "dd-MM-yyyy" ); } So it's always going to be shifted to "dd-MM-yyyy" format. Either remove the event handler or modify it so the changed text is formatted the yyyyMMdd you prefer.

Amit Gupta · Answer

There is no chance, the solution won't work. Recommend you to remove the reference if interop and take a fresh reference, clean and rebuild your project.

Shane Nicholson · Answer

Good morning Amit, I tried that too. I added it at the beginning of the code after creating a new xlworksheet, still the same format. I cannot believe how diffiuclt something like this is..

Shane Nicholson · Answer

Morning All, Thank you all for taking the time to reply. Its relly nice to know there are so many knowledgeable people out there who are willing to help. I have provided the entire code below for more information. The document still saves in the format ddMMyyyy with the current code, its becoming frustrating. I removed the dattimepicker and replaced it with textBox11 to try and make it easier. Best regards.. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Runtime.InteropServices; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.IO; using Excel = Microsoft.Office.Interop.Excel; using Microsoft.Office.Interop.Excel; using System.Runtime.ExceptionServices; using System.Globalization; namespace Navigations_delivery_report { public partial class Form1 : Form { Excel.Application xlap = new Excel.Application(); public Form1() { InitializeComponent(); textBox1.Text = Environment.UserName; textBox11.Text = DateTime.Now.ToString( "yyyy-MM-dd" ); textBox10.Enabled = false ; comboBox1.Items.Add( "SLT AVOIDANCE" ); comboBox1.Items.Add( "MTT" ); comboBox1.Items.Add( "MCT" ); comboBox1.Items.Add( "MFT" ); comboBox1.Items.Add( "MDT" ); comboBox1.Items.Add( "DEFRTE" ); comboBox1.Items.Add( "PRED" ); } private void label1_Click( object sender, EventArgs e) { } private void label5_Click( object sender, EventArgs e) { } private void textBox1_TextChanged( object sender, EventArgs e) { } private void button1_Click( object sender, EventArgs e) { string result = "Please Enter " ; if (textBox1.Text == string .Empty) { result += " Name," ; } if (textBox2.Text == string .Empty) { result += " CallSign," ; } if (textBox3.Text == string .Empty) { result += " Departure," ; } if (textBox4.Text == string .Empty) { result += " Arrival," ; } if (textBox5.Text == string .Empty) { result += " Fuel," ; } if (textBox6.Text == string .Empty) { result += " Cost," ; } if (textBox7.Text == string .Empty) { result += " Time," ; } if (comboBox1.SelectedItem == null ) { result += " Type Of Route," ; } if (comboBox2.SelectedItem == null ) { result += " Route Method," ; } if (checkBox1.Checked == false ) { result += " Confirm OFP Matches ATC Plan," ; } else { if (result.Contains( ',' )) { result = result.Remove(result.LastIndexOf( ',' )); } } if (result != "Please Enter " ) { MessageBox.Show(result); } else { Excel.Workbook xlworkbook; Excel.Worksheet xlworksheet; object misValue = System.Reflection.Missing.Value; xlworkbook = xlap.Workbooks.Add(misValue); xlworksheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(1); Excel.Sheets worksheets = xlworkbook.Worksheets; worksheets[3].Delete(); worksheets[2].Delete(); xlworksheet.Cells[1, 1] = textBox1.Text; xlworksheet.Cells[1, 2] = textBox11.Text = DateTime.Now.ToString( "yyyy-MM-dd" ); xlworksheet.Cells[1, 3] = textBox2.Text; xlworksheet.Cells[1, 4] = textBox3.Text; xlworksheet.Cells[1, 5] = textBox4.Text; xlworksheet.Cells[1, 6] = textBox5.Text; xlworksheet.Cells[1, 7] = textBox6.Text; xlworksheet.Cells[1, 8] = textBox7.Text; xlworksheet.Cells[1, 9] = textBox8.Text; xlworksheet.Cells[1, 10] = textBox9.Text; xlworksheet.Cells[1, 13] = textBox10.Text; xlworksheet.Cells[1, 11] = comboBox1.Text; xlworksheet.Cells[1, 12] = comboBox2.Text; xlworkbook.SaveAs(@ "\172.21.4.14\BusinessLanding\BusinessLanding_Sync\operations_sync\FlightPlanning\savings_" + DateTime.Now.ToString( "yyyyMMddHHmmss" ) + ".csv" , Excel.XlFileFormat.xlCSVWindows); xlworkbook.Close( true ); xlap.Quit(); textBox2.Clear(); textBox3.Clear(); textBox4.Clear(); textBox5.Clear(); textBox6.Clear(); textBox7.Clear(); textBox8.Clear(); textBox9.Clear(); textBox10.Clear(); comboBox1.SelectedIndex = -1; comboBox2.SelectedIndex = -1; checkBox1.Checked = false ; textBox2.Focus(); } } private void comboBox2_SelectedIndexChanged( object sender, EventArgs e) { } private void button2_Click( object sender, EventArgs e) { Close(); } public void Button3_Click( object sender, EventArgs e) { textBox2.Clear(); textBox3.Clear(); textBox4.Clear(); textBox5.Clear(); textBox6.Clear(); textBox7.Clear(); textBox8.Clear(); textBox9.Clear(); textBox10.Clear(); comboBox1.SelectedIndex = -1; comboBox2.SelectedIndex = -1; checkBox1.Checked = false ; textBox2.Focus(); } private void textBox8_TextChanged( object sender, EventArgs e) { } private void textBox9_TextChanged( object sender, EventArgs e) { } private void button4_Click( object sender, EventArgs e) { } private void textBox6_TextChanged( object sender, EventArgs e) { if (! string .IsNullOrEmpty(textBox6.Text) && textBox6.Text != "-" ) { double firstboxS; double SECONDS; double answerS; var format = new NumberFormatInfo(); format.NegativeSign = "-" ; format.NumberDecimalSeparator = "." ; firstboxS = double .Parse(textBox6.Text, format); SECONDS = double .Parse(textBox8.Text, format); answerS = firstboxS - SECONDS; textBox9.Text = (answerS.ToString()); } } private void textBox5_TextChanged( object sender, EventArgs e) { if (! string .IsNullOrEmpty(textBox5.Text) && textBox5.Text != "-" ) { double firstbox; double answer; var format = new NumberFormatInfo(); format.NegativeSign = "-" ; format.NumberDecimalSeparator = "." ; firstbox = double .Parse(textBox5.Text, format); answer = firstbox / 1000 * 3.18141 * 2.3 * 1.19; textBox8.Text = (answer.ToString()); } } private void button4_Click_1( object sender, EventArgs e) { } private void label7_Click( object sender, EventArgs e) { } private void label12_Click( object sender, EventArgs e) { } private void textBox7_TextChanged( object sender, EventArgs e) { } private void textBox10_TextChanged( object sender, EventArgs e) { } private void comboBox1_SelectedIndexChanged( object sender, EventArgs e) { int selectedIndex = comboBox1.SelectedIndex; if (selectedIndex != -1) { object selectedItem = comboBox1.SelectedItem; if (selectedItem.ToString() == "SLT AVOIDANCE" ) { textBox10.Enabled = true ; } else { textBox10.Enabled = false ; } } } private void textBox11_TextChanged( object sender, EventArgs e) { textBox11.Text = DateTime.Now.ToString( "dd-MM-yyyy" ); } }

Shane Nicholson · Answer

Hi Sachin, Thank you for taking the time to reply. The ((dateTime) part didnt work. I think I have figured it out anyway. But as always, much appreciated...