Reputation: 11

Export large datatable to excel 2003 by c# without excel COM

I have a datatable with larger than 300.000 row, i want export this datatable to excel 2003 but don't use Excel COM. I have used NPOI but it raise error OutOfMemory. I'm finding a thirt party component (free) can export for each row and write directly to file to avoid out of memory. Who can help me? thanks.

Upvotes: 0

Answers (3)

Koen

Reputation: 2571

You can export to an excel-file with the use of a webgrid (only in code). Under the assumption that you're creating a WinForm-application, you will have to make a reference to the System.Web library.

// Create the DataGrid and perform the databinding
var myDataGrid = new System.Web.UI.WebControls.DataGrid();
myDataGrid.HeaderStyle.Font.Bold = true;
myDataGrid.DataSource = myDataTable;
myDataGrid.DataBind();
string myFile = "put the name here with full path.xls"
var myFileStream = new FileStream( myFile, FileMode.Create, FileAccess.ReadWrite );
// Render the DataGrid control to a file
using ( var myStreamWriter = new StreamWriter( myFileStream ) )
{     
    using (var myHtmlTextWriter = new System.Web.UI.HtmlTextWriter(myStreamWriter ))         
    {         
        myDataGrid .RenderControl( myHtmlTextWriter );     
    } 
}

I haven't tested it with so many records, so I don't know the impact of this memory-wise.

Edit:

Take note of the comment of @Charles Williams that excel 2003 has a row limitation

Upvotes: 0