Member Avatar for 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.

Edited by mayankshri because: n/a
  • 2 Contributors
  • 5 Replies
  • 212 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by thines01

Dani AI

Generated

Quick, practical plan: open the workbook once, load the A/B pairs into an ordered in-memory list, keep a class-level index, and on each button click show the next list item. is right to suggest "load once and close" — that avoids keeping Excel running — but a Dictionary doesn't guarantee preserving the original row order, so use a List or DataTable if you need sequential navigation.

Load the rows robustly by detecting the last-used row, reading the Range into an object[,] (Value2) and converting each cell with Convert.ToString(...) to avoid nulls and numeric/date surprises. Release COM objects when done so Excel.exe does not stay in memory. Example load pattern (trimmed and adapted):

List<(string german, string english)> rows = new List<(string, string)>();
int currentIndex = -1;

void LoadSheet(string filePath)
{
    var app = new Excel.Application();
    var wb = app.Workbooks.Open(filePath);
    var ws = (Excel.Worksheet)wb.Worksheets[1];

    int startRow = 2;
    int lastRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1;
    object[,] values = (object[,])ws.Range["A" + startRow, "B" + lastRow].Value2;

    for (int r = 1; r <= values.GetLength(0); r++)
    {
        string a = values[r, 1] != null ? Convert.ToString(values[r, 1]) : string.Empty;
        string b = values[r, 2] != null ? Convert.ToString(values[r, 2]) : string.Empty;
        rows.Add((a, b));
    }

    wb.Close(false);
    Marshal.ReleaseComObject(ws);
    Marshal.ReleaseComObject(wb);
    app.Quit();
    Marshal.ReleaseComObject(app);
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

On the button click simply advance the index and write the two strings to the text boxes:

void btnNext_Click(object sender, EventArgs e)
{
    if (rows.Count == 0) return;
    currentIndex = (currentIndex + 1) % rows.Count; // or stop at end
    var p = rows[currentIndex];
    txtsearchgerman.Text = p.german;
    txtsearchenglish.Text = p.english;
}

Notes and troubleshooting: call Marshal.ReleaseComObject for Range/Worksheet/Workbook/Application and run GC.Collect/WaitForPendingFinalizers to avoid orphaned Excel processes. If Excel is not required on the target machine, consider using a library like ClosedXML/EPPlus or an OLEDB read to avoid COM interop. For large sheets load in a background thread and marshal results to the UI to keep the app responsive.

Recommended Answers

All 5 Replies

Member Avatar for 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?

Member Avatar for 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.

Member Avatar for 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).

Member Avatar for mayankshri

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

Member Avatar for 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];
         }
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.