9 Replies - 13501 Views - Last Post: 07 May 2009 - 01:26 AM Rate Topic: -----

#1 newProgram  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 160
  • Joined: 28-October 08

Excel Date Format

Post icon  Posted 06 May 2009 - 11:14 PM

H ito all,

I have a Windows application that will export the value of DataGridView into an excel file. Exporting is ok, but the problem is that when it export the date which is in this format "yyyy-MM-dd HH:mm:ss", it is being replace by this format "MM/dd/yyyy HH:mm". My question is how to export the data on its original format?

Here is my code in writing the data on excel:
 /*** Add the data from the grid to the sheet starting in row 2 ***/
				for (i = 0; i < datagridview.RowCount - 1; i++)
				{
					for (c = 0; c < datagridview.ColumnCount; c++)
					{
						Parameters = new Object[2];
						Parameters[0] = columns[c] + Convert.ToString(i + 2);
						Parameters[1] = Missing.Value;
						objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
						BindingFlags.GetProperty, null, objSheet_Late, Parameters);
						Parameters = new Object[1];
						Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString();
						objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
						null, objRange_Late, Parameters);
					}
				}



Thanks in advance.

Is This A Good Question/Topic? 1

Replies To: Excel Date Format

#2 ashishshevale  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 92
  • Joined: 14-April 09

Re: Excel Date Format

Posted 06 May 2009 - 11:17 PM

your default windows date format is mm/dd/yy so it showing you in that format
the export is happening in the same way as in datagridview
Was This Post Helpful? 0
  • +
  • -

#3 newProgram  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 160
  • Joined: 28-October 08

Re: Excel Date Format

Posted 06 May 2009 - 11:20 PM

View Postashishshevale, on 6 May, 2009 - 10:17 PM, said:

your default windows date format is mm/dd/yy so it showing you in that format
the export is happening in the same way as in datagridview

Quote

Is there a way to change the format?

Was This Post Helpful? 0
  • +
  • -

#4 ashishshevale  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 92
  • Joined: 14-April 09

Re: Excel Date Format

Posted 06 May 2009 - 11:24 PM

View PostnewProgram, on 6 May, 2009 - 10:20 PM, said:

View Postashishshevale, on 6 May, 2009 - 10:17 PM, said:

your default windows date format is mm/dd/yy so it showing you in that format
the export is happening in the same way as in datagridview

Quote

Is there a way to change the format?

yes
to change windows date format go to control panel and then regoinal settings
I think there is some option to change format settings in excel in tools
have to look at it
Was This Post Helpful? 0
  • +
  • -

#5 newProgram  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 160
  • Joined: 28-October 08

Re: Excel Date Format

Posted 06 May 2009 - 11:57 PM

View Postashishshevale, on 6 May, 2009 - 10:24 PM, said:

View PostnewProgram, on 6 May, 2009 - 10:20 PM, said:

View Postashishshevale, on 6 May, 2009 - 10:17 PM, said:

your default windows date format is mm/dd/yy so it showing you in that format
the export is happening in the same way as in datagridview

Quote

Is there a way to change the format?

yes
to change windows date format go to control panel and then regoinal settings
I think there is some option to change format settings in excel in tools
have to look at it

Thanks for the response. I will try this. One more thing, what I'm doing right now is to change the system DateTime format during the run time of my application but theres no luck.

I tried this:
RegistryKey reg;
			reg = Registry.Users.OpenSubKey("S-1-5-21-1757981266-861567501-682003330-500\\Control Panel\\International",true);
			reg.SetValue("sLongDate", "yyyy-MM-dd HH:mm:ss");


and I got this error "Object reference not set to an instance of an object." on the reg.SetValue.
Was This Post Helpful? 0
  • +
  • -

#6 ashishshevale  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 92
  • Joined: 14-April 09

Re: Excel Date Format

Posted 07 May 2009 - 12:04 AM

View PostnewProgram, on 6 May, 2009 - 10:57 PM, said:

Thanks for the response. I will try this. One more thing, what I'm doing right now is to change the system DateTime format during the run time of my application but theres no luck.

I tried this:
RegistryKey reg;
			reg = Registry.Users.OpenSubKey("S-1-5-21-1757981266-861567501-682003330-500\\Control Panel\\International",true);
			reg.SetValue("sLongDate", "yyyy-MM-dd HH:mm:ss");


and I got this error "Object reference not set to an instance of an object." on the reg.SetValue.

in the line
reg = Registry.Users.OpenSubKey("S-1-5-21-1757981266-861567501-682003330-500\\Control Panel\\International",true);


you are not geting any object instance
put a breakpoint there and see wheter you are geting or not
else
go to run and type regedit and find the key in hklm/users and check whether it exists or in some other format

This post has been edited by ashishshevale: 07 May 2009 - 12:04 AM

Was This Post Helpful? 0
  • +
  • -

#7 newProgram  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 160
  • Joined: 28-October 08

Re: Excel Date Format

Posted 07 May 2009 - 12:10 AM

View Postashishshevale, on 6 May, 2009 - 11:04 PM, said:

View PostnewProgram, on 6 May, 2009 - 10:57 PM, said:

Thanks for the response. I will try this. One more thing, what I'm doing right now is to change the system DateTime format during the run time of my application but theres no luck.

I tried this:
RegistryKey reg;
			reg = Registry.Users.OpenSubKey("S-1-5-21-1757981266-861567501-682003330-500\\Control Panel\\International",true);
			reg.SetValue("sLongDate", "yyyy-MM-dd HH:mm:ss");


and I got this error "Object reference not set to an instance of an object." on the reg.SetValue.

in the line
reg = Registry.Users.OpenSubKey("S-1-5-21-1757981266-861567501-682003330-500\\Control Panel\\International",true);


you are not geting any object instance
put a breakpoint there and see wheter you are geting or not
else
go to run and type regedit and find the key in hklm/users and check whether it exists or in some other format

Thanks. I've change the key and theres no error. But nothing happens, the DateTime format does not change.
Was This Post Helpful? 0
  • +
  • -

#8 ashishshevale  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 92
  • Joined: 14-April 09

Re: Excel Date Format

Posted 07 May 2009 - 12:22 AM

I think you are not doing it in a right way
check this link
you might get something
Was This Post Helpful? 0
  • +
  • -

#9 newProgram  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 160
  • Joined: 28-October 08

Re: Excel Date Format

Posted 07 May 2009 - 12:27 AM

View Postashishshevale, on 6 May, 2009 - 11:22 PM, said:

I think you are not doing it in a right way
check this link
you might get something

I have changed it directly on the registry, but still no luck
Was This Post Helpful? 0
  • +
  • -

#10 ashishshevale  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 92
  • Joined: 14-April 09

Re: Excel Date Format

Posted 07 May 2009 - 01:26 AM

try doing this

Quote

Start a registry editor (e.g., regedit.exe).
Navigate to the HKEY_USERS registry key.
From the File menu, select Load Hive.
Navigate to the \%systemdrive%\documents and settings\default user folder, select ntuser.dat, then click Open.
Enter a name for the registry entry (e.g., Default User), then click OK.
Navigate to the HKEY_USERS\Default User\Control Panel\International registry subkey.
Double-click the sShortDate registry entry.
Set the value to the date format required (e.g., dd/MM/yyyy), then click OK.
You can also change the sLongDate registry entry for the longer date format (e.g., dd MMMM yyyy), then click OK.
Select the HKEY_USERS\Default User registry subkey (or whatever you named it) again, then from the File menu select Unload Hive.
Click Yes to the confirmation.
Close the registry editor

might change registry value
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1