NPOI Now Supports Excel 2007 and Later

NPOI's latest release, which as of 6/20/2013 is still in beta, now supports XLSX files. This is really great news, because as the world's technology problems get bigger, it needs spreadsheets that support more than 65,000 rows and 256 columns.

I'm a little bit late to the party -- the beta was released in March -- but I've been too busy lately to get to reviewing it.

I've modified my wrapper to support the new XSSFWorkbook class, so the usage of my wrapper remains essentially the same - see http://oopstruggles.blogspot.com/2011/12/npoi-wrapper.html for usage.

*** Download the wrapper here. ***

The newest NPOI download can be found at http://npoi.codeplex.com/releases/view/97124

One of the things that I'd like to add in my wrapper is support for Excel ranges. It doesn't support that yet, and that will definitely be the next thing I add...hopefully in the next month or two.

NPOI Wrapper

There's a utility that I find quite useful, and probably use it as much as any other 3rd party library. It's called NPOI, which is a .Net version of a Java project called POI. NPOI offers the ability to read and write Excel 97-2003 files. NPOI does not offer Excel 2007 and later manipulation (as far as I know), but that's for a different post.


***Update: NPOI now supports Excel 2007 and later. See this post for more details.

Prior to stumbling onto NPOI, I used to use Interop. I would occassionally use ADO, as well; however, both Interop and ADO were clunky, and had some pretty major drawbacks, including slowness, and awkward implementation. I'm also not a fan of using COM directly, because I'm not very good at it. So, eventually I found NPOI.

The problem with NPOI, (at least, my problem with NPOI), is that even though it is a fantastic tool, I had a tough time finding documentation all in one place. On top of that, I didn't find its external interfaces particularly intuitive.

So, what does a programmer do when he finds a tool useful, yet lacking in documentation and intuitiveness? He (or she) starts building a wrapper! And that's exactly what I did.

You can download the wrapper here.

The wrapper, as it is, covers the bulk of what I need to do with Excel (save for the lack of Excel 2007 support - this is because Microsoft changed the file format of Excel files in 2007 to a XML/binary format). However, there's probably things that some people feel inclined to do with Excel that this wrapper does not cover. If you're so inclined to use this wrapper, and add functionality to cover your needs, feel free to comment on this post.

I'll spare you some of the epiphanies and stories about how I had to track down various functionality of NPOI, and just get to the meat of how to use it, in a variety of examples.

Example 1 - Creating a new file

   ExcelWrapper excel = new ExcelWrapper();
   excel.CreateFile(@"c:\ExcelFile.xls");

Example 2 - Opening an existing file, then saving it

   ExcelWrapper excel = new ExcelWrapper(@"c:\ExcelFile.xls");
   excel.SaveFile();

Example 3 - Getting cell values (note, as opposed to Interop, NPOI is 0-based, not 1-based)

   ExcelWrapper excel = new ExcelWrapper(@"c:\ExcelFile.xls");
   string a1 = excel.GetCellValue(0, 0);
   string b1 = excel.GetCellValue(0, 1);
   string b2 = excel.GetCellValue(1, 1);
   string d4 = excel.GetCellValue(3, 3);

Example 4 - Getting a sheet as a System.Data.DataTable

   ExcelWrapper excel = new ExcelWrapper(@"c:\ExcelFile.xls");
   DataTable dt = excel.ToDataTable();

Example 5 - Working with different sheets

   ExcelWrapper excel = new ExcelWrapper(@"c:\ExcelFile.xls");
   excel.SetActiveSheet("Sheet2");
   excel.SetActiveSheet(1); /// The sheet collection in a workbook is 0-based
   excel.DeleteSheet("Sheet3");
   excel.CreateSheet("Sheet99");
   excel.SaveFile();

Example 6 - Manipulating Cells

   ExcelWrapper excel = new ExcelWrapper(@"c:\ExcelFile.xls");
   excel.WriteCellValue(0, 0, "This is A1");
   excel.WriteCellValue(1, 1, "This is B2 - bolded and italicized", true, true);
   excel.HighlightCell(1, 1, ExcelColors.Yellow);
   excel.SetCellFontColor(1, 1, ExcelColors.Blue);
   excel.SaveFile();

