Search code examples
pythonexcelcolorsfontsopenpyxl

Transfering Excel multiple Font Color in a single cell from one workbook to another, using python openpyxl or another

I am trying to figure out a way where I can make my Python script read cells from a workbook, copy them with the correct data and format (including multiple color fonts in a single cell), and paste them in a different workbook using the openpyxl library or another.

I have been successful in copying and pasting the data while also keeping the formatting partially intact. However, I could not find a way to keep the color of the font.

The cells that contain color font are mixed, sometimes black font, then red font or another color in the same cell, and I need that type of information to also be passed to the other workbook

I found this old 'possible solution' post, although it did not work for me. Retrieving full rich-text data from a cell (multiple font color / styles within cell)

My sample code is as follows:

from openpyxl import load_workbook
from openpyxl.styles import Font

# Paths
source_path = "D:\\Python Projects\\Testing Copy Color Font\\Test 1.xlsx"
target_path = "D:\\Python Projects\\Testing Paste Color Font\\Test 2.xlsx"

# Workbooks
source_wb = load_workbook(source_path)
target_wb = load_workbook(target_path)

# Sheets
source_sheet = source_wb.active
target_sheet = target_wb.active

# Cells
source_cell = source_sheet['A1']
target_cell = target_sheet['A1']

# Copy
target_cell.value = source_cell.value

# Copy format
font = source_cell.font
target_cell.font = Font(name=font.name, size=font.size, bold=font.bold, italic=font.italic,
                        vertAlign=font.vertAlign, underline=font.underline, strike=font.strike,
                        color=font.color)

# Save
target_wb.save(target_path)

I made a sample where I wrote 'Hello World' in Test 1's first cell. 'Hello' being an automatic black color and 'World' being a red color. However, when I execute my script in several attempts, it simply pastes the data in black.

I attempted using all the libraries posted in that old post plus that patch. I also attempted using pywin32 to mimic a manual copy/paste action and a VBA macro-enabled file as a helper, but it was way too complex.

If you have ever encountered this problem and have an idea, I would greatly appreciate your feedback

Solution

  • This should be achievable with Xlwings just using Excel copy/paste.
    Example code shown below using he 'Hello World' copy where 'Hello' font is default black and 'World' is red.
    The text in cell A1 is duplicated in the target workbook (this includes font type, size etc) 

    import xlwings as xw


# Paths
source_path = "D:\\Python Projects\\Testing Copy Color Font\\Test 1.xlsx"
target_path = "D:\\Python Projects\\Testing Paste Color Font\\Test 2.xlsx"

with xw.App(visible=True) as app:
    # Workbooks
    source_wb = xw.Book(source_path)
    target_wb = xw.Book(target_path)

    # Sheets
    source_sheet = source_wb.sheets.active
    target_sheet = target_wb.sheets.active

    # Copy/Paste cell A1
    source_sheet.range("A1").copy()
    target_sheet.range("A1").paste()

    target_wb.save(target_path)