Generate Excel file on the fly without using Microsoft Excell and download it in Chunk-chunk way.
Posted January 6, 2012
on:- 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(); } } }
5 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”.

Thanks Enam



Can you provide a code for having multiple spreadsheets

January 6, 2012 at 11:00 am
good article,thanks Rashim,
January 9, 2012 at 10:10 am
Thanks