Member Avatar for eagles1

I have an excel spreadsheet that has 2 used columns. Using Python I have to read the data in those to columns. So far I have a code that lists the item in column A and B on Python GUI. Once that is done I have to write code so that it takes that information and writes into Notepad in a certain way.

So far the code i have that read and print the excel information and actually works is:

1. def excel():
2.	from win32com.client import Dispatch     
3.	xlApp = Dispatch ("Excel.Application")   #Calls for Excel
4.     xlWb = xlApp.Workbooks.Open('IT.xls')    #It finds the workbook
5.      xlSht = xlWb.Worksheets (1)   Goes to sheet 1
6.	dataList = []
7.	for row in range (1,198): #It goes through the 198 items in A and B
8.		for col in (1,2):
9.			dataList.append(xlSht.Cells(row,col))
10.	for item in dataList:
11.		print item

I just have a simple code for notepad but i do not know how to integrate them:

1. def notepad():
2. 	 print
3.	 text_file = open("write.txt","w")
4.	 lines = ["Line 1/n",
5.		  "This is line 2"]
6.	 text_file.writelines(lines)
7.	 text_file.close()

The information needs to appear in notepad in this format without the numbers on the left.

{
define host

        use                     generic-AP

        host_name               Router Name

        alias                   Router Name

        address                IP Address

}

Router name and IP Address changes but everything else remains the same. This needs to get repeated for cells. So in essence this needs to get repeated 198 times.

  • 2 Contributors
  • 3 Replies
  • 719 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by baki100

Dani AI

Generated

— good start; was right to suggest passing a list to the writer. The simplest, robust pattern is: (1) read column A/B as (name, ip) pairs (use the cell .Value), (2) normalize/filter rows, then (3) format and write one repeated host block per pair. The example below uses Excel COM (so it runs on Windows with Excel installed), closes Excel cleanly, and shows a reusable writer you can call from your reader.

def extract_name_ip_pairs(xls_path, sheet_index=1):
    from win32com.client import Dispatch
    app = Dispatch("Excel.Application")
    app.Visible = False
    wb = app.Workbooks.Open(xls_path)
    ws = wb.Worksheets(sheet_index)

    pairs = []
    r = 1
    while True:
        a = ws.Cells(r, 1).Value
        b = ws.Cells(r, 2).Value
        if not a and not b:
            break
        if a and b:
            name = str(a).strip()
            ip = str(b).strip()
            pairs.append((name, ip))
        r += 1

    wb.Close(False)
    app.Quit()
    return pairs


def write_host_blocks(pairs, out_path):
    tpl = (
        "{\n"
        "define host\n\n"
        "        use                     generic-AP\n\n"
        "        host_name               {name}\n\n"
        "        alias                   {name}\n\n"
        "        address                 {ip}\n\n"
        "}\n\n"
    )
    with open(out_path, "w", encoding="utf-8") as fh:
        for name, ip in pairs:
            fh.write(tpl.format(name=name, ip=ip))

Troubleshooting notes:

  • Use .Value (or .Value2) to get the real cell contents — appending the cell object will print COM proxies instead of text.
  • If Excel stays running after your script, make sure you call wb.Close(False) and app.Quit() (and delete references if needed).
  • If some IPs come back as numbers (floats like 123.0), convert numeric values to integers before stringifying.
  • If you really need exactly 198 rows, iterate a fixed range instead of the empty-row stop; otherwise the loop above stops at the first blank line.
  • As suggested, keep read and write separate (extract -> write); that makes testing and changes much easier.
Member Avatar for baki100

you could have your def notepad accept a list data type parameter, and the iterate through the list (dataList) and write the information to file. you would basically call the notepad def in the excel def.

Member Avatar for eagles1

you could have your def notepad accept a list data type parameter, and the iterate through the list (dataList) and write the information to file. you would basically call the notepad def in the excel def.

This might sound stupid but is there anyway you could show me an idea. I'm kinda new to Python and I'm struggling.

Member Avatar for baki100 
def notepad(dataList):
    text_file = open("write.txt","w")
    for item in dataList:
        #write the data however you want
        text_file.write('%s\n'%item)
    text_file.close()

def excel():
    from win32com.client import Dispatch     
    xlApp = Dispatch ("Excel.Application")   #Calls for Excel
    xlWb = xlApp.Workbooks.Open('IT.xls')    #It finds the workbook
    xlSht = xlWb.Worksheets (1)   Goes to sheet 1
    dataList = []
    for row in range (1,198): #It goes through the 198 items in A and B
        for col in (1,2):
            dataList.append(xlSht.Cells(row,col))
    notepad(dataList)
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.