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.

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.

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.

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.

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 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 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.