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
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,693 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
  Is there a *.bat DOS batch script to *.py Python Script converter? pstein 3 3,000 Jun-29-2023, 11:57 AM
Last Post: gologica
  how to read txt file, and write into excel with multiply sheet jacklee26 14 9,505 Jan-21-2023, 06:57 AM
Last Post: jacklee26
  Trying to use 2 values from excel in my script but getting error.. cubangt 3 1,624 May-11-2022, 07:12 AM
Last Post: normanwolf
  Reading Excel file and use a wildcard in file name and sheet name randolphoralph 6 6,852 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 2,190 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 2,289 Feb-05-2021, 04:37 PM
Last Post: nilamo
  Append excel sheet using openpyxl TammyP 1 2,312 Feb-02-2021, 06:32 PM
Last Post: nilamo
  How to Copy Single Value From One Excel Sheet to Another SunWers 4 4,197 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 2,625 Dec-16-2020, 05:26 AM
Last Post: Vokofe

Forum Jump:

User Panel Messages

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