How To Use Python Openpyxl To Freeze Excel Rows Columns And Split Panes

In this article i will tell you how to use python openpyxl library to freeze excel sheet rows and columns, it also tell you how to split excel sheet into multiple panes with examples.

1. How To Freeze Excel Sheet Rows And Columns Use Python Openpyxl.

  1. Load excel file into a openpyxl.Workbook object.
    from openpyxl import Workbook, worksheet
    
    from openpyxl import load_workbook
    
    work_book = load_workbook(excel_file_path, read_only=False)
  2. Get the active excel sheet (worksheet) use Workbook.active attribute.
    work_sheet = work_book.active
  3. Freeze rows and columns by assign the cell name to work_sheet_object‘s freeze_panes attribute. In below example, columns A, B will be frozen, and top 5 rows will be frozen also.
    work_sheet.freeze_panes = 'C6'
  4. Below is the result after you run above code. When you scroll down the excel sheet, the top 5 rows will not scroll, you can always see them. When you scroll to right of the excel sheet, the A, B column will not scroll, you can always see them.
    freeze excel sheet top rows and left columns

2. How To Split Excel Sheet To Multiple Panes Use Python Openpyxl.

  1. Create openpyxl.Workbook object and get required worksheet object as section 1. This time you should import openpyxl.worksheet.views.Pane class.
    from openpyxl.worksheet.views import Pane
    
    from openpyxl import Workbook, worksheet
    
    from openpyxl import load_workbook
    
    work_book = load_workbook(excel_file_path, read_only=False)
        
    work_sheet = work_book.active
  2. Create an instance of openpyxl.worksheet.views.Pane object, specify the split point’s x, y coordinate value. Below example code will split excel sheet to 4 panes. Arguments xSplit, ySplit‘s value are the split point’s x, y coordinate. The activePane specify the active pane should be top or left pane. The state value means the excel sheet is split.
    pane_object = Pane(xSplit=6000, ySplit=3000, activePane='topLeft', state='split')
  3. If you just want to split the excel sheet in horizontal direction, you can use ySplit = None. If you want to split the excel sheet in vertical direction, you can use xSplit = None in above code.
  4. Now you should assign above Pane object to work_sheet.sheet_view.pane attribute.
    work_sheet.sheet_view.pane = pane_object
  5. When you run above source code, you will get below excel sheet, there are 4 panes in it.
    split excel sheet to 4 panes
READ :   How To Introspect ( Look Inside ) Python Objects In Ipython

3. Freeze Excel Sheet Rows And Columns, Split Excel Sheet To Panes Example.

freeze excel sheet rows columns and split excel sheet in python example

The example file name is freeze_split_excel_pane.py. There are 6 methods in it. You can see code comments for detail explanation.

freeze_split_excel_pane.py

'''
Created on 03-Oct-2020

@author: zhaosong
'''

import string

from openpyxl.worksheet.views import Pane

from openpyxl import Workbook, worksheet

from openpyxl import load_workbook

# excel file prefix.
excel_file_prefix = './data/financial_sample'

# source excel file path.
excel_file_path = excel_file_prefix + '.xlsx'

# target excel file path
target_file_path = excel_file_prefix + '_freeze_1.xlsx'


'''
   Freeze excel by excel cell, get the cell object by passed in row and column index.
   
   work_sheet : the excel work sheet object.
   
   cell_row : cell row number.
   
   cell_col : cell column number. 
'''
def freeze_by_cell(work_sheet, cell_row, cell_col):
    
    # get excel cell by specified cell coordinates. 
    top_left_cell = work_sheet.cell(row=cell_row, column=cell_col)
    
    # freeze the rows and columns at top left side of the cell.
    work_sheet.freeze_panes = top_left_cell
    

'''
   Freeze excel pane by cell name, get the cell by cell name, get the cell name by passed in row and column index. 

   work_sheet : the excel work sheet object.
   
   cell_row : cell row number.
   
   cell_col : cell column number. 
'''
def freeze_by_cell_name(work_sheet, cell_row, cell_col):
       
    # get excel cell name by cell row and column index.        
    top_left_cell_name = string.ascii_uppercase[cell_col -1] + str(cell_row)
    
    # freeze the rows and columns at top left side of the cell by name.
    work_sheet.freeze_panes = top_left_cell_name    
    


'''
   Freeze first row ( rows above row 2 ). 
'''    
def freeze_pane_on_top_row():
    
    # load the excel file.
    work_book = load_workbook(excel_file_path, read_only=False)
    
    # get the first work sheet object.
    work_sheet = work_book.active
    
    # freeze the rows top than row 2 and left than column A ( it means freeze the first row).
    work_sheet.freeze_panes = 'A2'      
    
    work_book.save(target_file_path)
    
    print("Freeze excel file has been created.")
    
    
'''
   Freeze first column ( columns at left side of column B). 
'''
def freeze_pane_on_left_column():
    
    work_book = load_workbook(excel_file_path, read_only=False)
    
    # get the first work sheet object.
    work_sheet = work_book.active
    
    # freeze the rows top than row 1 and left than column B ( it means freeze the first column).
    work_sheet.freeze_panes = 'B1'           
    
    work_book.save(target_file_path)
    
    print("Freeze excel file has been created.")
    
'''
   Freeze first row and first column. 
'''
def freeze_pane_on_top_row_and_left_column():
    
    work_book = load_workbook(excel_file_path, read_only=False)
    
    # get the first work sheet object.
    work_sheet = work_book.active
    
    # freeze  columns left than column B and rows above row 2 ( it means freeze row 1 and column 1).
    work_sheet.freeze_panes = 'C6'    
        
    work_book.save(target_file_path)
    
    print("Freeze excel file has been created.")
    
'''
   Split excel sheet to multiple panes.
'''
def split_pane_on_top_row_and_left_column():
    
    work_book = load_workbook(excel_file_path, read_only=False)
    
    # get the first work sheet object.
    work_sheet = work_book.active
    
    
    #work_sheet.sheet_view.pane = Pane(xSplit=2000, ySplit=2000, activePane='topLeft', state='split')
    
    # split the excel sheet to 4 pane, the split point location is (2000, 2000) and the active pane locate at bottom.
    work_sheet.sheet_view.pane = Pane(xSplit=6000, ySplit=None, activePane='bottomRight', state='split')      
    
    work_book.save(target_file_path)
    
    print("Freeze excel file has been created.")

if __name__ == '__main__':
    
    # you can run each function a time to see the effect
    #freeze_pane_on_top_row()
    
    #freeze_pane_on_left_column()
    
    #freeze_pane_on_top_row_and_left_column()
    
    split_pane_on_top_row_and_left_column()

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.