using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ReplaceText
{
    public partial class _Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            //SearchText();
        }

        private void SearchText()
        {
            string File_name = Server.MapPath("~\\Column Mapping.xlsx");
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook oWB;
            Microsoft.Office.Interop.Excel.Worksheet oSheet;
            try
            {
                object missing = System.Reflection.Missing.Value;
                oWB = oXL.Workbooks.Open(File_name, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[1];
                Microsoft.Office.Interop.Excel.Range oRng = GetSpecifiedRange("abc", oSheet);
                if (oRng != null)
                {
                    var a = oRng.Row;
                    var b = oRng.Column;
                }
                else
                {
                    var c = "Text is not found";
                }
                oWB.Close(false, missing, missing);

                oSheet = null;
                oWB = null;
                oXL.Quit();
            }
            catch (Exception ex)
            {

            }
        }
        private Microsoft.Office.Interop.Excel.Range GetSpecifiedRange(string matchStr, Microsoft.Office.Interop.Excel.Worksheet objWs)
        {
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Range currentFind = null;
            Microsoft.Office.Interop.Excel.Range firstFind = null;
            currentFind = objWs.get_Range("A1", "AM100").Find(matchStr, missing,
                           Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
                           Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
                           Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
                           Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
            return currentFind;
        }

        private Microsoft.Office.Interop.Excel.Range GetSpecifiedRange12(Microsoft.Office.Interop.Excel.Worksheet objWs,string inputtxt)
        {
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Range currentFind = null;
            Microsoft.Office.Interop.Excel.Range firstFind = null;
            int countRows = objWs.UsedRange.Rows.Count;
            int countColumns = objWs.UsedRange.Columns.Count;
            //currentFind = objWs.get_Range(countRows, countColumns);
            object[,] data = objWs.Range[objWs.Cells[1, 1], objWs.Cells[countRows, countColumns]].Cells.Value2;
            try
            {

                for (int k = 1; k <= data.GetLength(0); k++)
                {

                    string str = Convert.ToString((data[k, 1]));
                    if (inputtxt.ToLower().Contains(str.ToLower()))
                    {
                        string strout = Convert.ToString((data[k, 2]));
                        if (!String.IsNullOrEmpty(strout))
                        {
                            //inputtxt = inputtxt.Replace(str, strout);
                            string replace = str;
                             string pattern = @"\b" + replace + "\\b";
                            //string pattern = @"\bDWH_ACCOUNT\b";
                            inputtxt = Regex.Replace(inputtxt, pattern, strout);
                        }
                    }

                }
                TextBoxoutput.Text = inputtxt;
            }
            catch (Exception ex)
            {

            }

            return currentFind;
        }

        protected void Buttonrplcvt_Click(object sender, EventArgs e)
        {
            string[] words = TextBoxinput.Text.Split(' ');
            string inputtxt = TextBoxinput.Text;
            List<string> outputword = new List<string>();
            string File_name = Server.MapPath("~\\Column Mapping.xlsx"); ;
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook oWB;
            Microsoft.Office.Interop.Excel.Worksheet oSheet;

            

            try
            {
                object missing = System.Reflection.Missing.Value;
                oWB = oXL.Workbooks.Open(File_name, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[1];
                

                Microsoft.Office.Interop.Excel.Range range = GetSpecifiedRange12(oSheet, inputtxt);


                //foreach (var word in words)
                //{
                //    Microsoft.Office.Interop.Excel.Range oRng = GetSpecifiedRange(word, oSheet);
                //    if (oRng != null)
                //    {
                //        var a = oRng.Row;
                //        var b = oRng.Column;
                //        var cellValue = (string)(oSheet.Cells[a, b+1] as Microsoft.Office.Interop.Excel.Range).Value;
                //        outputword.Add(cellValue);
                //    }
                //    else
                //    {
                //        outputword.Add(word);  //var c = "Text is not found";
                //    }

                //}
                //string combindedString = string.Join(" ", outputword.ToArray());
                //TextBoxoutput.Text = combindedString;
                //oWB.Close(false, missing, missing);

                //oSheet = null;
                //oWB = null;
                //oXL.Quit();
                System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("Excel");
                foreach (System.Diagnostics.Process p in process)
                {
                    if (!string.IsNullOrEmpty(p.ProcessName))
                    {
                        try
                        {
                            p.Kill();
                        }
                        catch { }
                    }
                }
            }
            catch (Exception ex)
            {

            }
        }

        protected void Buttonrplcvt0_Click(object sender, EventArgs e)
        {
            TextBoxinput.Text = null;
            TextBoxoutput.Text = null;
        }
    }
}