how to open different cell of excel in text box in every click in desktop application

[mayankshri]

Hi i am open a excel file and it automatically open first two cell A1,B2 in two text boxes.
now i want how to open different cells in different click of button.

i am using this code-

OpenFileDialog openexcel = new OpenFileDialog();
                openexcel.Title = "Mayank";
                openexcel.InitialDirectory = @"c:\";
                openexcel.RestoreDirectory = true;
                openexcel.DefaultExt = ".xls";
                openexcel.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*";
                openexcel.FilterIndex = 2;
                if (openexcel.ShowDialog() == DialogResult.OK)
                {
                    Excel.Application xlApp;
                    Excel.Workbook xlWorkBook;
                    Excel.Worksheet xlWorkSheet;
                    object filename = openexcel.FileName;
                    object misValue = System.Reflection.Missing.Value;
                    xlApp = new Excel.ApplicationClass();
                    xlWorkBook = xlApp.Workbooks.Open(filename.ToString(), misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    //xlApp.Visible = true;
                    txtsearchgerman.Text = (xlWorkSheet.get_Range("A2", "A2").Value2.ToString());
                    txtsearchenglish.Text = (xlWorkSheet.get_Range("B2", "B2").Value2.ToString());
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    xlApp = null;

Thanks in Advance.

[thines01]

Is the data going to change while you have the sheet open?

Can you just read all of the data, then move through the collection of data on button click?

[mayankshri]

No data is same.i just want to read the data.
actually i want-
suppose there are two columns in excel sheet german and english so when i click on button i want these two column values(A2) in first text boxs and B2 in second text box. and when i click again in button i want next two values A3 in first text box and B3 in second text box.

[thines01]

I understand.
Read all of the data first and store it in a Dictionary<string, string> and then close the spreadsheet.

Your data will be in RAM and you can present it to the user when a button is clicked (up or down).

[mayankshri]

please check my code is on top ...and give me a reply

[thines01]

I did look at the code that is loading two cells inside of a button push.

I would make a loader like this:

private static Dictionary<string, string> LoadDictionary(string strXlsFileName, string strSheetName, ref string strError)
{
   Dictionary<string, string> map_s2sRetVal = null;

   try
   {
      map_s2sRetVal = new Dictionary<string, string>();
      Application excel = new Application();
      Workbook wb = excel.Workbooks.Open(strXlsFileName);
      Worksheet sheet1 = (Worksheet)wb.Worksheets[strSheetName];

      //encompass the area containing the data (like A1-B10)
      List<string> lst_str = ((Array)sheet1.get_Range("A1", "B10").Value2)
         .OfType<string>().ToList();
            
      for (int i = 0; i < lst_str.Count; i+=2)
      {
         map_s2sRetVal.Add(lst_str[i], lst_str[i + 1]);
      }

      wb.Close(XlSaveAction.xlDoNotSaveChanges, Missing.Value, Missing.Value);
      excel.Quit();
   }
   catch (Exception exc)
   {
      strError = exc.Message;
   }

   return map_s2sRetVal;
}

Then I would load the sheet once like this:

string strError = "";
         Dictionary<string, string> mapLang1 =
            LoadDictionary(@"c:\science\Numbers.xls", "Numbers", ref strError);

         if (null == mapLang1)
         {
            System.Diagnostics.Debug.WriteLine(
               "Could not load spreadsheet: " + strError);
            return;
         }

         Dictionary<string, string> mapLang2 =
            mapLang1.ToDictionary(k => k.Value, v => v.Key);

         //Display
         foreach (KeyValuePair<string, string> kvp in mapLang1)
         {
            System.Diagnostics.Debug.WriteLine(kvp.Key + "->" + kvp.Value);
         }

         foreach (KeyValuePair<string, string> kvp in mapLang2)
         {
            System.Diagnostics.Debug.WriteLine(kvp.Key + "<-" + kvp.Value);
         }

         //TESTING ...

         string strTestVal = "ein";
         string strResponse = string.Empty;

         // Using lang1 as the key
         if (mapLang1.ContainsKey(strTestVal))
         {
            strResponse = mapLang1[strTestVal];
         }

         // or using lang2 as the key
         strTestVal = "one";
         if (mapLang2.ContainsKey(strTestVal))
         {
            strResponse = mapLang2[strTestVal];
         }