python:创建 Excel 工作簿并将 csv 文件转储为工作表

发布于 2024-11-02 06:17:12 字数 778 浏览 4 评论 0原文

我有几个 csv 文件,我想将其转储为 Excel 工作簿(xls/xlsx)中的新工作表。 我该如何实现这一目标?

谷歌搜索并找到“pyXLwriter”,但该项目似乎已停止。 当我尝试“pyXLwriter”时想知道是否有任何替代方案/建议/模块?

非常感谢。

[编辑]

这是我的解决方案:(有人有更精简、更Pythonic的解决方案吗?请发表评论。谢谢)

import glob
import csv
import xlwt
import os

wb = xlwt.Workbook()


for filename in glob.glob("c:/xxx/*.csv"):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(str(f_short_name))
    spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"')
    row_count = 0
    for row in spamReader:
        for col in range(len(row)):
            ws.write(row_count,col,row[col])
        row_count +=1

wb.save("c:/xxx/compiled.xls")

print "Done"

I have few csv files which I would like to dump as new worksheets in a excel workbook(xls/xlsx).
How do I achieve this?

Googled and found 'pyXLwriter' but it seems the project was stopped.
While Im trying out 'pyXLwriter' would like to know are there any alternatives/suggestions/modules?

Many Thanks.

[Edit]

Here is my solution: (anyone has much leaner, much pythonic solution? do comment. thx)

import glob
import csv
import xlwt
import os

wb = xlwt.Workbook()


for filename in glob.glob("c:/xxx/*.csv"):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(str(f_short_name))
    spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"')
    row_count = 0
    for row in spamReader:
        for col in range(len(row)):
            ws.write(row_count,col,row[col])
        row_count +=1

wb.save("c:/xxx/compiled.xls")

print "Done"

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

妳是的陽光 2024-11-09 06:17:12

不确定所说的“更精简、更Python”是什么意思,但你当然可以稍微修饰一下:

import glob, csv, xlwt, os
wb = xlwt.Workbook()
for filename in glob.glob("c:/xxx/*.csv"):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(f_short_name)
    spamReader = csv.reader(open(filename, 'rb'))
    for rowx, row in enumerate(spamReader):
        for colx, value in enumerate(row):
            ws.write(rowx, colx, value)
wb.save("c:/xxx/compiled.xls")

Not sure what you mean by "much leaner, much pythonic" but you certainly could spruce it up a bit:

import glob, csv, xlwt, os
wb = xlwt.Workbook()
for filename in glob.glob("c:/xxx/*.csv"):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(f_short_name)
    spamReader = csv.reader(open(filename, 'rb'))
    for rowx, row in enumerate(spamReader):
        for colx, value in enumerate(row):
            ws.write(rowx, colx, value)
wb.save("c:/xxx/compiled.xls")
臻嫒无言 2024-11-09 06:17:12

您将在此 xlwt 教程中找到所需的所有内容。该库(xlrd 和 xlwt)是在 Python 中管理 Excel 交互的最流行选择。缺点是,目前它们仅支持 Excel 二进制格式 (.xls)。

You'll find all you need in this xlwt tutorial. This libraries (xlrd and xlwt) are the most popular choices for managing Excel interaction in Python. The downside is that, at the moment, they only support Excel binary format (.xls).

你丑哭了我 2024-11-09 06:17:12

使用 xlsxwriter 在 python 中创建并写入 Excel 文件。

通过以下方式安装:pip install xlsxwriter

import xlsxwriter


# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# Write some simple text.
worksheet.write('A1', 'Hello')

# Text with formatting.
worksheet.write('A2', 'World', bold)

# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

# Insert an image.
worksheet.insert_image('B5', 'logo.png')

workbook.close()

Use xlsxwriter to create and write in a excel file in python.

Install it by : pip install xlsxwriter

import xlsxwriter


# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# Write some simple text.
worksheet.write('A1', 'Hello')

# Text with formatting.
worksheet.write('A2', 'World', bold)

# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

# Insert an image.
worksheet.insert_image('B5', 'logo.png')

workbook.close()
零時差 2024-11-09 06:17:12

