我xlsx在同一目錄中有 30 多個檔案，并且 python我想使用 utf-8 編碼將所有檔案轉換為 csv，無論檔案中存在什么編碼。我正在使用 python 的魔法庫來獲取檔案名（下面的代碼）。對于轉換，我嘗試了 SO 用戶 Julian在這里提到的代碼（我使用了這里發布的代碼），但是代碼拋出了一個錯誤說"InvalidFileException: openpyxl does not support file format, please check you can open it with Excel first. Supported formats are: .xlsx,.xlsm,.xltx,.xltm。下面是引發錯誤的代碼。第二個問題是基于我有限的python知識，我相信代碼適用于一個 excel 檔案。我應該如何使它適用于多個檔案？
在此先感謝您的幫助！
# import a library to detect encodings
import magic
import glob
print("File".ljust(45), "Encoding")
for filename in glob.glob('path*.xlsx'):
with open(filename, 'rb') as rawdata:
result = magic.from_buffer(rawdata.read(2048))
print(filename.ljust(45), result)
此處提到的來自 SO User github 鏈接的代碼拋出錯誤
from openpyxl import load_workbook
import csv
from os import sys
def get_all_sheets(excel_file):
sheets = []
workbook = load_workbook(excel_file,read_only=True,data_only=True)
all_worksheets = workbook.get_sheet_names()
for worksheet_name in all_worksheets:
sheets.append(worksheet_name)
return sheets
def csv_from_excel(excel_file, sheets):
workbook = load_workbook(excel_file,data_only=True)
for worksheet_name in sheets:
print("Export " worksheet_name " ...")
try:
worksheet = workbook.get_sheet_by_name(worksheet_name)
except KeyError:
print("Could not find " worksheet_name)
sys.exit(1)
your_csv_file = open(''.join([worksheet_name,'.csv']), 'wb')
wr = csv.writer(your_csv_file, quoting=csv.QUOTE_ALL)
for row in worksheet.iter_rows():
lrow = []
for cell in row:
lrow.append(cell.value)
wr.writerow(lrow)
print(" ... done")
your_csv_file.close()
if not 2 <= len(sys.argv) <= 3:
print("Call with " sys.argv[0] " <xlxs file> [comma separated list of sheets to export]")
sys.exit(1)
else:
sheets = []
if len(sys.argv) == 3:
sheets = list(sys.argv[2].split(','))
else:
sheets = get_all_sheets(sys.argv[1])
assert(sheets != None and len(sheets
) > 0)
csv_from_excel(sys.argv[1], sheets)
uj5u.com熱心網友回復：
首先，第一個錯誤很明顯：
InvalidFileException: openpyxl does not support file format, please check you can open it with Excel first.
Excel 是否成功打開此檔案？如果是，我們需要作業簿（或其中的一小部分）。
第二個問題的答案：
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# vi:ts=4:et
"""I test to open multiple files."""
import csv
from pathlib import Path
from openpyxl import load_workbook
# find all *.xlsx files into current directory
# and iterate over it
for file in Path('.').glob('*.xlsx'):
# read the Excel file
wb = load_workbook(file)
# small test (optional)
print(file, wb.active.title)
# export all sheets to CSV
for sheetname in wb.sheetnames:
# Write to utf-8 encoded file with BOM signature
with open(f'{file.stem}-{sheetname}.csv', 'w',
encoding="utf-8-sig") as csvfile:
# Write to CSV
spamwriter = csv.writer(csvfile)
# Iterate over rows in sheet
for row in wb[sheetname].rows:
# Write a row
spamwriter.writerow([cell.value for cell in row])
您也可以將csv 的方言明確指定為csv.writer引數。
uj5u.com熱心網友回復：
您是否嘗試過使用Pandas庫？您可以使用 將所有檔案存盤在串列中os。然后，您可以遍歷串列并Excel使用打開每個檔案read_excel，然后寫入csv. 所以它看起來像這樣：
"""Code to read excel workbooks and output each sheet as a csv"""
""""with utf-8 encoding"""
#Declare a file path where you will store all your excel workbook. You
#can update the file path for the ExcelPath variable
#Declare a file path where you will store all your csv output. You can
#update the file path for the CsvPath variable
import pandas as pd
import os
ExcelPath = "C:/ExcelPath" #Store path for your excel workbooks
CsvPath = "C:/CsvPath" #Store path for you csv outputs
fileList = [f for f in os.listdir(ExcelPath)]
for file in fileList:
xls = pd.ExcelFile(ExcelPath '/' file)
sheets = xls.sheet_names #Get the names of each and loop to create
#individual csv files
for sheet in sheets:
fileNameCSV = str(file)[:-5] '.' str(sheet) #declare the csv
#filename which will be excelWorkbook SheetName
df = pd.read_excel(ExcelPath '/' file, sheet_name = sheet)
os.chdir(CsvPath)
df.to_csv("{}.csv".format(fileNameCSV), encoding="utf-8")
不是最好的，但應該滿足您的需求
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/484701.html
標籤：Python python-3.x 擅长 CSV 编码