python openpyxl 返回单元格行列_使用来自python的openpyxl更新行和列

Problem: Add "(C)" or "(S)" in every column or row in an excel file by using openpyxl - python.

Example of a patient record or list of exercises

The list will have dozens if not hundreds of exercises for every (physical therapy) patient.

Exercise Repetition

Running 5 minutes

Walking 10 minutes

Squats 3x12

curls 3x12

.... .....

I want to add an indicator to the exercises:

(C) for Cardio

(S) for Strenght

Note: There will be more indicators (~20). We have thousands of files of patients records that don't have any categorization.

For example, We want to add the (C) for Running:

A B

Exercise Repetition

(C) Running Time minutes

(C) Walking Time minutes

(S) Squats 3x12

(S) curls 3x12

.... .....

NOTE: Due to the table limitation A1 is Exercise B1 is Repetition, A2 is Running and B2 will be Time minutes,

This is how I am setting it up:

Note: I am not allowed to install any package at the work computer. However, I am using openpyxl because it was already installed in the system.

## Load your work book into a global variable

wb = openpyxl.load_workbook('ExcersiceList.xlsx')

## Read the sheets name for your entire workbook

wb.get_sheet_names()

## Create a variable for each sheet in the work book, to manipulate each sheet

sheet1 = wb.get_sheet_by_name('Sheet1')

In theory, I want to do this but for every row and column

## To add the strings to existing values in a cell, use the following

varB2 = sheet1[‘A2’].value ## assign the value of A2 to varA2

sheet1[‘A2’] = ‘(C) ’ + varA2 ## this combines the value of A2 with (U)

sheet1[‘A2’].value ## you will notice a value change

wb.save(‘ExcersiceList.xlsx’)

NOTE: This worked well. However, we want to be able to loop through the entire columns and rows. I acknowledge I need another file or dictionary to mark all the exercises accordingly.

I tried to do a loop at least for the rows:

##loop through sheet1 max row

for row in range(1, sheet1.max_row+1):

st1 = '(c) ' + str(row)

print st1enter code here

wb.save(‘yourFileName.xlsx’)

However st1 = it is only being assigned not written back into the excel file.

I appreciate your time and guidance in advance. Please let me know if you need more information. However, please understand that I can't reveal real patient data.

解决方案

You can use the cell function, see Accessing one cell in the documentation.

For example, assuming you've loaded the sheet into sheet1 and the details are in column one:

for row in range(1, sheet1.max_row+1):

cell = sheet1.cell(row=row, column=1)

if cell.value is not None:

cell.value = "(c) " + cell.value

wb.save('test2.xlsx')