Transcription
Hello everyone! I present to you a mini-course on working with Excel using Python. In this course, we will install the OpenPyExcel library, which allows Python to edit Excel documents.
Next, we will cover the most important operations, such as creating workbooks, creating worksheets, renaming them, and deleting them. Then, we will learn how to read data from cells and explore cell formatting. All of this will be done solely with Python. Enjoy watching!
You can find the source code we write in this video on my Telegram channel; the link will be in the description. Subscribe, and it will be interesting!
Let's open the programming environment PyCharm to create a new project. I will use this programming environment, but you can use any other code editor that you find convenient. The main thing is that it can run scripts and install libraries.
Now, I click on the "New Project" button. By the way, creating a new project in PyCharm is the most convenient, so I recommend using PyCharm. We create a new project, name it "Python Excel," specify the location where this project will be located, and now we click on the "Custom environment" button, which is already selected.
Let's make sure that the "Generate New" checkbox is checked. Now we will create a virtual environment for the new project. If you have difficulty understanding what a virtual environment is, or if you are not very familiar with Python, I recommend my course "Python for Analysts." This course covers everything you need to program well for solving analytical tasks. The link to this course will be in the description of this video.
So, we click on the "Create" button to create a new project. A new virtual environment is now being created. Now my project has loaded. Let's create a new script to ensure everything works. I will name the file "hello.py" and write `print("Hello, world!")`.
Now I press Shift + Ctrl + F10. We see that the phrase "Hello, world!" has appeared in the console. I would like to reiterate that in this course, I assume you know at least the basics of Python. If you still have difficulties, refer to my course or other resources.
Out of the box, Python does not work with Excel, so to read and modify Excel files, we need to install the OpenPyExcel library. I just opened Google and searched for "OpenPyExcel." The "Py" stands for Python, and "Excel" is short for Excel.
From the first link, we can go to the library's website. We need the "Real" section to get instructions on how to install this library. We will need to type in the terminal: `pip install openpyexcel`.
I copy this command and go to our PyCharm, where we already have a terminal. Now the terminal has loaded. PyCharm does everything for us, so if we were to set up the virtual environment separately, we would have to install and activate it manually. But in PyCharm, everything is much simpler. We paste our command and press Enter.
"Pip" is the package manager, "install" is the installation instruction, and "openpyexcel" is the name of our library. As we know, all libraries are installed using pip, which can install everything in our project automatically. If you are not familiar with pip, it is covered in my course for analysts.
So now we just press Enter and wait for the library to install. Great! The library has been installed. Now I clear the terminal as I no longer need it.
Let's check if the library is installed. We write `from openpyexcel import Workbook`, and let's say we write `wb = Workbook()`. Now I press Shift + F10. We see that "Hello" appeared on the console, meaning the library was successfully imported, and we reached this line. So, OpenPyExcel is installed successfully.
Now we have a couple more actions to configure our project. In fact, this is not strictly necessary, but it is better for the project to be well-structured for convenient work.
Currently, in my project folder, there is both the virtual environment and the script I ran. This is not a good practice. I will delete this script and create a folder named "Python Excel." In this folder, I will have separate code and data.
I create a folder named "Data," where I will store the data, and then I will create a Python file named "simplest_program.py." I will write `print("Hello")` in it. Now I run it, and it works.
Next, I will fill our "Data" folder with a file. I will add a file named "sales.xlsx." Let's open it. Now we see that an Excel file has opened on the screen, where I have already prepared a small table with data about fruits, the quantity sold, and the price.
Now we will work with this Excel file. Let's close this file and write the simplest program. We will write `from openpyexcel import Workbook`. Now we will write a program that loads the "sales.xlsx" workbook and displays the list of worksheets on the screen.
We load the workbook with `wb = load_workbook('Data/sales.xlsx')`. Here we use a relative path, meaning it does not start from the C drive. We are in the "Python Excel" folder, which contains the "Data" folder and the "sales.xlsx" file.
Now let's do this: `print(wb.sheetnames)`. Indeed, we see that the console shows that some workbook has been loaded, and now we will display the list of worksheets.
We see that OpenPyExcel read our workbook and displayed three worksheets: "sales," "04," "05," and "06," which correspond to the months. Knowing how to work with Excel through Python will definitely be useful for data analysts.
If you are interested in this profession, I suggest you read my extensive article on Zen, where I cover all aspects of the data analyst profession, what types of analysts exist, where they work, the specifics of the profession, and how to transition into this profession as a specialist with experience.
If the article was helpful, subscribe to my Zen channel; the link will be in the description of this video.
Having written such a simple program, we can now start exploring the capabilities of the OpenPyExcel library. We will create another Python file, which I will name "create_workbook.py," because now we will create a workbook and do something with its cells, formulas, formatting, and so on.
So, we write `from openpyexcel import Workbook`, which means we are importing the Workbook object. Now we create this workbook, meaning we have imported the OpenPyExcel class Workbook and created an instance of this class.
In fact, we have a new empty Workbook object, but no Excel workbook has been created on the disk yet. Let's do this: we will use the `save` method for this workbook.
We will save it in the "Data" folder as "new.xlsx." Now let's open it. We have created a workbook, and it has one sheet, which is named "Sheet."
Now, let's say we want to create a workbook with a specific sheet name. Let's delete the default sheet and create the sheet we want. For example, let’s name this sheet "calculations."
Let's write `ws = wb.create_sheet("calculations")`. Now I press Shift + F10. I have to restart it constantly because the OpenPyExcel library cannot edit a workbook when it is open, as the file is locked.
We see that the sheet "calculations" has been created. Now we will write a program that deletes the sheet "Sheet" from this workbook.
Let's do this: we will create a variable named `default_sheet` and then use the `if` statement to check if the sheet name is in the list of sheet names in our workbook. If it is, we will proceed to remove it.
We will use an indexer to get the worksheet object and then use this object to delete the worksheet.
Now, let's check if this works. We open the workbook again, and we see that only the "calculations" sheet remains.
To understand better, let's set a breakpoint and press Shift + F9. Now we can see that the `sheetnames` is indeed a list of strings.
Next, we will explore the library further, focusing on reading data from cells. Let's write a comment: "Set Styles" and try to set styles for the cells.
For example, let's say cell A4 will be the total. We will set the font for this cell. Now we need to import the necessary classes from the `openpyexcel.styles` module.
We will import `Font`, `PatternFill`, and `Alignment`. These classes will help us format the font, fill, and alignment.
Now we can set the font to bold and specify the color. Let's run it. Great! Now we see that the cell has changed to a maroon color and is bold.
Next, let's set the fill color for the cell. We will use the `fill` property. We will set the fill type to "solid" and specify the color.
Let's run it again. We see that the cell has a sandy color now.
Finally, let's center the value in the cell. We will create an `Alignment` object and set both horizontal and vertical alignment to center.
Let's check it. Everything worked! Now we see that the value is centered both vertically and horizontally.
With the OpenPyExcel library, you can solve much more complex tasks than just writing data in cells and formatting. For example, in "sales.xlsx," we have data on sales for three months: the fourth, fifth, and sixth months.
The data is presented in the form of dates, fruits sold, the quantity of sold products, and the selling price. We can create a new column to calculate revenue for each product.
In the end, we will calculate the total sales revenue for each month and create another sheet to show that in April, for example, products worth 127 monetary units were sold.
I have partially written this program. The complexity lies in determining the number of rows with data for each sheet. We don't need to loop through a million rows; we just need to count the number of rows with data on each sheet.
The program that partially solves this task is displayed on the screen. This program calculates the revenue from sales for each product. You can find this program in my Telegram channel; the link will be in the description.
I won't go into detail about this code now because we already know how to do all of this. The only challenge that may arise is calculating the number of occupied rows on the sheet.
For this, I created a function `get_count`, which takes a workbook and a sheet name as input and returns the number of rows with data. After that, everything is done as we did earlier, so I am confident you can figure this out on your own.