In this article I will tell you how to use the python openpyxl library to freeze excel sheet rows and columns, it also tells you how to split excel sheets into multiple panes with examples.
1. How To Freeze Excel Sheet Rows And Columns Use Python Openpyxl.
- Load the excel file into an openpyxl.Workbook object.
from openpyxl import Workbook, worksheet from openpyxl import load_workbook work_book = load_workbook(excel_file_path, read_only=False)
- Get the active excel sheet (worksheet) use Workbook.active attribute.
work_sheet = work_book.active
- Freeze rows and columns by assign the cell name to work_sheet_object‘s freeze_panes attribute. In the below example, columns A, B will be frozen, and the top 5 rows will be frozen also.
work_sheet.freeze_panes = 'C6'
- After you run the 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 the right of the excel sheet, the A, B columns will not scroll, you can always see them.
2. How To Split Excel Sheet To Multiple Panes Use Python Openpyxl.
- 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 - Create an instance of openpyxl.worksheet.views.Pane object, specify the split point’s x, y coordinate value. Below example code will split the excel sheet into 4 panes. Arguments xSplit, ySplit‘s value are the split point’s x, y coordinate. The activePane specifies the active pane should be the topLeft pane. The state value means the excel sheet is split.
pane_object = Pane(xSplit=6000, ySplit=3000, activePane='topLeft', state='split')
- If you just want to split the excel sheet in the horizontal direction, you can set ySplit = None. If you want to split the excel sheet in the vertical direction, you can set xSplit = None in the above code.
- Now you should assign the above Pane object to work_sheet.sheet_view.pane attribute.
work_sheet.sheet_view.pane = pane_object
- When you run the above example source code, you will get an excel sheet that has 4 panes in it.
3. Freeze Excel Sheet Rows And Columns, Split Excel Sheet To Panes Example.
- The example file name is freeze_split_excel_pane.py.
- There are 6 methods in the example python file, each method demos for one excel freeze function, all the method names are very straightforward. You can see code comments for detailed explanations.
- freeze_split_excel_pane.py
''' @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 bypassed in row and column index. work_sheet: the excel worksheet 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 bypassed in row and column index. work_sheet: the excel worksheet 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 worksheet object. work_sheet = work_book.active # freeze the rows above row 2 and after column A ( it means to freeze the first row). work_sheet.freeze_panes = 'A2' work_book.save(target_file_path) print("Freeze excel file has been created.") ''' Freeze the first column ( columns at the left side of column B). ''' def freeze_pane_on_left_column(): work_book = load_workbook(excel_file_path, read_only=False) # get the first worksheet object. work_sheet = work_book.active # freeze the rows above row 1 and left besides column B ( it means to 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 worksheet 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 worksheet object. work_sheet = work_book.active #work_sheet.sheet_view.pane = Pane(xSplit=2000, ySplit=2000, activePane='topLeft', state='split') # split the excel sheet into 4 panes, the split point location is (2000, 2000) and the active pane locates at the 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()
