CODEWITHSUNDEEP
c#.netstring
Karthika Subramanian
Karthika Subramanian

Reputation: 346

C# - format column when writing to CSV file(cell formatting)

Have written the method for the exporting the data to csv files. If the value of the cell has DEC20 in csv is getting as 20-Dec which is not correct.

My code is like this:

for (int i = 0; i < myData.Count(); i++)
{
    sb.AppendLine(string.Join(delimiter, 
                              Common.FormatExportString(myData[i].Code),
                              Common.FormatExportString(myData[i].Name),
                              Common.FormatExportString(myData[i].Description)));
}
//returns the file after writing the stream to the csv file.
return File(new System.Text.UTF8Encoding().GetBytes(sb.ToString()), "text/csv", fileName);

Kindly help me to get the exact string format to be displayed in the excel (csv) file using C#.

Ex: myData[i].Name data will be

DEC20, or 2-5

like this,

but the output in csv(excel) is getting displayed as

20-Dec and 5-May

instead of the original one.

Upvotes: 5

Views: 21392

Answers (4)

Tomer W
Tomer W

Reputation: 3443

The problem is not the CSV Output,

The issue is that Excel is "Too Smart for it's own good" and automatically detects the data-type and deduce that DEC20 is "December 20", even though it is not!

you can instruct Excel of a Text column by adding a ' before the text. as
"'my Val", "'Dec10", "'54/40/2042", "'22/7/2013", "439.54"

All these will be treated as Text, and not Date/Numeric or anything else.

Cheers

Upvotes: 2

Happy coder
Happy coder

Reputation: 59

If the string is being collapsed, should manipulate the strings in the right way to be accepted by excel.

if the string has, single quotes('') add "'ur string'" and with overall string should be within the ="your string with so all special characters and the commas"

Upvotes: 0

Tobia Zambon
Tobia Zambon

Reputation: 7629

The problem is not concerning the csv exportation, if you open the csv file with notepad it is well formed. It is Excel that auto-detects the cell type as a date and shows it as a date.

To avoid this behavior wrap the text between quotes and put a = before it, as shown below:

= "DEC20"

The file should became as

= "field1", = "field2", = "field...", = "DEC20", ...

Your method should become:

for (int i = 0; i < myData.Count(); i++)
{
    sb.AppendLine(string.Join(delimiter, 
                              " = \"",
                              Common.FormatExportString(myData[i].Code),
                              Common.FormatExportString(myData[i].Name),
                              Common.FormatExportString(myData[i].Description)
                              "\""));
}

//returns the file after writing the stream to the csv file.
return File(new System.Text.UTF8Encoding().GetBytes(sb.ToString()), "text/csv", fileName);

Upvotes: 12

Nir
Nir

Reputation: 4013

CSV is a very simple format that will allow Excel to apply default formats on the text. I suggest you write your Excel file without the use of CSV, but Excel API You should specify how you want to format the cells.

The Excel COM object will allow you to write native Excel file. You can read about it here: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ef11a193-54f3-407b-9374-9f5770fd9fd7

Upvotes: 2

Related Questions