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:
- I have only used these for simple, internal tools, not enterprise-critical production environments. Therefore they are not streamlined for performance.
- 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.AddSheet("Test1");
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()
{
this.StartExcel();
}
private Excel.Application excelApp = null;
private void StartExcel()
{
if( this.excelApp == null )
{
this.excelApp = new Excel.ApplicationClass(); //starts an Empty instance of Excel
CreateNewWorkBook();
}
}
private void CreateNewWorkBook() //string strFilePath
{
Excel.Workbook wb = this.excelApp.Workbooks.Add(Type.Missing);
//delete all worksheets but 1:
DeleteSheet(3);
DeleteSheet(2);
}
public void DeleteSheet(int intIndex)
{
((Excel.Worksheet)this.excelApp.ActiveWorkbook.Sheets[intIndex]).Delete();
}
public void AddSheet(string strName)
{
Excel.Worksheet newWorkSheet;
newWorkSheet =
(Excel.Worksheet)this.excelApp.Worksheets.Add(
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorkSheet.Name = strName;
}
public void SetCellContent(string strWorkSheet, Cell c, string strContent)
{
Excel.Worksheet sheet1 =
(Excel.Worksheet)this.excelApp.Sheets.get_Item(1);
((Excel.Range)sheet1.Cells[c.Row,c.Column]).Value2 = strContent;
}
public bool Show
{
get
{
return this.excelApp.Visible;
}
set
{
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;
}
}
}
}
|
links: digg this technorati
want to learn more about EXCEL,
or other subjects that interest you,
you can see our free lessons,
we have hundreds of free lessons in <a
href='http://www.wisetrainer.com'>www.wisetrainer.com</a>
try us
its free,
you can even put our free movies in your site
