python如何保持数据类型不变,在Python中使用OpenPyXL包写入数据后，如何保持样式格式不变？...

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:

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 :

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.

解决方案

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.