Python 将 csv 转换为 xlsx
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17684610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python convert csv to xlsx
提问by user670186
In this postthere is a Python example to convert from csv to xls.
在这篇文章中有一个从 csv 转换为 xls 的 Python 示例。
However, my file has more than 65536 rows so xls does not work. If I name the file xlsx it doesnt make a difference. Is there a Python package to convert to xlsx?
但是，我的文件有超过 65536 行，所以 xls 不起作用。如果我将文件命名为 xlsx，它并没有什么区别。是否有可转换为 xlsx 的 Python 包？
采纳答案by alecxe
Here's an example using xlsxwriter:
这是使用xlsxwriter的示例：
import os
import glob
import csv
from xlsxwriter.workbook import Workbook
for csvfile in glob.glob(os.path.join('.', '*.csv')):
workbook = Workbook(csvfile[:-4] + '.xlsx')
worksheet = workbook.add_worksheet()
with open(csvfile, 'rt', encoding='utf8') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
FYI, there is also a package called openpyxl, that can read/write Excel 2007 xlsx/xlsm files.
仅供参考，还有一个名为openpyxl的包，可以读取/写入 Excel 2007 xlsx/xlsm 文件。
Hope that helps.
希望有帮助。
回答by chfw
With my library pyexcel,
用我的图书馆pyexcel，
$ pip install pyexcel pyexcel-xlsx
you can do it in one command line:
您可以在一个命令行中完成：
from pyexcel.cookbook import merge_all_to_a_book
# import pyexcel.ext.xlsx # no longer required if you use pyexcel >= 0.2.2
import glob
merge_all_to_a_book(glob.glob("your_csv_directory/*.csv"), "output.xlsx")
Each csv will have its own sheet and the name will be their file name.
每个 csv 都有自己的工作表，名称将是它们的文件名。
回答by Rubycon
How I do it with openpyxllib:
我如何使用openpyxllib做到这一点：
import csv
from openpyxl import Workbook
def convert_csv_to_xlsx(self):
wb = Workbook()
sheet = wb.active
CSV_SEPARATOR = "#"
with open("my_file.csv") as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
for idx, val in enumerate(col.split(CSV_SEPARATOR)):
cell = sheet.cell(row=r+1, column=idx+1)
cell.value = val
wb.save("my_file.xlsx")
回答by zhuhuren
First install openpyxl:
首先安装openpyxl：
pip install openpyxl
Then:
然后：
from openpyxl import Workbook
import csv
wb = Workbook()
ws = wb.active
with open('test.csv', 'r') as f:
for row in csv.reader(f):
ws.append(row)
wb.save('name.xlsx')
回答by David Ding
There is a simple way
有一个简单的方法
import os
import csv
import sys
from openpyxl import Workbook
reload(sys)
sys.setdefaultencoding('utf8')
if __name__ == '__main__':
workbook = Workbook()
worksheet = workbook.active
with open('input.csv', 'r') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
for idx, val in enumerate(col.split(',')):
cell = worksheet.cell(row=r+1, column=c+1)
cell.value = val
workbook.save('output.xlsx')
回答by patrickjlong1
Adding an answer that exclusively uses the pandas library to read in a .csv file and save as a .xlsx file. This example makes use of pandas.read_csv(Link to docs) and pandas.dataframe.to_excel(Link to docs).
添加一个专门使用 pandas 库读取 .csv 文件并另存为 .xlsx 文件的答案。此示例使用pandas.read_csv（链接到文档）和pandas.dataframe.to_excel（链接到文档）。
The fully reproducible example uses numpy to generate random numbers only, and this can be removed if you would like to use your own .csv file.
完全可重现的示例仅使用 numpy 生成随机数，如果您想使用自己的 .csv 文件，可以将其删除。
import pandas as pd
import numpy as np
# Creating a dataframe and saving as test.csv in current directory
df = pd.DataFrame(np.random.randn(100000, 3), columns=list('ABC'))
df.to_csv('test.csv', index = False)
# Reading in test.csv and saving as test.xlsx
df_new = pd.read_csv('test.csv')
writer = pd.ExcelWriter('test.xlsx')
df_new.to_excel(writer, index = False)
writer.save()
回答by Bhanu Sinha
Simple two line code solution using pandas
使用 pandas 的简单两行代码解决方案
import pandas as pd
read_file = pd.read_csv ('File name.csv')
read_file.to_excel ('File name.xlsx', index = None, header=True)
回答by Larry W
Simple 1-to-1 CSV to XLSX file conversion without enumerating/looping through the rows:
简单的 1 对 1 CSV 到 XLSX 文件转换，无需枚举/循环遍历行：
import pyexcel
sheet = pyexcel.get_sheet(file_name="myFile.csv", delimiter=",")
sheet.save_as("myFile.xlsx")
Notes:
笔记：
- I have found that if the file_name is really long (>30 characters excluding path) then the resultant XLSX file will throw an error when Excel tries to load it. Excel will offer to fix the error which it does, but it is frustrating.
- There is a great answer previously provided that combines all of the CSV files in a directory into one XLSX workbook, which fits a different use case than just trying to do a 1-to-1 CSV file to XLSX file conversion.
- 我发现如果 file_name 真的很长（> 30 个字符不包括路径），那么当 Excel 尝试加载它时，生成的 XLSX 文件将引发错误。Excel 将提供修复它所做的错误，但它令人沮丧。
- 之前提供了一个很好的答案，它将目录中的所有 CSV 文件组合到一个 XLSX 工作簿中，这适用于不同的用例，而不仅仅是尝试将 1 对 1 CSV 文件转换为 XLSX 文件。
