Click here to Skip to main content
Click here to Skip to main content

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

, 12 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
How to generate excel file on the fly without using Microsoft Excel and download it in chunk chunk way?

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;    
    byte[] buffer = new Byte[10000];   
    int length;  
    long dataToRead;    
    string fileName = request["FileName"];
    System.Collections.Generic.List<Test> lstTest = "Your List of Test Object";
    
    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();
        }
    }
}

The Test Class is given below

public class Test
{    
    public Test()
    {
    }
    public string Title { get; set; }

    public string FirstName { get; set; }
    
    public string SurName { get; set; }
   
    public string Email { get; set; }
     
    public string TelePhoneNumber { get; set; }

    public string OrderNumber { get; set; }
   
    public string SubmissionDate { get; set; }  
}
This is the ExcelWriter class. The code has been taken from this article
public class ExcelWriter
{
    private Stream stream;
    private BinaryWriter writer;

    private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0 };
    private ushort[] clEnd = { 0x0A, 00 };

    private void WriteUshortArray(ushort[] value)
    {
        for (int i = 0; i < value.Length; i++)
            writer.Write(value[i]);
    } 
    public ExcelWriter(Stream stream)
    {
        this.stream = stream;
        writer = new BinaryWriter(stream);
    }   
    public void WriteCell(int row, int col, string value)
    {
        ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
        int iLen = value.Length;
        byte[] plainText = Encoding.ASCII.GetBytes(value);
        clData[1] = (ushort)(8 + iLen);
        clData[2] = (ushort)row;
        clData[3] = (ushort)col;
        clData[5] = (ushort)iLen;
        WriteUshortArray(clData);
        writer.Write(plainText);
    }   
    public void WriteCell(int row, int col, int value)
    {
        ushort[] clData = { 0x027E, 10, 0, 0, 0 };
        clData[2] = (ushort)row;
        clData[3] = (ushort)col;
        WriteUshortArray(clData);
        int iValue = (value << 2) | 2;
        writer.Write(iValue);
    }   
    public void WriteCell(int row, int col, double value)
    {
        ushort[] clData = { 0x0203, 14, 0, 0, 0 };
        clData[2] = (ushort)row;
        clData[3] = (ushort)col;
        WriteUshortArray(clData);
        writer.Write(value);
    }   
    public void WriteCell(int row, int col)
    {
        ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
        clData[2] = (ushort)row;
        clData[3] = (ushort)col;
        WriteUshortArray(clData);
    }  
    public void BeginWrite()
    {
        WriteUshortArray(clBegin);
    }   
    public void EndWrite()
    {
        WriteUshortArray(clEnd);
        writer.Flush();
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Md. Rashim uddin
Software Developer (Senior) KAZ Software Limited.
Bangladesh Bangladesh
I am a true visionary as well as have always been passionate about learning. I always love to work with thought-provoking personnel to develop amazing, exciting and innovative ideas as part of an aggressive team that challenges my ingenious ideas, creativeness and extensive experience.
 
My Blog: http://rashimuddin.wordpress.com/
 
My Email: rashimiiuc at yahoo dot com
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionGenerates excel sheet in Protected View PinprofessionalCodeBlack11-Aug-14 19:58 
QuestionHow to change worksheet name in this code? Pinmemberhiteshcode14-Nov-13 0:44 
Questionspecial characters in text Pinmembermazzat2-Dec-12 0:57 
GeneralThanks. PinmemberSaily21-Aug-12 7:08 
SuggestionBritish currency Pinmemberfritterfatboy18-Jun-12 8:07 
GeneralMy vote of 5 PinmemberMember 432084427-May-12 10:53 
QuestionChunk way? PinmemberHaBiX15-May-12 1:04 
QuestionNew Worksheet Pinmembersalimbai21-Mar-12 21:02 
AnswerRe: New Worksheet PinmemberBruce Goodman5-Jun-12 23:02 
GeneralMy vote of 5 Pinmemberenamur18-Mar-12 20:55 
GeneralRe: My vote of 5 PinmemberMd. Rashim uddin18-Mar-12 21:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141220.1 | Last Updated 12 May 2012
Article Copyright 2012 by Md. Rashim uddin
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid