Xlsxwriter - Does not display 0 after performing conditional format

Question

I have the below code that does conditional formatting using Xlsxwriter package. The only problem I am currently facing is that when the cell value is '0' (zero), the cells are formatted as per the condition, but the number does not appear. However, when I click on the cell, I can see the value in the Excel formula bar. Could anyone advice as to how could I have the zero value shown.

db_conn()
dwh_cur.execute("""select prod.name,count(sale.id),count(is_defect), count(sale.is_defect)/count(sale.id) as Percent
from sales group by prod.name""")
df = dwh_cur.fetchall()

workbook = xlsxwriter.Workbook('Report.xlsx')
worksheet1 = workbook.add_worksheet('Report')

number_format = workbook.add_format({'num_format': '#,###', 'font_size': 14})

#Conditional format - Dark Red color
bad = workbook.add_format({'bg_color': '#ff0000',
                                'font_size': 14,
                                'font_name' :'Calibri',
                                'font_color': '#000000'}) 

#Conditional format - Red color
notbad = workbook.add_format({'bg_color': '#dd5b5b',
                                'font_size': 14,
                                'font_name' :'Calibri',
                                'font_color': '#000000'})

#Conditional format - Green color
good = workbook.add_format({'bg_color': '#008000',
                                'font_size': 14,
                                'font_name' :'Calibri',
                                'font_color': '#000000'})

#Conditional format - Orange color
ok = workbook.add_format({'bg_color': '#ffa500',
                                     'font_size': 14,
                                     'font_name': 'Calibri',
                                     'font_color': '#000000'}) 


worksheet1.conditional_format('E2:E200',
                              {'type': 'formula',
                               'criteria': '=$E2 <= 20',
                               'format': bad})

worksheet1.conditional_format('E2:E200',
                              {'type': 'formula',
                               'criteria': '=$E2 <= 49',
                               'format': notbad})

worksheet1.conditional_format('E2:E200',
                              {'type': 'formula',
                               'criteria': '=$E2 >= 80',
                               'format': good})

worksheet1.conditional_format('E2:E200',
                              {'type': 'formula',
                               'criteria': '=$E2 <= 79',
                               'format': ok})

column_format = workbook.add_format({
    'bold': 1,
    'border': 0,
    'font_size': 14,
    'font_color': 'black'})



## Raw data Sheet (Report 1)
worksheet1.conditional_format('A1:E1', {'type': 'no_errors', 'format': light_blue_bg_format})
worksheet1.set_column(0, 20, 30)

# Adding title on the first row
worksheet1.write('A1', 'Prod Name', column_format)
worksheet1.write('B1', 'Total Count', column_format)
worksheet1.write('C1', 'Defect Count', column_format)
worksheet1.write('D1', 'Percent', column_format)

# Pulling monthly data for columns for weekly raw data sheet
row = 1
col = 0

# Iterate through the array you have and unpack the tuple at each index
for elm1, elm2, elm3, elm4, elm5 in df:
    worksheet1.write(row, col, elm1, number_format)
    worksheet1.write(row, col + 1, elm2, number_format)
    worksheet1.write(row, col + 2, elm3, number_format)
    worksheet1.write(row, col + 3, elm4, number_format)
    row += 1


    workbook.close()

Sample view of df

Prod Name,Total Count,Defect Count,Percent
Prod_A,10,3,30%
Prod_B,20,10,50%
Prod_C,8,0,0%
Prod_D,2,0,0%

Given the above sample view of df, last 2 rows do not display the 0% but have the cells formatted to be in Red as per the conditional formatting rule set above. But on clicking on the cell, we can see the 0% in the cell.

The issue occurs when count(sale.is_defect) is 0, the cell is blank though it displays 0 when we click on the formula bar. Same applies to the column that is conditionally formatted.

Answer 1

The issue isn't related to the conditional formats. The issue is here:

number_format = workbook.add_format({'num_format': '#,###', 'font_size': 14})

This #,### number format is causing zero values to be hidden. I don't know if that is a valid format or not. You probably need to verify it in Excel.

Something like the following will work:

number_format = workbook.add_format({'num_format': '0.000', 'font_size': 14})

# Or:

number_format = workbook.add_format({'num_format': '#,##0', 'font_size': 14})