Parsing a .Net C# data class into Excel

I have a data class that is populated from a SQL Statement and I would like to easily parse into Excel. I won't always know how many property fields are in the data class in order to populate cell by cell so I'm looking for a higher level method to easily parse into Excel. Anyone have any ideas?
Who is Participating?
StephanConnect With a Mentor Lead Software EngineerCommented:
Well, you can convert a List<T> to a DataTable but it must not container nullable parameters like Nullable<int> (int?), Nullable<DateTime> etc..

public class ListtoDataTableConverter
        public DataTable ToDataTable<T>(List<T> items)
            DataTable dataTable = new DataTable(typeof(T).Name);
            //Get all the properties
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
                //Setting column names as Property names
             foreach (T item in items)
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                    //inserting property values to datatable rows
                    values[i] = Props[i].GetValue(item, null);
             //put a breakpoint here and check datatable
            return dataTable;

Open in new window

Please note that this is not performance friendly, there are third party libraries that will have better performance like Vanilla, Hyper
StephanLead Software EngineerCommented:
I used aspose.cells that generates excel files and it can import datatables.
Alexandre SimõesManager / Technology SpecialistCommented:
The excel file is a list of data or you have to fill only certain cells?

If it's for certain cells what I propose is to have custom placeholders on the cells you want to populate and replace them with the property fields of your class.

If you don't have a property field for a found place holder just clear the cell, otherwise replace by the property field value.

A placeholder could be: ${Name}
Search all cells and if you find one that contains ${ then it's a place holder, parse it, get the property field name, search on your data class if you have it and eather clean the cell or replace by the value.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Dennis AriesCEO @ Arkro ITCommented:
You forget to mention whether you want to parse it into an existing document or if you want to create a new document that can be opened using Excel.

For the latter one, the most easy way is using XML-Excel. Codeproject has a nice example for doing so. I use this technique a lot since it is a simple XML-file to create and can be openend by all clients I've encountered.

If you need to parse it into an existing closed document, I'd recommend using ADO

for instance:
string fileName = @"D:\test.xlsx"; 
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" + 
        "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName); 

using(OleDbConnection cn = new OleDbConnection(connectionString))
    OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Sheet1$] " + 
         "([Column1],[Column2],[Column3],[Column4]) " + 
         "VALUES(@value1, @value2, @value3, @value4)", cn);
   cmd1.Parameters.AddWithValue("@value1", "Key1");
   cmd1.Parameters.AddWithValue("@value2", "Sample1");
   cmd1.Parameters.AddWithValue("@value3", 1);
   cmd1.Parameters.AddWithValue("@value4", 9);

Open in new window

If the document is opened, use Excel-Automation to fill the cells you require.
Some examples can be found at
csalernoAuthor Commented:
All good info.
But what I'm really looking for is a non-third party way to parse a List<T> of data classes thru Excel Automation. I have all of the code, just missing a clean way of parsing. Some of my data classes have a couple hundred properties.
csalernoAuthor Commented:
Thanks for the code that worked perfectly.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.