Creating overlapping boarder colours with xlsxwriter and pandas

Question

Given the following data:

import pandas as pd
import numpy as np
# sample data
np.random.seed(1)
np.random.seed(1)
df = pd.DataFrame(
    np.random.randint(low=0, high=1000, size=(6, 5)),
    columns=["X", "a", "b", "c", "d"],
    index=["a", "b", "X", "d", "e", "X"],
)

I can create a xlsx file with formatted boarders using the following:

with pd.ExcelWriter("testing.xlsx") as writer:
    #  need to create xlsx output for this example
    sheet_name = "Sheet1"
    df.to_excel(writer, sheet_name=sheet_name)
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]

    # create horizontal line
    row_range = (3, 3)
    col_range = (1, 8)
    worksheet.conditional_format(
        row_range[0],
        col_range[0],
        row_range[1],
        col_range[1],
        {
            "type": "no_errors",
            "format": workbook.add_format(
                {
                    "top": 2,
                    "border_color": "red",
                }
            ),
        },
    )
    # create vertical line
    row_range = (1, 9)
    col_range = (4, 4)
    worksheet.conditional_format(
        row_range[0],
        col_range[0],
        row_range[1],
        col_range[1],
        {
            "type": "no_errors",
            "format": workbook.add_format(
                {
                    "left": 2,
                    "border_color": "red",
                }
            ),
        },
    )

However the formatting is incorrect (or, not as desired), and looks as:

The part which is incorrect is:

It seems that I am unable to format a cell twice? Which is an issue here.

This question might generalise from creating an overlapping boarder to creating overlapping conditional formatting, I'm not familiar enough with xlsxwriter to say.

Answer 1

I find out the workaround cause it seems like a bug and I'm not an author of the module.

The idea is to not let the cells to overlap. I split the 2 ranges into 5 ranges. The cell which lies at the crossroad has coloured 2 borders.

import pandas as pd
import numpy as np
# sample data
np.random.seed(1)
np.random.seed(1)
df = pd.DataFrame(
    np.random.randint(low=0, high=1000, size=(6, 5)),
    columns=["X", "a", "b", "c", "d"],
    index=["a", "b", "X", "d", "e", "X"],
)

with pd.ExcelWriter("C:\\Users\\kemot\\Desktop\\programy Python\\testing.xlsx") as writer:
    #  need to create xlsx output for this example
    sheet_name = "Sheet1"
    df.to_excel(writer, sheet_name=sheet_name)
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]

 
   # create horizontal line
    worksheet.conditional_format(
        'A4:B4',
        {
            "type": "no_errors",
            "format": workbook.add_format(
                {
                    "bottom": 2,
                    "border_color": "red",
                }
            ),
        },
    )

    worksheet.conditional_format(
        'C4',
        {
            "type": "no_errors",
            "format": workbook.add_format(
                {
                    "right": 2,
                    "bottom": 2,
                    "border_color": "red",
                }
            ),
        },
    )

    worksheet.conditional_format(
        'D4:F4',
        {
            "type": "no_errors",
            "format": workbook.add_format(
                {
                    "bottom": 2,
                    "border_color": "red",
                }
            ),
        },
    )
    # create vertical line

    worksheet.conditional_format(
        'D1:D3',
        {
            "type": "no_errors",
            "format": workbook.add_format(
                {
                    "left": 2,
                    "border_color": "red",
                }
            ),
        },
    )

    worksheet.conditional_format(
        'D5:D10',
        {
            "type": "no_errors",
            "format": workbook.add_format(
                {
                    "left": 2,
                    "border_color": "red",
                }
            ),
        },
    )