Click here to Skip to main content
11,428,004 members (58,365 online)
Click here to Skip to main content

Generate Excel files without using Microsoft Excel

, 22 Jun 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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
/// <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:

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)

Share

About the Author

Serhiy Perevoznyk
Architect
Belgium Belgium
No Biography provided
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionVery Easy Pin
texgfalco30-Jun-11 14:32
membertexgfalco30-Jun-11 14:32 
AnswerRe: Very Easy Pin
Serhiy Perevoznyk1-Jul-11 10:37
memberSerhiy Perevoznyk1-Jul-11 10:37 
GeneralMy vote of 5 Pin
Jamal Alqabandi28-Jun-11 0:59
memberJamal Alqabandi28-Jun-11 0:59 
Questionnoob Pin
Barbo23-Jun-11 4:29
memberBarbo23-Jun-11 4:29 
AnswerRe: noob Pin
AORD22-May-12 13:10
memberAORD22-May-12 13:10 
GeneralMy vote of 5 Pin
Omar Gamil22-Jun-11 22:38
memberOmar Gamil22-Jun-11 22:38 
GeneralAlternative way to create Excel files without the need of Excel beeing installed [modified] Pin
aron.sinoai16-Jun-11 13:02
memberaron.sinoai16-Jun-11 13:02 
GeneralHow to do CELL Format Pin
Member 390017225-May-11 21:55
memberMember 390017225-May-11 21:55 
GeneralRe: How to do CELL Format Pin
Serhiy Perevoznyk22-Jun-11 10:34
memberSerhiy Perevoznyk22-Jun-11 10:34 
GeneralHow can we format a cell like make it bold or give a background color using your write cell function? Pin
Samar pratap28-Apr-11 9:01
memberSamar pratap28-Apr-11 9:01 
GeneralRe: How can we format a cell like make it bold or give a background color using your write cell function? Pin
Member 390017225-May-11 22:01
memberMember 390017225-May-11 22:01 
GeneralRe: How can we format a cell like make it bold or give a background color using your write cell function? Pin
Serhiy Perevoznyk22-Jun-11 10:35
memberSerhiy Perevoznyk22-Jun-11 10:35 
GeneralCodepage problems Pin
TiMiNOd9-Apr-11 3:32
memberTiMiNOd9-Apr-11 3:32 
GeneralRe: Codepage problems Pin
TiMiNOd9-Apr-11 4:26
memberTiMiNOd9-Apr-11 4:26 
The problem was covered in ASCII to the coding, another doesn't support
Cry | :((
GeneralRe: Codepage problems Pin
Serhiy Perevoznyk22-Jun-11 10:39
memberSerhiy Perevoznyk22-Jun-11 10:39 
GeneralNice Pin
CikaPero29-Mar-11 0:57
memberCikaPero29-Mar-11 0:57 
GeneralMy vote of 5 Pin
Jesper Bihrmann2-Feb-11 4:05
memberJesper Bihrmann2-Feb-11 4:05 
GeneralI didn't open excel file was generated by your code Pin
Helen.Yu14-Jan-11 14:40
memberHelen.Yu14-Jan-11 14:40 
QuestionHow to write a Cell with DateTime format? Pin
Mark Carranza12-Jan-11 20:59
memberMark Carranza12-Jan-11 20:59 
GeneralMy vote of 4 Pin
cimsunf29-Nov-10 5:23
membercimsunf29-Nov-10 5:23 
GeneralMy vote of 4 Pin
rlejason28-Nov-10 21:42
memberrlejason28-Nov-10 21:42 
GeneralNPOI.codeplex.com .NET library (open source) could be considered as alternative, has more and source code Pin
AlexandreN12-Nov-10 9:03
memberAlexandreN12-Nov-10 9:03 
GeneralRe: NPOI.codeplex.com .NET library (open source) could be considered as alternative, has more and source code Pin
Serhiy Perevoznyk13-Nov-10 1:31
memberSerhiy Perevoznyk13-Nov-10 1:31 
GeneralSuggested change to code sample Pin
sdesciencelover9-Nov-10 5:30
membersdesciencelover9-Nov-10 5:30 
GeneralMy vote of 4 Pin
sdesciencelover9-Nov-10 5:26
membersdesciencelover9-Nov-10 5:26 
GeneralAdding Formulas Pin
twehr9-Nov-10 4:45
membertwehr9-Nov-10 4:45 
GeneralROCK STAR! Pin
Roger Ireland3-Nov-10 16:27
memberRoger Ireland3-Nov-10 16:27 
QuestionAdding simple formatting? Pin
Member 9171743-Nov-10 12:12
memberMember 9171743-Nov-10 12:12 
AnswerRe: Adding simple formatting? Pin
Serhiy Perevoznyk22-Jun-11 10:36
memberSerhiy Perevoznyk22-Jun-11 10:36 
GeneralHtml is also possible Pin
Tom Janssens2-Nov-10 22:44
memberTom Janssens2-Nov-10 22:44 
GeneralRe: Html is also possible Pin
Roger Ireland3-Nov-10 16:28
memberRoger Ireland3-Nov-10 16:28 
GeneralRe: Html is also possible Pin
Tom Janssens4-Nov-10 8:14
memberTom Janssens4-Nov-10 8:14 
GeneralVery Good! Pin
xzz01952-Nov-10 3:42
memberxzz01952-Nov-10 3:42 
Generalnice articles Pin
RyanALEX16-Aug-10 20:01
memberRyanALEX16-Aug-10 20:01 
Questioncreate cell with font size and bold Pin
Member 254080414-May-10 13:33
memberMember 254080414-May-10 13:33 
AnswerRe: create cell with font size and bold Pin
Serhiy Perevoznyk22-Jun-11 10:37
memberSerhiy Perevoznyk22-Jun-11 10:37 
GeneralThank You Pin
kartp2-Apr-10 22:35
memberkartp2-Apr-10 22:35 
GeneralSet the width of the Columns of the Excel file Pin
Dazheng Xu11-Mar-10 12:42
memberDazheng Xu11-Mar-10 12:42 
GeneralRe: Set the width of the Columns of the Excel file Pin
Serhiy Perevoznyk22-Jun-11 10:38
memberSerhiy Perevoznyk22-Jun-11 10:38 
QuestionHow to write excel with exporting data from ms word doc table? Pin
ssuriyani223-Dec-09 16:09
memberssuriyani223-Dec-09 16:09 
GeneralMicrosoft Excel 2.1 Worksheet format Pin
edgar_v_reyes28-Nov-09 8:52
memberedgar_v_reyes28-Nov-09 8:52 
GeneralRe: Microsoft Excel 2.1 Worksheet format Pin
Serhiy Perevoznyk28-Nov-09 23:33
memberSerhiy Perevoznyk28-Nov-09 23:33 
GeneralCell limitation to 255 chars Pin
eddy mulyono28-Oct-09 19:12
membereddy mulyono28-Oct-09 19:12 
GeneralDutch characters break the document Pin
D.Janjicek10-Aug-09 21:40
memberD.Janjicek10-Aug-09 21:40 
GeneralRe: Dutch characters break the document Pin
DanielLey9-Oct-09 4:06
memberDanielLey9-Oct-09 4:06 
GeneralRe: Dutch characters break the document Pin
Member 30944881-Jan-10 22:57
memberMember 30944881-Jan-10 22:57 
QuestionRe: Dutch characters break the document Pin
Tropics2a21-Apr-10 3:14
memberTropics2a21-Apr-10 3:14 
AnswerRe: Dutch characters break the document Pin
Phr34ker18-Mar-11 3:01
memberPhr34ker18-Mar-11 3:01 
QuestionCan I modify an existing file ? Pin
trikeur10-Jun-09 0:25
membertrikeur10-Jun-09 0:25 
AnswerRe: Can I modify an existing file ? Pin
Member 864037323-Oct-13 6:06
memberMember 864037323-Oct-13 6:06 

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
Web04 | 2.8.150428.2 | Last Updated 22 Jun 2011
Article Copyright 2009 by Serhiy Perevoznyk
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid