+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Python script for excel sheet (/thread-33820.html)
Python script for excel sheet - Nabil - May-30-2021
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
RE: Python script for excel sheet - jefsummers - May-30-2021
What have you tried so far? We will help but not do it for you.
RE: Python script for excel sheet - Nabil - May-31-2021
(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'RE: Python script for excel sheet - jefsummers - May-31-2021
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
RE: Python script for excel sheet - Pedroski55 - Jun-01-2021
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)