C# Generic List Extensions for Data Output to Formatted String, CSV File, and Excel Worksheet Window

• Download source - 6.7 KB

Introduction

The Generic List is my mostly used collection data structure. I very often need to view all or parts of the data from the Generic List with particular type in a console, debugging, or Excel Worksheet window, or sometimes send the data to a CSV file. There are so many tools and code snippets regarding the data output from a List to a string or CSV/Excel file. However, I couldn't find one that meets my needs. Hence I wrote my own methods based on the following requirements.

1. Easy to use as a form of extension methods for the List<T>
2. Options for including or excluding data fields (or properties of the object type)
3. Formatted string for a list of object item if the output is a single string
4. Ability to save the data from List<T> to a CSV file
5. Directly opening a Microsoft Excel Worksheet window with the data in the List<T>

Extension Method Syntax

Output to string (overloaded extension method):

public string IList.ToString<T>([string include = ""], [string exclude = ""])

Output to CSV file: 

public void IList.ToCSV<T>([string path = ""], [string include = ""], [string exclude = ""])

Output to Excel (without using Interop library):

public void IList.ToExcelNoInterop<T>
       ([string path = ""], [string include = ""], [string exclude = ""])

Output to Excel (no file creation but need to use Interop library):

public void IList.ToExcel<T>([string include = ""], [string exclude = ""])

All arguments of the methods are optional. It is recommended using named argument form, i.e., "parameter_name: parameter_value" regardless of the parameter sequence. For example, you can call to create a CSV file from the data in a list (DataSource.Products) having the Product type like this:

DataSource.Products.ToCSV<Product>
           (exclude: "ProductId,OutOfStock", path:@"D:\TestProducts.csv");

Extension Method Details

The GenericListOutput.cs file in the downloaded source contains the code shown below. All necessary comments are attached or code lines are self-explainable. You can add this class into any C# project using the .NET Framework 4.0/Visual C# 2010 and above. The List<T> extension methods should then be ready to use. You may need to add some assembly references to your project if any required reference is missing.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

public static class GenericListOutput
{   
    public static string ToString<T>
           (this IList<T> list, string include = "", string exclude = "")
    {
        //Variables for build string
        string propStr = string.Empty;
        StringBuilder sb = new StringBuilder();

        //Get property collection and set selected property list
        PropertyInfo[] props = typeof(T).GetProperties();
        List<PropertyInfo> propList = GetSelectedProperties(props, include, exclude);
        
        //Add list name and total count
        string typeName = GetSimpleTypeName(list);        
        sb.AppendLine(string.Format("{0} List - Total Count: {1}", 
                      typeName, list.Count.ToString()));

        //Iterate through data list collection
        foreach (var item in list)
        {
            sb.AppendLine("");
            //Iterate through property collection
            foreach (var prop in propList)
            {                    
                //Construct property name and value string
                propStr = prop.Name + ": " + prop.GetValue(item, null);                        
                sb.AppendLine(propStr); 
            }
        }
        return sb.ToString();
    }
    
    public static void ToCSV<T>(this IList<T> list, string path = "", 
                                string include = "", string exclude = "")
    {
        CreateCsvFile(list, path, include, exclude);        
    }

    public static void ToExcelNoInterop<T>(this IList<T> list, 
                  string path = "", string include = "", string exclude = "")
    {
        if (path == "") 
            path = Path.GetTempPath() + @"ListDataOutput.csv";  
        var rtnPath = CreateCsvFile(list, path, include, exclude);
        
        //Open Excel from the file
        Process proc = new Process();
        //Quotes wrapped path for any space in folder/file names
        proc.StartInfo = new ProcessStartInfo("excel.exe", "\"" + rtnPath + "\"");
        proc.Start();        
    }

