Preface ：
Generally, data processing uses pandas and numpy library , But the fill cell color needs to be in excel in , It uses openpyxl library , So we can't directly meet our needs , You need to link the two libraries , First say openpyxl Fill color ,pandas Is to read data directly , however openpyxl It is not , It has to be sheet be in active state , And it must be sheet Select to read data
import openpyxlfrom openpyxl import load_workbook# Like opening test.xlsxwb = load_work(filename='test.xlsx')# Use the first one sheet As a workbook work = wb[wb.sheetnames[0]]openpyxl Fill color description
call openpyxl in PatternFill
Solid fill use solid
import openpyxlfrom openpyxl.styles import PatternFill# According to the above work Make cell selection # Set the style ( Fill color )# Color must be used hex Hexadecimal and no '#' Symbol Listed as yellow fill = PatternFill('solid',fgColor='FFFF00')# fill D4 It's yellow d4 = work['D4']d4.fill = fillFaced with a problem , We need to create Excel The column of , such as ’A’,‘B’,'C’ wait
Use python produce 26 English letters . And combine
import mathimport stringdef cycle_letter(arr,level): import string list1 = string.ascii_uppercase tempArr = [] letterArr = [i for i in list1] arrNum = len(arr) if(level==0 or arrNum==0): return letterArr for index in range(arrNum): for letter in letterArr: tempArr.append(arr[index]+letter) return tempArrdef reduce_excel_col_name(num): tempVal = 1 level = 1 while(tempVal): tempVal = num/(math.pow(26, level)) if(tempVal>1): level += 1 else: break excelArr = [] tempArr = [] for index in range(level): tempArr = cycle_letter(tempArr,index) for numIndex in range(len(tempArr)): if(len(excelArr)<num): excelArr.append(tempArr[numIndex]) else: return excelArr return excelArr You can fill in numbers , Produce what is needed excel Column
example ： produce 31 individual
Case study ：
This is our data ：
Now you need to put everything greater than 50( It doesn't contain 50) The number of , Mark in yellow
# Import the required Library import mathimport stringimport openpyxlimport pandas as pdfrom openpyxl import load_workbook# Pictured above , altogether 10 Column , from 0 To 9# Produced as 10 Of excel The corresponding column def cycle_letter(arr,level): list1 = string.ascii_uppercase tempArr = [] letterArr = [i for i in list1] arrNum = len(arr) if(level==0 or arrNum==0): return letterArr for index in range(arrNum): for letter in letterArr: tempArr.append(arr[index]+letter) return tempArrdef reduce_excel_col_name(num): tempVal = 1 level = 1 while(tempVal): tempVal = num/(math.pow(26, level)) if(tempVal>1): level += 1 else: break excelArr = [] tempArr = [] for index in range(level): tempArr = cycle_letter(tempArr,index) for numIndex in range(len(tempArr)): if(len(excelArr)<num): excelArr.append(tempArr[numIndex]) else: return excelArr return excelArr# Save to charter_listcharter_list = reduce_excel_col_name(10)# loop data Select all >50 The number of # And take out index and col_index And save to excel in a = []b = []for i in range(len(data.values)): for y in data.values[i]: if y > 50: # Get the number of lines a.append(i) # Get the number of columns a.append(data.values[i].tolist().index(y)) b.append(a) a = []# The first one in the list represents the row , The second number represents the column # Use the second number charter_list Produced in place of # Because the second number in the list corresponds to charter_list Of indexfor i in b: i[1] = charter_list[i[1]]# What needs to be noted here is excel from 1 Start , So our first number # Number of rows required +1, But because of the listing 0,1,2,3 To 9 The existence of , So you need to add a 2for i in b: i[0] = i[0]+2# Change the list to 'A2','C2', accord with excle The habit of list_color_all = [x[1]+str(x[0]) for x in b]# Use load_workbook load excel data , Color fill wb = load_workbook(filename='excel_col.xlsx')work = wb[wb.sheetnames[0]]fill = PatternFill("solid", fgColor='FFFF00')for i in list_color_all: work[i].fill = fillwb.close()wb.save('excel_col.xlsx')Run a screenshot ：
This is about python Realize to excel This is the end of the article about the cell fill color of the data required in , More about python Yes excel Please search the previous articles of SDN or continue to browse the related articles below. I hope you can support SDN in the future ！
