Sunday, June 5, 2005

Creating Excel Workbooks in .Net

[This was originally posted at]

Every now and then I find myself wanting to dynamically create an Excel Workbook in .Net. Maybe it's for a data export, or to set up a structured template. Either way, there's only a small set of common utilities needed to get started. However, because Excel's automation model is based in COM (not .Net), it can be difficult to create those utilities. So below are the ones I've started used. Two disclaimers:

  1. I have only used these for simple, internal tools, not enterprise-critical production environments. Therefore they are not streamlined for performance.
  2. I've collected snippets here and there from other articles (which I no longer know where), so the idea isn't original.

My goal is to be able to programmatically create an Excel sheet, like so:

ExcelHelper.Utilities u = new ExcelHelper.Utilities();
u.SetCellContent("A",new Cell("B",3), "Hello World!");
u.SetCellContent("A",new Cell("B",4), "=B3");
u.Show = true;

In this code, I first create an ExcelHelper.Utilities object which provides the methods to create and manipulate the workbook. I then add a sheet, set specific cell contents, and show the end result. This is sufficient for assembling most simple workbooks.

The code for ExcelHelper is below. Note that we needed to import the Microsoft Excel Core Library (in the COM tab):

using System;
using Microsoft.Office.Core;
using System.Diagnostics;

namespace ExcelHelper
  public class Utilities
    public Utilities()

    private Excel.Application excelApp = null;

    private void StartExcel()
      if( this.excelApp == null )
        this.excelApp = new Excel.ApplicationClass();
//starts an Empty instance of Excel

    private void CreateNewWorkBook()  
//string strFilePath
      Excel.Workbook wb = this.excelApp.Workbooks.Add(Type.Missing);

//delete all worksheets but 1:

    public void DeleteSheet(int intIndex)

    public void AddSheet(string strName)
      Excel.Worksheet newWorkSheet;
      newWorkSheet =
        Type.Missing, Type.Missing, Type.Missing, Type.Missing);
      newWorkSheet.Name = strName;

    public void SetCellContent(string strWorkSheet, Cell c, string strContent)
      Excel.Worksheet sheet1 =
      ((Excel.Range)sheet1.Cells[c.Row,c.Column]).Value2 = strContent;

    public bool Show
        return this.excelApp.Visible;
        this.excelApp.Visible = value;


Notice that I also created a "Cell" object. This simply takes a value in the familiar Letter(Column)-Number(Row) format and translates it to a Number-Number used by Excel's automation object.

using System;namespace ExcelHelper{  public struct Cell  {    public Cell(int intRow, int intCol)    {      _intRow = intRow;      _intCol = intCol;    }    public Cell(string strCol, int intRow)    {      _intRow = intRow;      _intCol = ConvertStringToIntColumn(strCol);      if (_intCol > 256)        throw new ArgumentException("Column cannot be greater than IV (256). Value was '" + strCol + "'.");    }    private static int ConvertStringToIntColumn(string strCol)     {      strCol = strCol.ToUpper();      //A --> 1, BD --> (26+4), IV      //Can only have max of two characters.      char[] ach = strCol.ToCharArray();            if (ach.Length == 0)        throw new ArgumentException("Column cannot have 0 length. Must be A - IV. Was: '" + strCol + "'.");            else if (ach.Length == 1)        return GetNumberValue(ach[0]);      else        return 26*GetNumberValue(ach[0]) + GetNumberValue(ach[1]);    }    private static int GetNumberValue(char c)     {      return (int)c - 64;    }    private int _intRow, _intCol;    public int Row     {      get       {        return this._intRow;      }    }    public int Column     {      get       {        return this._intCol;      }    }  }}

1 comment:

  1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Online Training from India. or learn thru ASP.NET Essential Training Online . Nowadays Dot Net has tons of job opportunities on various vertical industry.
    JavaScript Online Training from India