Automating Excel with Python

About the Technical Reviewer

1. Ethan Furman

Acknowledgments

Introduction

1. Who is this book for?
2. About the Author
3. Conventions

This is a tip block

1. Book Source Code
2. Reader Feedback
3. Errata
4. Cover Art

Chapter 1 - Setting Up Your Machine

1. Dependencies
2. Installing OpenPyXL with pip
3. Installing OpenPyXL with conda
4. Using OpenPyXL in a Python Virtual Environment
5. Wrapping Up

Chapter 2 - Reading Spreadsheets with OpenPyXL

1. Open a Spreadsheet
2. Read Specific Cells
3. Read Cells From Specific Row
4. Read Cells From Specific Column
5. Read Cells from Multiple Rows or Columns
6. Read Cells from a Range
7. Read All Cells in All Sheets
8. Wrapping Up

Chapter 3 - Creating a Spreadsheet with OpenPyXL

1. Creating a Spreadsheet
2. Writing to a Spreadsheet
3. Adding and Removing Sheets
4. Inserting and Deleting Rows and Columns
5. Editing Cell Data
6. Creating Merged Cells
7. Folding Rows and Columns
8. Freezing Panes
9. Wrapping Up

Chapter 4 - Styling Cells

1. Working with Fonts
2. Setting the Alignment
3. Adding a Border
4. Changing the Cell Background Color
5. Inserting Images into Cells
6. Styling Merged Cells
7. Using a Built-in Style
8. Creating a Custom Named Style
9. Wrapping Up

Chapter 5 - Conditional Formatting

1. Builtin Formats
2. Working with ColorScales
3. Adding IconSets
4. Creating a DataBar
5. Using DifferentialStyles
6. Wrapping Up

Chapter 6 - Creating Charts

1. Making Your First Chart
2. Adding Titles to the Chart
3. Changing Axis Orientation
4. Modifying Chart Layout
5. Changing the Chart Size
6. Using Styles
7. Creating Chartsheets
8. Wrapping Up

Chapter 7 - Chart Types

1. Area Charts
2. Bar Charts
3. Bubble Charts
4. Line Charts
5. Scatter Charts
6. Pie Charts
7. Doughnut Charts
8. Radar Charts
9. Surface Charts
10. Wrapping Up

Chapter 8 - Converting CSV to Excel

1. Converting a CSV file to Excel
2. Converting an Excel Spreadsheet to CSV
3. Wrapping Up

Chapter 9 - Using Pandas with Excel

1. Install Pandas and Dependencies
2. Read Excel Spreadsheets
3. Read Multiple Excel Worksheets
4. Write DataFrames to Excel
5. Convert CSV to Excel with Pandas
6. Wrapping Up

Chapter 10 - Python and Google Sheets

1. Install gspread
2. Create Credentials with Google
3. Create a New Google Sheet
4. Read Google Sheets
5. Update Google Sheets
6. Delete Google Sheets
7. Wrapping Up

Chapter 11 - XlsxWriter

1. Installation
2. Creating an Excel Spreadsheet
3. Formatting Cells
4. Adding a Chart
5. Creating Sparklines
6. Data Validation
7. Wrapping Up

Appendix A - Cell Comments

1. Adding Comments with Excel
2. Adding Comments to Cells with OpenPyXL
3. Loading Comments from a Workbook
4. Wrapping Up

Appendix B - Print Settings Basics

1. Centering Your Data
2. Adding Headers
3. Adding Print Titles
4. Specifying a Print Area
5. Wrapping Up

Appendix C - Formulas

1. The Parts of an Excel Formula
2. Adding a Formula in Excel
3. Adding a Formula with OpenPyXL
4. Wrapping Up

Afterword