0

I am trying to match an inputted ip address with a cell in a spreadsheet in excel. I input the IP, the and a variable searches the excel spreadsheet for the closest match. The issue with my code, is the excelIP variable no matter what only ever stores the first cell value from my spreadsheet so there is never a match.

using System;
using System.Net;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Data;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;

namespace Investigations
{
    class Program
    {


        static void Main(string[] args)
        {



            IPAddress addr = IPAddress.Parse("8.8.8.8");
            IPHostEntry entry = Dns.GetHostEntry(addr);
            Console.WriteLine("IP Address: " + addr);
            Console.WriteLine("Host Name: " + entry.HostName);


            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\Subnets1.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;





            for(int i = 0; i < xlWorksheet.Rows.Count; i++)
            {
                IPAddress excelIP;

                if (IPAddress.TryParse(xlWorksheet.Cells[i + 1, 1].Value.ToString(), out excelIP))
                {
                    if (excelIP.ToString().Equals(addr))
                    {
                        Console.Write(excelIP.ToString());
                        Console.WriteLine(" -This id was found");
                    }

                    else
                    {
                        Console.WriteLine("No Match ");
                        break;
                    }



                }
            }

        }

    }
 }
gahser1
  • 37
  • 7
  • Possible duplicate of [How to check if an IP address is within a particular subnet](http://stackoverflow.com/questions/1499269/how-to-check-if-an-ip-address-is-within-a-particular-subnet) – 3vts Mar 28 '17 at 20:18

1 Answers1

0

You need 2 small changes on your code

  1. Remove the break; on the else. This will cause the program to exit
  2. Change the for statement to use xlRange instead of xlWorksheet otherwise will break as well. The right statement will be as follows:

for (int i = 0; i < xlRange.Rows.Count; i++)

Give it a try and let me know your comments

EDIT: Just noticed you may want to have only 1 result either match or no match so you need to have this:

        var match = false;

        for (int i = 0; i < xlRange.Rows.Count; i++)
        {

            IPAddress excelIP;

            if (IPAddress.TryParse(xlWorksheet.Cells[i + 1, 1].Value.ToString(), out excelIP))
            {
                if (excelIP.ToString().Equals(addr))
                {
                    match = true;
                    Console.Write(excelIP.ToString());
                    Console.WriteLine(" -This id was found");
                }
            }
        }
        if (!match)
        {
            Console.WriteLine("No Match ");
        }

In this case the boolean match is declared as false and changes to true only when a match is found. using that variable we can then write to console only once if the value was found or not

3vts
  • 778
  • 1
  • 12
  • 25
  • for (int i = 0; i < xlRange.Rows.Count; i++) { IPAddress excelIP; if (IPAddress.TryParse(xlRange.Cells[i + 1, 1].Value.ToString(), out excelIP)) That's how I have it now, "no match" prints 100 times to the console. – gahser1 Mar 28 '17 at 18:43
  • @gahser1 Fixed that on the answer. Give i t a try now – 3vts Mar 28 '17 at 18:48
  • I get an unhandled exception at the first if statement. – gahser1 Mar 28 '17 at 18:58
  • Do you have more information about the error? Please check if yout have empty cells in between – 3vts Mar 28 '17 at 19:27
  • Empty cells at the very end of the sheet, is there anyway I can add a screenshot? When I run the loop, it prints every subnet IP in that column until the end than crashes too by the way if that helps. – gahser1 Mar 28 '17 at 19:31
  • The code I posted is working on I just tested it with dummy IPs. Check if the IPs are valid, If still have problems edit your question and include the code you are using right now – 3vts Mar 28 '17 at 19:38
  • It could be because the ip addresses in the sheet are subnet ranges where only the first 2 octets of my ip addresses matter. – gahser1 Mar 28 '17 at 19:41
  • Also just noticed I made a mistake on the answer. What you have to fix is the for statement., Also include this 2 lines at the end of your code. `xlWorkbook.Close();` `xlApp = null;` Otherwise you will have a lot of background Excel processes running – 3vts Mar 28 '17 at 19:41
  • Is there anyway that it can just match the first 2 octets than? That might be the issue.. – gahser1 Mar 28 '17 at 19:47
  • Try changing the `for` statement to this `for (int i = 0; i < xlRange.Rows.Count; i++)` If still have problems let me know – 3vts Mar 28 '17 at 19:49
  • if (IPAddress.TryParse(xlWorksheet.Cells[i + 1, 1].Value.ToString(), out excelIP)) --- It never seems to like that line no matter what I code. I changed the for statement to what you suggested – gahser1 Mar 28 '17 at 19:55
  • Can you send me a sample of one of hose IPs you are using? – 3vts Mar 28 '17 at 19:57
  • 10.2.34.203 the first 2 octets are part of the subnet range that I am after, so I was hoping it should just match. – gahser1 Mar 28 '17 at 19:58
  • Is it stored exactly like that (`10.2.34.203`) on the excel? or you are using `10.2` – 3vts Mar 28 '17 at 20:02
  • Focusing more on getting a match with 10.2 or as deep as 10.2.34. Last octet is kind of a write off. – gahser1 Mar 28 '17 at 20:04
  • Just looked at the sheet, it's stored 10.2.34.0/24 – gahser1 Mar 28 '17 at 20:12
  • Let me tell you your code will never work this way. Please [check this out](http://stackoverflow.com/a/1499350/3571692) it has the answer for the problem you have – 3vts Mar 28 '17 at 20:17
  • So pretty much, I have to translate that code above that you linked to my code? Thank you so much for the assistance – gahser1 Mar 28 '17 at 20:20
  • Yes. you have to link that and perform a check with the method `IsInSameSubnet`. Please be more explicit when asking your questions and if satisfied with the answer please mark it as the solution – 3vts Mar 28 '17 at 20:21
  • Is that for one subnet range? I have a whole lot of them. – gahser1 Mar 28 '17 at 20:26
  • You have to use that method on the second if statement because that one compares 2 IPs and returns a boolean that is true if the 2 IPs are on the same subnet and false if not. Check if you can do it. If not please open another question – 3vts Mar 28 '17 at 20:31