我总是通过字符串编写 Office 2003 XML 格式。与编写和压缩构成 xlsx 文档的内容相比,这很容易做到,也更容易管理。它也不需要任何外部库。 (尽管人们可以轻松地推出自己的)

此外,Excel 支持加载 CSV 文件。空格分隔或字符分隔。您可以直接加载它,或者尝试复制并复制它。粘贴它,然后按选项中的“文本到列”按钮。当然,这个选项与 python 无关。

I always just write the Office 2003 XML format through strings. It's quite easy to do and much easier to manage than writing and zipping up what constitutes a xlsx document. It also doesn't require any external libraries. (though one could easily roll their own)

Also, Excel supports loading CSV files. Both space delimited or character delimited. You can either load it right in, or try to copy & paste it, then press the Text-To-Columns button in the options. This option has nothing to do with python, of course.

猫烠⑼条掵仅有一顆心 2024-11-09 06:17:12

也可以在 GitHub 存储库“Kampfmitexcel”中找到...

import csv, xlwt, os

def input_from_user(prompt):
    return raw_input(prompt).strip()

def make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(directory):
    wb = xlwt.Workbook()
    for filename in os.listdir(data_folder_path):
        if filename.endswith(".csv") or filename.endswith(".txt"):
            ws = wb.add_sheet(os.path.splitext(filename)[0])
            with open('{}\\{}'.format(data_folder_path,filename),'rb') as csvfile:
                reader = csv.reader(csvfile, delimiter=',')
                for rowx, row in enumerate(reader):
                    for colx, value in enumerate(row):
                        ws.write(rowx, colx, value)
    return wb

if __name__ == '__main__':
    path_to_data = input_from_user("Where is the data stored?: ")
    xls = make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(path_to_data)
    xls_name = input_from_user('What do you want to name the excel file?: ')
    xls.save('{}\\{}{}'.format(data_folder_path,xls_name,'.xls'))
    print "Your file has been saved in the data folder."

Also available in GitHub repo "Kampfmitexcel"...

import csv, xlwt, os

def input_from_user(prompt):
    return raw_input(prompt).strip()

def make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(directory):
    wb = xlwt.Workbook()
    for filename in os.listdir(data_folder_path):
        if filename.endswith(".csv") or filename.endswith(".txt"):
            ws = wb.add_sheet(os.path.splitext(filename)[0])
            with open('{}\\{}'.format(data_folder_path,filename),'rb') as csvfile:
                reader = csv.reader(csvfile, delimiter=',')
                for rowx, row in enumerate(reader):
                    for colx, value in enumerate(row):
                        ws.write(rowx, colx, value)
    return wb

if __name__ == '__main__':
    path_to_data = input_from_user("Where is the data stored?: ")
    xls = make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(path_to_data)
    xls_name = input_from_user('What do you want to name the excel file?: ')
    xls.save('{}\\{}{}'.format(data_folder_path,xls_name,'.xls'))
    print "Your file has been saved in the data folder."
贱贱哒 2024-11-09 06:17:12

这是基于您的答案本身的答案。但我使用 xlsxwriter 的原因是因为它接受更多 xlsx 格式的数据。 xlwt 将您限制为 65556 行和 xls 格式。

import xlsxwriter
import glob
import csv
workbook = xlsxwriter.Workbook('compiled.xlsx') 
for filename in glob.glob("*.csv"):
    ws = workbook.add_worksheet(str(filename.split('.')[0]))
    spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"')
    row_count = 0
    print filename
    for row in spamReader:
        for col in range(len(row)):
            ws.write(row_count,col,row[col])
        row_count +=1

workbook.close()

This is basing on the answer your answer itself. But the reason I am using xlsxwriter is because, it accepts more data in xlsx format. Where as the xlwt limits you to 65556 rows and xls format.

import xlsxwriter
import glob
import csv
workbook = xlsxwriter.Workbook('compiled.xlsx') 
for filename in glob.glob("*.csv"):
    ws = workbook.add_worksheet(str(filename.split('.')[0]))
    spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"')
    row_count = 0
    print filename
    for row in spamReader:
        for col in range(len(row)):
            ws.write(row_count,col,row[col])
        row_count +=1

workbook.close()
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文