Key Mapper Developer Blog

Koogra is an open-source MIT Licensed library for reading Excel files: it's latest release  supports Excel 2007 format.

I use it for an app which imports Excel spreadsheets: using the Office interop libraries is awkward and brittle, and it has to work with an existing install of Office which you can't control versioning or upgrading. So, Koogra. I don't need to write to the spreadsheets, just read them.

There are at least three distinct syntaxes in Koogra:

1. Excel 2007 syntax
2. Excel 2003 syntax (more accurately, 97-2003)
3. The interface syntax which links the two together

For example, finding a worksheet by name:

using Net.SourceForge.Koogra;
using Excel2003 = Net.SourceForge.Koogra.Excel; 
using Excel2007 = Net.SourceForge.Koogra.Excel2007; 

// The Excel 2003 syntax for opening a worksheet 
Excel2003.Workbook workbook = new Excel2003.Workbook(fileName); 
Excel2003.Worksheet worksheet = workbook.Sheets.GetByName(sheetName); 

// The Excel 2007 syntax 
Excel2007.Workbook workbook = new Excel2007.Workbook(fileName); 
Excel2007.Worksheet worksheet = workbook.GetWorksheetByName(sheetName);

// The interface syntax 
IWorkbook workbook; 
if (Path.GetExtension(fileName).ToLower() == ".xlsx") 
{ 
    workbook = new Excel2007.Workbook(fileName); 
} 
else 
{ 
    workbook = new Excel97.Workbook(fileName);
}

IWorksheet sheet = workbook.Worksheets.GetWorksheetByName(sheetName);

The only one I am interested in is the interface syntax, as I have to support both file types, so here is a basic code sample for reading some data from a given sheet in a given file.

namespace JustKeepSwimming.Net.Koogra.Example
{
    using System;
    using System.Collections.Generic;
    using System.IO;
    using Net.SourceForge.Koogra;
    using Excel97 = Net.SourceForge.Koogra.Excel;
    using Excel2007 = Net.SourceForge.Koogra.Excel2007;

    public class DataImporter
    {
        public List ImportData(IImportData importData, string fileName)
        {
            // The Koogra Excel library uses 0 based counting
            // unlike some others
            List results = new List();

            IWorkbook workbook;

            if (Path.GetExtension(fileName).ToLower() == ".xlsx")
            {
                workbook = new Excel2007.Workbook(fileName);
            }
            else
            {
                workbook = new Excel97.Workbook(fileName);
            }

            IWorksheets sheets = workbook.Worksheets;
            IWorksheet sheet = null;

            if (string.IsNullOrEmpty(importData.SheetName) == false)
            {
                // GetWorksheetByName returns null if name not found..
                sheet = sheets.GetWorksheetByName(importData.SheetName);
            }

            // .. use the first sheet in the workbook.
            if (sheet == null)
            {
                sheet = sheets.GetWorksheetByIndex(0);
            }

            uint lastRow = sheet.LastRow;
            if (importData.IsLastRowTotal)
            {
                lastRow--;
            }

            for (ushort i = (ushort)(importData.StartRow - 1); i <= lastRow; i++)
            {
                IRow row = sheet.Rows.GetRow(i);
                string name = GetCellValue(row, (uint)importData.NameColumn);
                string amountString = GetCellValue(row, (uint)importData.AmountColumn);

                // Parse as double so if Excel returns 7.0000000E-2
                // for 0.07 it's still recognized.
                double amountDouble;
                if (double.TryParse(amountString, out amountDouble))
                {
                    ImportItem item = new ImportItem(name, amountDouble);
                    results.Add(item);
                }
            }
            return results;
        }

        private string GetCellValue(IRow cells, uint column)
        {
            if (cells != null && cells.GetCell(column) != null)
            {
                object value = cells.GetCell(column).Value;
                if (value != null)
                {
                    return value.ToString().Trim();
                }
            }
            return string.Empty;
        }

        private string GetFormattedValue(IRow cells, uint column)
        {
            if (cells != null && cells.GetCell(column) != null)
            {
                return cells.GetCell(column).GetFormattedValue();
            }
            return string.Empty;
        }
    }
}