Convert table to excel

Question

I want to write all the records from my table to an excel file. I have around 30000 rows in my table.

I am able to write 20000 rows to excel only. After the System out of memory Exception is happening.

This is my code. Thanks in advance.

using (SqlConnection con = new SqlConnection(strconnection))
{
   using (SqlCommand cmd = new SqlCommand("SELECT  * FROM tbl_ReportwithoutDup"))
   {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(dt, "Report1");

                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;filename=Report1.xlsx");
                    using (MemoryStream MyMemoryStream = new MemoryStream())
                    {
                        wb.SaveAs(MyMemoryStream);
                        MyMemoryStream.WriteTo(Response.OutputStream);
                        Response.Flush();
                        Response.End();
                    }
                }
            }
        }
    }
}

Are you using the Excel Interop service library? Or what library are you using to generate the Excel file? — mason, Dec 08 '14 at 14:28
don't use interop on server, look here: http://stackoverflow.com/a/9569827/351383 — Antonio Bakula, Dec 08 '14 at 14:36

score 0 · Answer 1 · answered Dec 08 '14 at 14:29

0

So, not a perfect solution, but since you're using a web framework (asp.net), you could always just write it all out into a HTML table, and then set the content-type of the response to application/vnd.ms-excel. The result is that the browser will download a CSV file that the client opens in Excel by default.

answered Dec 08 '14 at 14:29

Paul

35,689
11
93
122

I am using this code http://www.aspsnippets.com/Articles/Export-data-from-SQL-Server-to-Excel-in-ASPNet-using-C-and-VBNet.aspx – Aiju Dec 08 '14 at 14:30
? ok... My point is that you can do the whole thing w/o using Excel on the server if you like. And it's likely to be more memory efficient. – Paul Dec 08 '14 at 14:32
Paul, you say to write an HTML table, but you say the user will download a CSV file? How does that work? – mason Dec 08 '14 at 14:36
The HTML table is interpreted as a CSV file by the browser based on the content-type setting that I mentioned. I've done this several times with good success. – Paul Dec 08 '14 at 15:34

Convert table to excel

1 Answers1