exporting over 300k records to excel generates System.OutOfMemoryException

I am trying to export over 300k records to excel and I don't really want to use any dll for this.

I have created a sample application which simulates the issue. Below is class which has methods for generating dummy datatable and exporting data table to excel.

public class ExcelCreator
    {
        /// <summary>
        /// Create one Excel-XML-Document with SpreadsheetML from a DataTable
        /// </summary>
        /// <param name="dataSource">Datasource which would be exported in Excel</param>
        /// <param name="fileName">Name of exported file</param>


        public static DataTable GiveDummyDataTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("abc");
            dt.Columns.Add("bcd");
            dt.Columns.Add("dfd");
            dt.Columns.Add("wer");
            dt.Columns.Add("werw");
            dt.Columns.Add("rete");
            dt.Columns.Add("lkj");
            dt.Columns.Add("ert");
            dt.Columns.Add("poi");
            dt.Columns.Add("wers");
            dt.Columns.Add("mnb");
            dt.Columns.Add("oiwu");
            dt.Columns.Add("qwe");
            dt.Columns.Add("uio");
            for (int i = 0; i < 500000; i++)
            {
                dt.Rows.Add(new object[] { "babo", 120, "poi", "123 3428749020", 35, "6.000", "$24,590", "$13,432",
            "$12,659", "12/13/21", "1/30/27", 55, "sonumonu", "wer"});
            }


            return dt;
        }

        public static bool sonaKaExcelBanao(DataTable dt, string filename)
        {
            try
            {
                string sTableStart = @"<HTML><BODY><TABLE Border=1>";
                string sTableEnd = @"</TABLE></BODY></HTML>";
                string sTHead = "<TR>";
                StringBuilder sTableData = new StringBuilder();
                foreach (DataColumn col in dt.Columns)
                {
                    sTHead += @"<TH>" + col.ColumnName + @"</TH>";
                }
                sTHead += @"</TR>";
                foreach (DataRow sonurow in dt.Rows)
                {
                    sTableData.Append(@"<TR>");
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        sTableData.Append(@"<TD>" + sonurow[i].ToString() + @"</TD>");
                    }
                    sTableData.Append(@"</TR>");
                }
                string sTable = sTableStart + sTHead + sTableData.ToString() + sTableEnd;
                System.IO.StreamWriter oExcelWriter = System.IO.File.CreateText(filename);
                oExcelWriter.WriteLine(sTable);
                oExcelWriter.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
    }

Given below is how I call these methods.

DataTable dt = ExcelCreator.GiveDummyDataTable();          
            ExcelCreator.sonaKaExcelBanao(dt, @"c:\chunchuntaiyar.xls");

And here is the error which I get.

Exception of type 'System.OutOfMemoryException' was thrown.

and it happens in the below line.

string sTable = sTableStart + sTHead + sTableData.ToString() + sTableEnd;

sometimes,it works as well. If it is not simulating you may try to increase loop count to 500k from 300k.

I am using excel 2007/2010.

Solution

You're absolutely wasting memory in that piece of code. Although you did leverage the StringBuilder, which probably prevented that you would ran out of memory sooner, you missed a couple of opportunities to use the StringBuilder even more effective. For example you could have used it for the complete buildup.

Take this example from your code:

sTHead += @"<TH>" + col.ColumnName + @"</TH>";

Every + on that line will create a new string to hold the result. Use the StringBuilder instead.

This is where you already have the StringBuilder in place but your line of code still allocates extra strings:

sTableData.Append(@"<TD>" + sonurow[i].ToString() + @"</TD>");

You could have used AppendFormat there:

sTableData.AppendFormat(@"<TD>{0}</TD>", sonurow[i]);

so the strings would be copied effectively to the internal buffer of the StringBuilder instance.

I've opted for a solution without any StringBuilders. Simply write out directly to the stream:

public static bool ExcelExport(DataTable dt, string filename)
{
    try
    {
        // using makes sure the streamwriter gets closed and disposed
        using (StreamWriter oExcelWriter = File.CreateText(filename))
        {
            // leadin
            oExcelWriter.Write(@"<HTML><BODY><TABLE Border=1>");
            //header
            oExcelWriter.Write("<TR>");
            foreach (DataColumn col in dt.Columns)
            {
                oExcelWriter.Write(@"<TH>");
                oExcelWriter.Write(col.ColumnName);
                oExcelWriter.Write( @"</TH>");
            }
            oExcelWriter.Write("</TR>");
            // body 
            foreach (DataRow sonurow in dt.Rows)
            {
                oExcelWriter.Write(@"<TR>");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    oExcelWriter.Write(@"<TD>");
                    oExcelWriter.Write(sonurow[i]); // calls ToString in the overload
                    oExcelWriter.Write(@"</TD>");
                }
                oExcelWriter.Write(@"</TR>");
            }
            // leadout
            oExcelWriter.WriteLine(@"</TABLE></BODY></HTML>");
        }
    }
    catch(Exception exp)
    {
            Trace.WriteLine(exp.Message);
            return false;
    }
    return true;
}

This will not do many more allocations and should work even for larger datatables.