Introduction
Sometimes, when you're using the Excel.Interop
classes, you want to open a worksheet from code and tweak the values of various cells. It's hard to remember the syntax.
oSheet = (Excel.Worksheet)oBook.Worksheets[1];
((Excel.Range)oSheet.Cells[1, 1]).Value2 = "blah";
I don't know about you, but I might like to encapsulate the Excel.Worksheet
object I am using in a nice class with perhaps an operator []
overload, so I don't have to remember crazy syntax all the day long or be looking up code snippets and copying and pasting. I'm thinking that code of the form:
cellObj[1, 1]="blah";
int myCellValue = Convert.ToInt32(cellObj[1,2]);
is perhaps a bit more readable. So let's see if we can't make a class to help us with this.
ExcelCell Class
First, let's dive right into the meat. Here's the definition of our class, ExcelCell
, which encapsulates the Excel.Worksheet
object from Excel and allows access to individual cells.
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
public class ExcelCell : Object
{
private Excel.Worksheet oSheet;
public ExcelCell(Excel.Worksheet oSheet)
{
this.oSheet = oSheet;
}
public object this[int row, int column]
{
get
{
if (oSheet == null)
throw new InvalidOperationException(
"Excel.Worksheet reference is null.");
return ((Excel.Range)oSheet.Cells[row, column]).Value2;
}
set
{
if (oSheet == null)
throw new InvalidOperationException(
"Excel.Worksheet reference is null.");
((Excel.Range)oSheet.Cells[row, column]).Value2 = value;
}
}
public Excel.Range GetRange(int row, int column)
{
if (oSheet == null)
throw new InvalidOperationException(
"Excel.Worksheet reference is null.");
return ((Excel.Range)oSheet.Cells[row, column]);
}
}
Note: You should make sure that you have the Microsoft.Office.Core
and Microsoft.Office.Interop.Excel
modules loaded as references in your project. They can be version 12 and up.
Using the Class
To use the class, say you have an instance of Excel.Application
open and an instance of Excel.Workbook
open as well (in .xls or .xlsx etc. format), and your instances are named oExcel
and oBook
, respectively. Let's see this class in action:
ExcelCell cell = new ExcelCell((Excel.Worksheet)oBook.Worksheets[1]);
cell[4, 2] = "happy";
cell[4, 4] = "day";
int cellValue = Convert.ToInt32(cell[4,5]);
And that's all she wrote!
Points of Interest
This class is interesting in that it reveals how to overload the [,]
operator. A quick Google search can also find you the information, but watch how simplified your Excel programming gets after putting this class in your program.
History
- 23 Nov. 2010: Article submitted.
Dr. Brian Hart obtained his Ph.D. in Astrophysics from the University of California, Irvine, in 2008. Under Professor David Buote, Dr. Hart researched the structure and evolution of the universe. Dr. Hart is an Astrodynamicist / Space Data Scientist with Point Solutions Group in Colorado Springs, CO, supporting Space Operations Command, United States Space Force. Dr. Hart is a Veteran of the U.S. Army and the U.S. Navy, having most recently served at Fort George G. Meade, MD, as a Naval Officer with a Cyber Warfare Engineer designator. Dr. Hart has previously held positions at Jacobs Engineering supporting Cheyenne Mountain/Space Force supporting tests, with USSPACECOM/J58 supporting operators using predictive AI/ML with Rhombus Power, and with SAIC supporting the Horizon 2 program at STARCOM. Dr. Hart is well known to the community for his over 150 technical publications and public speaking events. Originally from Minneapolis/Saint Paul, Minnesota, Dr. Hart lives in Colorado Springs with his Black Lab, Bruce, and likes bowling, winter sports, exploring, and swimming. Dr. Hart has a new movie coming out soon, a documentary called "Galaxy Clusters: Giants of the Universe," about his outer space research. The movie showcases the Chandra X-ray Observatory, one of NASA’s four great observatories and the world’s most powerful telescopes for detecting X-rays. The movie has been accepted for screening at the U.S. Air Force Academy ("USAFA" for short) Planetarium and will highlight how scientists use clusters of galaxies, the largest bound objects in the Universe, to learn more about the formation and evolution of the cosmos --- as well as the space telescopes used for this purpose, and the stories of the astronauts who launched them and the scientists who went before Dr. Hart in learning more about the nature of the Universe.