    private static string CreateCsvFile<T>(IList<T> list, string path, 
                   string include, string exclude)
    {
        //Variables for build CSV string
        StringBuilder sb = new StringBuilder();
        List<string> propNames;
        List<string> propValues;
        bool isNameDone = false;        

        //Get property collection and set selected property list
        PropertyInfo[] props = typeof(T).GetProperties();
        List<PropertyInfo> propList = GetSelectedProperties(props, include, exclude);

        //Add list name and total count
        string typeName = GetSimpleTypeName(list);
        sb.AppendLine(string.Format("{0} List - Total Count: {1}", 
                      typeName, list.Count.ToString()));

        //Iterate through data list collection
        foreach (var item in list)
        {
            sb.AppendLine("");
            propNames = new List<string>();
            propValues = new List<string>();

            //Iterate through property collection
            foreach (var prop in propList)
            {
                //Construct property name string if not done in sb
                if (!isNameDone) propNames.Add(prop.Name);
                
                //Construct property value string with double quotes 
                //for issue of any comma in string type data
                var val = prop.PropertyType == typeof(string) ? "\"{0}\"" : "{0}";
                propValues.Add(string.Format(val, prop.GetValue(item, null)));
            }
            //Add line for Names
            string line = string.Empty;
            if (!isNameDone)
            {
                line = string.Join(",", propNames);
                sb.AppendLine(line);
                isNameDone = true;
            }
            //Add line for the values
            line = string.Join(",", propValues);
            sb.Append(line);
        }       
        if (!string.IsNullOrEmpty(sb.ToString()) && path != "")
        {
            File.WriteAllText(path, sb.ToString());
        }                
        return path;
    }

    public static void ToExcel<T>
           (this IList<T> list, string include = "", string exclude = "")
    {                        
        //Get property collection and set selected property list
        PropertyInfo[] props = typeof(T).GetProperties();
        List<PropertyInfo> propList = GetSelectedProperties(props, include, exclude);
        
        //Get simple type name
        string typeName = GetSimpleTypeName(list); 

        //Convert list to array for selected properties
        object[,] listArray = new object[list.Count + 1, propList.Count];
        
        //Add property name to array as the first row
        int colIdx = 0;
        foreach (var prop in propList)
        {        
            listArray[0, colIdx] = prop.Name;
            colIdx++;
        }        
        //Iterate through data list collection for rows
        int rowIdx = 1;
        foreach (var item in list)
        {
            colIdx = 0;
            //Iterate through property collection for columns
            foreach (var prop in propList)
            {
                //Do property value
                listArray[rowIdx, colIdx] = prop.GetValue(item, null);
                colIdx++;
            }
            rowIdx++;
        }
        //Processing for Excel
        object oOpt = System.Reflection.Missing.Value; 
        Excel.Application oXL = new Excel.Application();
        Excel.Workbooks oWBs = oXL.Workbooks;
        Excel.Workbook oWB = oWBs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
        Excel.Worksheet oSheet = (Excel.Worksheet)oWB.ActiveSheet;
        oSheet.Name = typeName;
        Excel.Range oRng = 
              oSheet.get_Range("A1", oOpt).get_Resize(list.Count+1, propList.Count);        
        oRng.set_Value(oOpt, listArray);
        //Open Excel
        oXL.Visible = true;
    }

    private static List<PropertyInfo> GetSelectedProperties
                   (PropertyInfo[] props, string include, string exclude)
    {
        List<PropertyInfo> propList = new List<PropertyInfo>();        
        if (include != "") //Do include first
        {
            var includeProps = include.ToLower().Split(',').ToList();
            foreach (var item in props)
            {                
                var propName = includeProps.Where
                    (a => a == item.Name.ToLower()).FirstOrDefault();
                if (!string.IsNullOrEmpty(propName))
                    propList.Add(item);
            }
        }        
        else if (exclude != "") //Then do exclude
        {
            var excludeProps = exclude.ToLower().Split(',');
            foreach (var item in props)
            {
                var propName = excludeProps.Where
                               (a => a == item.Name.ToLower()).FirstOrDefault();
                if (string.IsNullOrEmpty(propName))
                    propList.Add(item);
            }
        }        
        else //Default
        {
            propList.AddRange(props.ToList());
        }
        return propList;
    }

    private static string GetSimpleTypeName<T>(IList<T> list)
    {
        string typeName = list.GetType().ToString();
        int pos = typeName.IndexOf("[") + 1;
        typeName = typeName.Substring(pos, typeName.LastIndexOf("]") - pos);
        typeName = typeName.Substring(typeName.LastIndexOf(".") + 1);
        return typeName;
    }
}

Property Selection for Data Output

