Basic Operations with openpyxl: Workbooks, Sheets, Cells and Advanced Features

In openpyxl, three main concepts are used: Workbooks (the entire Excel file), Sheets (individual worksheets), and Cells (single grid positions). All operations follow the pattern of opening a workbook, selecting a sheet, and manipulating cells.

Typical tasks include creating a new workbook with Workbook() , accessing the active sheet via wb.active , and assigning values directly to cells (e.g., ws['A1'] = 42 ) or appending rows with ws.append([1, 2, 3]) . Python types are automatically converted, and dates can be inserted using datetime.datetime.now() . The file is saved with wb.save('sample.xlsx') .

Opening an existing file uses load_workbook('filename.xlsx') . Sheets can be created with wb.create_sheet('MySheet') or inserted at a specific position, and accessed by name ( wb['SheetName'] ) or wb.get_sheet_by_name() . Sheet names are listed via wb.sheetnames , and iteration over sheets is possible.

Cell access methods include direct indexing ( ws['A4'] ), ws.cell(row=4, column=2, value=10) , and iteration over rows or columns using sheet.rows and sheet.columns . Ranges can be selected with slicing syntax ( ws['A1':'C2'] ) or using ws.iter_rows() and ws.iter_cols() .

Additional utilities include converting between column letters and numbers with get_column_letter() and column_index_from_string() , removing sheets ( wb.remove(sheet) or del wb[sheet] ), and transposing matrices using list(zip(*rows)) .

Styling cells requires importing Font , colors , and Alignment from openpyxl.styles . A font can be defined ( bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True) ) and applied with sheet['A1'].font = bold_itatic_24_font . Alignment is set via sheet['B1'].alignment = Alignment(horizontal='center', vertical='center') . Row heights and column widths are adjusted with sheet.row_dimensions[2].height = 40 and sheet.column_dimensions['C'].width = 30 .

Merging cells is done with sheet.merge_cells('B1:G1') (data should be written to the top‑left cell), and unmerging with sheet.unmerge_cells('A1:C3') . After merging, only the top‑left cell retains data.

The guide also provides complete example scripts: a basic read/write workflow, a script that generates 500 rows of random data, and examples for creating 2D bar charts and 3D bar charts using BarChart and BarChart3D . Chart data is referenced with Reference , titles are set, and charts are added to the worksheet with ws.add_chart(chart, 'A10') . Finally, the workbook is saved.