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


Download XLSExportDemo.zip

Comments

  1. I have a probleme to insert long text. The cell is empty.

    Can you help me ?

    thanks

    ReplyDelete
  2. Great job, I need create a file with two worksheets.

    Can you help me?

    ReplyDelete
  3. This class can create only files with one worksheet, but I can recommend you another freeware library: http://code.google.com/p/excellibrary

    ReplyDelete
  4. 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?

    ReplyDelete
  5. 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

    ReplyDelete
  6. I need to append to an existing excel, this class overwrite all and let only de last element. Can you help me?
    My e-mail is wefaamigo@hotmail.com

    ReplyDelete
  7. 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

    ReplyDelete
  8. 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.

    ReplyDelete
  9. 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.

    ReplyDelete
  10. This class does not support formulas, sorry. If you need it you have to find a more advanced exel generator (there are plenty of freeware libraries for it)

    ReplyDelete
  11. 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?

    ReplyDelete
  12. Check the second part of my article: http://delphi32.blogspot.com/2011/06/generate-excel-files-without-using.html
    There is updated code available

    ReplyDelete
  13. 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

    ReplyDelete

Post a Comment

Popular posts from this blog

Quricol - QR code generator library

Smir - backup and restore Windows desktop icons position

EIDNative Library 2.0 released