using System;
using System.Collections;
using System.Reflection;
using System.Windows.Forms;
using Xls = Microsoft.Office.Interop.Excel;

namespace Utility.Graphics
{
	/// <summary>
	/// Excel
	/// </summary>
	public class Excel
	{
		public Excel()
		{		
		}
    	private string bookname="Book1", sheetname="Sheet1", 
			cell="A1", cellend="B2";
		public string NameOfBook
		{
			get{return bookname;}
			set{bookname = value;}
		}
		public string NameOfSheet
		{
			get{return sheetname;}
			set{sheetname = value;}
		}
		public string StartingCell
		{
			get{return cell;}
			set{cell = value;}
		}
		public string EndingCell
		{
			get{return cellend;}
			set{cellend = value;}
		}
		private Xls.Application objApp;
		private Xls.Workbooks objBooks;
		private Xls.Workbook objBook; 
		private Xls.Sheets objSheets;
		private Xls.Worksheet objSheet;
		private void Access(bool writeQ)
		{
			objApp = new Xls.Application();
			objBooks = objApp.Workbooks;
			objBook = objApp.Workbooks.Open
				(System.Windows.Forms.Application.StartupPath + "\\"+bookname+".xls",
				Type.Missing,Type.Missing,Type.Missing,
				Type.Missing,Type.Missing,Type.Missing,
				Type.Missing,Type.Missing,Type.Missing,Type.Missing,
				Type.Missing,Type.Missing,Type.Missing,Type.Missing); 
		
			objSheets = objBook.Worksheets;
			int findsheet = 0;
			for(int k = 0; k < objSheets.Count; k++)
			{
				objSheet = (Xls.Worksheet)objSheets.get_Item(k+1);
				if(objSheet.Name == sheetname) findsheet = k+1;
			}
			if(findsheet==0) 
			{
				if(writeQ)
				{
					objSheet = (Xls.Worksheet)objSheets.Add( Missing.Value,Missing.Value,
						Missing.Value, Missing.Value);
					objSheet.Name = sheetname;
				}
				else
				{
					try{throw new Exception();}
					catch (Exception)
					{
						MessageBox.Show("No sheet! Check the name of sheet.",
							"Invalid method",MessageBoxButtons.OK, MessageBoxIcon.Error );
					}
				}
			}
			else objSheet = (Xls.Worksheet)objSheets.get_Item(findsheet);
		}
		public void Write(double[,] obj)
		{
	    	Access(true);
		
			Xls.Range range;
			range = objSheet.get_Range(cell, Missing.Value);
		    range = range.get_Resize(obj.GetLength(0),obj.GetLength(1));
			range.set_Value(Missing.Value, obj);

			objBook.Save();

//			objApp.Visible = true;
//			objApp.UserControl = true;
			objApp.Quit();
		}
		public void Write(ArrayList[] als)
		{
			Access(true);
			
			Xls.Range range;
			range = objSheet.get_Range(cell, Missing.Value);
			range = range.get_Resize(als[0].Count, als.Length);
			Object[,] obj = new Object[als[0].Count, als.Length];
			for(int i = 0; i<als.Length; i++)
				for(int j = 0; j<als[0].Count; j++)
					obj[j,i] = als[i][j];

			range.set_Value(Missing.Value, obj);

			objBook.Save();

//			objApp.Visible = true;
//			objApp.UserControl = true;
			objApp.Quit();
		}  
		private int row, column;
		public int NumberOfRow
		{
			get{return row;}
		}
		public int NumberOfColumn
		{
			get{return column;}
		}
		public void FindSize()
		{
			Access(false);
	    
			Xls.Range range = objSheet.get_Range(cell,cellend);
			Object[,] x = (Object[,])range.get_Value( Missing.Value );
			row = x.GetLength(0); 
			column = x.GetLength(1); 
			
			objApp.Quit();
		}
		public void Read(double[,] y)
		{
			Access(false);
	
			Xls.Range range = objSheet.get_Range(cell,cellend);
			Object[,] x = (Object[,])range.get_Value( Missing.Value );
			for(int i = 0; i<y.GetLength(0); i++)
				for(int j = 0; j<y.GetLength(1); j++)
					y[i,j] = Convert.ToDouble(x[i+1,j+1]);

//			objApp.Visible = true;
//			objApp.UserControl = true;
			objApp.Quit();
		}
		public void Read(ArrayList[] y)
		{
			Access(false);
	
			Xls.Range range = objSheet.get_Range(cell,cellend);
			Object[,] x = (Object[,])range.get_Value( Missing.Value );
			for(int i = 0; i<y.Length; i++)
				for(int j = 0; j<x.GetLength(0); j++)
					y[i].Add(Convert.ToString(x[j+1,i+1]));

			//			objApp.Visible = true;
			//			objApp.UserControl = true;
			objApp.Quit();
		} 
	}
}