[Python] xlsxwriter not applying the border to the full dataset

I'm simply trying to create a nice border for my dataset. It applies it nicely to the entire dataset expect to the first row where the data actually starts.

import pandas as pd
import io

# In-memory buffer
output = io.BytesIO()

with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
# Start data from row 4 (Excel), which is index 3
df.to_excel(writer, sheet_name='Sheet1', startrow=3, startcol=1, index=False, header=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Formats
header_format = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter', 'border': 1})
subheader_format = workbook.add_format({'bold': True, 'align': 'center', 'border': 1})
bordered_format = workbook.add_format({'border': 1})

# Merged headers in row 2 (index 1)
worksheet.merge_range('F2:G2', 'portfolio', header_format)
worksheet.merge_range('H2:I2', 'inversion information', header_format)
worksheet.merge_range('J2:K2', '% of inversion', header_format)

# Column headers in row 3 (index 2)
start_col = 1 # Column B
for col_num, column_name in enumerate(df.columns):
worksheet.write(2, col_num + start_col, column_name, subheader_format)

# Get dimensions
num_rows, num_cols = df.shape
first_row = 1 # Merged headers (Excel row 2)
last_row = 3 + num_rows # Data ends at Excel row 4 + num_rows
first_col = start_col
last_col = start_col + num_cols - 1

# Apply borders from B2 to K[bottom row]
for row in range(first_row, last_row + 1):
for col in range(first_col, last_col + 1):
if row >= 4: # Data rows start from Excel row 4
value = df.iloc[row - 4, col - start_col]
worksheet.write(row, col, value, bordered_format)
elif row == 2:
continue # Subheaders already formatted
elif row == 1 and col < 5: # Empty cells before merged headers
worksheet.write_blank(row, col, None, bordered_format)

# Save file
with open('mypath/rbd_inversion.xlsx', 'wb') as f:
f.write(output.getvalue())



this is my code, can someone please help me debug the problem. I have also attached a screenshot of the row without it applying the border to it borderless screenshot

Continue reading...