英文:
Compare times with pandas and change font according to result
问题
我需要更改单元格的颜色，如果其值低于08:00:00，则在整数上有效，但在字符串上无效。
import xlsxwriter
workbook = xlsxwriter.Workbook('test.xlsx')
worksheet1 = workbook.add_worksheet()
# 添加格式。浅红色填充与深红色文本。
format1 = workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
# 添加格式。绿色填充与深绿色文本。
format2 = workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'})
import pandas as pd
data = pd.DataFrame({'Time': ['07:02:07', '15:16:55', '15:17:20', '15:28:58', '15:32:28', '15:38:54'],
'Payload': ['[0]->[1]', '[1]->[0]', '[0]->[1]', '[0]->[1]', '[1]->[0]', '[0]->[1]']})
caption = ('Cells with values >= 08:00:00 are in light red. '
'Values < 50 are in light green.')
# 写入数据。
worksheet1.write('A1', caption)
for row, row_data in enumerate(data):
worksheet1.write_row(row + 2, 1, row_data)
if len(data) > 0:
for i in range(len(data)):
worksheet1.write('D' + str(i + 5), (data['Time'].iloc[i]))
worksheet1.write('E' + str(i + 5), (data['Payload'].iloc[i]))
# 在范围上写入条件格式。
worksheet1.conditional_format('D5:D12', {'type': 'cell',
'criteria': '>=',
'value': '08:00:00',
'format': format1})
workbook.close()
上面的代码只是一个示例，在我的原始代码中，时间是先前计算的结果。
英文:
I need to change the color of a cell if its value is lower than 08:00:00, it worked on integer but not on strings
import xlsxwriter
workbook = xlsxwriter.Workbook('test.xlsx')
worksheet1 = workbook.add_worksheet()
# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
'font_color': '#9C0006'})
# Add a format. Green fill with dark green text.
format2 = workbook.add_format({'bg_color': '#C6EFCE',
'font_color': '#006100'})
import pandas as pd
data= pd.DataFrame({'Time':['07:02:07', '15:16:55', '15:17:20', '15:28:58','15:32:28','15:38:54'],
'Payload':['[0]->[1]', '[1]->[0]','[0]->[1]','[0]->[1]','[1]->[0]','[0]->[1]']})
caption = ('Cells with values >= 08:00:00 are in light red. '
'Values < 50 are in light green.')
# Write the data.
worksheet1.write('A1', caption)
for row, row_data in enumerate(data):
worksheet1.write_row(row + 2, 1, row_data)
if len (data) >0:
for i in range (len(data)):
worksheet1.write('D'+str(i+5),(data['Time'].iloc[i]))
worksheet1.write('E'+str(i+5),(data['Payload'].iloc[i]))
# Write a conditional format over a range.
worksheet1.conditional_format('D5:D12', {'type': 'cell',
'criteria': '>=',
'value': '08:00:00',
'format': format1})
workbook.close()
the code above is just an example, in my original code the time is the result of previous calculations.
答案1
得分: 2
以下是翻译好的内容：
在代码中存在一些问题，但主要问题是像'07:02:07'这样的值是字符串，而不是日期/时间。
为了使它们在Excel中作为时间正常工作，它们需要被转换为日期时间对象。之后程序应该正常运行。以下是带有一些修复的示例：
import xlsxwriter
import datetime
workbook = xlsxwriter.Workbook('test4.xlsx')
worksheet1 = workbook.add_worksheet()
# 添加格式。浅红色背景，深红色文本。
format1 = workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
# 添加格式。绿色背景，深绿色文本。
format2 = workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'})
import pandas as pd
data = pd.DataFrame({'Time': [datetime.time(7, 2, 7), datetime.time(15 ,16, 55),
datetime.time(15 ,17, 20), datetime.time(15 ,28, 58),
datetime.time(15 ,32, 28), datetime.time(15 ,38, 54)],
'Payload': ['[0]->[1]', '[1]->[0]','[0]->[1]',
'[0]->[1]','[1]->[0]','[0]->[1]']})
caption = ('Cells with values >= 08:00:00 are in light red. '
'Values < 50 are in light green.')
# 写入数据。
worksheet1.write('A1', caption)
for row, row_data in enumerate(data):
worksheet1.write(row + 2, 1, row_data)
time_format = workbook.add_format({'num_format': 'hh:mm::ss'})
if len(data) > 0:
for i in range(len(data)):
worksheet1.write('D'+str(i+5), data['Time'].iloc[i], time_format)
worksheet1.write('E'+str(i+5), data['Payload'].iloc[i])
# 在一定范围上写入条件格式。
worksheet1.conditional_format('D5:D12', {'type': 'date',
'criteria': '>=',
'value': datetime.time(8, 0, 0),
'format': format1})
workbook.close()
输出：
英文:
There are a number of issue in the code but the main one is that the values like '07:02:07' are strings and not dates/times.
In order for them to work as times in Excel they need to be converted to datetime objects. After that the program should work. Here is an example with some fixes:
import xlsxwriter
import datetime
workbook = xlsxwriter.Workbook('test4.xlsx')
worksheet1 = workbook.add_worksheet()
# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
'font_color': '#9C0006'})
# Add a format. Green fill with dark green text.
format2 = workbook.add_format({'bg_color': '#C6EFCE',
'font_color': '#006100'})
import pandas as pd
data= pd.DataFrame({'Time': [datetime.time(7, 2, 7), datetime.time(15 ,16, 55),
datetime.time(15 ,17, 20), datetime.time(15 ,28, 58),
datetime.time(15 ,32, 28), datetime.time(15 ,38, 54)],
'Payload': ['[0]->[1]', '[1]->[0]','[0]->[1]',
'[0]->[1]','[1]->[0]','[0]->[1]']})
caption = ('Cells with values >= 08:00:00 are in light red. '
'Values < 50 are in light green.')
# Write the data.
worksheet1.write('A1', caption)
for row, row_data in enumerate(data):
worksheet1.write(row + 2, 1, row_data)
time_format = workbook.add_format({'num_format': 'hh:mm::ss'})
if len (data) >0:
for i in range (len(data)):
worksheet1.write('D'+str(i+5),(data['Time'].iloc[i]), time_format)
worksheet1.write('E'+str(i+5),(data['Payload'].iloc[i]))
# Write a conditional format over a range.
worksheet1.conditional_format('D5:D12', {'type': 'date',
'criteria': '>=',
'value': datetime.time(8, 0, 0),
'format': format1})
workbook.close()
Output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论