Format excel date fields using OledbCommand

Question

0.00/5 (No votes)

See more:

I'm writing values to excel as follows

C#

OleDbConnection excelConnection = null;
excelConnection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + location + ";Mode=ReadWrite;Extended Properties=Excel 8.0;");
excelConnection.Open();

OleDbCommand cmd = new OleDbCommand(createCommand, excelConnection);
cmd.ExecuteNonQuery();

Then I create Insert command and insert records to excel sheet.

C#

insertCommand.ExecuteNonQuery();

But when I insert Date value with the format of "yyy.mm.dd" it writes date with the current regional language format to the excel.

Is there any way to set date format in excel using OleDbCommand

Posted 20-Feb-12 22:20pm

Sanyon_d

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pietvredeveld · Accepted Answer · 2012-02-20T22:40:00

I don't think there is a solution for it.
It is a good thing that Excel uses the current regional settings for the date format. Excel documents can be from computers using different regional settings without any problem.

When you're sure you want to store a date in a specific regional format best use a String data type for it.

Piet

Ganesan Senthilvel · Accepted Answer · 2012-02-20T22:40:00

Solution 2

Excel converts value fields like Number/Date etc to the appropriate format set on the machine. On changing the system culture, we can change the format; not sure about OleDB code.

Posted 20-Feb-12 22:40pm

Ganesan Senthilvel

Comments

pietvredeveld 21-Feb-12 8:02am

When you change the system culture of the culture of the current thread, OleDB will use that culture. In my understanding the requirement is to store in another culture than the current. That can not been achieved as far as I know.