我想能夠在excel中控制背景單元格的顏色。我有大量的資料,我需要確定資料是否符合預期。我已經可以用Python完成了。但是,我想在excel中顯示這些資料,為了便於閱讀,我想根據資料的好壞給單元格上色。在
我已經使用style.applymap根據特定的單元格值為單元格指定顏色。例如,如果行動電話顯示「失敗」,那麼我可以把它塗成紅色。在
因此,如果我仔細檢查資料,建立一個新的「透過」/「不透過」串列,並將這些值輸入到excel檔案中,我就可以得到我想要的顏色。下面顯示了這段程式碼,我可以得到類似於的內容
但是,如果我想在單元格中輸入實際值,那麼我不知道如何獲得我想要的背景顏色。在
到目前為止我所擁有的:import pandas as pd
#in the example, I have a list of office items, chairs and computers
names=['chair1','chair2','chair3','chair4','chair5','computer1','computer2','computer3','computer4','computer5']
#Each name has an assigned type. The type determines what the expectations are for the lifetime
inv_type=['furniture','furniture','furniture','furniture','furniture','electronics','electronics','electronics','electronics','electronics']
#The age of each item before breakdown is recorded here
inv_age=[2.2, 5, 7.3, 0.6, 4.3, 3.2, 1.7, 2.3, 2.2 ,0.9]
# a dictionary defines the minimum expected lifetime
expected_life={'furniture':4,'electronics':2}
# initialise the pass_fail list
inventory_pass_fail=[]
# cyle through the items and append 'pass' or 'fail' to the list depending on if the item
#has reached the minimum expected age.
for i in range(len(names)):
if inv_age[i]>expected_life[inv_type[i]]:
inventory_pass_fail.append('pass')
else:
inventory_pass_fail.append('fail')
#get names, type, and pass/fail list into one list for the excel sheet
final_list_report=list(zip(*[names,inv_type,inventory_pass_fail]))
df = pd.DataFrame(final_list_report,columns=['Name','Type','Pass/Fail'])
#define function that determines background colour
def color_code_by_text(val):
if val=='N/A' or val=='pass':
color='background-color: %s' % 'green'
elif val=='fail':
color='background-color: %s' % 'red'
else:
color=''
return color
#use style.applymap and the color_code_by_text function to colour background cells
styled = (df.style.applymap(color_code_by_text))
# and save the end result
styled.to_excel('inventory_report.xlsx', engine='openpyxl')
我嘗試過用實際值覆蓋同一個檔案。把這個顏色去掉也行。我想要的是這樣的,顏色表示透過/失敗狀態,但單元格保留實際數值:
如有任何建議,謝謝!在