Home Update rows and column using openpyxl from python
Reply: 0

Update rows and column using openpyxl from python

user2708
1#
user2708 Published in May 21, 2018, 2:59 am

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. Exercise Repetition
  2. Running 5 minutes
  3. Walking 10 minutes
  4. Squats 3x12
  5. curls 3x12
  6. .... .....

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:

  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,

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 need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304045 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO