none
Calculating distance using values from Excel file - C#

    Question

  • Hello! I have a small problem and I don't know where is the error in my code.

    private void CalculateDistance()
            {
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlW = xlApp.Workbooks.Open(@"C:\Users\Daniel\Documents\Visual Studio 2017\Projects\WindowsFormsApp1\WindowsFormsApp1\Matrice.xlsx");
                Excel.Worksheet sheet = xlW.Worksheets[1];
                Excel.Range xlR = sheet.UsedRange;
    
                string loc1 = null, loc2 = null, loc3 = null, loc4 = null;
                int index1 = 0, index2 = 0, index3 = 0, index4 = 0;
                int distance = 0;
    
                for(int i = 3; i <= xlR.Rows.Count; i++)
                {
                    var cell = (Excel.Range)sheet.Cells[i, 2];
                    if(cell.Value2 != null)
                    {
                        if(cell.Value2 == comboBox2.SelectedItem)
                        {
                            loc1 = comboBox2.SelectedItem.ToString();
                            index1 = i;
                        }
                    }
                }
    
                for (int i = 3; i <= xlR.Rows.Count; i++)
                {
                    var cell = (Excel.Range)sheet.Cells[i, 2];
                    if (cell.Value2 != null)
                    {
                        if (cell.Value2 == comboBox3.SelectedItem)
                        {
                            loc2 = comboBox3.SelectedItem.ToString();
                            index2 = i;
                        }
                    }
                }
    
                for (int i = 3; i <= xlR.Rows.Count; i++)
                {
                    var cell = (Excel.Range)sheet.Cells[i, 2];
                    if (cell.Value2 != null)
                    {
                        if (cell.Value2 == comboBox4.SelectedItem)
                        {
                            loc3 = comboBox4.SelectedItem.ToString();
                            index3 = i;
                        }
                    }
                }
    
                for (int i = 3; i <= xlR.Rows.Count; i++)
                {
                    var cell = (Excel.Range)sheet.Cells[i, 2];
                    if (cell.Value2 != null)
                    {
                        if (cell.Value2 == comboBox5.SelectedItem)
                        {
                            loc4 = comboBox5.SelectedItem.ToString();
                            index4 = i;
                        }
                    }
                }
    
                if(index1 != 0 && index2 != 0 && index3 != 0&& index4 != 0)
                {
                    var cell = (Excel.Range)sheet.Cells[index1, index2];
                    if(cell.Value2 != null)
                    {
                        distance += (int)cell.Value2;
                    }
    
                    var cell1 = (Excel.Range)sheet.Cells[index2, index3];
                    if(cell1.Value2 != null)
                    {
                        distance += (int)cell1.Value2;
                    }
    
                    var cell2 = (Excel.Range)sheet.Cells[index3, index4];
                    if(cell2.Value2 != null)
                    {
                        distance += (int)cell2.Value2;
                    }
    
                    var cell3 = (Excel.Range)sheet.Cells[index4, index1];
                    if(cell3.Value2 != null)
                    {
                        distance += (int)cell3.Value2;
                    }
                }
                
                xlApp.DisplayAlerts = false;
    
                GC.Collect();
                GC.WaitForPendingFinalizers();
    
                Marshal.ReleaseComObject(xlR);
                Marshal.ReleaseComObject(sheet);
    
                xlW.Close(false);
                Marshal.ReleaseComObject(xlW);
    
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
    
                Process[] xlP = Process.GetProcessesByName("Excel");
                foreach (Process p in xlP)
                {
                    p.Kill();
                }
    
                richTextBox1.Text = distance.ToString();
           }

    Every time I execute it, it inserts into the RichTextBox "0" when it's supposed to insert the sum of 4 different values from an Excel WorkSheet. Can someone help me, please?
    Friday, April 7, 2017 3:54 PM

All replies

  • Nevermind, it returned 0 just because I used comboBox.SelectedItem instead of comboBox.SelectedItem.ToString(), haha :))
    Sunday, April 9, 2017 10:05 AM
  • Hi DannyDSB Official,

    If your issue is solved please Mark as answer or Vote as helpful post to the appropriate answer so that it will help other members to find solution if they faces similar issue.

    Your understanding and cooperation will be grateful.

    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Sunday, April 9, 2017 2:34 PM
    Moderator