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.