C# Excel Reads Characters at Date Format ?

Question

0.00/5 (No votes)

See more:

Hi,
I am exporting a table to the excel at c#.
There is a field which type is double. There are deciamal values at this field.
But when I export table to the excel, it converts the values to the date format.

For the value 4,5 , excel shows this value like 04.May.
I replaced values which contains "," with "." before sending to the excel. But that does not work. It shows the values at date format again.

How can I prevent this situation ?

Thanks for the replies in advance..

Posted 5-Nov-12 4:44am

no_-_namee

Add a Solution

Comments

njammy 5-Nov-12 10:48am

Are you explicitly exporting to XLS format or CSV?

no_-_namee 5-Nov-12 13:21pm

I am exporting to the excel at CSV format..

njammy 5-Nov-12 14:05pm

Can you post the sample csv content, or the line which is being incorrectly formatted, along with the headings(data types).

1 solution

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)

Maciej Los · Answer 1 · 2012-11-05T08:59:00

Solution 1

Use Range.NumberFormat[^] property to change cell/range format.

For example:

C#

Excel.Range oRange = objSheet.get_Range("A:A",system.type.missing);
oRange.NumberFormat = "General";
//or
oRange.NumberFormat = "# ##0.00";

Note: You need to change it before saving data into MS Excel sheet.

Posted 5-Nov-12 8:59am

Maciej Los

Comments

no_-_namee 6-Nov-12 1:42am

Thanks for the replies . I thought that it exported at CSV format because of the name similarity. But the object is written by anyone. There is a class which named CSVString .The columns are append by adding ";" between them. And for a row, every field of the row is read and appended to a stringBuilder before exported to the excel.

By the way , when I look to datatable at quickwatch before exporting it to the excel, the field records is seen like 4,5 as it should be. But excel reads the column which type is double, as datetime column.

Thanks for the reply..

Maciej Los 6-Nov-12 15:29pm

What is Excel decimal and thousand separator? What is windows system decimal and thousand separator? Show me a function to export data to Excel.