CODEWITHSUNDEEP
c#excelipsubnet
LeGreen95
LeGreen95

Reputation: 101

Matching a string value to a value in an imported excel spreadsheet

I am trying to input an IP address that will match with an IP in my excel spreadhseet. It starts crashing in the DataRow row = xlWorksheet.Rows[i]; line. I need it to loop because I am dealing with thousands of ip's and subnets, and it needs to match with the CLOSEST ip. I am thinking it may not be picking up all the cells in the spreadsheet? Because the IP address that gets inputted is a string variable, and when I use a code I commented out to display all columns it gets all the columns.!

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)
        {
            int rCnt = 0;
            int cCnt = 0;
            string str;


            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;
            Excel.Range currentFind = null;
            Excel.Range firstFind = null;
            Excel.XlFindLookIn xlValues;
            Excel.XlLookAt xlPart;

            string columnSubnet = "Network"; // change to the correct header

            Match match = Regex.Match(columnSubnet, @".*[0-3148].*");
            for (int i = 0; i < xlWorksheet.Rows.Count; i++)
            {
                // get the current row
                DataRow row = xlWorksheet.Rows[i];
                // get the ID from the row
                string idValue = row[columnSubnet].ToString();
                // check if the row value is equal to the textbox entry
                bool myMatch = idValue.Equals(addr);
                // if both of the above are true, do this
                if (match.Success && myMatch == true)
                {
                    Console.Write(idValue);
                    Console.WriteLine(" -This id was found");
                }

Upvotes: 2

Views: 91

Answers (1)

Joshua Hysong
Joshua Hysong

Reputation: 1072

There's a couple issues in your code in regards to object types.

First off the variable addr is of type IPAddress but later you are trying to compare the value to a string. A string object will never equal an IPAddress object. You need to parse the incoming ip string into an IPAddress object itself also like so:

IPAddress idValue = IPAddress.Parse(row[columnSubnet].ToString());

Secondly, in regards to your stated issue, xlWorksheet.Rows[i] gives you a Range object, not a DataRow object. Your code does not show that datarow being saved anywhere so I assume you are just using it to get your value. You can pull the needed value directly this way without having to check the regex either: (This assumes you know the column index)

for (int i = 0; i < xlWorksheet.Rows.Count; i++)
{   
    IPAddress excelIP;
    if (IPAddress.TryParse(xlWorksheet.Cells[i + 1, <ip column index>].Value.ToString(), out excelIP)) 
    {
        Console.Write(excelIP.toString());
        Console.WriteLine(" -This id was found");
    }
}

Upvotes: 1

Related Questions