Showing posts with label design pattern. Show all posts
Showing posts with label design pattern. Show all posts

Saturday, 29 June 2013

ExcelHandler updated version

About a year ago, I published the first release of the ExcelHandler library with the corresponding first NuGet package, it was the first experience with these powerful way of sharing code with the community. Since then, the time has passed and several things have happened, I received a pull request from Paulo Rezende that came with a good addition regarding to be able to get the number of sheets for a workbook, he also has some good ideas that I want to discuss with him and some of those are already in an experimental branch.

When looking for some kind of automatization at the moment of generating and publishing the NuGet Package, found an interesting Visual Studio Extension named NuGet.PackageNPublish.Extension that adds a new project template with the skeleton of a nuget package, it uses the text templating for recreate the .nuspec file each time the project is rebuilt and extracts as much as it can form the metadata in AssemblyInfo.cs file which is a good idea because the version and file names are prone to be out of synchronization when done manually.

Some of the improvement added to this library include: updated dependencies to newer versions, the NPOI and EPPlus libraries also evolved through the months, so there were some breaking changes that were changed in internal implementations for each dependency. Finally I added an automated test project, so it's not more necessary to run the console application sample. New elements in interfaces are:
  • NumberOfSheets property in IExcelHandler interface, now it's possible to know the amount of sheets in a workbook.
  • CreateSheet(string) method in IExcelHandler interface, now it's possible to add new sheets to a workbook.
  • LoadExelFile(string) doesn't require the file exists in both implementations, so it can also be used to create new Excel files and not just for editing existing ones.
The current version of source code can be found at https://bitbucket.org/abelperezok/excelhandler/ and the NuGet Package at https://nuget.org/packages/ExcelHandler/. Hope this to be useful and any comment or issue will be welcome.

Tuesday, 13 March 2012

Handling Excel spreadsheets XLS and XLSX with dot NET

More than once in my projects the format for input and/or output is Excel spreadsheet either using Excel 97-2003 (.xls) or Excel 2007 OOXML (.xlsx). The open source community contains several libraries, for legacy files the most famous is NPOI, which in fact is the .NET version of POI Java project at http://poi.apache.org/. The result about modern OOXML were more diverse, ExcelPackage was the first but this project was abandoned late 2010. However this code wasn’t to the trash, EPPlus is the evolved version based on it and strongly optimized by the new team.
If we want to use indiscriminately the two formats, it’s necessary to have updated the two libraries and since they are developed by two different teams, they have two completely different interfaces and ways to be used. Despite the fact it’s open source and the hard work is done, it’s a mess to deal with this difference in our code.
Start writing if-else statement alongside the code is not an option! This will get us to Spaghetti code anti-pattern and the consequent difficulties for maintaining the resulting software. So the design patterns to the rescue. The quick solution for the immediate needs, it’s necessary a common interface for basic operation with Excel files and we have two different implementations. The adapter pattern (aka wrapper) is the best match. The interfaces created are:
public interface IExcelHandler : IDisposable
{
    void LoadExelFile(string fileName);
    IExcelSheet GetSheet(int index);
    void Save();
    void Save(string filename);
}

public interface IExcelSheet
{
    string GetCellValue(int row, string column);
    void SetCellValue(int row, string column, object value);
}
 
The xml comments were removed for brevity but the methods’ names are self-explanatory (or at least I think so), the only thing might be confusing is the column parameter which is defined as string because it’s intended to be invoked with the actual Excel column name, such as A, B, etc. The operations defined above were enough in order to solve my needs; I know this might be extended for more complex scenarios but this is a first version and I don’t like to make over engineering or as we say in my town: killing mosquitos with cannons.
Having the common interface and all the implementations is not enough for a reusable component and abstract the client from the concrete implementation, how is to be created the instances of each implementation according to the file format? Design patterns again, the Factory Method may be one solution, in fact I made a simplified version using an interface that defines a Create method who is the responsible for create the concrete IExcelHandler instance.
public interface IExcelHandlerFactory
{
    IExcelHandler Create(string filename);
}
 
This interface might be injected via your favorite IoC Container and linked to the default implementation I provide in the class ExcelHandlerFactory or if you aren’t using dependency injection it can be used as a Singleton through the static property Instance.
As NPOI and EPPlus are already packages in the NuGet Gallery, I’ve done a package with these codes in order to contribute to the community and to be easier to bootstrap when handling Excel files. The package is available on NuGet feed at https://nuget.org/packages/ExcelHandler, just execute the following command at NuGet Package Manager Console:
Install-Package ExcelHandler
This will add automatically as dependencies the NPOI and EPPlus packages with all the corresponding references in the project. An example on how to use it is as follows:
Console.WriteLine("Opening and modifying a file with Excel 2007+ format...");
using (var excelHandler = ExcelHandlerFactory.Instance.Create(@"..\..\modern_file.xlsx"))
{
    var sheet = excelHandler.GetSheet(1);
    Console.WriteLine(sheet.GetCellValue(1, "A"));
    sheet.SetCellValue(1, "A", "Test value in A1 cell");
    excelHandler.Save();
}
Console.WriteLine("... done.");
Console.WriteLine("Opening and modifying a file with Excel 97-2003 format...");
using (var excelHandler = ExcelHandlerFactory.Instance.Create(@"..\..\legacy_file.xls"))
{
    var sheet = excelHandler.GetSheet(1);
    Console.WriteLine(sheet.GetCellValue(1, "A"));
    sheet.SetCellValue(1, "A", "Test value in A1 cell");
    excelHandler.Save();
}
Console.WriteLine("... done."); 
 
