A lot of time we come around task which includes creating a spreadsheet with lot of data, updating it or reading and fetching data from it. This boring task can be made a lot easier using a Python library openpyxl. Openpyxl is used for reading and writing Excel files with xlsx/xlsm/xltx/xltm extensions.
In my project I have taken a item_details.xlsx file which has all the details of product and the company that supplies the product.
Let's start the coding now!!
First, import the required package
Now, we will load the Excel file's Sheet1 by writing the following code:-
To understand the fetching of data from the excel file, I have done it through following exercises:-
Fetched the total number of products each supplier has supplied
Calculated total cost of all the items in stock supplied by each supplier
Found which items would soon go out of stock
All this will be shown in a dictionary. Initialization of 3 dictionaries is done as below:
Let's fetch each row by of the file using for loop:
Here item_list.max_row gives the maximum row count, which will help to traverse all the rows in the file.
Now we will fetch each column value and store it in a variable which will further help in fetching of data
Let's start with the first exercise:-
1. In this we will find total number of items supplied by each supplier, the code goes like this
2. In the next task, let's find out the total value of In Stock items by eah supplier. The code is as follows:
Count of in stock item multiply by price will give the value of each item that an be added one by one for each supplier to give total value.
3. As the last exercise to fetch data, we will find the product stock which is left less than 10 The coding for this is done as:
For all the 3 Exercises, output can be shown by printing the 3 dictionaries as:
OUTPUT
''' print(products_per_supplier) print(total_value_per_supplier) print(product_under_10_in_stock) '''
Write Data in an Excel File or Spreadsheet!!
Let's calculate the total price of product in stock and write it in the new cell.
Firstly, name the cell as Stock's Price
To write the data into each cell, simple code is as follows:
Now, just save this as a new file to observe changes with the old file:
The new file with Stock's Price added as new cell is created.
This completes my Code as well is Blog!! You can further customize the code and play around also explore other functions of openpyxl.
Thank you for reading. Hope it helps :)
Open for your feedback and suggestions.