Thursday, June 5, 2008

How to export data to CSV file in ASP.NET

Download the source code

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 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 be screwed 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=PerosnList.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 item in personList)

{

WriteUserInfo(item);

}

HttpContext.Current.Response.End();

}

private static void WriteUserInfo(Person item)

{

StringBuilder strb = new StringBuilder();

AddComma(item.Name, strb);

AddComma(item.Family, strb);

AddComma(item.Age.ToString(), strb);

AddComma(string.Format("{0:C2}", item.Salary), strb);

HttpContext.Current.Response.Write(strb.ToString());

HttpContext.Current.Response.Write(Environment.NewLine);

}

private static void AddComma(string item, StringBuilder strb)

{

strb.Append(item.Replace(',', ' '));

strb.Append(" ,");

}

private static void WriteColumnName()

{

string str = "Name, Family, Age, Salary";

HttpContext.Current.Response.Write(str);

HttpContext.Current.Response.Write(Environment.NewLine);

}

}

Download the source code

How to pass parameters to Reporting Services from .Net applications

In the previous post you saw how to create a report in reporting services and you also learned how to create parameter for the report. You simple can show the same report and you do not need to pass parameter since everything is inside the report. But unfortunately you cannot change the UI of State dropdownlist or View Report button. So you may want to remove this parameter palette and show this information in another custom control then pass the information to reporting service.

First add a dropdownlist to the page that has the states and also a report viewer control and a button to trigger to show the report.

Add all states on the dropdownlist either by loading information from database or adding them manually. Then on button click please write this code

ReportViewer1.ShowCredentialPrompts = false;

ReportViewer1.ServerReport.ReportServerCredentials = new ReportCredentials("emady", "MyPassword", "hhi");

ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://falcon/Reportserver");

ReportViewer1.ServerReport.ReportPath = "/Report Project State/Report1";

List<ReportParameter> parameters = new List<ReportParameter>();

parameters.Add(new ReportParameter("state", DropDownListStates.SelectedItem.Text));

ReportViewer1.ServerReport.SetParameters(parameters);

ReportViewer1.ProcessingMode = ProcessingMode.Remote;

ReportViewer1.ShowParameterPrompts = false;

ReportViewer1.ShowPromptAreaButton = false;

ReportViewer1.ServerReport.Refresh();

The only change that you may see compare to what we had in previous clip is that:

We set that we do not want seeing any credentials error prompt. Moreover, we create a list of ReportParameter and add the state parameter to this list and we are passing this list to SetParameter method. Also we are hiding parameter part from report. Finally we are hiding any prompt from report server.

Important: ReportCredentials class is a custom class please get the code from previous posts about reporting services. Also please see the clip to understand how it works.

please see the clip