Example 7 - Converting Excel Cell Names to 0-based Row and Column

   ExcelCell cell = new ExcelCell("B23");
   ExcelWrapper excel = new ExcelWrapper(@"c:\ExcelFile.xls");
   excel.WriteCellValue(cell.Row, cell.Column, "Cell B23 is set!");
   excel.SaveFileAs(@"c:\ExcelFile2.xls");

Example 8 - Working with formatting and styles

   ExcelWrapper excel= new ExcelWrapper("h:\\wrapperTest.xls");            
   ExcelStyle style = new ExcelStyle();
   style.BackColor = ExcelColors.Blue;
   style.ForeColor = ExcelColors.Yellow;
   style.IsBold = true;
   style.IsItalics = false;
   style.BorderType = BorderTypes.Dashed;
   style.BorderTop = true;
   style.BorderBottom = true;
   style.FontFace = CommonFonts.Calibri;
   style.FontSize = 15;
   excel.WriteCellValue(6, 2, "6, 2 coordinates", style);

So, there you have it. This is my NPOI wrapper, along with some examples. I'll probably add more to this post as it grows, but for now, this is what I've got.

The Vendor, The Consumer, and Loose Coupling

I recently had an epiphany that has made me a better programmer. Seeing as the intent of this blog is to share my experiences in my journey with object oriented programming, and considering this discovery relates to object oriented programming, I thought I would share.

We all know that code should be loosely coupled. That is, objects should avoid knowing too much about unrelated objects. If an object shouldn't need another object to exist and function, then why should that object know about it at all?

There's a lot of nuance in this concept, but part of it goes back to programming in layers.

So what happens when this nuance comes into play?

For a few years now, I've had a solution that worked well enough, but violated loose coupling principles.

I've dealt with this lack of eloquence for a time, partly because I didn't really have a better solution, and partly because it worked well enough.

But I've got a solution now, and it was something that I knew about all along: Event Handlers.

An Event Handler is the perfect solution for loosening up the coupling of your objects, because it puts the flow in the proper perspective. In code, there are vendors, consumers, and states. The Vendor in code is the code that has methods, attributes, etc. The Consumer is the code that uses another object's methods, attributes, etc. A state is sort of an abstract concept that describes when something happens - for example, when an object is out of work to do, or when a button gets clicked, or when an item is added to a collection.

A Vendor should generally have to know as little as possible about the consumer. This is a principle that my code has often failed to follow religiously. And the effect is tightly coupled code.

Consider a System.Windows.Forms.Button object. The Button object knows very little about other controls on a form, and it certainly doesn't know anything about your custom controls that do all sorts of funky stuff. And yet, you can make it so that a click of a button has an impact on all the other controls on the form.

How can this be?

Well, the short answer is by hooking an event handler up to the Clicked event of the button.

This is something a programmer learns within about 10 hours of programming in WinForms or WPF or whatever UI tool he (or she) has. But there's a bigger principle at play that a 10 hour old programmer probably doesn't know, and that is this loose coupling principle.

The Button has a Clicked event that some consumer (in this case, the Form that the Button is on) can subscribe to, and pass a delegate function to. So when the event fires (the button gets clicked), the consumer delegate that is hooked up to the vendor's (Button's) event gets called, and any behavior defined in the consumer's custom delegate gets executed.

This model is a beautiful one, and the underlying principle opens up a whole world of more eloquent solutions.

Consider (some version) of my real life example.

In an application I built about 8 months ago, I have an object called ActivityMonitor. The ActivityMonitor object keeps track of what's going on in the application, and exposes that information when a TCP/IP client requests it.

So, the ActivityMonitor looks something like this:

class ActivityMonitor
{
   ActivityCollection _activities;

   public void AddActivity(string activity)
   {
      Activity a = new Activity(activity);
      _activities.Add(a);
   }

   public string GetActivityString()
   {
     return _activities.ToString();
   }
}

So, before considering Event Handlers as a solution for this problem, when non-related objects did something, and I wanted the ActivityMonitor to know about it, I would send the ActivityMonitor to the consumer object as a parameter. This is a tightly coupled pattern, and as I see it now, something I'll avoid in the future.

For example:

public class BigSpecialObject
{
   ActivityMonitor _monitor;

