Export an IQueryable Collection to Excel

Question

I have a huge IQueryable that I want to export directly to Excel for data dumps, how would I do this?

I was trying to put it into a GridView and export from there, which works if I use only a chunk of the data, but if I use the whole IQueryable, nothing happens.

This is a table with 4k rows and each row has 70 or so columns.

Edit: CSV file will be fine too if that is easier!

Answer 1

Why not write the file directly? Use foreach on the IQueryable, and write out a CSV file row by row.

Answer 2

CSV might not be the best solution. I work with very large data sets all the time and use a function similar to this. I'm going to assume if it's IQueryable it is also IEnumerable. Here is the sample code, do with it what you like. If the delimiter is "," then it will output a CSV and if the delimiter is a "\t" you will output an Excel readable file. Either way you will need to do something similar to output a one line header containing the column titles before you run this method to output the data.

//This is changed slightly from my actual code but it should give you an idea of what to do
//tickStorage contains a dictionary named MessageData and the key is the column name
//Obviously tickStorage is what is being directly mapped to my listView component

private void OutputItems(StreamWriter writer, int startIndex, int endIndex, String delimiter)
{
    endIndex = endIndex < tickStorage.Count ? endIndex : tickStorage.Count;
    for (Int32 i = startIndex; i < endIndex; i++)
    {
    IEnumerator<Columns> fields = listView.ColumnsInDisplayOrder.GetEnumerator();
    fields.MoveNext();
    writer.Write((tickStorage[i].MessageData[fields.Current.Text]).Trim());
    while (fields.MoveNext())
    {
        writer.Write(delimiter + (tickStorage[i].MessageData[fields.Current.Text]).Trim());
    }
    writer.WriteLine();
    }
}