Python Forum

Full Version: Python script for excel sheet
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi all,
i'd like to make a script to read esch value in the first column and copy it to the empty cell of the corresponding line as follow:
A1 A2 A3 A4
2 . 6 .
3 5 . 11
6 7 . .
12 . 9 48
20 8 2 .

to be:

A1 A2 A3 A4
2 2 6 2
3 5 3 11
6 7 6 6
12 12 9 48
20 8 2 20
What have you tried so far? We will help but not do it for you.
(May-30-2021, 04:39 PM)jefsummers Wrote: [ -> ]What have you tried so far? We will help but not do it for you.

of course not.
i tried this but it does not work
#! Python 3
import openpyxl
import xlrd
loc = ("/home/dell/Bureau/test_python")
workbook = xlrd.open_workbook('test.xls')
worksheet = workbook.sheet_by_name('Sheet1')
wb_obj = openpyxl.load_workbook(loc)
sheet_obj = wb_obj.active

i = 0
j = 0
cell.value = wb_obj(i,j)
cell.value0 = wb_obj(i,j+1)
while cell.value > 0 :
      if cell.value0 == None:
       cell.value0 = wb_obj(i,j)
i get this error:
Error:
Traceback (most recent call last): File "python_test.py", line 7, in <module> wb_obj = openpyxl.load_workbook(loc) File "/usr/lib/python2.7/dist-packages/openpyxl/reader/excel.py", line 171, in load_workbook archive = _validate_archive(filename) File "/usr/lib/python2.7/dist-packages/openpyxl/reader/excel.py", line 118, in _validate_archive archive = ZipFile(filename, 'r', ZIP_DEFLATED) File "/usr/lib/python2.7/zipfile.py", line 779, in __init__ self.fp = open(file, modeDict[mode]) IOError: [Errno 21] Is a directory: '/home/dell/Bureau/test_python'
In line 7 you try to open the workbook, but you only give the folder name, not the file name. See documentation on the use HERE
I often have to do stuff like this.

Make yourself a function, put it in a module if you do this very often!

import openpyxl

# where is my file?
path = '/home/pedro/summer2021/19BE/'
# which file?
myfile = 'test_copy_column.xlsx'
# open the file
targetFile = openpyxl.load_workbook(path + myfile)
# sheet names are always handy
targetFileSheetNames = targetFile.sheetnames

# get the values in column 1 and write them to column 2
# could also add them or do other tricks
for sheet in targetFileSheetNames:
    # always need the max_row or max_column to do things like this
    maxRow = targetFile[sheet].max_row
    for rowNum in range(1, maxRow + 1):
        content = targetFile[sheet].cell(row=rowNum, column=1).value
        # this will overwrite without warning if column 2 already has content!!
        targetFile[sheet].cell(row=rowNum, column=2, value=content)

# don't forget to save it        
targetFile.save(path + myfile)