How to get/set the colour of the worksheet tab using xlwings in Python?-CodePudding

The following example comes from the documentation of xlsxwriter:

import xlsxwriter

workbook = xlsxwriter.Workbook('tab_colors.xlsx')

# Set up some worksheets.
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()

# Set tab colors
worksheet1.set_tab_color('red')
worksheet2.set_tab_color('green')
worksheet3.set_tab_color('#FF9900')  # Orange

# worksheet4 will have the default color.

workbook.close()

Is it possible to do the same in xlwings? I do not see anything in the code of the class Sheet related to colours.

CodePudding user response：

You can do with the xlwings api and set the color using the Excel colour number if you know it;
E.g.
255 Red
65280 Green
16711680 Blue
or the RBG colour code
or use the colour index

import xlwings as xw
# Set sheet tab to Blue

    wb = xw.Book('tab_colors.xlsx')
    # Either
    wb.sheets['sheet1'].api.Tab.Color = 16711680 
    wb.sheets['sheet2'].api.Tab.Color = '&HFF0000'
    wb.sheets['sheet3'].api.Tab.ColorIndex = 5

CodePudding user response：

On Windows, it is possible to get and set the colours of a tab using the pywin32 api. Please note that it makes your code platform specific and it will not work on Mac for example.

import xlwings as xw
from xlwings.utils import rgb_to_int, int_to_rgb

# Define RGB codes
green = (226, 239, 218)
grey = (242, 242, 242)
red = (252, 228, 214)

# Connect to the Excel file
wb = xw.Book(EXCEL_FILENAME)

for sht in wb.sheets:
    # Retrieve the tab colour
    print("Sheet name {}, Tab color int: {}, Tab color RGB: {}".format(sht.name, sht.api.Tab.Color, int_to_rgb(sht.api.Tab.Color)))

# Set the tab colour to these existing tabs
wb.sheets["Test Green"].api.Tab.Color = rgb_to_int(green)
wb.sheets["Test Grey"].api.Tab.Color = rgb_to_int(grey)
wb.sheets["Test Red"].api.Tab.Color = rgb_to_int(red)