Python How to excel paint the background cells

Question

It is necessary to color the background cells when the condition is satisfied.

import xlrd
import xlsxwriter

workbook = xlsxwriter.Workbook('file2.xlsx')
worksheet = workbook.add_worksheet()
format = workbook.add_format()
format.set_font_color('red')

excel_data_file1 = xlrd.open_workbook('file1.xlsx')
sheet_file1 = excel_data_file1.sheet_by_index(0)

excel_data_file2 = xlrd.open_workbook('file2.xlsx')
sheet_file2 = excel_data_file2.sheet_by_index(0)

col1 = sheet_file1.col(colx=0)
col2 = sheet_file2.col(colx=0)

a=set()
for i in range(len(col1)):
    a.add(sheet_file1.cell_value(rowx=i, colx=0))

for j in range(len(col2)):
    cellVal2 = sheet_file2.cell_value(rowx=j, colx=0)

    if cellVal2 not in a:
        worksheet.write_blank(j, 0, format)

by doing

worksheet.write_blank(j, 0, format)

Does not work, all values ​​in the sheet are erased

Answer 1

Here is a small demo, which makes use of Pandas module.

Let's first generate two sample Excel files:

import pandas as pd

pd.DataFrame(np.random.randint(10, size=(5, 3)), columns=list('abc')) \
  .to_excel('d:/temp/f1.xlsx', index=False)    
pd.DataFrame(np.random.randint(10, size=(5, 3)), columns=list('abc')) \
  .to_excel('d:/temp/f2.xlsx', index=False)

Now we will read/parse them into Pandas.DataFrame's:

In [89]: df1 = pd.read_excel(r'D:\temp\f1.xlsx')

In [90]: df2 = pd.read_excel(r'D:\temp\f2.xlsx')

In [91]: df1
Out[91]:
   a  b  c
0  0  5  3
1  9  4  9
2  3  6  5
3  9  1  5
4  2  5  0

In [92]: df2
Out[92]:
   a  b  c
0  0  7  9
1  2  2  3
2  4  9  0
3  7  9  1
4  8  6  5

A helper function which highlights cells in one series that are missing in other series:

def highlight_missing(s1, s2, col_name):
    if s1.name == col_name:
        return ['' if x in s2.values else 'background-color: yellow' for x in s1]   
    return [''] * len(s1)

Finally we can create a new Excel file with highlighted missing values:

df1.style.apply(highlight_missing, s2=df2['a'], col_name='a') \
   .to_excel('d:/temp/new.xlsx', index=False, engine='openpyxl')

Result:

Answer 2

This is probably due to write_blank() missing an extra parameter (see the documentation here: http://xlsxwriter.readthedocs.io/worksheet.html#write_blank).

To fix this problem you could change

worksheet.write_blank(j, 0, format)

to either:

worksheet.write_blank(j, 0, None, format)

or:

worksheet.write(j, 0, format)

Hope that helps.