I’ve seen Excel used for a ton of different solutions; Flow Charts, Business Forms, EDI, Photo Album, etc… Regardless of how Excel is being used, it’s one of the most common business document formats I know.  We commonly refer to Excel as duct tape, in the office.  I’ve traditionally limited my web & Excel integration to the capabilities of the System.Data.OleDb namespace. This has been a great solution, and when you can’t buy 3rd party controls or run code in full trust, it’s a proven solution. This has solved 99% of my problems, but there’s always been a need to do a bit more than use Excel as a basic data source.  Fortunate for me, I found a trail of posts in StackOverflow that lead me to NPOI.  It is a .NET implementation of the Apache POI Project.  NPOI is a bit behind POI, but this amazing library gives you the ability to develop rich Excel with a easy to use API.

I’ve run into a few bugs since starting, but I found lots of posts on CodePlex where the project is hosted.  I’ve found that running 1.2.2 (alpha) solves a bunch of bugs I immediately found when running 1.2.1.  I downloaded the latest build from CodePlex, but it was still on a 1.2.1 branch and was missing some of the 1.2.2 fixes.

Now that we know what NPOI is going to provide us, lets go grab the files we’ll need to get started.

  1. NPOI.Util Basic assistant class library
  2. NPOI.POIFS OLE2 format read/write library
  3. NPOI.DDF Drawing format read/write library
  4. NPOI.SS Formula evaluation library
  5. NPOI.HPSF Summary Information and Document Summary Information read/write library
  6. NPOI.HSSF Excel BIFF format read/write library

To keep the demo easy to follow and provide some good ways to refactor NPOI into your existing project, I’ve taken a old function that would output a DataTable to a HTML formatted document with an Excel (xls) extension.  This solution has worked for years, but every time you open one of these XLS files you get the following message (“The file you are trying to open “”, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?“).  It’s a bit annoying and scary to the user, but this approach was used to solve the problem of Excel truncating leading zero’s in numeric data (e.g. 00002345).

Before NPOI Function (DataTable to HTML document saved with a XLS extension)
    /// <summary>
    /// Exports a DataTable as a HTML formatted table and forces the results to be downloaded in a .HTM file.
    /// </summary>
    /// <param name="dataTable">DataTable</param>
    /// <param name="fileName">Output File Name</param>
    static public void DataTableToHtmlXLS(DataTable dataTable, string fileName)
    {
        //Add _response header
        HttpResponse response = HttpContext.Current.Response;
        response.Clear();
        response.ClearHeaders();
        response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", fileName));
        response.ContentEncoding = Encoding.Default;
        response.Charset = String.Empty;
        response.ContentType = "text/HTML";

        DataGrid dataGrid = new DataGrid();
        dataGrid.EnableViewState = false;
        dataGrid.DataSource = dataTable;
        dataGrid.DataBind();

        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        dataGrid.RenderControl(hw);
        response.Write(sw.ToString());

        response.End();
    }

Error opening HTML file saved as XLS

** If you were to look at the the XLS file in notepad, you’d see the contents are HTML.

NPOI Function (DataTable to Excel)
    /// <summary>
    /// Render DataTable to Excel File
    /// </summary>
    /// <param name="sourceTable">Source DataTable</param>
    /// <param name="fileName">Destination File name</param>
    public static void ExportDataTableToExcel(DataTable sourceTable, string fileName)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream memoryStream = new MemoryStream();
        HSSFSheet sheet = workbook.CreateSheet("Sheet1");
        HSSFRow headerRow = sheet.CreateRow(0);

        // handling header.
        foreach (DataColumn column in sourceTable.Columns)
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

        // handling value.
        int rowIndex = 1;

        foreach (DataRow row in sourceTable.Rows)
        {
            HSSFRow dataRow = sheet.CreateRow(rowIndex);

            foreach (DataColumn column in sourceTable.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            }

            rowIndex++;
        }

        workbook.Write(memoryStream);
        memoryStream.Flush();

        HttpResponse response = HttpContext.Current.Response;
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
        response.Clear();

        response.BinaryWrite(memoryStream.GetBuffer());
        response.End();
    }

** Pretty simple right?  In my production code this is actually split into multiple functions so I can reuse the DataTable to Excel code.  The output of this function is a Excel 2003 (XLS) BIFF.

Now that we are using NPOI to create “real” XLS documents, we can do something cool like add formatting to our header row.

        // handling header.
        foreach (DataColumn column in sourceTable.Columns)
        {
            // Create New Cell
            HSSFCell headerCell = headerRow.CreateCell(column.Ordinal);

            // Set Cell Value
            headerCell.SetCellValue(column.ColumnName);

            // Create Style
            HSSFCellStyle headerCellStyle = workbook.CreateCellStyle();
            headerCellStyle.FillForegroundColor = HSSFColor.AQUA.index;
            headerCellStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;

            // Add Style to Cell
            headerCell.CellStyle = headerCellStyle;
        }

Adding Color Headers to POI Excel File

There is a million more formatting options possible and by the time you read this you should find a few more example on my site.