Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Exporting a List of any type to Excel

, 9 Mar 2010
Rate this:
Please Sign up or sign in to vote.
A few months ago I posted a article about exporting and formatting a datagridview to Excel.Well what about exporting a List to Excel?Sure, some may wonder but what's the point? Cause it's very easy to havelike a hidden dataGridView and when exporting one could easily add something...
A few months ago I posted a article about exporting and formatting a datagridview to Excel.
 
Well what about exporting a List<T> to Excel?
Sure, some may wonder but what's the point? Cause it's very easy to have
like a hidden dataGridView and when exporting one could easily add something like dvg1.DataSource = myList;. While it is totally true, it does have a downside in the memory consumption. Sure for small amounts of data no problem but for like 50.000 items each containg like 10-15 properties(e.g. 50.000 rows by 10-15 columns) the extra memory is not so ignorable. In winforms the dgv keeps a copy of the actual datas => twice needed memory. Firts for the List and again
for the dgv.
 
Ok so I'm too lazy to update the article so I decided to post a tip/trick.
 
OK so for a cell by cell aproach(SLOOOW) it will be something like:
//somewhere in the export methods
//create the column(s) header(s)
int col=1;
foreach(var propInfo in lst[0].GetType().GetProperties())
{
    //write down the property names => column(s) header(s)
    //lst a List<object>
    excelSheet.Cells[1, col] = propInfo.Name;      
    col++;
}
 
//put the actual datas
for (int i = 0; i < lst.Count; i++)
{
  int j = 1;
  foreach (var propInfo in lst[i].GetType().GetProperties())
  {
     excelSheet.Cells[i+2, j].Value = propInfo.GetValue(lst[i], null);
     j++;
  }
}
 
Here is the code from the article that does a fast export.
It creates a two dimensional object[,] from the datagridview.
Ideea "stolen" from Peter Moon.
 
        //creates the two dimensional object[,] from the datagridview
        private object[,] CreateTwoDimensionalObject()
        {
            object[,] datas = new object[dgv.Rows.Count + 1, dgv.Rows[0].Cells.Count];
 
            //add the first row(the column headers) to the array
            for (int col = 0; col < dgv.Columns.Count; col++)
            {
                datas[0, col] = dgv.Columns[col].HeaderText;
            }
 
            //copy the actual datas
            for (int col = 0; col < dgv.Rows[0].Cells.Count; col++)
            {
                for (int row = 0; row < dgv.Rows.Count; row++)
                {
                    datas[row + 1, col] = dgv.Rows[row].Cells[col].Value.ToString();
                }
            }
 
            return datas;
        }
 
And Here is the method ported for a List<T>. List<object> actually:
 
//creates the two dimensional object[,] from the List
private object[,] CreateTwoDimensionalObject()
{
object[,] datas= new object[lst.Count + 1, lst[0].GetProperTies.Count]
 
int col=0;
foreach(var propInfo in lst[0].GetType().GetProperties())
{
    //write down the property names => column(s) header(s)
    //lst a List<object>
    datas[0, col] = propInfo.Name;
    col++;
}
//actual datas
for(int row=0; row<lst.Count; row++){
  int column = 0;
  foreach (var propInfo in lst[row].GetType().GetProperties()){
     datas[row+1, column] = propInfo.GetValue(lst[row], null);
     column++;
  }
}
 
Supose the method is named Export.
Now, to export a List<MyType> heres the needed code/trick:
List<object> lst = new List<object>
for(int i=0; i<5000; i++){
  MyType c = new MyType();
  c.Val = i;
  //add it to the list of objects
  lst.Add(c);
}
 
Now export it:
 
My.... .Export(lst);
 
Ahh... the VARIANCE... CO and CONTRA off course.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dan Mos

Romania Romania
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 9 Mar 2010
Article Copyright 2010 by Dan Mos
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid