将多个文本文件写到不同工作表上的一个excel工作簿? [英] Write multiple text files to one excel workbook on different sheets?

问题描述

我有多个文本文件，其中包含以下信息:

I have multiple text files with information such as the following:

Agilent Technologies, Inc.
Date|Previous Close|Open|Day Low|Day High
2017-02-12|$50.47|$50.51|$50.02|$50.59

每个文本文件均由其 ticker 命名，该代码位于文本文件 master.txt 的新行上.我想要一个工作簿，其中每张纸上都有上述数据，应由 ticker 命名，因此我使用了以下代码:

Each text file is named by its ticker that is located on a new line of the text file master.txt. I want one workbook with the above data on each sheet which should be named by the ticker therefore I used the following code:

import xlwt
textfile = "C:/Python27/SublimeText/Stocks/Master/Excel.txt"

with open("master.txt", 'r') as f:
    tickers = [line.rstrip('\n') for line in f]

def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        return False


style = xlwt.XFStyle()
style.num_format_str = '#,###0.00'

for ticker in tickers:
    try:
        f = open("%s.txt" % ticker, 'r+')
    except:
        pass
    row_list = []
    for row in f:
        row_list.append(row.split('|'))
    column_list = zip(*row_list)
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet('%s' % ticker)
    i = 0
    for column in column_list:
        for item in range(len(column)):
            value = column[item].strip()
            if is_number(value):
                worksheet.write(item, i, float(value), style=style)
            else:
                worksheet.write(item, i, value)
        i+=1
    workbook.save(textfile.replace('.txt', '.xls'))

运行上述代码时，每个<代码>股票代码会覆盖最后一个代码，而不是添加新的工作表.另一个问题是数据仅传输到第一列.例如，我在上面给出的示例文本文件在Excel中如下所示:

When running the above code each ticker overwrites the last instead of adding a new sheet. Another issue is that data is only transferred to the first column. For example, the example text file I gave above looks like the following in Excel:

Agilent Technologies, Inc.
Date
2017-02-12

推荐答案

workbook = xlwt.Workbook() # moved outside the loop
for ticker in tickers:
    with open("%s.txt" % ticker, 'r') as f: 
        worksheet = workbook.add_sheet('%s' % ticker)
        for row, line in enumerate(f):
            line = line.rstrip()
            for col, value in enumerate(line.split('|')):
                if is_number(value):
                    worksheet.write(row, col, float(value), style=style)
                else:
                    worksheet.write(row, col, value)

workbook.save('all_the_files.xls'))

这篇关于将多个文本文件写到不同工作表上的一个excel工作簿?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！