如何使用Python批量将100个各含20张工作表的Excel文件数字格式从2位小数修改为6位小数？（解决当前代码仅预览显示6位小数但未同步修改Excel文件的问题）

Alright, let's break down what's happening here and how to fix it. The core issue is this: when you read the Excel files with pandas, you're seeing the full numeric precision in your console, but pandas doesn't automatically update the cell display format in the actual Excel files. Those files still retain their original 2-decimal formatting, so the numbers look unchanged even though the underlying values are correct.

Below are two reliable solutions to address this, depending on whether you want to rewrite the files or modify them in-place.

Solution 1: Rewrite Files with XlsxWriter (Best for Data Overhaul) #

This approach uses pandas with the xlsxwriter engine to read your data, define the 6-decimal format, and write everything out to new files (you can adjust it to overwrite originals if you prefer—just make backups first!).

import pandas as pd
import os

# Target only Excel files in your working directory
path = os.getcwd()
excel_files = [f for f in os.listdir(path) if f.endswith(('.xlsx', '.xls'))]

for filename in excel_files:
    file_path = os.path.join(path, filename)
    print(f"Processing: {file_path}")
    
    # Load all sheets from the current Excel file
    xls = pd.ExcelFile(file_path)
    sheet_names = xls.sheet_names
    
    # Set up the Excel writer with xlsxwriter engine
    output_path = os.path.join(path, f"formatted_{filename}")
    with pd.ExcelWriter(output_path, engine='xlsxwriter') as writer:
        for sheet in sheet_names:
            # Read your targeted range (matches your original skiprows/nrows/usecols)
            df = pd.read_excel(
                file_path,
                sheet_name=sheet,
                skiprows=5,
                nrows=15,
                usecols='E:L'
            )
            
            # Write the DataFrame to the new sheet
            df.to_excel(writer, sheet_name=sheet, index=False)
            
            # Grab the worksheet object to apply formatting
            worksheet = writer.sheets[sheet]
            # Define the 6-decimal format
            six_dec_format = writer.book.add_format({'num_format': '0.000000'})
            
            # Apply the format to all columns in your DataFrame (E:L maps to cols 0-7 here)
            for col_idx in range(df.shape[1]):
                worksheet.set_column(col_idx, col_idx, None, six_dec_format)
    
    print(f"Completed: {output_path}\n")

Solution 2: Modify Files In-Place with OpenPyXL (Preserves Original Formatting) #

If you need to keep the original Excel structure (like colors, formulas, or other formatting), use openpyxl to directly edit the cell formats without rewriting the entire file. Note this only works with .xlsx files (not older .xls).

import openpyxl
import os

path = os.getcwd()
xlsx_files = [f for f in os.listdir(path) if f.endswith('.xlsx')]

for filename in xlsx_files:
    file_path = os.path.join(path, filename)
    print(f"Processing: {file_path}")
    
    # Load the workbook (keep_excel_formatting preserves existing styles)
    wb = openpyxl.load_workbook(file_path, keep_vba=False)  # Set keep_vba=True if you have macros
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        # Target your specific range: rows 6-20 (skiprows=5 = start at row 6), columns E-L (cols 5-12)
        for row in range(6, 6 + 15):
            for col in range(5, 13):
                cell = ws.cell(row=row, column=col)
                # Set the cell's number format to 6 decimal places
                cell.number_format = '0.000000'
    
    # Save the modified file (rename to avoid overwriting, or use the original path)
    wb.save(os.path.join(path, f"formatted_{filename}"))
    print(f"Completed: {file_path}\n")

Quick Notes #

• Solution 1 is great if you're already transforming data with pandas—it’s straightforward but will reset any existing Excel formatting (like cell colors or formulas) not captured in your DataFrame.
• Solution 2 is ideal when you need to preserve the original file’s look and feel—only the numeric formats get updated.
• Always back up your original files before overwriting them!

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