   public BigSpecialObject(ActivityMonitor monitor)
   {
     _monitor = monitor;
   }

   public void DoStuff()
   {
     _monitor.AddActivity("About to do a bunch of stuff");
     ///Do a bunch of stuff
     _monitor.AddActivity("Done doing a bunch of stuff");
   }
}

When I look at BigSpecialObject, I can infer a few things:

1. The consumer of BigSpecialObject knows about ActivityMonitor
2. BigSpecialObject is tightly coupled with ActivityMonitor
3. It didn't have to be this way

The issue of state comes into play. Presumably, at instantiation time, BigSpecialObject is not done doing stuff. After BigSpecialObject.DoStuff() is called, at some point, BigSpecialObject is done doing stuff. This is a great example of where an EventHandler is not only appropriate, but it is the optimal solution.

So, with a couple extra lines of code, I can really "bust some heads" (to use a Ghostbusters reference):

public class BigSpecialObject
{
   public event EventHandler WorkRequested;
   public event EventHandler WorkCompleted;


   public BigSpecialObject()
   {
     /// Notice that BigSpecialObject
     /// knows jack-squat about ActivityMonitor
   }

   public void DoStuff()
   { 
     this.alertWorkRequest();
     ///Do a bunch of stuff
     this.alertWorkDone();
   }

   private void alertWorkRequest()
   {
     if (this.WorkRequested == null)
       return;

     this.WorkRequested(this, new EventArgs());
   }

   private void alertWorkDone()
   {
     if (this.WorkCompleted == null)
       return;

     this.WorkCompleted(this, new EventArgs());
   }
}

So, the consumer of BigSpecialObject would take advantage of this loose coupling by doing the following:

public class Consumer
{
   ActivityMonitor _monitor;

   BigSpecialObject _bso;

   public Consumer()
   {
     this.initialize();
   }

   private void initialize()
   {
     _monitor = new ActivityMonitor();
     _bso = new BigSpecialObject();

     _bso.WorkRequested += new EventHandler(onBSOWorkRequested);
     _bso.WorkCompleted += new EventHandler(onBSOWorkCompleted);
   }

   private void onBSOWorkRequested(object sender, EventArgs e)
   {
     _monitor.AddActivity("About to do a bunch of stuff");
   }

   private void onBSOWorkCompleted(object sender, EventArgs e)
   {
     _monitor.AddActivity("Done doing a bunch of stuff");
   }

   public void DoSomeConsumption()
   {
     _bso.DoStuff();
   }
}

So, there you have it. Pattern versus Antipattern. Having the Consumer class alert the ActivityMonitor when the work is done is better, because that means that BigSpecialObject is more reusable. And even though we added more lines of code (believe it or not, I try to find solutions to problems that minimize lines of code), this is a better solution, because of the looser coupling.

In looking at code I've written in the past, it's completely littered with examples of tight coupling. Well, I guess it's time to get to work!

PHP Code that Looks like .Net code

This blog references PHP files available for download here: Click Here

In programming, I think it's important to learn. Learning can involve new aspects of your chosen language, or new languages. I've been thinking about my PHP days lately. I really know a lot more about programming now then I did when I was actively programming in PHP.

When I go back and look at some of the code I was producing in PHP, I cringe a bit at all of the principles I see myself violating. Most of the PHP code I wrote were big balls of mud.

So, as an academic experiment, as well as to try to re-sharpen some of my PHP skills, I decided to write some base libraries that I may (or may not) eventually use someday.

As with any language (programming or verbal), or with any foreign concept, the first thing to do is to find metaphors that allow us to relate the themes of the thing we're learning to the things we already know. The classic example in programming is the Hello World application.

Well, I'm not too interested in creating a "Hello World" for PHP, because I'm already familiar with a little bit of how PHP works. But what I wanted to do was to build some PHP code that looks (at least a little bit) like .Net code.

So, after thinking a little bit about what libraries I use most in .Net, I centered around System.Data, in particular DataTable (and DataRow, DataColumn, DataRowCollection, and DataColumnCollection). I haven't created DataSet yet, because there's only so much time in the day.

After thinking a bit more about how I would build some of this PHP code that looks like (has a similar interface) the System.Data .Net library, I concluded that I wanted to have a base Collection object. So I created it, and put it into a file called System.Collections.php. Below is a diagram of the code I created:



