Adjusting Rows and Column
Worksheet objects have row_dimensions and column_dimensions attributes that control row heights and column widths. A sheet’s row_dimensions and column_dimensions are dictionary-like values; row_dimensions contains RowDimension objects and column_dimensions contains ColumnDimension objects. In row_dimensions, one can access one of the objects using the number of the row (in this case, 1 or 2). In column_dimensions, one can access one of the objects using the letter of the column (in this case, A or B).
Example:
In this example, the openpyxl module is used to create a new Excel workbook and set values in specific cells. The content “hello” is placed in cell A1, and “everyone” is placed in cell B2. Additionally, the height of the first row is set to 70 units, and the width of column B is set to 20 units.
Python3
# import openpyxl module import openpyxl wb = openpyxl.Workbook() sheet = wb.active # writing to the specified cell sheet.cell(row=1, column=1).value = ' hello ' sheet.cell(row=2, column=2).value = ' everyone ' # set the height of the row sheet.row_dimensions[1].height = 70 # set the width of the column sheet.column_dimensions['B'].width = 20 # save the file wb.save('sample.xlsx') |
Output:
Merging Cells
A rectangular area of cells can be merged into a single cell with the merge_cells() sheet method. The argument to merge_cells() is a single string of the top-left and bottom-right cells of the rectangular area to be merged.
Example:
In this example, the openpyxl module is employed to create a new Excel workbook. The program merges cells A2 to D4, creating a single cell spanning multiple columns and rows, and sets its value to ‘Twelve cells join together.’ Additionally, cells C6 and D6 are merged, and the text ‘Two merge cells.’ is placed in the resulting merged cell.
Python3
import openpyxl wb = openpyxl.Workbook() sheet = wb.active sheet.merge_cells('A2:D4') sheet.cell(row=2, column=1).value = 'Twelve cells join together.' # merge cell C6 and D6 sheet.merge_cells('C6:D6') sheet.cell(row=6, column=6).value = 'Two merge cells.' wb.save('sample.xlsx') |
Output:
Unmerging Cells
To unmerge cells, call the unmerge_cells() sheet method.
Example:
In this example, the openpyxl module is used to load an existing Excel workbook (“sample.xlsx”). The program then unmerges previously merged cells, specifically cells A2 to D4 and cells C6 to D6.
Python3
import openpyxl wb = openpyxl.load_workbook('sample.xlsx') sheet = wb.active # unmerge the cells sheet.unmerge_cells('A2:D4') sheet.unmerge_cells('C6:D6') wb.save('sample.xlsx') |
Output:
Setting Font Style
To customize font styles in cells, important, import the Font() function from the openpyxl.styles module.
Example:
In this example, the openpyxl module is used to create a new Excel workbook. The program sets values in different cells with the text “w3wiki” and applies various font styles to each cell.
Python3
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 = "w3wiki" # set the size of the cell to 24 sheet.cell(row = 1, column = 1).font = Font(size = 24 ) sheet.cell(row = 2, column = 2).value = "w3wiki" # set the font style to italic sheet.cell(row = 2, column = 2).font = Font(size = 24, italic = True) sheet.cell(row = 3, column = 3).value = "w3wiki" # set the font style to bold sheet.cell(row = 3, column = 3).font = Font(size = 24, bold = True) sheet.cell(row = 4, column = 4).value = "w3wiki" # set the font name to 'Times New Roman' sheet.cell(row = 4, column = 4).font = Font(size = 24, name = 'Times New Roman') wb.save('sample.xlsx') |
Output:
Refer to the below article to get detailed information about adjusting rows and columns.
Working with Excel Spreadsheets in Python
You all must have worked with Excel at some time in your life and must have felt the need to automate some repetitive or tedious task. Don’t worry in this tutorial we are going to learn about how to work with Excel using Python, or automating Excel using Python. We will be covering this with the help of the Openpyxl module and will also see how to get Python in Excel.