Control Excel with Python & OpenPyXL

Explore workbooks and worksheets by creating, opening, saving, and copying them, then access sheets by name and indexes and obtain references to workbooks and sheets.

Learn to create and save workbooks in openpyxl by importing the library, instantiating a workbook object, saving as first workbook.xlsx, and viewing the default sheet in Excel.

Open an existing workbook with OpenPyXL by using load_workbook on 2.1_Hello_world.xlsx, inspect the sheets by iterating wb and printing each sheet.title, and save the workbook when changes are made.

Learn to create, rename, delete, and save sheets in OpenPyXL by manipulating workbooks and worksheets, including inserting at position zero and appending new sheets to the workbook.

Copy sheets in OpenPyXL by loading a workbook, selecting a source sheet, using wb.copy_worksheet to duplicate it, renaming the new sheet, and saving to copied_sheets.xlsx.

Learn how to access Excel sheets with Python using openpyxl by index or by name, load workbooks, check sheet names, and handle missing sheets with KeyError.

Learn to create and open workbooks, add and remove sheets, rename them, access sheets by index or name, copy worksheets, and enumerate sheet titles with openpyxl.

Learn to read data from Excel cells using absolute and relative references, with examples like A1 and C2, then apply offset and read a full data range.

Learn absolute and relative cell references in openpyxl by creating a workbook, selecting a sheet, and setting A1 and C3 values; use utils to convert column letters and indices.

Explore how to use openpyxl's offset method to reference adjacent cells, set values with ws.cell, and work with relative and absolute references in practical examples.

Learn how openpyxl creates and iterates over cell ranges, using relative and absolute referencing, printing values from A1 to C2, and using get_column_letter to reference columns.

Learn to iterate over cells using OpenPyXL, leveraging built-in iter_row and iter_col to simplify looping, and determine the spreadsheet size with max_row and max_column.

Loop through rows and columns in OpenPyXL using iterators and range, then print cell coordinates like A1 through C1 to navigate a worksheet.

Master iterating OpenPyXL cells with iter_rows and iter_cols, using min_row, max_row, min_col, max_col, and the values_only option to obtain row tuples and column outputs.

Learn to use OpenPyXL to get maximum row and column with max_row and max_column, print results, and handle closing workbooks, as seen with A1 to C4 and 1 to 12.

Learn to delete and insert rows and columns, use append to insert rows, move and copy ranges of cells, and insert formulas and tables into spreadsheets.

Apply openpyxl's append method to add headers and data rows to the end of a spreadsheet, using a sales and years example.

Learn to move and copy cell ranges in openpyxl by offsetting ranges like A1:J10, moving cells, and duplicating values with iter_rows and worksheet cells.

Insert formulas in openpyxl and transpose them, set A11 to =SUM(A1:A10), move and transpose cell references with the translate parameter, then save and review results.

Learn to insert an Excel table with openpyxl by creating a sales table from A1:C5, applying table style medium 9, enabling row stripes, and adding the table to the worksheet.

Congratulations on reaching half the course. Post questions or comments in the discussion forum, offer feedback as a review, and continue studying.

Learn cell formatting in Excel, including color, font, size, and data formats, copy and merge cells, apply autofilter for sorting, use the fold method, and configure page setups for printing.

Learn how to format cells in openpyxl, including fonts, fills, borders, alignment, and number formats, and implement workbook and sheet protection with passwords.

Learn to merge and unmerge cells in OpenPyXL, using range borders like A1:B1 and A1:C4, and understand how merging preserves the upper-left value while discarding others.

Learn to use freeze panes in OpenPyXL to keep top rows or left columns visible while scrolling by setting the freeze panes reference (A2, B1, C1) or unfreezing.

Learn to configure print page setups in openpyxl by applying paper size, orientation, and margins to sheets or workbooks, using page_setup options and predefined constants.

Group columns and rows in OpenPyXL with the group method, using A to D and rows 1 to 10. Explain that ungrouping is manual.

Create charts such as pie charts and bar charts, then place them on a chart sheet, and insert images into Excel using OpenPyXL.

Create bar, scatter, and pie charts in openpyxl by defining data references, configuring series, and placing charts with titles and axis labels.

Create a chart sheet in openpyxl by moving a chart to a sheet with data. Build an area chart using references and add it to the chart sheet with cs.add_chart.

Install the pillow library, import image from openpyxl.drawing.image, create an image object, insert it into cell C10, then save and view the workbook.

Explore essential Python code for OpenPyXL workflows, including Tkinter file dialogs and script timing. Note this is not an OpenPyXL chapter, but it covers creating files, folders, and retrieving names.

Use tkinter file dialogs to open or save Excel workbooks with openPyXL, employing ask open file name and ask save as, and set the initial directory.

Learn how to create files and folders with Python using os and OpenPyXL, write to text files, read lines, and create directories with error handling.

Learn to use pathlib to manage file paths and join the current working directory with file names. Retrieve files by extension with glob and open and read them in Python.

apply pathlib to open multiple files from a list, join their paths, and read each line with with open. learn how enumerate can provide indices while iterating over file paths.

Time your Python scripts with the time module to measure OpenPyXL operations, using start and end timestamps. Observe how results vary with input size and avoid background processes.

Start Excel for viewing your work using Python and the os module to launch Excel.exe and open your workbook with OpenPyXL, such as 8.6_Open_me.xlsx, enabling editing and quick verification.

Take your finished course work into the real world by tackling a real-world Excel task and sharing feedback in the discussion forum to build confidence with more projects.