Change language

Python | Adjusting rows and columns of an Excel file using the openpyxl module

Prerequisites: Excel file using openpyxl reading

Set the height and width of the cells:

Sheet row_dimensions objects have row_dimensions and column_dimensions that control the row height and column width.  row_dimensions and column_dimensions are dictionary values; row_dimensions contains RowDimension objects and column_dimensions contains ColumnDimension objects. In row_dimensions, one of the objects can be accessed using the row number (in this case 1 or 2). In column_dimensions, you can access one of the objects using the letter of the column (in this case, A or B).

Code # 1: Program for setting cell sizes.

# importing the openpyxl module

import openpyxl

 
# Calling the Workbook () function from openpyxl
# create a new blank Workbook object

wb = openpyxl.Workbook ()

 
# Get worksheet of active sheet
# from active attribute.

sheet = wb.active

 
# write to the specified cell

sheet.cell (row = 1 , column = 1 ). value = ’ hello ’

  

sheet.cell (row = 2 , column = 2 ). value = ’everyone’

 
# set line height

sheet.row_dimensions [ 1 ]. height = 70

 
# set column width

sheet.column_dimensions [ ’B’ ]. width = 20

 
# save file

wb.save ( ’dimension.xlsx’ )

Exit:

Cell fusion :

A rectangular area of ​​cells can be merged into one cell using the merge_cells () worksheet method. The argument for merge_cells () is one line of the top-left and bottom-right cells of the rectangular region to be merged.

Code # 2: Program to merge cells.

import openpyxl

wb = openpyxl.Workbook ()

sheet = wb.active

  
# merge cell A2 to D4 ie
# A2, B2, C2, D2, A3, B3, C3, D3, A4, B4, C4 and D4.
# A2: D4 & # 39; combines 12 cells into one cell.

sheet.merge_cells ( ’ A2: D4’ )

 

sheet.cell (row = 2 , column = 1 ). value = ’Twelve cells join together.’

  
# merge cells C6 and D6

sheet.merge_cells ( ’C6: D6’ )

  

sheet.cell (row = 6 , column = 6 ). value = ’Two merge cells.’

 

wb.save ( ’ merge.xlsx’ )

Exit:

Dissolving cells:

To remove cells, call the sheet’s unmerge_cells () method.

Code # 3: Program for removing cells.

import openpyxl

wb = openpyxl.load_workbook ( ’merge.xlsx’ )

sheet = wb.active

 
# dissolve cells

sheet.unmerge_cells ( ’A2: D4’ )

 

sheet.unmerge_cells ( ’C6: D6’ )

  

wb.save ( ’merge.xlsx’ )

Exit :

Setting cell font styles :

It is important to customize the font styles in cells, import the Font () function from the openpyxl.styles module.

Code # 4: Program for setting the text font.

import openpyxl

 
# import font function from openpyxl

from openpyxl.styles import Font

 

wb = openpyxl.Workbook ()

sheet = wb.active

 

sheet.cell ( row = 1 , column = 1 ). value = "Ankit Rai"

 
# set cell size to 24

sheet .cell (row = 1 , column = 1 ). font = Font (size = 24 )

 

sheet.cell (row = 2 , column = 2 ). value = " Ankit Rai "

  
# set font style to italic

sheet.cell (row = 2 , column = 2 ). font = Font (size = 24 , italic = True )

 

sheet.cell (row = 3 , column = 3 ). value = "Ankit Rai"

 
# set font style to bold

sheet.cell (row = 3 , column = 3 ). font = Font (size = 24 , bold = True )

 

sheet.cell (row = 4 , column = 4 ). value = "Ankit Rai"

 
# set font name & # 39; Times New Roman & # 39;

sheet.cell (row = 4 , column = 4 ). font = Font (size = 24 , name = ’Times New Roman’ )

  

wb.save ( ’styles.xlsx’ )

Exit:

Shop

Gifts for programmers

Best laptop for Excel

$
Gifts for programmers

Best laptop for Solidworks

$399+
Gifts for programmers

Best laptop for Roblox

$399+
Gifts for programmers

Best laptop for development

$499+
Gifts for programmers

Best laptop for Cricut Maker

$299+
Gifts for programmers

Best laptop for hacking

$890
Gifts for programmers

Best laptop for Machine Learning

$699+
Gifts for programmers

Raspberry Pi robot kit

$150

Latest questions

PythonStackOverflow

Common xlabel/ylabel for matplotlib subplots

1947 answers

PythonStackOverflow

Check if one list is a subset of another in Python

1173 answers

PythonStackOverflow

How to specify multiple return types using type-hints

1002 answers

PythonStackOverflow

Printing words vertically in Python

909 answers

PythonStackOverflow

Python Extract words from a given string

798 answers

PythonStackOverflow

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

606 answers

PythonStackOverflow

Python os.path.join () method

384 answers

PythonStackOverflow

Flake8: Ignore specific warning for entire file

360 answers

News


Wiki

Python | How to copy data from one Excel sheet to another

Common xlabel/ylabel for matplotlib subplots

Check if one list is a subset of another in Python

How to specify multiple return types using type-hints

Printing words vertically in Python

Python Extract words from a given string

Cyclic redundancy check in Python

Finding mean, median, mode in Python without libraries

Python add suffix / add prefix to strings in a list

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

Python - Move item to the end of the list

Python - Print list vertically