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
#6
https://www.sevenmentor.com/python-classes-in-chennai
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Add a new column when I extract each sheet in an Excel workbook as a new csv file shantanu97 0 612 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 593 Feb-05-2021, 04:37 PM
Last Post: nilamo
  Append excel sheet using openpyxl TammyP 1 606 Feb-02-2021, 06:32 PM
Last Post: nilamo
  How to Copy Single Value From One Excel Sheet to Another SunWers 4 838 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 599 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Writing to existing excel sheet jksvend 0 595 Oct-12-2020, 11:19 AM
Last Post: jksvend
  Create new Excel instead of update certain sheet Pietertl 1 808 Sep-17-2020, 07:04 AM
Last Post: Pietertl
  Edit Open and Active Excel sheet in Python JoeDainton123 1 749 Jul-29-2020, 12:52 AM
Last Post: Larz60+
  copy content of text file with three delimiter into excel sheet vinaykumar 0 633 Jul-12-2020, 01:27 PM
Last Post: vinaykumar
  How to kill a bash script running as root from a python script? jc_lafleur 4 1,649 Jun-26-2020, 10:50 PM
Last Post: jc_lafleur

Forum Jump:

User Panel Messages

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