CODEWITHSUNDEEP
pythonexcelstringpandasxlwings
ARAVINTHKUMAR J
ARAVINTHKUMAR J

Reputation: 69

Using xlwings to open excel sheet. Need to search a string and print full line

I'm using xlwings to open excel sheet. Need to search a string in a specific column and print full line of the string without search item and until new line(\n). Output should be in new column of same sheet.

Input:

enter image description here

search string: [game] Output:

enter image description here

Upvotes: 0

Views: 4519

Answers (1)

Jasper Nichol M Fabella
Jasper Nichol M Fabella

Reputation: 460

import xlwings as xw

open excel file using xlwings

filename = r'input.xlsx'
book = xw.Book(filename)
sheet = book.sheets[0]

find the last row of the sheet on a specific range in this case from column 'A'

lrow = sheet.range('A' + str(sheet.cells.last_cell.row)).end('up').row

declare a separate variable for the string that you will search and the column where your output will be located.

search_string = '[game]'
sheet.range('B1').value = 'output'
output_index = 2

now loop through that range to see if your search_string is in that range

for i in range(1, lrow + 1):
    if search_string in str(sheet.range('A{}'.format(i)).value):
        temp = str(sheet.range('A{}'.format(i)).value)
        temp = temp.split(search_string)[1]
        if '[' in temp:
            temp = temp.split('[')[0]
        sheet.range('B{}'.format(output_index)).value = temp
        output_index += 1


book.save()
book.close()

Below is the full code >>

import xlwings as xw
filename = r'input.xlsx'

book = xw.Book(filename)
sheet = book.sheets[0]
lrow = sheet.range('A' + str(sheet.cells.last_cell.row)).end('up').row
search_string = '[game]'
sheet.range('B1').value = 'output'
output_index = 2

for i in range(1, lrow + 1):
    if search_string in str(sheet.range('A{}'.format(i)).value):
        temp = str(sheet.range('A{}'.format(i)).value)
        temp = temp.split(search_string)[1]
        if '[' in temp:
            temp = temp.split('[')[0]
        sheet.range('B{}'.format(output_index)).value = temp
        output_index += 1


book.save()
book.close()

Upvotes: 3

Related Questions