Monday, May 17, 2010

Convert Generic List to CSV

In my recent development with c#, I have to code for the functionality to Convert one Generic list of class XYZ to CSV downloadable File with dynamically suppress some of the column and different descriptive headers then the properties name for the column.

So I develop one Generic function which will convert List to CSV and return the string, which will then wrapped to Http Response to available as downloadable file.

Here is a Function which will convert List of Generic type T to CVS format string with 2 arguments. First Argument is List of objects to be converted into CSV and second is the 2 dimensional string array with the name of properties of object to be include in conversion with the Header Text for that properties to use while it is converting into CSV.

private static string ListToCsv(ref List<T> list, string[,] HeaderAndColumns)
        {
            if (list == null || list.Count == 0) return "";

            Type t = typeof(T);
            int lenght = HeaderAndColumns.GetLength(0);

            StringWriter sw = new StringWriter();

            // Create Header Row..
            for (int l = 0; l < lenght; l++)
            {
                sw.Write(HeaderAndColumns[l, 0].Replace(',', ' '));
                if (l < lenght - 1)
                    sw.Write(",");
            }
            sw.Write(sw.NewLine);

            //this acts as datarow

            foreach (T item in list)
            {
                for (int i = 0; i < lenght; i++)
                {
                    string whatToWrite =
                        Convert.ToString(item.GetType().GetProperty(HeaderAndColumns[i, 1]).GetValue(item, null)).Replace(',', ' ');

                    if (i < lenght - 1)
                        whatToWrite += ",";

                    sw.Write(whatToWrite);
                }
                sw.Write(sw.NewLine);
            }

            return sw.ToString();
        }

If we have class XYZ as follow

public class XYZ
    {
        public string prop1 { get; set; }
        public string prop2 { get; set; }
    }

And we want to convert list of XYZ's object (List<XYZ>) to CSV we have to call the function like following

// 2D string arry , 1st is header , 2nd is Property name
    string[,] HeaderAndColumns = new string[,] {
            {"Header 1",      "prop1"},
            {"Header 2","prop2"}
            };

string strCSV = ListToCsv(ref list, HeaderAndColumns);

To warp is with Http Response you can use following code. This will help you to through the converted CSV string as downloadable file.

HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename={0}", filename));
            HttpContext.Current.Response.ContentType = "application/ms-excel";

            using (StringWriter objSw = new StringWriter())
            {
                   string[,] HeaderAndColumns = new string[,] {
                        {"Header 1",      "prop1"},
                        {"Header 2","prop2"}
                        };
                  objSw.Write(ListToCsv(ref list, HeaderAndColumns));
                  HttpContext.Current.Response.Write(objSw.ToString());
                HttpContext.Current.Response.End();
            }