using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace ExcelManager
{
    public class ExcelFile
    {
        string fileName;

        Excel.Application xlApp;
        Excel.Workbook workBook;

        Dictionary<string, Excel.Worksheet> xlSheets;
        Excel.Worksheet currentWorksheet;
        Excel.Range currentWorksheetCells;
        bool isWorksheetOpen = false;
        bool isOpen = false;



        public ExcelFile(string fullName)
        {
            this.fileName = fullName;            

        }

        public bool Open(bool isRunningInBackground)
        {
            if (!isOpen)
            {
                try
                {
                    if (xlApp == null)
                    {
                        xlApp = new Excel.ApplicationClass();
                        xlApp.Visible = isRunningInBackground;
                    }
                    
                    workBook = xlApp.Workbooks.Open(fileName);
                    GetWorksheets();
                    isOpen = true;
                }
                catch (Exception ex)
                {
                    DisposeComObjects();

                    throw new Exception("Count not open Excel File : " + ex.Source + " : " + ex.Message);
                }
            }
            else
            {
                return false;
            }
            return true;
        }

        public int GetNumberOfWorksheets()
        {
            return xlSheets.Count;
        }

        public List<string> ListWorksheets()
        {
            List<string> result = new List<string>();

            if (isWorksheetOpen)
            {
                for (int i = 0; i < xlSheets.Count; i++)
                {
                    result.Add(xlSheets.ElementAt(i).Key);
                }
            }

            return result;
        }

        public void SelectWorksheet(string sheetName)
        {
            if (!isWorksheetOpen)
            {
                currentWorksheet = xlSheets[sheetName];
                currentWorksheetCells = GetCurrentWorksheetCells();
                isWorksheetOpen = true;
            }
            else
            {
                //ReleaseInteropObject(currentWorksheetCells);
                //ReleaseInteropObject(currentWorksheet);
                currentWorksheet = xlSheets[sheetName];
                currentWorksheetCells = GetCurrentWorksheetCells();
            }
        }

        public bool FindAndReplaceMarkers(Dictionary<string, string> data)
        {
            if (isWorksheetOpen)
            {
                Excel.Range cells = currentWorksheetCells;
                Excel.Range found = null;
                KeyValuePair<string, string> keyval;
                
                for(int i = 0;i<data.Count;i++)
                {
                    keyval = data.ElementAt(i);
                    found = cells.Find(keyval.Key);

                    if (found != null)
                    {
                        found.Value = found.Value.ToString().Replace(keyval.Key, keyval.Value);
                    }
                }

                //ReleaseInteropObject(found);
                
            }            
            
            return false;
        }

        public bool FindMarkedCell(out int rowIndex, out int columnIndex, string marker)
        {
            rowIndex = -1;
            columnIndex = -1;

            if (isWorksheetOpen)
            {
                Excel.Range foundCell;
                foundCell = currentWorksheetCells.Find(marker);

                if (foundCell != null)
                {
                    rowIndex = foundCell.Row;
                    columnIndex = foundCell.Column;
                    //ReleaseInteropObject(foundCell);
                    return true;
                }
                else
                {
                    //ReleaseInteropObject(foundCell);
                    return false;
                }
            }
            else
            {
                return false;
            }


            return false;
        }

        public bool FillDataInColumn(int startRow, int startCol, List<string> data)
        {
            int row, col;
            row = startRow;
            col = startCol;

            Excel.Range currentCell = null;

            try
            {
                for (int i = 0; i < data.Count; i++)
                {
                    currentCell = (Excel.Range)currentWorksheet.Cells[row + i, col];
                    currentCell.Value = data[i];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error writing values to excel sheet :" + currentWorksheet.Name + " ERROR : " + ex.Message);
            }

            //ReleaseInteropObject(currentCell);

            return true;
        }

        public bool SaveFile()
        {
            try
            {
                workBook.Save();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return true;
        }

        public void CloseFile()
        {
            DisposeComObjects();
        }
        
        private void DisposeComObjects()
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();

            if (currentWorksheetCells != null)
                ReleaseInteropObject(currentWorksheetCells);            

            if (currentWorksheet != null)
                ReleaseInteropObject(currentWorksheet);

            for (int i = 0; i < xlSheets.Count; i++)
                ReleaseInteropObject(xlSheets.ElementAt(i).Value);

            if (workBook != null)
                workBook.Close();
            ReleaseInteropObject(workBook);

            if (xlApp != null)
                xlApp.Quit();
            ReleaseInteropObject(xlApp);

            try
            {
                Process proc;

                proc = Process.GetProcessesByName("EXCEL")[0];

                proc.Kill();
            }
            catch (Exception ex)
            {
                // Write to log file....
            }

        }

        private void GetWorksheets()
        {
            Excel.Worksheet sheet = null;

            xlSheets = new Dictionary<string, Excel.Worksheet>();

            for (int i = 1; i <= workBook.Sheets.Count; i++)
            {
                sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                xlSheets.Add(sheet.Name, sheet);
            }

            //ReleaseInteropObject(sheet);

        }

        private Excel.Range GetCurrentWorksheetCells()
        {
            return currentWorksheet.Cells;
        }

        private void ReleaseInteropObject(object target)
        {
            try
            {
                if (target != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(target);
                    target = null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error releasing excel application resources. Error : " + ex.Message);
            }
            finally
            {
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
        }

        public bool PrintFile(bool showPreview, int copies)
        {
            try
            {
                currentWorksheet.PrintOutEx(1, 1, copies, showPreview, Type.Missing, false, true, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            {
                throw new Exception("Error Printing file...." + ex.Message);
            }
            return true;
        }

        public bool PrintWorkSheet(string sheetname,bool showPreview, int copies)
        {
            Excel.Worksheet sheet = xlSheets[sheetname];

            try
            {
                sheet.PrintOutEx(1, 1, copies, showPreview, Type.Missing, false, true, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            {
                ReleaseInteropObject(sheet);
                throw new Exception("Error Printing file...." + ex.Message);
            }

            //ReleaseInteropObject(sheet);
            return true;
        }

        #region TO BE IMPLEMENTED

        /*
     

        public bool WriteDataByCellTag(Dictionary<string, List<string>> data)
        {
            return false;
        }

        public bool WriteDataByRowTag(Dictionary<string, List<string>> data)
        {
            return false;
        }

        public void AddNewSheet(string sheetName)
        {

        }
        */
        #endregion
    }
}