excel_reader

namespace Interreg.Infra.Forms{
  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Runtime.InteropServices;
  using Microsoft.Office.Interop.Excel;
  public interface IExcelReader:IDisposable{
    void Open(string path);
    string Get(string key);
    void ChangeSheet(string key);
  }
  public class ExcelReader:IExcelReader{
    private readonly Dictionary<string,object[,]>
      _sheetData=new Dictionary<string,object[,]>();
    private Microsoft.Office.Interop.Excel.Application _ap;
    private bool _open;
    private string _path;
    private string _sheetName;
    private Workbook _wb;
    private Worksheet _ws;
    //http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c/159419#159419
    public void Dispose(){
      GC.Collect();
      GC.WaitForPendingFinalizers();
      GC.Collect();
      GC.WaitForPendingFinalizers();
      if(_ws!=null)
        Marshal.FinalReleaseComObject(_ws);
      if(_wb!=null){
        _wb.Close();
        Marshal.FinalReleaseComObject(_wb);
      }
      if(_ap!=null){
        _ap.Quit();
        Marshal.FinalReleaseComObject(_ap);
      }
    }
    public void Open(string path){
      if(path==null) throw new ArgumentNullException();
      _path=path;
    }
    public string Get(string key){
      EnsureItsOpen();
      var rindex=(uint)key.ExtractNumber();
      var cindex=(key.Replace((rindex).ToString(),string.Empty)).ColNameToIndex();
      var val=_sheetData[_sheetName][rindex,cindex];
      return val!=null?val.ToString().Trim():string.Empty;
    }
    public void ChangeSheet(string key){
      EnsureItsOpen();
      if(_sheetName==key) return;
      _ws=(Worksheet)_wb.Worksheets[key];
      _sheetName=key;
      //http://dotnetperls.com/excel-interop 
      //perf increase reading whole sheet in object[,] =>
      //22 passed, 0 failed, 0 skipped, took 9,69 seconds (xUnit.net 1.6.1 build 1521).
      //22 passed, 0 failed, 0 skipped, took 2,50 seconds (xUnit.net 1.6.1 build 1521).
      var range=_ws.UsedRange;
      _sheetData[_sheetName]=(object[,])range.Value[XlRangeValueDataType.xlRangeValueDefault];
    }
    private void EnsureItsOpen(){
      if(_open) return;
      _ap=new Microsoft.Office.Interop.Excel.Application {DisplayAlerts=false,Visible=false};
      _wb=_ap.Workbooks.Open(_path);
      _open=true;
    }
  }
  public static class ExcelReaderExtensions{
    public static IList<string> GetRow(this IExcelReader r,IEnumerable<string> cells){
      return cells.Select(r.Get).ToList();
    }
  }
}