using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Threading;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace Test
{
      /// <summary>
    /// 09/29/06 - jb
    /// Excel wrapper for file import directly avoiding use of oledb driver which has a bunch of problems.
      /// </summary>
      public class ExcelWrapperService
      {
            private Application _app = null;
            private Workbook _workbook = null;
            private Sheets _sheets = null;
            private Worksheet _worksheet = null;
            private static object _missing      = System.Reflection.Missing.Value;
            private static object _visible      = true;
            private static object _false  = false;
            private static object _true         = true;
            private bool _appVisible = false;
            private object _fileName;

        
        /*********************************************/
            #region Variables for workbook

            private object _updateLinks = 0;
            private object _readOnly = _true;
            private object _format = 1;
            private object _password = _missing;
            private object _writeResPassword = _missing;
            private object _ignoreReadOnlyRecommend = _true;
            private object _origin = _missing;
            private object _delimiter= _missing;
            private object _editable = _false;
            private object _notify = _false;
            private object _converter = _missing;
            private object _addToMRU = _false;
            private object _local = _false;
            private object _corruptLoad = _false;
            
        private object _saveChanges = _false;
            private object _routeWorkbook = _false;
            #endregion


            /// <summary>
            /// Constructor
            /// </summary>
            public ExcelWrapperService()
            {
                  this.startExcel();
            }

            
        /// <summary>
            /// Construct excel object, with visibility
            /// visible is a parameter, true/false
            /// </summary>
            /// <param name="visible">Visible parameter, true for visible, false for non-visible</param>
        public ExcelWrapperService(bool visible)
            {
                  this._appVisible = visible;
                  this.startExcel();
            }

            /// <summary>
            /// Start excel
            /// </summary>
            private void startExcel()
            {
                  if( _app == null )
                  {
                _app = new ApplicationClass();
                  }
                  
                  // Make Excel Visible
            this._app.Visible = _appVisible;
            }


            /// <summary>
            /// Stop Excel
            /// </summary>
            public void StopExcel()
            {
            if (_app != null)
                  {
                        Process[] pProcess; 
                        pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
                        pProcess[0].Kill();
                  }
            }


            /// <summary>
            /// The following function will take in a filename, and a password
            /// associated, if needed, to open the file.
            /// </summary>
            /// <param name="fileName"></param>
            /// <param name="password"></param>
            public void OpenFile(string fileName, string password)
            {
                  _fileName = fileName;

                  if( password.Length > 0 )
                  {
                        _password = password;
                  }

                  try
                  {
                        // Open a workbook in Excel
                        _workbook = _app.Workbooks.Open(fileName, _updateLinks, _readOnly, _format, _password,
                                                _writeResPassword, _ignoreReadOnlyRecommend, _origin,
                                                          _delimiter, _editable, _notify, _converter, _addToMRU,
                                                          _local, _corruptLoad);
                  }
                  catch(Exception e)
                  {
                        this.CloseFile();
                throw e;
                  }
            }


            public void CloseFile()
            {
                  _workbook.Close( _saveChanges, _fileName, _routeWorkbook );
            }

            /// <summary>
            /// Get Excel Sheets
            /// </summary>
            public void GetExcelSheets()
            {
                  if( this._workbook != null )
                  {
                        _sheets = _workbook.Worksheets;
                  }
            }

            /// <summary>
            /// Search for named worksheet, if found return TRUE
            /// </summary>
            /// <returns>bool</returns>
            public bool FindExcelWorksheet(string worksheetName)
            {
                  bool sheetFound = false;
                  if( _sheets != null )
                  {
                        // Step thru the worksheet collection and see if worksheet is
                        // available. If found return true;
                        for( int i = 1; i <= _sheets.Count; i++ )
                        {
                              _worksheet = (Worksheet)_sheets.get_Item((object)i);
                              if( _worksheet.Name.ToLower().Equals(worksheetName.ToLower()))
                              {
                                    _worksheet.Activate();
                                    sheetFound = true;
                        break;
                              }
                        }
                  }
                  return sheetFound;
            }


            /// <summary>
            /// Return content of range from the selected range
            ///     "A1" returns all cells as currently defined
            /// </summary>
            /// <param name="range">Range parameter: Example, GetRange("A1:D10")</param>
            public Array GetRange(string range)
            {
            int rowCount = _worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, _missing).Row;
            int columnCount = _worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, _missing).Column;

            Range workingRangeCells = null;
            if (range.Length == 0)
            {
                workingRangeCells = _worksheet.UsedRange;

            }
            else
            {
                workingRangeCells = _worksheet.get_Range(range, Type.Missing);
            }
            Array array = (System.Array)workingRangeCells.EntireColumn.Formula;
   
                  return array;
            }
      }
}