Excel 97-2003 BIFF (binary file) NPOI Reader/Writer Helper for .NET

This NPOI wrapper/helper aims to make reading and writing of Excel files extremely easy (only a few lines to read or write an entire sheet), then exposes the NPOI object so you may do other more advanced operations. NPOI is a little known .NET port of an Apache project to create an open source Microsoft Office 97-2003 file reader and writer (without using or having Microsoft Office installed). NPOI has various examples included, but they are basic, disjointed and the API is complex for many people's usage.

My VB.NET / C# code makes it very easy to read an entire worksheet or update or write new sheets with some formatting. I have also included extra functions to export a Data Table or Grid View to an XLS file. This class is not meant to be the be all and end all of Excel reading and writing. It is just meant to give you a "leg up" so you can to do the basics or get started with NPOI. This class, with a few unreleased extras specific to my apps covers 95% of my corporate usage - so i hope it helps other people out too.

See NPOI's web site for the latest assemblies: http://code.google.com/p/npoi/ or http://npoi.codeplex.com/
This wrapper is designed to work with NPOI version 1.2.4

NPOI's own description of what it is: "This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application. For example, you can use it to generate a Excel report without Microsoft Office suite installed on your server and more efficient than call Microsoft Excel ActiveX at background; you can also use it to extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines)."

API:

This code (ExcelNPOI.vb or ExcelNPOI.cs) contains XML comments. Please refer to these (or Intellisense in Visual Studio) for the API help. There are functions to open, save, read cells (in various types), write cells and add formatting. It can also export a Data Table (with or without custom headers and selective columns).

I have left ExcelNPOI121.vb and ExcelNPOI122.vb in this zip in case you prefer the older versions of NPOI.

Sample VB Code:

This code will read the entire first sheet to a label called lblMessage. Then it will write a few values and save the file. All in 15 lines (I excluded classes and sub's for easier reading - I assume you have basic .NET knowledge so you know what to do with it.

Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel
Imports NPOI.HPSF
Imports NPOI.POIFS.FileSystem

Dim xl As New NPOIExcel
xl.Open()
xl.WriteNewRow()
xl.WriteCell("text")
xl.WriteCell(1234)
xl.WriteCell(Now)
xl.WriteCellAsDate(Now, "dd/MM/yy")
xl.FormatLastCell(True, CellStyle.RIGHT, 15, 2, NPOI.HSSF.Util.HSSFColor.BLUE.index, NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index, True)
xl.Save("c:\path\sample data.xls")
xl.Open("c:\path\sample data.xls")
While xl.ReadRow
    While xl.CanReadCell()
        lblMessage.Text &= "," & xl.ReadCell().ToString
    End While
    lblMessage.Text &= "<p/>"
End While

This code will save a DataTable to an xls file. All in 2 lines:

Dim xl As New Excel
xl.TableToXLS(dt, Nothing, Nothing, True, "c:\path\export.xls")

C# Version:

The C# version has been machine converted from the VB class. I have manually added the overloads (as C# prior to vers 4 does not support optional params / defaults). VS does not give me any errors, but i have not tried to run it.

Licence:

Public Domain. Permission to use, copy, modify, and distribute this software and its documentation for any purpose and without fee is hereby granted.
This software is provided "as is" without express or implied warranty.

If you improve this code, please send me a copy! Thanks!

Hunter Beanland
hunter @ beanland.net.au
http://www.beanland.net.au/