Generate Excel file on the fly without using Microsoft Excell and download it in Chunk-chunk way.

Posted by: Md. Rashim Uddin on: January 6, 2012

In: .NET | Codeproject | Technical
5 Comments

Couple of days ago I was working with excel file manipulation. The requirement was like that the file has to be created on the fly based on the data getting from database and then download it to the client PC. So what I did here is that, I have just created the excel file on the fly and send the data to the client in a chunk way. To do that, I wrote a handler which takes the key as the request, getting the data based on the key from the database, write the excel file and then download it to the chunk way. Fortunately, I have got an excellent article in code project which helped me for generating excel files without using Microsoft Excel.

The Handler class is given below,

public void ProcessRequest(HttpContext context)
        {
            HttpRequest request = context.Request;
            System.IO.MemoryStream currentStream = null;
            var buffer = new Byte[10000];
            int length;
            long dataToRead;
            var fileName = request["FileName"];

            var lstTest = DataAccess.GetDataByFileName(fileName);

            try
            {
                currentStream = new System.IO.MemoryStream();
                ExcelWriter writer = new ExcelWriter(currentStream);
                writer.BeginWrite();

                writer.WriteCell(0, 0, "Title");
                writer.WriteCell(0, 1, "FirstName");
                writer.WriteCell(0, 2, "Surname");
                writer.WriteCell(0, 3, "Email");
                writer.WriteCell(0, 4, "TelePhoneNumber");
                writer.WriteCell(0, 5, "OrderNumber");
                writer.WriteCell(0, 6, "SubmissionDate");

                if (lstTest != null)
                {
                    for (int row = 0; row < lstTest.Count; row++)
                    {
                        writer.WriteCell(row + 1, 0, lstTest[row].Title);
                        writer.WriteCell(row + 1, 1, lstTest[row].FirstName);
                        writer.WriteCell(row + 1, 2, lstTest[row].SurName);
                        writer.WriteCell(row + 1, 3, lstTest[row].Email);
                        writer.WriteCell(row + 1, 4, lstTest[row].TelePhoneNumber);
                        writer.WriteCell(row + 1, 5, lstTest[row].OrderNumber);
                        writer.WriteCell(row + 1, 6, lstTest[row].SubmissionDate);
                    }
                }

                writer.EndWrite();

                currentStream.Position = 0;
                context.Response.AddHeader("Content-Length", currentStream.Length.ToString());
                context.Response.AddHeader("Accept-Ranges", "bytes");
                context.Response.Buffer = false;
                context.Response.AddHeader("Connection", "Keep-Alive");
                context.Response.ContentType = "application/octet-stream";
                context.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                dataToRead = currentStream.Length;
                context.Response.ContentType = "application/octet-stream";

                while (dataToRead > 0)
                {
                    if (context.Response.IsClientConnected)
                    {
                        length = currentStream.Read(buffer, 0, 10000);
                        context.Response.OutputStream.Write(buffer, 0, length);
                        context.Response.Flush();
                        buffer = new Byte[10000];
                        dataToRead = dataToRead - length;
                    }
                    else
                    {
                        dataToRead = -1;
                    }
                }
            }

            catch (Exception ex)
            {
                context.Response.Write(ex);
            }
            finally
            {
                if (currentStream != null)
                {
                    currentStream.Close();
                    currentStream.Dispose();
                }
            }
        }