manipulating excel spreadsheets with python

Question

I am new to Python especially when it comes to using it with Excel. I need to write code to search for the string “Mac”, “Asus”, “AlienWare”, “Sony”, or “Gigabit” within a longer string for each cell in column A. Depending on which of these strings it finds within the entire entry in column A’s cell, it should write one of these 5 strings to the corresponding row in column C’s cell. Else if it doesn’t find any of the five, it would write “Other” to the corresponding row in column C. For example, if Column A2’s cell contained the string “ProLiant Asus DL980 G7, the correct code would write “Asus” to column C2’s cell. It should do this for every single cell in column A, writing the appropriate string to the corresponding cell in column C. Every cell in column A will have one of the five strings Mac, Asus, AlienWare, Sony, or Gigabit within it. If it doesn’t contain one of those strings, I want the corresponding cell in column 3 to have the string “Other” written to it. So far, this is the code that I have (not much at all):

import openpyxl
wb = openpyxl.load_workbook(path)
sheet = wb.active
    for i in range (sheet.max_row):
    cell1 = sheet.cell (row = i, column = 1)
    cell2 = sheet.cell (row = I, column = 3)

# missing code here

wb.save(path)

Are you just asking for the code to achieve this? Or did you have a specific question? — ss7
– ss7, Commented Jun 11, 2018 at 21:46
I have figured out a different way to do this without using the helpful ideas below (to fit my specific need), but am having one specific issue. I can get my code to write to the excel spreadsheet but only if I designate a specific row (thus just filling in one cell). If I use row = i (trying to iterate through all the rows), I get an error saying "ValueError: Row or column values must be at least 1" but obviously I'm trying to fill in the entire third column and leave the code open to adjustments in the number of rows. I am probably making a simple but ignorant mistake. — Royale_w_cheese
– Royale_w_cheese, Commented Jun 12, 2018 at 14:07
ok i figured it out guys. thanks for your help. I simply needed to change "i" to "i+1" — Royale_w_cheese
– Royale_w_cheese, Commented Jun 12, 2018 at 15:24

Trevor K · Accepted Answer · 2018-06-11 21:50:00Z

1

You haven't tried writing any code to solve the problem. You might want to first get openpyxl to write to the excel workbook and verify that is working - even if it's dummy data. This page looks helpful - here

Once that is working all you'd need is a simple function that takes in a string as an argument.

def get_column_c_value(string_from_column_a):
     if "Lenovo" in string_from_column_a:
          return "Lenovo"
     else if "HP" in string_from_column_a:
          return "HP"
     # strings you need to check for here in the same format as above
     else return "other"

Try out those and if you have any issues let me know where you're getting stuck.

edited Jun 11, 2018 at 21:50

answered Jun 11, 2018 at 21:41

Trevor K

1235 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Trevor K Over a year ago

Of course! I'm happy to help if you run into issues just comment here!

mkamerath · Accepted Answer · 2018-06-11 21:36:00Z

1

I have not worked much with openpyxl, but it sounds like you are trying to do a simple string search.

You can access individual cells by using

cell1.internal_value

Then, your if/else statement would look something like

if "HP" in str(cell1.internal_value):

Data can be assigned directly to a cell so you could have

ws['C' + str(i)] = "HP"

You could do this for all of the data in your cells

answered Jun 11, 2018 at 21:36

mkamerath

3422 silver badges13 bronze badges

1 Comment

Royale_w_cheese Over a year ago

Thanks I'll give that a try!

Collectives™ on Stack Overflow

manipulating excel spreadsheets with python

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related