This example just opens two Excel files, read the content from the cell A1 and set some text in the same cell. Since the interface IExcelHandler inherit from IDisposable it can be used in a using statement in order to simplifying the resource disposing. The source code is available at https://bitbucket.org/abelperezok/excelhandler.

Sunday, 22 January 2012

Good practice for ASP.NET MVC Pager


When the data to be listed in a page becomes too long everybody agrees that it’s necessary to split the long flow into several small pieces, well that’s known in IT world as paging data, as you should know from day-to-day work in web applications. Every web technology gives us in some way the opportunity to do this without too much complexity. ASP.NET MVC is not an exception, after a quick Google-research I’ve found, of course since this problem is so common, tons of implementations, but the most relevant (at least in my modest opinion) was the one we can find at http://en.webdiyer.com/. There is another important implementation at https://github.com/martijnboland/MvcPaging that has also a NuGet package for ease the distribution. Almost all the implementations I found were inspired by ScottGu's PagedList idea.
I inspected many others but the general idea was in a way or another the same mentioned before over and over again. Finally I decided grab the first one and start using it as it comes out of the box. It was really easy to use: just make sure of your viewmodel has a IPagedList object, then specify the controller, action, pagination options, ajax options and html custom attributes.
Long story short, everything went good until I found a scenario where I needed a parameter combination that the author did not foresee. Wow! I really appreciate the amount of overloads made by the author, but in some way it was not enough for my scenario, so that’s Open Source magic. I downloaded the project and inspected it in a more detailed way. It really allowed the most common ways to make the pager but I needed at same time to specify controller, action, route data and ajax options for accomplish my task, after all I think this is not so uncommon scenario, I had a filter for my list and of course when I changed the current page the filter information must keep between round trips to the server.
The solution was actually simple, just make another overload in the Helper class and ready, but I wanted to dig deeper in the project structure and implementation and I discovered something I didn’t like too much (and it’s something I’ve seen in almost every projects in my research). The logic and view were strongly coupled. I am not a software philosopher but I like the good practices and the life had demonstrated to me that they are important. When I say strongly coupled I mean the same class is responsible of these two different tasks and concerns.
The relation between the classes is like this:
+-------------+    Render Pager    +--------------+
| PagerHelper |------------------->| PagerBuilder |
+-------------+                    +--------------+

The idea was to split that big class into two classes PagerModel and PagerView (obvious naming?) where the class PagerModel is responsible for all the logic involved such as the tedious calculations about page numbers, amount of pages, etc. model integrity validations using as transport a class PagerItem, this class holds data about each item to be shown in the pager but NOT how is to be shown, it’s only responsible for return a valid list of PagerItem. This separation of concerns is not just for to be compliant with the “holy bible” of design good practices, I’ve done this because I forecasted that in a short future it was going to be useful.
My refactoring looks like this:
+-------------+ Render Pager   +-----------+  Get Model +------------+
| PagerHelper |--------------->| PagerView |----------->| PagerModel |
+-------------+                +-----------+            +------------+

That future moment is arrived a few days ago, when I had to implement the same pager but this time with a different markup, actually just HTML and links and no ajax compatibility despite the fact I’m using unobtrusive from the default template. With this new distribution it was easy to me to create a new type of view, and why not, a little change of name, PagerView to AjaxPagerView and the new view named HtmlPagerView. It looks this way:
            Render         +---------------+  Get Model             
       +------------------>| AjaxPagerView +-----------------+        
       |                   +---------------+                 V        
+------+------+                                         +------------+
| PagerHelper |                                         | PagerModel |
+------+------+                                         +------------+
       |                   +---------------+                 ^        
       +------------------>| HtmlPagerView +-----------------+        
            Render         +---------------+   Get Model             

This is my vision of making a reusable design; in this case, instead of making a bunch of if-else or switch-case statements I apply a mixture between the Strategy and Decorator Design Pattern where every kind of view is a different “strategy” of rendering and “decorates” the model, which allows me in a future to implement many other types of view without affecting the core functionality and the actual logic: the model. The input options must be processed by the model class and make all the proper decisions returning a data structure which in most cases is named view model, ready for the view engine to read the necessary data from it. The view engine could be dependent in some way of the model element, but the model can NEVER be dependent of the view engine.
I hope this little refactor-reflection had made you think a bit more about real code reuse and the importance of using good practices and design patterns not just for to follow magic guidelines. I’d like to remark the consequences of developing software without keeping on eyes these techniques.
By the way, tell me about my ASCII-UML? Not bad, is it?