As you can see, in my PHP code, a DataTable has a DataRowCollection and a DataColumnCollection, as well as a couple of the external interface behaviors of .Net System.Data.DataTable (NewRow() and AddRow()); however, because of core differences between C# (or I guess .Net languages) and PHP, there are a few changes. Below are the issues that I encountered in this mini-project:

1. I couldn't figure out if I could use an indexer type property to have square brackets ([]) to represent an indexed item in a collection. Therefore, I left that for later.

2. There are some pretty serious differences between C# and PHP in terms of how they type variables. In C#, generally you generally define a variable as a type on the left side of the variable name, and you initialize it on the right side (eg int a = 3; OR Person p = new Person()). You don't have to do that in PHP, so you can initialize a variable as any type, which throws a bit of a wrench into the model of how I see the world.

3. PHP (at least 5.0) supports a lot classic OOP concepts such as inheritance, interfaces, polymorphism, Exception handling, etc. Obviously, the syntax of how to do this is different than it is in C#, so I dealt with some of the pains of the languages' differences.

4. PHP (from my understanding) does not support generic variables. So I can't use List variables the way I like to in C#. So, that's why DataRow and DataRowCollection both inherit from my Collection class.

But, I think my finished product gives me a basis to build on for later. So, if I wanted to initialize a DataTable in PHP code, I would include a reference to System.Data.php, and do something like below:

 
include 'System.Data.php';


$dt = new DataTable();
$dt->AddColumn("FirstName");
$dt->AddColumn("LastName");
$dt->AddColumn("Age");


$dr = $dt->NewRow();
$dr->AddValue("FirstName", "Tim");
$dr->AddValue("LastName", "Claason");
$dr->AddValue("Age", 29);


$dt->AddRow($dr);

Notice in the above that, unlike c#, I can't do $dr["FirstName"]. Maybe there's a way to do this in PHP, but in my (short) research time, I didn't find it.

Obviously, this interface isn't exactly the same as .Net's System.Data, but it's close enough to give me a decent metaphor between PHP and C#.

Please note that I haven't tested this code yet, so there are no guarantees as to whether or not it works. If it does work, expect more blog entries on this topic.

To download the PHP Code I've got so far, Click Here

ID3 Decision Tree in C#

This blog references an executable and source code available for download. To download the referenced executable, Click Here

To download source, Click Here

It seems like just about anything "new and shiny" can distract me from the last "new and shiny" thing that I decide to devote all my energy to learning about and/or building. Case in point: AI (artificial intelligence). There are all kinds of ways to use AI, and there's all kinds of subtechnologies that make up the field of Artificial Intelligence.

Having said that, the first technology that has been easy enough for me to get my head around is "Decision Tree Learning." A decision tree is basically an algorithm that takes a set of collected data, the outcomes of each collection of inputs, and builds a tree to demonstrate the best input variable for a particular output. From my college days, I had lots of statistics classes that were very similar to this concept - particularly, regression analysis.

A decision tree ends up looking a bit like the image below:



An example of a decision tree implementation is, if you want to gather a bunch of information about what inputs affect whether or not it's going to rain, a decision tree can build a graphical representation on whether it will rain or not based on the inputs you provided. Inputs for a decision tree may include cloudiness, temperature, relative humidity, what the weather report says, etc. The decision tree algorithm should calculate the input that best guesses whether or not it's going to rain, and then finds the next best variable, etc, until a tree has been built that demonstrates a decision tree for determining whether or not it's going to rain.

To demonstrate with words: If the weather report says it will rain, and if the relative humidity is high, and it's very cloudy, then the output will be rain.

Well, you get the idea...I don't completely have my head around the various ways to build a decision tree. In fact, I'm quite a novice when it comes to demonstrating decision tree algorithms.

The reason that I'm writing this blog entry at all is because I found a pretty decent implementation of an ID3 decision tree in C# at codeproject.com. But when trying to use it to suit my needs, I wasn't able to make the original code suit my needs. There were a few problems with the code that I felt compelled to fix.

I don't know much about fellow who wrote this particular C# ID3 algorithm except that his screen name is "Roosevelt" and he's from Brazil - and the source code is commented in Portugese. And he wrote it a long time ago.

