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
This is not the most up to date version of this article. The most recent version can be found here.
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.
|
|
Sat, Oct 18, 2008 8:35 AM
by anonymouswrites
|
You can use http://www.filehelpers.com/
to export to CSV and read CSV data
|
|
Wed, Dec 17, 2008 4:59 AM
by sirdneo
|
Nice code, but it will be better if it is more generic code i.e. I just pass data table to code and it will convert it to csv.
|
|
Tue, Jan 20, 2009 5:12 PM
by joseluis2411
|
very nice, thank you
|
|
Fri, Mar 6, 2009 2:34 PM
by Grady Christie
|
Very good article. This will help me out a great deal.
|
|
Mon, May 25, 2009 4:16 AM
by neha16jain
|
Good.. I tried and it is working perfectly fine. Thanks a lot for sharing...
|
|
Wed, Jul 1, 2009 8:54 AM
by srinivaskotra
|
good article, if you have time write export to csv from gridview.
|
|
Fri, Jul 3, 2009 1:23 AM
by prajeeshkkindia
|
working fine....
|
|
Thu, Oct 29, 2009 1:19 AM
by yrb.yogi
|
Great work..
|
|
Thu, Jul 15, 2010 12:16 PM
by bazmanblue
|
But how do you call this class on page load? protected void Page_Load(object sender, EventArgs e) { CSVExporter call = new CSVExporter(); cat... }
|
|
Sun, Dec 19, 2010 1:02 PM
by jonathanwood
|
Your code strips out embedded commas within CSV values. It's possible store such values unchanged by wrapping them in double quotes. Note that this also requires special handling for embedded double quotes. I presented a couple of simple classes that correctly handle both cases for both reading and writing CSV files. I've posted it at http://www.blackbeltcoder.com/Articles/files/reading-and-writing-csv-files-in-c.
|
|
Tue, May 24, 2011 7:53 AM
by WebSlider
|
I have a problem. I have a page that displays a report when you click a button called "View" (depending on what values you select in the drop down fields on the page). The click handler for the "View" button is: void ViewButton_Click(object sender, EventArgs e) { CurrentPageIndex = 1; GenerateReport(); } If on the other hand I click on another button called "Export to CSV" another handler is called. The code for this handler is: private void WriteCsvOutput(string csvOutput, string fileName) { Response.ContentType = "application/octet-stream"; Response.AppendHeader("content-disposition", string.Format("attachment; filename=\"{0}\"", fileName)); Response.Clear(); Response.Output.Write(csvOutput); Response.End(); } The problem is this. Once I click on the "Export to CSV" button, now matter how many times I click the "View" button, the handler for the "View" button never, ever gets called after that. Instead the handler for the "Export to CSV" button gets called. Which means that instead of getting a display of the report on the page I get a CSV file whether I click "View" or "Export to CSV". Anyone know why this is happening and what can be done about it? Thanks, Sach
|