My blog has moved!

You should be automatically redirected in 6 seconds. If not, visit
http://perevoznyk.wordpress.com
and update your bookmarks.

Friday, October 30, 2009

Write Date value in ExcelWriter

People asking me how to insert the date value into Excel coulmn, but as a date, not just as a text in my C# ExcelWriter class.
I extended this class and added 2 additional methods:

  •  public void WriteFormat(string value)
  •  public void WriteCell(int row, int col, DateTime value, int formatIndex)


// C# ExcelWriter class v1.1
// by Serhiy Perevoznyk, 2008-2009

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


and example of usage:

// This sample demonstrates how to create a file using the Microsoft Excel 
// Binary Interchange File Format (BIFF). 
// If this program works, it was written by Serhiy Perevoznyk.
// If not, I don't know who wrote it.

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

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.WriteFormat(@"dd\-mm\-yyyy"); //index 0
            writer.WriteFormat(@"dd/mm/yyyy");
            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, "date");
            writer.WriteCell(3, 1, DateTime.Now, 1);
            writer.EndWrite();
            stream.Close();
        }
    }
}