时间戳

首页 > 解决方案 > 如何使用 xlwt 和 xlrd 将每五行放入一张纸中？

python - 如何使用 xlwt 和 xlrd 将每五行放入一张纸中？

问题描述

现在我只能将所有 13 行数据保存到 Sheet1。但实际上，我想将每 5 行放在一张表中，例如： 在此处输入图像描述

现在我的代码是这样的：

import xlwt
import xlrd
from xlutils.copy import copy
myyear = '2018'
myweek = '10'
mycd='1001'
fileName = myyear + myweek + '.xls'
workbook = xlwt.Workbook(encoding='UTF-8')
worksheet = workbook.add_sheet('sheet1')
worksheet.write(0, 0, label='year')
worksheet.write(0, 1, label='week')
worksheet.write(0, 2, label='CD')
worksheet.write(0, 3, label='keyword')
worksheet.write(0, 4, label='importance')
savePath = r'D:/' + fileName
workbook.save(savePath)
rexcel = xlrd.open_workbook(savePath)
rows = rexcel.sheets()[0].nrows
excel = copy(rexcel)
worksheet = excel.get_sheet(0)
current_row = rows
for cmp_noun, value in [[1,1],[2,2],[3,3],[4,4],[5,5],[6,6],[7,7],[8,8],[9,9],[10,10],[11,11],[12,12],[13,13]]:
    if current_row<=6:
        worksheet.write(current_row, 0, label=myyear)
        worksheet.write(current_row, 1, label=myweek)
        worksheet.write(current_row, 2, label=mycd)
        worksheet.write(current_row, 3, label=cmp_noun)
        worksheet.write(current_row, 4, label=value)
        current_row += 1
excel.save(savePath)

如何修改代码成为我想要的效果？

标签： pythonexcelxlrdxlwt

解决方案

在逻辑段中构建你的代码，这样你就可以单独测试每一个

虚拟数据

你的虚拟数据可以来自任何地方，这里我把它做成了一个简单的生成器

def generate_data():
    year = '2018'
    week = '10'
    cd='1001'
    for i in range(1, 14):
        cmp_noun = value = i
        yield (year, week, cd, cmp_noun, value)

分组

from itertools import zip_longest
def grouper(iterable, n, fillvalue=None):
    """
    Collect data into fixed-length chunks or blocks
    https://docs.python.org/3.5/library/itertools.html#itertools-recipes
    """
    # grouper('ABCDEFG', 3, 'x') --> ABC DEF Gxx"
    args = [iter(iterable)] * n
    return zip_longest(*args, fillvalue=fillvalue)

list(grouper(generate_data(), 5))

分组数据：

[(('2018', '10', '1001', 1, 1),
  ('2018', '10', '1001', 2, 2),
  ('2018', '10', '1001', 3, 3),
  ('2018', '10', '1001', 4, 4),
  ('2018', '10', '1001', 5, 5)),
 (('2018', '10', '1001', 6, 6),
  ('2018', '10', '1001', 7, 7),
  ('2018', '10', '1001', 8, 8),
  ('2018', '10', '1001', 9, 9),
  ('2018', '10', '1001', 10, 10)),
 (('2018', '10', '1001', 11, 11),
  ('2018', '10', '1001', 12, 12),
  ('2018', '10', '1001', 13, 13),
  None,
  None)]

制作工作簿

def generate_workbook(header, data, n=5):
    groups = grouper(data, n)
    workbook = xlwt.Workbook(encoding='UTF-8')
    for i, group in enumerate(groups, 1):
        worksheet = workbook.add_sheet(f'sheet{i}')
        group = filter(None, group)
        populate_sheet(worksheet=worksheet, header=header, data=group)
    return workbook

这会将数据分组，启动一个WorkBook. 然后它为每个组制作一个新的工作表，并填充它。filter(None, group)是当数据不是 的倍数时丢弃剩余的行n。

填充工作表

def populate_sheet(worksheet, header, data):
    for i, column in enumerate(header):
        worksheet.write(0, i, column)
    for row_no, row in enumerate(data, 1):
        for i, item in enumerate(row):
            worksheet.write(row_no, i, label=item)

将标题写入第一行，然后填充以下行。通过使用enumerate我们消除了很多硬编码的数字，并使整体更加通用

保存工作簿

if __name__ == '__main__':
    name = 'test.xls'
    data = generate_data()
    header = ['year', 'week', 'CD', 'keyword', 'importance']
    wb = generate_workbook(header=header, data=data, n = 5)
    wb.save(name)

推荐阅读