我有 24 個 excel 檔案，我的目標是將 .xslx 資料復制到它們各自的 24 個 .csv 檔案中。我已經復制了資料，但是它在 .csv 檔案中創建了 10 個副本，我相信它與for回圈有關。我試過使用writerow()而不是writerows()這樣做有幫助。我正在嘗試了解 openpyxl 及其撰寫器和讀取器物件。
import openpyxl, os, csv
from pathlib import Path
for excelFile in os.listdir('./excelspreadsheets'):
if excelFile.endswith('.xlsx'): # Skip non xlsx files, load the workbook object
wb = openpyxl.load_workbook('./excelspreadsheets/' excelFile)
for sheetName in wb.sheetnames:
# Loop through every sheet in the workbook
sheet = wb[sheetName]
sheetTitle = sheet.title
# Create the CSV filename from the Excel filename and sheet title
p = Path(excelFile)
excelFileStemName = p.stem
CsvFilename = excelFileStemName '_' sheetTitle '.csv'
# Create the csv.writer object for this CSV file
print(f'Creating filename {CsvFilename}...')
outputFile = open(CsvFilename, 'w', newline='')
outputWriter = csv.writer(outputFile)
# Create reader object for each excel sheet
fileObj = open('./excelspreadsheets/' excelFile)
fileReaderObj = csv.reader(fileObj)
# Loop through every row in the excel sheet
for rowNum in range(1, sheet.max_row 1):
rowData = [] # append each cell to this list
# Loop through each cell in the row
for colNum in range(1, sheet.max_column 1):
rowData.append(sheet.values)
# write the rowData list to the CSV file.
for row in rowData:
outputWriter.writerows(row)
outputFile.close()
因此，每個新創建的 .csv 檔案都會寫入正確的資料，但會寫入 10 次，而不是一次。
感謝任何反饋謝謝。
uj5u.com熱心網友回復：
您可以使用read_excel和to_csv作為 pandas 的一部分來讀取 excel 檔案并將資料寫入 csv 檔案。從編碼的角度來看，它更簡單，因為讀取和寫入將在一行中完成。它也在Openpyxl下面使用。更新后的代碼如下。
import openpyxl, os, csv
from pathlib import Path
import pandas as pd
for excelFile in os.listdir('./excelspreadsheets'):
if excelFile.endswith('.xlsx'): # Skip non xlsx files, load the workbook object
xls = pd.ExcelFile('./excelspreadsheets/' excelFile)
for sheetname in xls.sheet_names:
#Read each sheet into df
df = pd.read_excel('./excelspreadsheets/' excelFile, sheetname)
#Remove .xlsx from filename and create CSV name
CsvFilename = excelFile.rstrip('.xlsx') '_' sheetname '.csv'
print(f'Creating filename {CsvFilename}...')
#Write df as CSV to file
df.to_csv(CsvFilename, index=False)
如果您發現任何錯誤，請告訴我...
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/490630.html