Click here to Skip to main content
15,867,141 members
Articles / Programming Languages / C#

Generate Excel files without using Microsoft Excel

Rate me:
Please Sign up or sign in to vote.
4.80/5 (93 votes)
22 Jun 2011CPOL2 min read 644.9K   24.3K   269   153
A C# class to create Excel files without requiring Microsoft Excel.

Introduction

Generating Excel files from web pages or other applications is a major subject in many articles. I'd like to present here a solution that does not require MS Excel to be installed on the target computer. This article shows how to create Microsoft Excel Binary Interchange File Format (BIFF) without using Microsoft Excel. BIFF is the native file format for Excel data, and can be viewed and modified in Microsoft Excel 97 or later.

Approaches and problems

The technique that is most frequently used to transfer data to an Excel workbooks is Automation. With Automation, you can call methods and properties that are specific to Excel tasks, but this solution has many drawbacks. Some of them are described in the Microsoft Knowledge Base. Additionally, you have to manage the lifetime of the temporary XLS files created on the server. Also, it is slow, because Excel runs in a separate process.

ExcelWriter class

The solution presented here is to write directly to a stream in Excel binary file format. For this purpose, I created very small C# class called ExcelWriter.

ExcelWriter class diagram
C#
/// <summary>
/// Produces Excel file without using Excel
/// </summary>
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]);
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="ExcelWriter"/> class.
    /// </summary>
    /// <param name="stream">The stream.</param>
    public ExcelWriter(Stream stream)
    {
        this.stream = stream;
        writer = new BinaryWriter(stream);
    }

    /// <summary>
    /// Writes the text cell value.
    /// </summary>
    /// <param name="row">The row.</param>
    /// <param name="col">The col.</param>
    /// <param name="value">The string value.</param>
    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);
    }

    /// <summary>
    /// Writes the integer cell value.
    /// </summary>
    /// <param name="row">The row number.</param>
    /// <param name="col">The column number.</param>
    /// <param name="value">The value.</param>
    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);
    }

    /// <summary>
    /// Writes the double cell value.
    /// </summary>
    /// <param name="row">The row number.</param>
    /// <param name="col">The column number.</param>
    /// <param name="value">The value.</param>
    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);
    }

    /// <summary>
    /// Writes the empty cell.
    /// </summary>
    /// <param name="row">The row number.</param>
    /// <param name="col">The column number.</param>
    public void WriteCell(int row, int col)
    {
        ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
        clData[2] = (ushort)row;
        clData[3] = (ushort)col;
        WriteUshortArray(clData);
    }

    /// <summary>
    /// Must be called once for creating XLS file header
    /// </summary>
    public void BeginWrite()
    {
        WriteUshortArray(clBegin);
    }

    /// <summary>
    /// Ends the writing operation, but do not close the stream
    /// </summary>
    public void EndWrite()
    {
        WriteUshortArray(clEnd);
        writer.Flush();
    }
}

Using the code

The ExcelWriter sample is a console application that writes an XLS file to the name and location specified. The XLS file that is created is simply an empty spreadsheet. Then, you can start to write cell values by calling the WriteCell method with the row number, column number, and a cell value as parameters:

C#
namespace XLSExportDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            FileStream stream = new FileStream("demo.xls", FileMode.OpenOrCreate);
            ExcelWriter writer = new ExcelWriter(stream);
            writer.BeginWrite();
            writer.WriteCell(0, 0, "ExcelWriter Demo");
            writer.WriteCell(1, 0, "int");
            writer.WriteCell(1, 1, 10);
            writer.WriteCell(2, 0, "double");
            writer.WriteCell(2, 1, 1.5);
            writer.WriteCell(3, 0, "empty");
            writer.WriteCell(3, 1);
            writer.EndWrite();
            stream.Close();
        }
    }
}

The picture below shows the final result of this sample:

The ExcelWriter class supports numeric and text values for the cells, and can be used for exporting data from the database to an XLS file, or for producing an XLS stream from a website on the fly.

I hope this article gives you a head start in working with Excel files from .NET and C#.

History

  • 03 March 2009 - Initial submission.

License

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


Written By
Architect
Belgium Belgium
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionGreat work!! Pin
CurtCobrain7-Apr-12 4:57
CurtCobrain7-Apr-12 4:57 
GeneralMy vote of 5 Pin
Mikhail-T19-Mar-12 8:50
Mikhail-T19-Mar-12 8:50 
QuestionCreating .XLSX file Pin
Bhavtosh-Shama2-Mar-12 0:57
Bhavtosh-Shama2-Mar-12 0:57 
AnswerRe: Creating .XLSX file Pin
daiwuju29-Jun-12 19:00
daiwuju29-Jun-12 19:00 
AnswerRe: Creating .XLSX file Pin
Constance R18-Dec-13 23:28
Constance R18-Dec-13 23:28 
QuestionProtected View Pin
ccchg9-Feb-12 0:22
ccchg9-Feb-12 0:22 
QuestionSpecial characters are not coming Pin
iliyash11-Jan-12 4:37
iliyash11-Jan-12 4:37 
AnswerRe: Special characters are not coming Pin
iliyash11-Jan-12 20:42
iliyash11-Jan-12 20:42 
Questionprint Pin
Member 85283912-Jan-12 10:17
Member 85283912-Jan-12 10:17 
QuestionI get an Office File Validation warning Pin
Leo Muller19-Dec-11 20:27
Leo Muller19-Dec-11 20:27 
GeneralMy vote of 5 Pin
Member 366623525-Nov-11 0:10
Member 366623525-Nov-11 0:10 
QuestionHO TO MERGE COLUMS AND ROWS Pin
sheriefes11-Nov-11 0:08
sheriefes11-Nov-11 0:08 
Questioninsert image Pin
Jorge Teodoro1-Nov-11 9:08
Jorge Teodoro1-Nov-11 9:08 
QuestionExcel file gives error on opening Pin
shubhamalhotra17-Oct-11 3:18
shubhamalhotra17-Oct-11 3:18 
Questionwarrior!that would be perfect if ExcelReader.cs and ExcelSheet is adding to the file Pin
batsword13-Sep-11 16:31
batsword13-Sep-11 16:31 
QuestionMy vote of 5 Pin
Filip D'haene9-Sep-11 5:58
Filip D'haene9-Sep-11 5:58 
QuestionAdding new work sheet would be a great feature in the next version Pin
Nikolay Dovgaluk1-Aug-11 4:06
Nikolay Dovgaluk1-Aug-11 4:06 
QuestionHow to create 2nd Excel sheet using your method? Pin
Asif Abdulla29-Jul-11 18:50
Asif Abdulla29-Jul-11 18:50 
AnswerRe: How to create 2nd Excel sheet using your method? Pin
AA123546548978917-May-12 4:00
AA123546548978917-May-12 4:00 
AnswerRe: How to create 2nd Excel sheet using your method? Pin
KapilDesai28-Aug-12 21:48
KapilDesai28-Aug-12 21:48 
QuestionVery Easy Pin
texgfalco30-Jun-11 13:32
texgfalco30-Jun-11 13:32 
AnswerRe: Very Easy Pin
Serhiy Perevoznyk1-Jul-11 9:37
Serhiy Perevoznyk1-Jul-11 9:37 
GeneralMy vote of 5 Pin
Jamal Alqabandi27-Jun-11 23:59
Jamal Alqabandi27-Jun-11 23:59 
Questionnoob Pin
Barbo23-Jun-11 3:29
Barbo23-Jun-11 3:29 
AnswerRe: noob Pin
AORD22-May-12 12:10
AORD22-May-12 12:10 

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

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