C# ExcelWriter class

This sample demonstrates how to create a file using the Microsoft Excel Binary Interchange File Format (BIFF). In other words it shows how to produce Excel files from your application directly without using Excel. It can be useful when you are not sure if your customer has Excel installed.


// C# ExcelWriter class v1.0
// by Serhiy Perevoznyk, 2008

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

namespace XLSExportDemo
{
///
/// Produces Excel file without using Excel
///

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

///
/// Initializes a new instance of the class.
///

/// The stream.
public ExcelWriter(Stream stream)
{
this.stream = stream;
writer = new BinaryWriter(stream);
}

///
/// Writes the text cell value.
///

/// The row.
/// The col.
/// The string value.
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);
}

///
/// Writes the integer cell value.
///

/// The row number.
/// The column number.
/// The value.
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);
}

///
/// Writes the double cell value.
///

/// The row number.
/// The column number.
/// The value.
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);
}

///
/// Writes the empty cell.
///

/// The row number.
/// The column number.
public void WriteCell(int row, int col)
{
ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
}

///
/// Must be called once for creating XLS file header
///

public void BeginWrite()
{
WriteUshortArray(clBegin);
}

///
/// Ends the writing operation, but do not close the stream
///

public void EndWrite()
{
WriteUshortArray(clEnd);
writer.Flush();
}
}
}

Download XLSExportDemo.zip

Advertisements

14 thoughts on “C# ExcelWriter class

  1. I received few times the question about inserting the long text into one cell.
    OK, with this library the max. size of the text is 255 characters as it uses old and simple BIFF format for storing the file.
    Also I am not sure what the practical usage of writing 4000 characters in one cell?

    Like

  2. This format does not support 2 worksheets. It can do only what it can do. If something is not implemented in this library – it will be not implemented in the future as well. So if you need long strings, colors, formatting, worksheets, etc… – this library is not for you.
    There are many big and powerful libraries that can do all the things you needed. Some of them are free.
    The main purpose of ExcelWriter to make it “small and simple” and I want to keep it this way

    Like

  3. As you see the name of the class is “ExcelWriter”, so it's designed for writing files, not reading. In case if you have to read one, you can easy find a lot of freeware alternatives, on sourceforge, for example

    Like

  4. Good One. But opening the file in office 2007 and saving it back throws an error saying “it cannot be saved in the current format. Click OK to save in the latest format”. Are we missing something in the header, I mean BOF.

    Like

  5. If I try to use in a cell a formula like “=a1+a2” then I don't get the result in Excel but the exact text I meant as a formula. How can I tell to the cell to calculate that? Thanks.

    Like

  6. Big thanks to you. Does this code support unicode characters while writing it in excel file? I have seen that your code uses default encoding and some places ASCII and I have replaced that with UTF-8 but still it doesn't write unicode characters in excel file. any thoughts on that?

    Like

  7. Hi,

    On my ASP.Net Production Server, there is no MS-Office but for working with Excel (Like Reading and Exporting textual Table) we have installed AccessDatabaseEngine_x64.exe .

    Now While Exporting textual Table we also want to export dynamic image formed by MS-Chart on .aspx page.

    Paralelly we can not use Microsoft.Office.Interop.Excel.dll as we do not have installed MS-Office on Production Server.

    So how we can export such type of dynamic MS-Chart image with data only in Excel ?

    Thanks in Advance

    Rajesh Singh

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s