The funny thing is that this was really the only C# code I could find on the subject, and this code was written 7 1/2 years ago. ID3 is not the most recent technology in Decision trees (evidently, C4.5 is a more recent iteration of decision tree learning). Sure, Java code exists, but I'm not a Java developer, so some of the base libraries referenced made it difficult for me to translate the Java code to C#.

So, I decided to see if I could take Roosevelt's code and make it less rigid (you see, all of the source data and attributes are statically defined in the code. There's no way to configure the data without recompiling, and that just won't do). In my iteration, the decision tree can be built dynamically based on the source data - it does not rely on statically defined concepts within the code, anymore (and output is not in Portugese, either).

I did some other refactoring of the code as well, and made it a bit better - probably still not quite right, but I think it's quite a bit better.

To download the executable I built, Click Here

To download source, Click Here

Now that I've gotten my fill of this "new and shiny" thing, I can get back to the last "new and shiny" thing I was working on, and maybe some day (hopefully sooner than 7 1/2 years from now), someone who is zealous enough to improve my code can do so, and share it with the world. For now, this is my contribution.

Writing Code That Writes Code

This blog references an executable and source code available for download. To download the referenced executable, Click Here

To download source, Click Here

The Pragmatic Programmer, which is a really nifty book that outlines principles for how a programmer should conduct himself (or herself), tells us to "write code that writes code."

There are several reasons one would want to have code that writes code:

1. We tend to write code in a consistent way.
2. If we don't write code in a consistent way, we should
3. Because of 1 & 2, reducing the amount of lines of code we have to physically write can increase our productivity, because it takes less physical work to create more functionality.

When I'm building a database application in C#, my business logic classes often look very similar. When considering how I would build an application that would write code for me, I started by looking at the consistencies in my code. I found the following:

1. I mostly write database applications, and business classes that represent (more or less) table definitions in my database. For example, for a CRM application that manages marketing campaigns, I might have a table called "Person" that represents people in my database; I may also have a table called "MarketingCampaign" that allows me to keep track of my marketing campaigns. I may have a link table called "MarketingRecipient" that links "Person" to "MarketingCampaign" (in other words, the MarketingRecipient table has a record for each person a marketing campaign targeted). To represent these business concepts in code, I would probably build *at least* 3 classes (probably more because I'm careful to avoid violating the Open-Closed principle). But these classes would probably be: Person, MarketingCampaign, and MarketingHistory (which is a collection of MarketingCampaign objects). I don't use ORM (object-relational mapping), because I don't like to surrender as much as it seems ORM asks us to surrender for the sake of convenience.

2. Most of the business layer classes I build have a load() method and a Save() method, and they often look similar; however, not similar enough to over-rely on inheritance.

3. I often have a need to represent business layer classes as a collection. These collections often look very similar - again, not similar enough to use inheritence (IMHO).

4. I make an effort to document (comment) every private member, public attribute, constructor, and method.

5. I often have a need to have a corresponding public attribute for every private member I have. Obviously this is not always the case.

Armed with these bits of information, I embarked on building a rinky-dink code generator that suits my needs.

So, this little application looks as follows:



As you can see, this app is quite simple, and the top input control is for "Class Name". This, as you might guess, asks you to enter a class name.

In the below example, I create a class name of "Person". Once I click the "Create" button, the panel below is activated.


At this point, I can start adding attributes to the class. I can manually type in the data type I want, or there is a set of primitive types in the dropdown.



Once I'm done adding attributes to the class, I click the "Generate Code" button in the lower right corner:


And voila - we have code. Granted, there's not a ton of functionality or code smarts this buys me, but it does do the following:

1. It does quite a bit of typing of members and attributes - this can be a big time saver when talking about 10-50 classes being created
2. It comments for me. Maybe not the best comments, but I like to comment all my members, attributes, and methods
3. It saves me from having to rewrite the same things over and over again, which helps me to abide by the "Don't repeat yourself" principle.

Granted, I often have to change things that are producted from this little app, but as mentioned above, this little app has turned out to be a big time saver. I'm sure there are better code generators out there, but for now, this works for me.

To download the referenced executable, Click Here

To download source, Click Here