Automate color scaling in excel with Python

color-scaling on excel with python

MS Excel is a spreadsheet program developed by Microsoft in 1985, Which We all must have used for the sole purpose of helping businesses compile all their data, sorting, searching, presenting data, etc.

One of the features is color scaling. We use color scaling for identifying data and filtering groups from a bunch of data and many more things.
Now that can be done by putting rules and editing rules descriptions. You can google the steps for it.

How to automate it?
Suppose we have such excel sheets for multiple datacenters/ data types. and there are hundreds of excel sheets that need to be done color scale for some particular rules.
With excel rules this will take ages to edit and apply the rule to each sheet.

How should we do it?
with the use of python library xlwt. It’s a simple python package with no required dependencies. The package itself is pure Python with no dependencies on modules or packages outside the standard Python distribution.

pip install xlwt

For example, If we want to make an automated script for the below data type, we are creating a report on an excel sheet and want to put a specific type of color scales or making this a part of some automation process/ script.

An excel sheet with some data in tuples

For this import Statement,
import xlwt

Color code standards: Let’s create some color code standards. Here I am creating simple colors. But you can create different variants of these colors or can put conditions while creating the color statements.

#Here I am creating simple color code standards like green, yellow, blue, etc.
st = xlwt.easyxf(‘pattern: pattern solid,fore-colour light_green;’)#green
st1 = xlwt.easyxf(‘pattern: pattern solid,fore-colour light_blue;’)#yellow
st2 = xlwt.easyxf(‘pattern: pattern solid,fore-colour blue;’)# Blue
st3 = xlwt.easyxf(‘pattern: pattern solid,fore-colour light_yellow;’)#light yellow
st4 = xlwt.easyxf(‘pattern: pattern solid,fore-colour white;’)# White

Let’s consider our input looks like the above excel file and it is already created and has some data in it. So we will import it.

w_sheet = wb.get_sheet(0) #get a sheet or
w_sheet1 = wb.add_sheet('XYZ')
numrows = mysheet.nrows
numcols = mysheet.ncols

Below is the conditional code for the data I am considering above with applying the color statements, you can have different conditions as per your data type.

while col < numcols:
 for y in range(numrows):
 value = mysheet.cell(y , col)
 value = str(value)
 value = value[5:]
 print(value)
 
 if value == "'(0, 0)'":
 w_sheet.write(y,col,value.strip('\''),st)
 elif value == "'(0, 1)'" :
 w_sheet.write(y,col,value.strip('\''),st1)
 elif value == "'(1, 0)'" :
 w_sheet.write(y,col,value.strip('\''),st2)
 elif value == "'(1, 1)'" :
 w_sheet.write(y,col,value.strip('\''),st3)
 y +=1
# elif col == 0 or y == 0 :
# w_sheet.write(y,col,value,st4)
col +=1
col = 0

Don't forget to save the workbook as we are modifying all the changes over an open workbook.

wb.get_sheet(0).name = 'neurons'
wb.save(os.path.join(dirpath,'colored_result.xls'))
os.remove(os.path.join(dirpath,'result.xlsx'))

The output will be something like the below image,

Tuple data report with color-coding

You can also apply the same for different types of data types in an excel sheet. Like, numbers, strings, or characters.

Let me know If it helps.