Introduction
As my searches on the Internet show, there are enormous ways and libraries (mostly commercial) to use Excel files and Charts in a programmatic way. One of the official ways relating to the .NET world is using Primary Interop Assemblies or PIAs as a bridge between Office components and .NET programs.
It is true that Interop for many COM types could be generated automatically by "Type Library Importer (Tlbimp.exe)" and works well in many cases, but for some reason Microsoft decided to publish a special set of pre-generated Interops to work with its Office components. These Interops can be downloaded here and all you have to do at this point is to make a reference in your C# or VB project to the Interop.
Unfortunately, working with these Interops (specially Microsoft.Office.Interop.Excel) is not straightforward at all. Its complexity makes many programmers reluctant to use its powers and abilities. There are many details that should be considered, for example, to read a single cell from a worksheet.
The library introduced here works as wrapper around official Interop and hides its intricacies. Moreover another way for using Excel is presented which is a new approach inspired by the way some O/R mappers are using nowadays. You will be able to save an instance of a class into an Excel file or read data from different Excel files into an object all at once.
Background
The way I used here is different from Visual Studio Tools for Office which involves attaching managed code to documents and the code responds to events that occur in the document or workbook. Also, the method used is NOT creating *.xml files with Office defined schema. We are going to create and use REAL Excel files by our REAL programs. By REAL Excel file, I mean, all files that are directly used by Excel like XLS and XLT and by REAL program I mean any EXE or ASP.NET Web page you may create to answer customers' needs. On the other hand, we are not limited to file types and deployment platforms.
The first thing you need is the Microsoft.Office.Interop.Excel assembly. It can be found as a Redistributable package on the Microsoft download site (latest known direct link is here). Please notice that there are different versions of PIAs based on your Office version and you have to select and redistribute the version matches Office version of the destination machine. The Primary Interop assemblies are installed in Global Assembly Cache by default but obtaining a local reference to them is technically possible (however, there may be copyright restrictions and this matter must be considered before redistribution of the assemblies).
Your project also needs a reference to Taramon.Exceller.dll (which is provided in this article's download section) and after these two references, you will be ready to use the code.
Using the Code
Two main classes are provided for you in Taramon.Exceller namespace. The major one is ExcelManager and the cool one is ExcelMapper. The latter heavily uses methods and tools provided by the first one.
ExcelManager is a utility class and its usage is as shown below:
using (ExcelManager em = new ExcelManager())
{
em.Open("myfile.xls");
}
As you can see, ExcelManager is implemented as a disposable class. The Excel application is loaded into memory when ExcelManager is constructing (but don't worry, there would be no visual sign of Excel application on screen! Everything goes in the background!). After disposal of class (when execution follow exits from {} block), the Excel application would be wiped out and resources would release.
From now on, every access to Excel would be through the created instance of ExcelManager (em in this case).
Four groups of methods are available:
- Workbook related methods
Create()Open()Close()Save()SaveAs()
- Worksheet related methods
ActivateSheet()RenameSheet()RenameCurrentSheet()
- Cell related methods
GetValue()GetNumericValue()GetFormattedValue()SetValue()
- Range related methods
GetRangeValues()GetRangeNumericValues()GetRangeFormattedValues()SetRangeValue()SetRangeValues()
The first two groups are simple enough. An example of using them could be as follows:
using (ExcelManager em = new ExcelManager())
{
em.Open("myfile.xls");
em.ActivateSheet("Sheet 2");
em.RenameCurrentSheet("My Sheet");
em.SaveAs("myfile2.xls");
}
For using Cell and Range related methods, you should be familiar with the concept of Category:
In Excel, every cell may have two different values according to its formatting. Cell's numeric value represents the real value in the cell. This value can be used in calculations (like any numeric type, for example float, double, and so on). Cells also have another value that is a string (text) value. This value is used for display purposes and depends on the formatting used for the cell. For example, in a cell, you may type 1000. The numeric value would be 1000.0 and formatted value might be "1,000.00".
In my library, I have used an enum named Category and it could have two enumerators: Category.Numeric and Category.Formatted. A method like GetValue() expects you to explicitly tell which category of cell value (Numeric or Formatted) you mean. There are also different method names for working with different categories like GetNumericValue() and GetFormattedValue().
The following excerpt shows how to use a GetValue method:
using (ExcelManager em = new ExcelManager())
{
em.Open("myfile.xls");
double? n = em.GetNumericValue("A1");
if (n.HasValue)
{
Console.WriteLine("half of A1 is {0}", n.Value / 2);
}
else
{
Console.WriteLine("A1 is empty");
}
}
You can see that the returning type of GetNumericValue() is double? (double followed by a question mark). It is a nullable double. Employing a nullable type is useful for indicating an empty cell and a cell with some value.
In Cell related group of methods, there is only a value setter method - SetValue(). According to Excel paradigm, you can only set the value of a cell and Excel decides about formatted value (of course based on the format you have picked for the cell).
Range methods are similar for Cell group methods with only one difference: you have to specify both start and end addresses, like:
using (ExcelManager em = new ExcelManager())
{
em.Open("myfile.xls");
em.SetRangeValue("A1","B10","Hello!");
em.Save();
}
In this group, there are two setter methods:
SetRangeValue() which fills a range with a single value SetRangeValues() (notice 's' at the end of method name) which fills the Range with values from an implementation of IList (like ArrayList)
Map For Ease
Another class of the library is ExcelMapper. By using this class, you would be able to read one or more Excel files into an instance of a class or write contents of an object into an Excel file. This is somehow like the O/R mapping approach which saves an object to a relational database or restores back the object's state from database. But you should not be worried about this if you are not familiar with terms and world of mapping. All you need to know is in this library a mapper is used to read and write an object to and from an Excel file.
The first thing you need is a map class. This could be one of your program's business objects or a class you like to use as a tool to exchange data with Excel. A map class looks like this:
class Student
{
private string _Name;
[FromCell("A2")]
public string Name
{
get { return _Name; }
set { _Name = value; }
}
}
It is a normal class with a single property (Name). By using an attribute like [FromCell] we map this property to a cell in our Excel file. Then we would use this class like the following code:
ExcelMapper mapper = new ExcelMapper();
Student info = new Student();
mapper.Read(info, "C:\\Book1.xls");
Console.WriteLine(map.Name);
The above code reads Books.xls and looks for [FromCell] attributes, then fills properties with the associated cells.
When we plan to write a property to a cell, [ToCell] attribute and Write method of ExcelMapper are available. Furthermore, [FromRange] and [ToRange] attributes act as our weapon when dealing with ranges like this:
class Map
{
private ArrayList _Range;
[ToRange("A1", "A4")]
public ArrayList Range
{
get { return _Range; }
set { _Range = value; }
}
}
ExcelMapper mapper = new ExcelMapper();
Map map = new Map();
map.Range = new System.Collections.ArrayList();
map.Range.Add(10);
map.Range.Add(11);
map.Range.Add(12);
map.Range.Add(13);
map.Range.Add(14);
mapper.Write(map, "C:\\Book1.xls");
Accessing cells in different sheets would be easy. Just add [UseSheet] attribute before a property like the following example:
class Map
{
private string _Test;
[UseSheet("Sheet 2")]
[FromCell("A1")]
public string Test
{
get { return _Test; }
set { _Test = value; }
}
}
You can see that working with Excel cells and sheets is so easy and straightforward with this library.
Points of Interest
Since I have written this library (which was almost two years ago), I found that it can answer 90 percent of my Excel-related needs. Other special requirements could be added to the library with ease. For example, one may need saving charts as images which would be few lines in the ExcelManager class.
History
- 22nd August, 2008: Initial version
