python处理存在的excel，保持原格式输出的方法

51 阅读 0 评论 34 点赞

我是靠谱客的博主危机蜡烛，最近开发中收集的这篇文章主要介绍python处理存在的excel，保持原格式输出的方法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

#!/usr/bin/env python
# coding: utf-8

# In[63]:


import xlrd, xlwt
from xlutils.copy import copy
import time
import os

################################这部分是最主要的一部分


def setOutCell(outSheet, row, col, value):
    """ Change cell value without changing formatting. """

    def _getOutCell(outSheet, rowIndex, colIndex):
        """ 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

    # HACK to retain cell style.
    previousCell = _getOutCell(outSheet, row, col)
    # END HACK, PART I

    outSheet.write(row, col, value)

    # HACK, PART II
    if previousCell:
        newCell = _getOutCell(outSheet, row, col)
        if newCell:
            newCell.xf_idx = previousCell.xf_idx

################################

def read_excel():
    # 打开文件
    rdworkBook = xlrd.open_workbook('table.xls', formatting_info=True);
    wtworkBook = copy(rdworkBook)

    sheet1 = rdworkBook.sheet_by_index(0);  # sheet索引从0开始
    sheet2 = wtworkBook.get_sheet(1);

    #    print(sheet2.cell_value(14,4))
    time_now = time.strftime("%Y-%m-%d", time.localtime())
    print(time_now)

    for num in range(0, 5):
        for i in range(0, 4):
            for j in range(0, 5):
                if i == 0:
                    setOutCell(sheet2,num * 8 + 1, 1, sheet1.cell_value(2 * num + 2, 0))
                    setOutCell(sheet2,num * 8 + 1, 4, sheet1.cell_value(2 * num + 2, 1))
                    setOutCell(sheet2,num * 8 + 1, 7, sheet1.cell_value(2 * num + 3, 0))
                    setOutCell(sheet2,num * 8 + 1, 10, sheet1.cell_value(2 * num + 3, 1))
                elif i == 3:
                    # print("run")
                    setOutCell(sheet2,num * 8 + 6, 1, time_now)
                    setOutCell(sheet2,num * 8 + 6, 4, sheet1.cell_value(2 * num + 2, 12))
                    setOutCell(sheet2,num * 8 + 6, 7, time_now)
                    setOutCell(sheet2,num * 8 + 6, 10, sheet1.cell_value(2 * num + 3, 12))
                else:
                    setOutCell(sheet2,num * 8 + i * 2 + 1, j, sheet1.cell_value(2 * num + 2, 5 * (i - 1) + 2 + j))
                    setOutCell(sheet2,num * 8 + i * 2 + 1, j + 6, sheet1.cell_value(2 * num + 3, 5 * (i - 1) + 2 + j))

    # os.remove("table.xls")
    wtworkBook.save("table1.xls")
    print("done!")

if __name__ == '__main__':
    read_excel();