Python Forum
Python script for excel sheet
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python script for excel sheet
#1
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
Reply
#2
What have you tried so far? We will help but not do it for you.
Reply
#3
(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'
Larz60+ write May-31-2021, 11:00 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Fixed for you this time. Please use bbcode on future posts.
Reply
#4
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
Reply
#5
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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Trying to use 2 values from excel in my script but getting error.. cubangt 3 515 May-11-2022, 07:12 AM
Last Post: normanwolf
  Reading Excel file and use a wildcard in file name and sheet name randolphoralph 6 1,424 Jan-13-2022, 10:20 PM
Last Post: randolphoralph
  Add a new column when I extract each sheet in an Excel workbook as a new csv file shantanu97 0 1,385 Mar-24-2021, 04:56 AM
Last Post: shantanu97
  Python script to summarize excel tables, then output a composite table? i'm a total n surfer349 1 1,298 Feb-05-2021, 04:37 PM
Last Post: nilamo
  Append excel sheet using openpyxl TammyP 1 1,363 Feb-02-2021, 06:32 PM
Last Post: nilamo
  How to Copy Single Value From One Excel Sheet to Another SunWers 4 1,755 Dec-29-2020, 05:39 PM
Last Post: SunWers
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 1,478 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Writing to existing excel sheet jksvend 0 1,232 Oct-12-2020, 11:19 AM
Last Post: jksvend
  Create new Excel instead of update certain sheet Pietertl 1 1,343 Sep-17-2020, 07:04 AM
Last Post: Pietertl
  Edit Open and Active Excel sheet in Python JoeDainton123 1 1,275 Jul-29-2020, 12:52 AM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020