Python Forum
Find last filled column in openpyxl
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Find last filled column in openpyxl
#1
I work with excel file and know filename and sheet name of this file. How i can search last filled column and insert my data in next column of this file? I work with openpyxl library.
Reply
#2
This will illustrate the basic concept.
from openpyxl import load_workbook

reference_letters = [chr (x) for x in range (65, 91)]
workbook_name = 'sample.xlsx'
workbook = load_workbook (filename = workbook_name)
sheet = workbook.active

for letter in reference_letters :
	cell = letter + '1'
	if sheet [cell].value == None :
		sheet [cell] = 'New Data'
		break

workbook.save (filename = 'new_' + workbook_name)
Irv1n likes this post
Reply
#3
These 2 give you the maximum column and row:

maxRowSourceFile = sourceFile[sheet].max_row
maxColSourceFile = sourceFile[sheet].max_column
This will copy the last column and put it in the column after that.

def myApp():
    import openpyxl

    # the folder with the XL file
    folder = 'temp'
    # the course
    course = '20BE'
    pathToTT = f'/home/pedro/{folder}/{course}/'
    tt = f'{folder}_{course}latestPyMade_newest.xlsx'
    sourceFile = openpyxl.load_workbook(pathToTT + tt)
    sourceSheetNames = sourceFile.sheetnames
    for sheet in sourceSheetNames:       
        maxRowSourceFile = sourceFile[sheet].max_row
        maxColSourceFile = sourceFile[sheet].max_column
        print('Maximum row for sheet', sheet, 'is', maxRowSourceFile)
        print('Maximum column for sheet', sheet, 'is', maxColSourceFile)
        
    for sheet in sourceSheetNames:       
        maxRowSourceFile = sourceFile[sheet].max_row
        maxColSourceFile = sourceFile[sheet].max_column
        for rowNum in range(1, maxRowSourceFile + 1): # +1 to get the last row
            # get the value in the last column
            last_col_value = sourceFile[sheet].cell(row=rowNum, column=maxColSourceFile).value
            # write the value of the last column to the next column
            # the values will be written without any kind of formatting
            sourceFile[sheet].cell(row=rowNum, column=maxColSourceFile+1, value=last_col_value)

    # openpyxl does not have a file.close() open files remain open until your program is finished
    savename = 'changed_sourceFile.xlsx'
    sourceFile.save(pathToTT + savename)
    print('Last column copied to the next column up and saved to', pathToTT + savename)
    print('All done!')
For formatting from openpyxl look at:

from openpyxl.styles import PatternFill
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment  
from openpyxl.drawing.image import Image
BashBedlam and Irv1n like this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Find a string from a column of one table in another table visedwings049 8 1,085 Sep-07-2023, 03:22 PM
Last Post: deanhystad
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,574 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  Making a hotel filled with people categorizing them lucasrohr 3 1,412 Jan-09-2022, 05:13 PM
Last Post: lucasrohr
  pandas pivot table: How to find count for each group in Index and Column JaneTan 0 3,229 Oct-23-2021, 04:35 AM
Last Post: JaneTan
  OpenPyxl: How to iterate through each Column (in 1 row) to find a value? chatguy 2 17,844 Apr-06-2021, 04:52 AM
Last Post: carlhyde
  How can I iterate through all cells in a column (with merge cells) with openpyxl? aquerci 1 7,449 Feb-11-2021, 09:31 PM
Last Post: nilamo
  Openpyxl tkinter search a value in Excel column Heathcliff_1 0 3,212 Dec-02-2020, 04:35 PM
Last Post: Heathcliff_1
  Python Openpyxl is unable to check from Column 6 onwards Skye 0 1,685 Oct-13-2020, 06:11 AM
Last Post: Skye
  Using OpenPyXL How To Read Entire Column Into Dictionary jo15765 1 2,644 Jun-08-2020, 04:10 AM
Last Post: buran
  Need to copy column of cell values from one workbook to another with openpyxl curranjohn46 3 11,086 Oct-12-2019, 10:57 PM
Last Post: curranjohn46

Forum Jump:

User Panel Messages

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