Warm tip: This article is reproduced from stackoverflow.com, please click
python-2.7 openpyxl

How to keep style format unchanged after writing data using OpenPyXL package in Python?

发布于 2020-04-03 23:44:10

I'm using openpyxl library package to read and write some data to an existing excel file test.xlsx.

Before writing some data to it, the content of file look like this:

enter image description here

  • cell A1 is contain Khmer Unicode character, and English character is in Bold style.

  • cell A3 used font lemons1 font-face, and English character is in Italic style.

I was using the script below to read and write data "It is me" to cell B2 of this excel file:

from openpyxl import load_workbook
import os
FILENAME1 = os.path.dirname(__file__)+'/test.xlsx'
from flask import make_response
from openpyxl.writer.excel import save_virtual_workbook
from app import app

@app.route('/testexel', methods=['GET'])
def testexel():
    with app.app_context():
        try:
            filename = 'test'
            workbook = load_workbook(FILENAME1, keep_links=False)
            sheet = workbook['Sheet1']
            sheet['B2']='It is me'

            response = make_response(save_virtual_workbook(workbook))
            response.headers['Cache-Control'] = 'no-cache'
            response.headers["Content-Disposition"] = "attachment; filename=%s.xlsx" % filename
            response.headers["Content-type"] = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8"

            return response

        except Exception as e:

            raise

Then format of resulted excel file was modified as this, which I've never wanted it to be like this :

enter image description here

The formatting style is quite different from the original file before writing data to it:

  • cell A1 all data is all bold taking style format from English character

  • cell B3 English character became a normal style, and font was change to font-face limons1 taking from that of khmer character in front of it.

What I am trying to accomplish is to keep existing content of file in the same format (style and font-face) as it was, while writing additional data to it.

Please kindly advise what is wrong with my script and how can I do to keep existing style and font-face unchanged after running above script? Thanks.

Questioner
Houy Narun
Viewed
115
Yvonne Aburrow 2018-11-21 03:52

According to the answer to this question, you can format cells in Excel using openpyxl.

The answer given there only changes the target cell to bold, but maybe you can change the font face back to lemons1.

from openpyxl.workbook import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
ws['B3'] = "Hello"
ws['B3'].font =  Font(name='lemons1', size=14)
wb.save("FontDemo.xlsx")

However, according to the documentation, you can only apply styles to whole cells, not to part of a cell. So you would need to put the Khmer characters in one cell and the English characters in another cell.