Providing options of selecting output data properties (a.k.a., fields) is a powerful feature for viewing the desired data held in the Generic List. We usually use LINQ to select object item in a List collection (equivalent to rows in a table) but use reflection to select properties (equivalent to columns in a table). Sometimes, we want to exclude some properties, especially for auto added non-user data properties in particular type of projects. Other times, we may just need part, but not all, of the properties. With these extension methods, we can specify the property names in a comma delimited string as the optional include or exclude argument based on the needs. These arguments are case-insensitive for easy use although the property names in the object are case-sensitive.

Also note that the include argument, if having a non-empty string value, will always take the precedence in the code processing logic.

As mentioned in previous section, the named argument form is recommended for calling these extension methods. Named arguments free you from the need to remember or to fit the order of parameters in the parameter lists of called methods, especially for the optional arguments. You can see the examples in the below section.

Would Not Like Interop?

The IList<T>.ToExcel() method calls the Microsoft.Office.Interop.Excel library, converts the List data to the Excel Worksheet format, and then opens the Excel window without any file created first. Some developers may not like to add the Interop reference to projects. Then the alternative is to open the Excel from the created CSV file using the IList<T>.ToExcelNoInterop() method. There are two options on dealing with the CSV file creation when using this option.

1. Calling the method without taking care of the CSV file. The method defaults the path to the current user’s temporary directory with the file name ListDataOutput.csv. You will take no action on the CSV file as if it was not there. Although this file will not automatically be deleted, only one file with this name is kept in the temporary directory. This temporary file will be overwritten each time when you call the same method. You can, of course, manually save the CSV file or the Worksheet file as whatever file name or available type to any other location from the opened Excel window.
    
2. Calling the method with file path you specify. In this case, the CSV file is saved to the path specified and the Excel window will automatically be opened with the data from the CSV file.

Double Quote Wrappers

The code in two CSV related methods handles some small tricks by using double quote wrappers to prevent those methods or data output from exceptions that could break the method executions or data structures.

1. When a file path contains space in folder or file names, the methods in the .NET Framework System.IO.Path class such as GetTempPath() handles them well. It’s not so lucky if we inject such a file path string into the ProcessStartInfo constructor. When starting the process of Excel application, we need to send the path string wrapped by double quotes as shown in this line of code:

   //Quotes wrapped path for any space in folder/file names
   proc.StartInfo = new ProcessStartInfo("excel.exe", "\"" + path + "\"");

2. Any comma in a data value will break the intact structure of a CSV file if the value is not wrapped by double quotes. But we may only need the double quotes for values of string type. Since we already get the property into using the reflection, we can easily add the code for the issue.

   //Construct value string with double quotes for issue of any comma in string type data
   var val = prop.PropertyType == typeof(string) ? "\"{0}\"" : "{0}";
   propValues.Add(string.Format(val, prop.GetValue(item, null)));

Examples of Calling Extension Methods

The downloaded source includes the DataSource class in which the Products property points to the List<Product> collection that is populated with the Product data. After you build the sample application, you can run it to see all four kinds of output results or just test the methods one by one.

1. Output to a string for displaying in the Console window:

   //Get string result for display on Console or Debugging window
   string result = DataSource.Products.ToString<Product>
                   (include:"ProductName,CategoryId,UnitPrice");
   Console.Write(result);

   The screenshot below shows the formatted string for the List data.

   

2. Output to a CSV file:

   //Export to CSV file
   DataSource.Products.ToCSV<Product>
       (exclude: "ProductId,OutOfStock", path: @"D:\TestProducts.csv");

   This saves the CSV file in the path specified.  

3. Opening an Excel Worksheet window from a CSV file created without referencing the Interop:

   //Open Excel from file created in user temp directory without Interop
   DataSource.Products.ToExcelNoInterop<Product>(exclude: "ProductId,OutOfStock");

   This saves the ListDataOutput.csv file in the user temporary directory by default and then automatically opens the Excel Worksheet window.  

   

4. Opening an Excel Worksheet window referencing the Interop library:

   //Directly open Excel
   DataSource.Products.ToExcel<Product>(exclude: "ProductId");

   No file is created in this case. Sheet1 is displayed in the window's title. When exiting the Excel window, you will then be prompted to save the file.

   

Summary  

The C# Generic List data output extension methods described here have many features and are easy to use. I hope that these methods are helpful to developers who need them for their daily work.

History

• 19^th Nov 2013: Initial version