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.