First let’s see what the CSV file is:
CSV file is a text based file in which data are separated by comma. It can be opened by excel so you can use excel functionality. Each row of data including the title is in separate line. Meanwhile, each row has data separated by comma.
How to create a CSV file?
We need to write data to the Response object. Meanwhile the content type should be text/csv also a header type of attachment should be added to response. Afterwards, column names are written to response then to actual data is written (each row should be just in one line).Finally you need to call Response.End to finalize your work.
Important: if any part of data has comma unfortunately the CSV file will screw up. What I did in the code is that I am replacing comma with space so I am sure that the only comma exists in each row is just for separating data rather than being part of data.
public class CSVExporter
{
public static void WriteToCSV(List<Person> personList)
{
string attachment = "attachment; filename=PersonList.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
WriteColumnName();
foreach (Person person in personList)
{
WriteUserInfo(person);
}
HttpContext.Current.Response.End();
}
private static void WriteUserInfo(Person person)
{
StringBuilder stringBuilder = new StringBuilder();
AddComma(person.Name, stringBuilder);
AddComma(person.Family, stringBuilder);
AddComma(person.Age.ToString(), stringBuilder);
AddComma(string.Format("{0:C2}", person.Salary), stringBuilder);
HttpContext.Current.Response.Write(stringBuilder.ToString());
HttpContext.Current.Response.Write(Environment.NewLine);
}
private static void AddComma(string value, StringBuilder stringBuilder)
{
stringBuilder.Append(value.Replace(',', ' '));
stringBuilder.Append(", ");
}
private static void WriteColumnName()
{
string columnNames = "Name, Family, Age, Salary";
HttpContext.Current.Response.Write(columnNames);
HttpContext.Current.Response.Write(Environment.NewLine);
}
} |
Revision number 3, Sunday, June 15, 2008 7:19:51 PM by binoj7
You must Login to comment.
|
Sat, Jun 7, 2008 1:17 PM
by XIII
|
Edited the original article and removed the advertorial links. Made up the layout of the code.
|
|
Tue, Jun 10, 2008 8:35 AM
by Dynamic2008
|
Nice technique! Can we programmatically change the column names as well?
|
|
Mon, Jun 16, 2008 12:25 PM
by Tigraine
|
@Dynamic2008 No, and I think the solution is overly complex for the simple task at hand.
Although I like splitting code into functions, I think AddComma could have been done inline to reduce complexity.
|
|
Mon, Jun 16, 2008 12:50 PM
by Tigraine
|
Ok, stupid me. I was thinking of a lambda solution, but due to the need to replace "," from the original stuff I couldn't come up with anything better.
My only suggestion right now is to keep the ColumNames in a seperate list, pass the HttpContext as a parameter so the code can be used in a dll library as well.
|
|
Wed, Jul 23, 2008 3:57 PM
by sambeetpatra
|
There is a better approach to handle commas. Each column should be enclosed in "" and then separated by comma. The quoted identifiers will then make excel ignore the commas. Now the obvious question will be how to handle tests with " in it. The answer to that is you need to replace each such " with "". So a column with value this"is,junk will be shown as "this""is,junk"" in the csv file.
|
|
Tue, Jul 29, 2008 9:47 AM
by seanon
|
totally agree with Sam, what I tend to do is simply always wrap string data in quote characters (although for extendability you might want to consider accepting separator; usually ",", and qualifying characters "usually "" in the construct). I also like to use reflection to iterate through the properties of a data object to write the header and data therein. That way if you data changes, you don't have to revisit the code to write the CSV file. Only draw back is that the consumer of the csv file is then responsible for hiding/removing any superfluous information. But this is usually easily done in any spreadsheet program.
|
|
Tue, Jul 29, 2008 9:56 AM
by seanon
|
I agree with Sam, except the part about enclosing each column in the data qualifiers. You would generally only want to enclose strings. Some spreadsheet applications prefer numerics to be left as is, 1+1 = 2, "1" + "1" = "11"... Ideally you would be aware of (or check) the data type for each fields and handle accordingly. String in quotes and numerics as is. Also, this is probably out of scope for the article, but I've grown attached to the idea of using reflection to iterate through the properties of a data object to print the column headers and data. Doing this prevents the need to update your CSV writer code if your data object changes in the future (something I would usually forget to do). Only draw back I've found to this approach is that some of the data may be superfluous to the consumer of the CSV file. My feeling there is that most spreadsheet software they would be using to view the file, would likely easily allow the user to remove/hide such data.
|