Python Openpyxl - Copy values from column A to B with same rows range

Question

I'm iterating through the column A. Inside this column I'm using two functions to unicode values.

# -*- coding: utf-8 -*-

from django.template import defaultfilters
from unidecode import unidecode
import openpyxl

wb = openpyxl.load_workbook('sheet.xlsx', use_iterators=True)
sheet = wb.get_sheet_by_name('Sheet1')

translated = []

for row in sheet.iter_rows('A2:A74'):
   for cell in row:
       if cell.value is not None:
           defaultfilters.slugify(unidecode(cell.value))
           translated.append(defaultfilters.slugify(unidecode(cell.value)))

It all works so far, but now I'd like to paste these "converted" values to column B with the same range B2-B74. Has anyone would help me with this code to solve my problem ? I was "googling" but I didn't find solution so far...

Ogaday · Accepted Answer · 2015-12-10T16:27:00.420

2

Have you tried doing it in a similar way to way you read them?

Edit, couple of fixes:

Edit2, minor improvements (I had some tautological logic):

ie:

import openpyxl

wb = openpyxl.load_workbook('sheet.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

translated = []
for row in sheet.iter_rows('A2:A74'):
    for cell in row:
        translated.append(cell.value)

for row, val in zip(sheet.iter_rows('B2:B74'), translated):
    for cell in row:
        cell.value = val

wb.save("sheet.xlsx")

So, very similar to how you access the cells, and then you need to save the workbook at the end! Does this work for you? Previously you were opening the workbook in read only mode.

edited Dec 10 '15 at 16:27

answered Dec 10 '15 at 16:01

Ogaday

469
3
13

TypeError: 'list' object cannot be interpreted as an index. – Maciej Januszewski Dec 10 '15 at 16:06
Have a look now. Needed to do a bit of testing, but that should work now. – Ogaday Dec 10 '15 at 16:12
After reading and before writing you can manipulate and transform the data however you like. Also, the openpyxl examples are pretty good once you get your head around the structure of excel http://openpyxl.readthedocs.org/en/default/usage.html#write-a-workbook – Ogaday Dec 10 '15 at 16:16
Made a few improvements because some lines of code were redundent. It should still work and is much neater now. You want to append `None`s to translated because that will help you keep the right values on the right row, and as empty cells return `None`, our job is done for us, so we can remove some unnecessary checks. edit: Though I guess you need to handle the `None` when you translate the values. Beware of that. – Ogaday Dec 10 '15 at 16:29
It's worth noting that cells have an `offset()` method which would be useful here. – Charlie Clark Dec 10 '15 at 17:48

Python Openpyxl - Copy values from column A to B with same rows range

1 Answers1