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.

Edited 4 Years Ago by mayankshri: n/a

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?

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.

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).

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];
         }
This article has been dead for over six months. Start a new discussion instead.