使用 python 的 xlrd、xlwt 和 xlutils.copy 保留样式 [英] Preserving styles using python's xlrd,xlwt, and xlutils.copy

问题描述

我正在使用 xlrd、xlutils.copy 和 xlwt 打开一个模板文件，复制它，用新的填充它值，然后保存.

I'm using xlrd, xlutils.copy, and xlwt to open up a template file, copy it, fill it with new values, and save it.

然而，似乎没有任何简单的方法可以保留单元格的格式；它总是被吹走并设置为空白.有什么简单的方法可以做到这一点吗?

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?

谢谢！/YGA

示例脚本:

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')

版本:

• xlrd: 0.7.1
• xlwt: 0.7.2

• xlrd: 0.7.1
• xlwt: 0.7.2

推荐答案

这有两个部分.

首先，您必须在打开源工作簿时启用格式信息的读取.然后复制操作会将格式复制过来.

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.

这不太漂亮；我使用以下 hack 手动复制格式索引 (xf_idx):

This is less pretty; I use the following hack where I manually copy the formatting index (xf_idx) over:

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.

这篇关于使用 python 的 xlrd、xlwt 和 xlutils.copy 保留样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！