Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version
Go to top

Generate Excel files without using Microsoft Excel

, 22 Jun 2011
A C# class to create Excel files without requiring Microsoft Excel.
ConsoleApplication1.zip
ConsoleApplication1
ConsoleApplication1
bin
Debug
ConsoleApplication1.exe
ConsoleApplication1.pdb
ConsoleApplication1.vshost.exe
demo.xls
ConsoleApplication1.vbproj.user
My Project
Application.myapp
Settings.settings
obj
Debug
ConsoleApplication1.exe
ConsoleApplication1.pdb
ConsoleApplication1.Resources.resources
ConsoleApplication1.vbproj.GenerateResource.Cache
ConsoleApplication1.vbproj.ResolveComReference.cache
TempPE
ConsoleApplication1.suo
XLSExportDemo.zip
XLSExportDemo
XLSExportDemo
Properties
XLSExportDemo_Update.zip
// C# Excel Writer library v2.0
// by Serhiy Perevoznyk, 2008-2011

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace XLSExportDemo
{
    /// <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 date format.
        /// </summary>
        /// <param name="value">The date format value.</param>
        public void WriteFormat(string value)
        {
            ushort[] clData = { 0x001E, 0 };
            byte[] plainText = Encoding.ASCII.GetBytes(value);
            int iLen = plainText.Length;
            clData[1] = (ushort)(1 + iLen);
            WriteUshortArray(clData);
            writer.Write((byte)iLen);
            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 cell.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <param name="col">The col.</param>
        /// <param name="value">The value.</param>
        /// <param name="formatIndex">Index of the date format.</param>
        public void WriteCell(int row, int col, DateTime value, int formatIndex)
        {
            DateTime baseDate = new DateTime(1899, 12, 31);
            TimeSpan ts = value - baseDate;
            
            ushort days = (ushort)(ts.Days + 1);
            ushort[] clData = { 0x0002, 09, 0, 0};
            clData[2] = (ushort)row;
            clData[3] = (ushort)col;
            WriteUshortArray(clData);
            writer.Write((byte)0x0);
            byte indexValue = (byte)(formatIndex & 0x3F);
            writer.Write((byte)indexValue);
            writer.Write((byte)0x0);
            writer.Write(days);
        }

        /// <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();
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

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+

| Advertise | Privacy | Mobile
Web01 | 2.8.140922.1 | Last Updated 22 Jun 2011
Article Copyright 2009 by Serhiy Perevoznyk
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid