How to match exact string value or closest match with excel column cell

Question

I have a code where I enter the IP address and then it will loop and search for the closest match in the excel column for that IP. It just loops every IP, how do I put an argument where it matches that IP?

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\subnets.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;                  

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

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

Rufus L · Accepted Answer · 2017-03-27T17:41:58.123

0

Compare the one you found with the one you're searching for (you can also move the declaration of excelIP out of the loop - you only need to declare it once). I also created a flag in case you need to take some action based on whether or not you found the IP you were looking for after you exit the loop:

bool foundIP = false;
IPAddress excelIP;

for (int i = 0; i < xlWorksheet.Rows.Count; i++)
{
    if (IPAddress.TryParse(xlWorksheet.Cells[i + 1, 1].Value.ToString(), out excelIP))
    {       
        // Compare the IP address we found with the one we're looking for                 
        if (excelIP.Equals(addr))
        {
            foundIP = true;
            break;   // Exit the for loop since we found it
        }                
    }
}  

if (foundIP)
{
    Console.WriteLine("Found the IP address!");
    // If you need to do something with the IP, you can use either excelIP
    // or addr, since they are both the same at this point
}
else
{
    Console.WriteLine("IP address was not found.");
}

edited Mar 27 '17 at 17:41

answered Mar 27 '17 at 17:35

Rufus L

36,127
5
30
43

It's currently just hanging after the first part of the code, where the one you helped with starts. I assume it's a lot of compiling being done? – LeGreen95 Mar 27 '17 at 17:42
On which line is it hanging? – Rufus L Mar 27 '17 at 17:45
When I crash the program, it gives a handling exception at if (IPAddress.TryParse(xlWorksheet.Cells[i + 1, 1].Value.ToString(), out excelIP)) I also need to print out the excel IP, the closest match. – LeGreen95 Mar 27 '17 at 17:47
Well, what is the exception, what does it say? That part is your original code. Also what do you mean by closest match? – Rufus L Mar 27 '17 at 17:52
i have a range of IP subnets, so the closest match to that ip address in the sheet, it will print out that subnet. I know it will be slow as we are dealing with lots of data. I am currently running it again, and I added a print of the excelIP, now it is just hanging. Now it has crashed, can not perform runtime binding on a null reference – LeGreen95 Mar 27 '17 at 17:54
You can print excelIp if you're inside the `TryParse` block (because that is what assigns it a value). See this answer for how to check if an IP address is within a range: http://stackoverflow.com/questions/9622967/how-to-see-if-an-ip-address-belongs-inside-of-a-range-of-ips-using-cidr-notation – Rufus L Mar 27 '17 at 18:05
I am dealing with thousands of subnets. And I have it in a excel doc, because incase the document ever changes the code isn't garbage then. I looked at that possibility, I could do it that way I suppose. Putting the excelIP in the appropriate spot gives me that same error at crashing at this line if (excelIP.Equals(addr)) – LeGreen95 Mar 27 '17 at 18:10

How to match exact string value or closest match with excel column cell

1 Answers1