Click here to Skip to main content
11,412,516 members (69,932 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

 
GeneralGenerate Excel files without using Microsoft Excel Pin
vishnus790 at 16-Mar-15 8:26
membervishnus79016-Mar-15 8:26 
QuestionHow to Parse excel Formula or What is mean by "Parsed expression" in Formula Cell Pin
Member 11426223 at 5-Feb-15 22:35
memberMember 114262235-Feb-15 22:35 
QuestionWriting Unicode Pin
Member 11072271 at 10-Sep-14 3:43
memberMember 1107227110-Sep-14 3:43 
Question[My vote of 1] Completely Worthless Pin
MSDEVTECHNIK at 28-Aug-14 10:56
memberMSDEVTECHNIK28-Aug-14 10:56 
QuestionSee also this: Pin
Dietmar Schoder at 29-Jul-14 6:26
professionalDietmar Schoder29-Jul-14 6:26 
Questionis anyone still develop this way? Pin
bonjot at 5-Jul-14 21:41
memberbonjot5-Jul-14 21:41 
QuestionExcel row Limit Pin
csharpi at 16-May-14 10:58
membercsharpi16-May-14 10:58 
QuestionAttach ExcelDocument to Email Pin
vaughantrebilco at 10-Apr-14 13:46
membervaughantrebilco10-Apr-14 13:46 
GeneralPerfect Class Pin
William Costa Rodrigues at 19-Feb-14 13:22
memberWilliam Costa Rodrigues19-Feb-14 13:22 
Questionexcel cannot open the file .xlsx because the file format Pin
bellona888 at 10-Feb-14 22:43
memberbellona88810-Feb-14 22:43 
QuestionSpecial Characters Problem Pin
Emre Can Serteli at 7-Dec-13 6:14
memberEmre Can Serteli7-Dec-13 6:14 
AnswerRe: Special Characters Problem Pin
Member 10585247 at 14-Jul-14 0:33
memberMember 1058524714-Jul-14 0:33 
QuestionHow to avoid protected view? Pin
Amit Ghorai at 24-Aug-13 6:04
memberAmit Ghorai24-Aug-13 6:04 
Questionhow to create a new worksheet in the same document Pin
Member 10221967 at 20-Aug-13 20:55
memberMember 1022196720-Aug-13 20:55 
AnswerRe: how to create a new worksheet in the same document Pin
Javier Chan at 26-Feb-15 19:48
memberJavier Chan26-Feb-15 19:48 
QuestionMy Vote of 5! Pin
jiaxing yee at 6-Jun-13 15:45
memberjiaxing yee6-Jun-13 15:45 
GeneralMy vote of 5 Pin
hygoh at 30-May-13 17:28
memberhygoh30-May-13 17:28 
Generalvery cool stuff Pin
Jerry13 at 9-May-13 3:31
memberJerry139-May-13 3:31 
QuestionI can't open the generated XLS file through Excel 2010 Pin
Josen.L.Zhang at 9-Apr-13 1:03
memberJosen.L.Zhang9-Apr-13 1:03 
AnswerRe: I can't open the generated XLS file through Excel 2010 Pin
sangheraajit at 14-Apr-13 23:04
membersangheraajit14-Apr-13 23:04 
GeneralMy vote of 5 Pin
DineshMaind at 5-Mar-13 7:01
memberDineshMaind5-Mar-13 7:01 
QuestionPlease add Border Style at next upgrade Pin
phong1040572 at 24-Jan-13 17:29
memberphong104057224-Jan-13 17:29 
SuggestionColumn Width AutoFit? Pin
adriancs at 9-Jan-13 15:46
mvpadriancs9-Jan-13 15:46 
SuggestionCode Patch, Write DataTable [modified] Pin
adriancs at 9-Jan-13 15:01
mvpadriancs9-Jan-13 15:01 
QuestionIs it possible to Write stream to respose headers instead of a demo.xls file? Pin
shabari7 at 13-Dec-12 1:25
membershabari713-Dec-12 1:25 
QuestionMore feature rich alternative Pin
Andrew Kirillov at 11-Nov-12 23:30
memberAndrew Kirillov11-Nov-12 23:30 
AnswerRe: More feature rich alternative Pin
Member 9407990 at 29-Mar-13 17:58
memberMember 940799029-Mar-13 17:58 
QuestionMultiple Sheets Pin
Member 8107811 at 28-Oct-12 17:09
memberMember 810781128-Oct-12 17:09 
Questionlimited to 255 chars per cell. how we can increase the limit ..? Pin
aisoft at 19-Oct-12 6:50
memberaisoft19-Oct-12 6:50 
limited to 255 chars per cell. how we can increase the limit ..?
QuestionGenerating Excel Pin
nirajzambad at 7-Aug-12 4:03
membernirajzambad7-Aug-12 4:03 
GeneralMy vote of 5 Pin
Huỳnh Hữu Ân at 24-Jul-12 19:06
memberHuỳnh Hữu Ân24-Jul-12 19:06 
QuestionThe generated xls file could not be read by OLEDB Pin
daiwuju at 4-Jul-12 23:57
memberdaiwuju4-Jul-12 23:57 
QuestionA minor problem in Chinese Font Pin
daiwuju at 29-Jun-12 20:05
memberdaiwuju29-Jun-12 20:05 
AnswerRe: A minor problem in Chinese Font Pin
Member 10879821 at 14-Sep-14 23:49
memberMember 1087982114-Sep-14 23:49 
QuestionMere cells Pin
daniloao at 31-May-12 14:50
memberdaniloao31-May-12 14:50 
QuestionBorders Pin
Member 9012599 at 29-May-12 7:49
memberMember 901259929-May-12 7:49 
QuestionMore Than one WorkSheet Pin
hichamveo at 24-May-12 7:57
memberhichamveo24-May-12 7:57 
QuestionGreat work!! Pin
CurtCobrain at 7-Apr-12 5:57
memberCurtCobrain7-Apr-12 5:57 
GeneralMy vote of 5 Pin
Mikhail Tsennykh (devnoob) at 19-Mar-12 9:50
memberMikhail Tsennykh (devnoob)19-Mar-12 9:50 
QuestionCreating .XLSX file Pin
bhavtosh.sharma at 2-Mar-12 1:57
memberbhavtosh.sharma2-Mar-12 1:57 
AnswerRe: Creating .XLSX file Pin
daiwuju at 29-Jun-12 20:00
memberdaiwuju29-Jun-12 20:00 
AnswerRe: Creating .XLSX file Pin
Constance R at 19-Dec-13 0:28
memberConstance R19-Dec-13 0:28 
QuestionProtected View Pin
ccchg at 9-Feb-12 1:22
memberccchg9-Feb-12 1:22 
QuestionSpecial characters are not coming Pin
iliyash at 11-Jan-12 5:37
memberiliyash11-Jan-12 5:37 
AnswerRe: Special characters are not coming Pin
iliyash at 11-Jan-12 21:42
memberiliyash11-Jan-12 21:42 
Questionprint Pin
Member 8528391 at 2-Jan-12 11:17
memberMember 85283912-Jan-12 11:17 
QuestionI get an Office File Validation warning Pin
Leo Muller at 19-Dec-11 21:27
memberLeo Muller19-Dec-11 21:27 
GeneralMy vote of 5 Pin
Member 3666235 at 25-Nov-11 1:10
memberMember 366623525-Nov-11 1:10 
QuestionHO TO MERGE COLUMS AND ROWS Pin
sheriefes at 11-Nov-11 1:08
membersheriefes11-Nov-11 1:08 
Questioninsert image Pin
Jorge Teodoro at 1-Nov-11 10:08
memberJorge Teodoro1-Nov-11 10:08 

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