/*
 * ExcelReader.cs 0.1
 *
 * Copyright 2006 Ian Escarro. All rights reserved.
 * PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace RuralNet.Shinobi.Data
{
	/// <summary>
	/// 
	/// </summary>
	public class ExcelReader : IExcelReader
	{
		private string fileName;
		private string worksheet;
		private DataTable dt = new DataTable();
		private OleDbConnection conn; 
		private int current = 0;
		private int step = 1;
		private Excel.Application excel = new Excel.Application();
		//		private Excel.Sheets sheets;
		//		private Excel.Worksheet sheet;
		//		private Excel.Range range;

		public ExcelReader(string fileName, string worksheet) 
		{
			this.FileName = fileName;
			this.conn = GetExcelConnection();
			this.Worksheet = worksheet;
		}

		public ExcelReader(string fileName) : this (fileName, "") 
		{
			this.FileName = fileName;
			this.conn = GetExcelConnection();
		}

		/// <summary>
		/// Creates a new instance of ExcelReader.
		/// </summary>
		public ExcelReader() : this ("", "") {}

		public string FileName 
		{
			get { return fileName; }
			set { fileName = value; }
		}

		public string Worksheet 
		{
			get { return worksheet; }
			set 
			{ 
				worksheet = value; 
				PopulateTable();
			}
		}

		public int Step 
		{
			get { return step; }
			set { step = value; }
		}

		public bool IsDone 
		{
			get { return (current >= dt.Rows.Count) ? true : false; }
		}

		public ExcelRow CurrentItem 
		{
			get { return new ExcelRow(dt.Rows[current]); }
		}

		public OleDbConnection Connection 
		{
			get { return conn; }
		}

		public ExcelRow First() 
		{
			current = 0;
			return new ExcelRow(dt.Rows[current]);
		}

		public ExcelRow Next() 
		{
			current += step;
			if (!IsDone) 
				return new ExcelRow(dt.Rows[current]);
			else return null;
		}

		private OleDbConnection GetExcelConnection() 
		{
			return new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + 
				@"Data Source=" + fileName  + ";" + 
				@"Extended Properties=" + Convert.ToChar(34).ToString() + 
				@"Excel 8.0;" + "HDR=YES" + Convert.ToChar(34).ToString());
		}

		private void PopulateTable() 
		{
			OleDbDataAdapter a = new OleDbDataAdapter();
			a.SelectCommand = new OleDbCommand(@"SELECT * FROM [" + worksheet + "$]", conn);
			a.Fill(dt);
		}
	}
}