Rashim's Blog

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

Posted on: January 6, 2012

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();
                }
            }
        }
Advertisements

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

nice job mate. keep going. i like the methodology “chunk chunk way”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: