使用 python 的 xlrd、xlwt 和 xlutils.copy 保留样式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3723793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Preserving styles using python's xlrd,xlwt, and xlutils.copy
提问by YGA
I'm using xlrd, xlutils.copy, and xlwtto open up a template file, copy it, fill it with new values, and save it.
我正在使用xlrd, xlutils.copy, andxlwt打开一个模板文件，复制它，用新值填充它，然后保存它。
However, there doesn't seem to be any easy way to preserve the formatting of the cells; it always gets blown away and set to blank. Is there any simple way I can do this?
但是，似乎没有任何简单的方法可以保留单元格的格式。它总是被吹走并设置为空白。有什么简单的方法可以做到这一点吗？
Thanks! /YGA
谢谢！/YGA
A sample script:
示例脚本：
from xlrd import open_workbook
from xlutils.copy import copy
rb = open_workbook('output_template.xls',formatting_info=True)
rs = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)
for i,cell in enumerate(rs.col(8)):
if not i:
continue
ws.write(i,2,22,plain)
wb.save('output.xls')
Versions:
版本：
xlrd: 0.7.1xlwt: 0.7.2
xlrd: 0.7.1xlwt: 0.7.2
回答by John Machin
Here's an example of usage of code that I'll propose as a patch against xlutils 1.4.1
这是我将建议作为针对 xlutils 1.4.1 的补丁的代码使用示例
# coding: ascii
import xlrd, xlwt
# Demonstration of copy2 patch for xlutils 1.4.1
# Context:
# xlutils.copy.copy(xlrd_workbook) -> xlwt_workbook
# copy2(xlrd_workbook) -> (xlwt_workbook, style_list)
# style_list is a conversion of xlrd_workbook.xf_list to xlwt-compatible styles
# Step 1: Create an input file for the demo
def create_input_file():
wtbook = xlwt.Workbook()
wtsheet = wtbook.add_sheet(u'First')
colours = 'white black red green blue pink turquoise yellow'.split()
fancy_styles = [xlwt.easyxf(
'font: name Times New Roman, italic on;'
'pattern: pattern solid, fore_colour %s;'
% colour) for colour in colours]
for rowx in xrange(8):
wtsheet.write(rowx, 0, rowx)
wtsheet.write(rowx, 1, colours[rowx], fancy_styles[rowx])
wtbook.save('demo_copy2_in.xls')
# Step 2: Copy the file, changing data content
# ('pink' -> 'MAGENTA', 'turquoise' -> 'CYAN')
# without changing the formatting
from xlutils.filter import process,XLRDReader,XLWTWriter
# Patch: add this function to the end of xlutils/copy.py
def copy2(wb):
w = XLWTWriter()
process(
XLRDReader(wb,'unknown.xls'),
w
)
return w.output[0][1], w.style_list
def update_content():
rdbook = xlrd.open_workbook('demo_copy2_in.xls', formatting_info=True)
sheetx = 0
rdsheet = rdbook.sheet_by_index(sheetx)
wtbook, style_list = copy2(rdbook)
wtsheet = wtbook.get_sheet(sheetx)
fixups = [(5, 1, 'MAGENTA'), (6, 1, 'CYAN')]
for rowx, colx, value in fixups:
xf_index = rdsheet.cell_xf_index(rowx, colx)
wtsheet.write(rowx, colx, value, style_list[xf_index])
wtbook.save('demo_copy2_out.xls')
create_input_file()
update_content()
回答by S?ren L?vborg
There are two parts to this.
这有两个部分。
First, you must enable the reading of formatting info when opening the source workbook. The copy operation will then copy the formatting over.
首先，您必须在打开源工作簿时启用格式信息的读取。然后复制操作将复制格式。
import xlrd
import xlutils.copy
inBook = xlrd.open_workbook('input.xls', formatting_info=True)
outBook = xlutils.copy.copy(inBook)
Secondly, you must deal with the fact that changing a cell value resets the formatting of that cell.
其次，您必须处理更改单元格值会重置该单元格格式的事实。
This is less pretty; I use the following hack where I manually copy the formatting index (xf_idx) over:
这不那么漂亮；我使用以下 hack 手动复制格式索引 ( xf_idx)：
def _getOutCell(outSheet, colIndex, rowIndex):
""" HACK: Extract the internal xlwt cell representation. """
row = outSheet._Worksheet__rows.get(rowIndex)
if not row: return None
cell = row._Row__cells.get(colIndex)
return cell
def setOutCell(outSheet, col, row, value):
""" Change cell value without changing formatting. """
# HACK to retain cell style.
previousCell = _getOutCell(outSheet, col, row)
# END HACK, PART I
outSheet.write(row, col, value)
# HACK, PART II
if previousCell:
newCell = _getOutCell(outSheet, col, row)
if newCell:
newCell.xf_idx = previousCell.xf_idx
# END HACK
outSheet = outBook.get_sheet(0)
setOutCell(outSheet, 5, 5, 'Test')
outBook.save('output.xls')
This preserves almost all formatting. Cell comments are not copied, though.
这几乎保留了所有格式。但是，不会复制单元格注释。
