Color Excel Cells Based on their Values using Python | Openpyxl Tutorial

hello everybody my name is Michael and welcome to matech your YouTube channel for automation coding and more in this video I show you how to bring color to your boring looking Excel files using python this video covers various use cases for example how to color specific cells based on their values or how to highlight an entire column based on its name maybe you want to color an entire row based on the value of a specific column or you don't want to highlight anything but you want your Excel spreadsheet to look more readable to the human eye in this case you can convert your Excel spreadsheet to this alternating color style okay let's start with the first case how to color individual cells based on their values first we need to import the load uncore workbook function from Open PI Exel in the next step we will open our Excel workbook for this purpose we create a variable that contains the absolute file path to the Excel file then we use the load uncore workbook function and pass the Excel file path as an input parameter to open the workbook once the workbook is loaded we need to activate the worksheet I've prepared an Excel file with client data it contains various information such as name gender age nationality and many more since there is only one worksheet in this Excel file I don't need to specify it if your Excel file contains more than one worksheet you can select the worksheet by the name for example you want to work with the worksheet table two for this you write WB table 2 in square brackets okay let's continue with the next step we need to get the index of our desired column let's take a look on our Excel sheet again for example we want to color all cells in the gender column that have the value female for this purpose we Loop through the cells of the first row to find the column gender let's print the variable cell to see what we get from this loop as you can see we get a cell object for every cell in the row that contains a value now we can use the cell object and check if its value is equal to gender for this we use the method internal underscore value in an if statement if the cell value is equal to gender we save the index of the column into a variable and break the loop we get the column index of the cell by using the method callcore idx since the gender column is the fourth column in our Excel sheet we expect the number four to be stored as an index into the callcore index variable let's check this with a print command do we get index 4 printed out in the next step we want to color all cells of the column gender whose value is female for this we Loop through all rows of the column and check their values we can do this with the eer uncore rows method of the worksheet object this for Loop will go row by row through all the cells that contain a value because the first row contains only the column name we want to start from the second row we can realize that by setting the parameter mean underscore row to two and we also want only to iterate through the column gender to achieve this we set the parameters mean underscore call and maxcore coal to the index of the desired column the index of our column gender is stored in the variable coore index so we use this variable for the parameters the line of code is very long here for that reason I write the parameters one below the other let's print the variable cells underscore and underscore row to check if the eer underscore rows function really Loops only through the cells of the fourth column and yes it does we get a toule of all cells for every row because we Loop only through a single column there is only one cell object in each Tuple in the next step we access the cell object of the tupal using cells underscore and underscore row and zero in square brackets we use an if statement to check if the value of the cell equals female if the cell's value is equal to female we color the entire cell to do this we need first to import pattern fill from Open PI xl. Styles this is necessary to be able to color Excel cells now we can use the fill method on the cell object with a class pattern fill we can change a cell's background color two parameters need to be specified for the pattern fill class we set the parameter pattern type equal to Solid the second parameter FG color is used to specify the color the color must be given as hex code you can get the hex code of every color very easily just search for Color Picker here you can select every color you want by using the slider and clicking on the color area then copy the hex code and paste it into the pattern fill function I chose a red color in the final step we save the workbook saving the workbook is mandatory otherwise the changes will not be retained in the worksheet to do this we type wb. saave and pass the absolute path of the Excel file which is saved in the variable Excel uncore file as parameter the code is finished now let's take a look into the Excel worksheet after running the code as you can see all cells in the column gender that contain the value female are highlighted in red you can highlight every value in every cell in the same way you just need to specify the column name and the value in the code okay let's continue with the next use case how can we color an entire column based on the column name for example we want to highlight the entire column occupation for this purpose we just need to modify our code a little bit first we change the column name to occupation to get the index of this column then we remove the if statement here because we want to color all the cells of the column in dependent of their values for The Coloring of the entire column I would like to change the color to Yellow to do this I copy the hex code from the Color Picker into the pattern fill function let's run the code and open the Excel file as you can see the entire column is highlighted in yellow but unfortunately the borders were also colored the grid within the column is not visible anymore we can fix this by coloring the borders after the cell is colored for that purpose we need first to import Border Side and Border uncore thin from Open PI Exel do styles. borders then we can set the color of the Border we create an object of the side class and save it in a new variable border uncore color this class object gets two parameters the parameter border uncore style gets the value border uncore thin the second parameter is for the color which must be specified as a hex code here I using the hex code of the light gray color of the Excel grid next we create a border class object let's name it border here we need to set the appearance of all four borders of the cell the side class object border uncore color will be provided to the parameters Left Right top and bottom now we can use the Border method of the cell object and set the value equal to border this ensures that the borders are set according to the specification we made in the Border object let's run the code again and take a look at the Excel sheet do you see the difference the column is highlighted yellow and the grid within the highlighted column is visible now this looks much better let's continue with the next use case case how can we color an entire row based on the value of a specific column this time we choose an example with numerical values we want to highlight all people who are not older than 30 years for this we need to make some modifications in our code first we change the column name to age to get the index of the age column then we need to iterate through the column and find all rows whose values are not higher than 30 to do this we add an if statement that checks if the numeric value matches our conditions if a cell was found we save the row number into a list for this we create the variable row underscore list with an empty list as initial value now we can add the row number to the list with the ained method the row of the cell can be obtained through the row method of the cell object in the next step we will color all the rows that were added to row underscore list for this we use a for Loop to iterate through the list then we use another for Loop to iterate through all cells that are present within the row and like in our examples before the fill method and the Border method are applied to the cell objects for this example I changed the color to Green now let's run the code and check if the rows have been highlighted nice all clients that are not older than 30 years old have been highlighted the entire row is colored in green let's move on to our final use case how can we convert this Excel table to this colored Excel table with alternating row colors I removed the unnecessary steps from our code only the steps for opening the workbook setting the Border colors and saving the workbook remained we start with setting the colors we specify three different colors that will be saved in three separate variables for the header we choose a dark orange color the first color for the rose is white and the second color is light orange in the next step we use these colors to color the rose in an alternating style for this we iterate through all rows by using the worksheet eater uncore rows method this gives us a tupal for every Row the tupal contains all cell objects of the row we access the first cell of the tupal and check its row number with an if statement if the row number equals one it is the header in this case we create the variable color and set its value equal to colorcore header the variable color will be needed in a later step and the row number equals not one we check if the row number is an even number we can do this by using the modulo operator if the modulo of the row number and two equals zero the row number is even in this case we set the variable color equal to colorcore 1 in all other cases the variable color will be set to colorcore two in the next step we iterate through all the cells in the row and like in our examples before we apply the fill method and the Border method to the cell objects but here we use the variable color for the parameter FG color as input the code is finished let's execute it after running the code we get this beautiful Excel table as result this brings us to the end of the video you are now able to automate the process of coloring Excel files I hope you enjoyed the video and if you want to see more content on the topic of how to automate Excel with python please subscribe to the channel more videos will follow

Channel: MiwaTec

Views: 356

Keywords: Openpyxl, PatternFill, Python, Color, Cells, Excel, Worksheet, Border, Side

Id: TRci6_hzuXM

Length: 10min 16sec (616 seconds)

Published: Sun Nov 12 2023