如何用Python将多工作表的.xls文件批量转换为CSV格式

Nice work getting the single-sheet conversion up and running! Let's expand that code to handle every sheet in your .xls file seamlessly. Here's how to do it:

Core Approach #

Instead of hardcoding a single sheet name, we'll:

• Fetch a list of all sheet names in the workbook
• Iterate over each sheet one by one
• Generate a unique CSV file for each sheet (using the original filename + sheet name to avoid overwriting)
• Write each sheet's content to its dedicated CSV

Updated Code #

import xlrd
import csv
import os

def convert_xls_to_multiple_csv(excel_path):
    # Open the target Excel workbook
    wb = xlrd.open_workbook(excel_path)
    
    # Extract base filename (without .xls extension) for CSV naming
    base_name = os.path.splitext(os.path.basename(excel_path))[0]
    # Set output directory to match the Excel file's location (or current folder if path is relative)
    output_folder = os.path.dirname(excel_path) or os.getcwd()
    
    # Loop through every sheet in the workbook
    for sheet_name in wb.sheet_names():
        # Access the current sheet
        current_sheet = wb.sheet_by_name(sheet_name)
        
        # Create a unique CSV path: [original_filename]_[sheet_name].csv
        csv_filename = f"{base_name}_{sheet_name}.csv"
        csv_full_path = os.path.join(output_folder, csv_filename)
        
        # Use 'with' statement to auto-manage file closure (safer than manual close)
        with open(csv_full_path, 'w', newline='', encoding='utf-8') as csv_file:
            writer = csv.writer(csv_file, quoting=csv.QUOTE_ALL)
            # Write each row from the sheet to the CSV
            for row_num in range(current_sheet.nrows):
                writer.writerow(current_sheet.row_values(row_num))
                
        print(f"Converted {sheet_name} to {csv_filename} successfully!")

# Example: Replace with your actual .xls file path
convert_xls_to_multiple_csv("your_file.xls")

Key Details & Improvements #

• wb.sheet_names(): This method returns a list of all sheet names in the workbook (e.g., ['Sheet1', 'Sheet2', 'Sheet3']), letting us automate the loop for every sheet.
• Unique Filenames: Using os.path.splitext to strip the .xls extension, then appending the sheet name ensures no two CSVs overwrite each other.
• with Statement: Eliminates the need for manual csv_file.close()—it guarantees the file is properly closed even if an error occurs mid-write.
• Encoding & Line Endings: encoding='utf-8' prevents character garbling for non-English text, while newline='' ensures consistent line breaks across Windows, macOS, and Linux.

Quick Notes #

• Ensure you have xlrd installed: run pip install xlrd if you haven't already. (Note: xlrd no longer supports .xlsx files, but it's perfect for your .xls use case.)
• To save CSVs to a different folder, just update the output_folder variable to your target directory path.

内容的提问来源于stack exchange，提问作者Prashant