使用python中的openpyxl更新行和列 [英] Update rows and column using openpyxl from python

问题描述

问题:通过使用openpyxl-python在Excel文件的每一列或每一行中添加(C)"或(S)".

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.

1. 运动重复
2. 运行5分钟
3. 步行10分钟
4. 蹲3x12
5. 卷曲3x12
6. ......

我想在练习中添加一个指标: (C)有氧运动 (S)代表力量 注意:会有更多的指示器(〜20).我们有成千上万个没有任何分类的患者记录文件.

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.

例如，我们要添加(C)来运行:

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

1. A B
2. 运动重复
3. (C)运行时间分钟
4. (C)步行时间分钟
5. (S)下蹲3x12
6. (S)卷曲3x12
7. ....... 注意:由于表格限制，A1为练习B1为重复，A2为跑步，B2为时间分钟，

1. A B
2. Exercise Repetition
3. (C) Running Time minutes
4. (C) Walking Time minutes
5. (S) Squats 3x12
6. (S) curls 3x12
7. .... ..... NOTE: Due to the table limitation A1 is Exercise B1 is Repetition, A2 is Running and B2 will be Time minutes,

这是我的设置方式: 注意:不允许在工作计算机上安装任何软件包.但是，我使用的是openpyxl，因为它已经安装在系统中.

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’)

但是st1 =它仅被分配，没有写回到excel文件中.

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.

推荐答案

您可以使用cell函数，请参见访问文档中的一个单元格.

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

例如，假设您已将工作表加载到sheet1中，并且详细信息在第一列中:

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')

这篇关于使用python中的openpyxl更新行和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！