如何用Python从SQL数据库导出含多Sheet的自定义样式XLSX文件

Got it, let's get your multi-sheet Excel export working with all the customizations you need. First, I noticed some logical errors in your SQL queries that would prevent any data from being returned—we'll fix those first. Then, we'll use pandas (which plays nicely with xlsxwriter) to simplify the process, add custom sheet names, column headers, and styling.

Step 1: Fix SQL Query Logic Issues #

Your WHERE clauses have conflicting AND conditions that will never match any rows. For example:

• site_type = 'Existing' AND site_type = 'New' → a site can't be both, use OR instead
• region = 'Cairo' AND region = 'Red sea & Sinai' → same issue, use OR
• Third query is missing an operator between site_type = 'Cow' and site_type = 'Indoor'

Here are the corrected queries (we'll embed these in the full code below):

-- SHR Phase 1 (fixed WHERE clause)
SELECT id, site_code, site_name, region, site_type, tac_name, dt_readiness, rfs, 
rfs_date, huawei_1st_submission_date, te_1st_submission_date, 
huawei_2nd_submission_date, te_2nd_submission_date, 
huawei_3rd_submission_date, te_3rd_submission_date, 
acceptance_date_opt, acceptance_date_plan, signed_sites, 
as_built_date, as_built_status, date_dt, dt_status, 
shr_status, dt_planned, integeration_status, comments_snags 
FROM %s 
WHERE (site_type = 'Existing' OR site_type = 'New') 
AND (region = 'Cairo' OR region = 'Red sea & Sinai');

-- SHR Phase 2 (fixed WHERE clause)
SELECT id, site_code, site_name, region, site_type, tac_name, dt_readiness, rfs, 
rfs_date, huawei_1st_submission_date, te_1st_submission_date, 
huawei_2nd_submission_date, te_2nd_submission_date, 
huawei_3rd_submission_date, te_3rd_submission_date, 
acceptance_date_opt, acceptance_date_plan, signed_sites, 
as_built_date, as_built_status, date_dt, dt_status, 
shr_status, dt_planned, integeration_status, comments_snags 
FROM %s 
WHERE (site_type = 'Existing' OR site_type = 'New') 
AND region = 'Delta';

-- SHR Phase 3 (fixed WHERE clause with OR)
SELECT id, site_code, site_name, region, site_type, tac_name, dt_readiness, rfs, 
rfs_date, huawei_1st_submission_date, te_1st_submission_date, 
huawei_2nd_submission_date, te_2nd_submission_date, 
huawei_3rd_submission_date, te_3rd_submission_date, 
acceptance_date_opt, acceptance_date_plan, signed_sites, 
as_built_date, as_built_status, date_dt, dt_status, 
shr_status, dt_planned, integeration_status, comments_snags 
FROM %s 
WHERE site_type = 'Cow' OR site_type = 'Indoor';

Step 2: Full Python Code with Multi-Sheet Export & Styling #

We'll use pandas to fetch data and xlsxwriter as the engine to add custom styling—this is way cleaner than writing loops manually:

from sqlalchemy import create_engine
import pandas as pd
import MySQLdb

# MySQL Connection Details
MYSQL_USER = 'root'
MYSQL_PASSWORD = 'xxxxxxxxxx'
MYSQL_HOST_IP = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_DATABASE = 'mydb'
govtracker_table = 'govtracker'

# Define query configurations: sheet name, SQL query, custom column headers
query_configs = [
    {
        "sheet_name": "SHR Phase 1",
        "sql": """
            SELECT id, site_code, site_name, region, site_type, tac_name, dt_readiness, rfs, 
            rfs_date, huawei_1st_submission_date, te_1st_submission_date, 
            huawei_2nd_submission_date, te_2nd_submission_date, 
            huawei_3rd_submission_date, te_3rd_submission_date, 
            acceptance_date_opt, acceptance_date_plan, signed_sites, 
            as_built_date, as_built_status, date_dt, dt_status, 
            shr_status, dt_planned, integeration_status, comments_snags 
            FROM %s 
            WHERE (site_type = 'Existing' OR site_type = 'New') 
            AND (region = 'Cairo' OR region = 'Red sea & Sinai');
        """ % govtracker_table,
        "custom_columns": [
            "ID", "Site Code", "Site Name", "Region", "Site Type", "TAC Name", 
            "DT Readiness", "RFS", "RFS Date", "Huawei 1st Submission", "TE 1st Submission",
            "Huawei 2nd Submission", "TE 2nd Submission", "Huawei 3rd Submission", "TE 3rd Submission",
            "Acceptance Date (Opt)", "Acceptance Date (Plan)", "Signed Sites", "As Built Date",
            "As Built Status", "DT Date", "DT Status", "SHR Status", "DT Planned",
            "Integration Status", "Comments/Snags"
        ]
    },
    {
        "sheet_name": "SHR Phase 2",
        "sql": """
            SELECT id, site_code, site_name, region, site_type, tac_name, dt_readiness, rfs, 
            rfs_date, huawei_1st_submission_date, te_1st_submission_date, 
            huawei_2nd_submission_date, te_2nd_submission_date, 
            huawei_3rd_submission_date, te_3rd_submission_date, 
            acceptance_date_opt, acceptance_date_plan, signed_sites, 
            as_built_date, as_built_status, date_dt, dt_status, 
            shr_status, dt_planned, integeration_status, comments_snags 
            FROM %s 
            WHERE (site_type = 'Existing' OR site_type = 'New') 
            AND region = 'Delta';
        """ % govtracker_table,
        "custom_columns": [
            "ID", "Site Code", "Site Name", "Region", "Site Type", "TAC Name", 
            "DT Readiness", "RFS", "RFS Date", "Huawei 1st Submission", "TE 1st Submission",
            "Huawei 2nd Submission", "TE 2nd Submission", "Huawei 3rd Submission", "TE 3rd Submission",
            "Acceptance Date (Opt)", "Acceptance Date (Plan)", "Signed Sites", "As Built Date",
            "As Built Status", "DT Date", "DT Status", "SHR Status", "DT Planned",
            "Integration Status", "Comments/Snags"
        ]
    },
    {
        "sheet_name": "SHR Phase 3",
        "sql": """
            SELECT id, site_code, site_name, region, site_type, tac_name, dt_readiness, rfs, 
            rfs_date, huawei_1st_submission_date, te_1st_submission_date, 
            huawei_2nd_submission_date, te_2nd_submission_date, 
            huawei_3rd_submission_date, te_3rd_submission_date, 
            acceptance_date_opt, acceptance_date_plan, signed_sites, 
            as_built_date, as_built_status, date_dt, dt_status, 
            shr_status, dt_planned, integeration_status, comments_snags 
            FROM %s 
            WHERE site_type = 'Cow' OR site_type = 'Indoor';
        """ % govtracker_table,
        "custom_columns": [
            "ID", "Site Code", "Site Name", "Region", "Site Type", "TAC Name", 
            "DT Readiness", "RFS", "RFS Date", "Huawei 1st Submission", "TE 1st Submission",
            "Huawei 2nd Submission", "TE 2nd Submission", "Huawei 3rd Submission", "TE 3rd Submission",
            "Acceptance Date (Opt)", "Acceptance Date (Plan)", "Signed Sites", "As Built Date",
            "As Built Status", "DT Date", "DT Status", "SHR Status", "DT Planned",
            "Integration Status", "Comments/Snags"
        ]
    }
]

# Establish database connection
db_conn = MySQLdb.connect(
    user=MYSQL_USER, 
    passwd=MYSQL_PASSWORD, 
    host=MYSQL_HOST_IP, 
    db=MYSQL_DATABASE
)

# Create Excel writer with xlsxwriter engine
output_filename = "SHR_Progress_Tracker_23_May.xlsx"
excel_writer = pd.ExcelWriter(output_filename, engine='xlsxwriter')

# Define custom styles
workbook = excel_writer.book
header_style = workbook.add_format({
    'bold': True,
    'bg_color': '#4F81BD',  # Blue background (adjust hex code for your preferred color)
    'font_color': 'white',
    'border': 1,
    'align': 'center',
    'valign': 'vcenter'
})
data_cell_style = workbook.add_format({
    'border': 1,
    'align': 'left',
    'valign': 'vcenter'
})

# Process each query and write to its own sheet
for config in query_configs:
    # Fetch data into a pandas DataFrame
    df = pd.read_sql(config["sql"], db_conn)
    # Replace default column names with custom ones
    df.columns = config["custom_columns"]
    
    # Write data to sheet (skip header—we'll add it manually with styling)
    df.to_excel(excel_writer, sheet_name=config["sheet_name"], startrow=1, index=False, header=False)
    
    # Get the worksheet object to apply styling
    worksheet = excel_writer.sheets[config["sheet_name"]]
    
    # Style headers and set column widths
    for col_idx, header in enumerate(df.columns):
        worksheet.write(0, col_idx, header, header_style)
        # Set column width (adjust based on your data's length)
        worksheet.set_column(col_idx, col_idx, 18)
    
    # Apply style to all data cells
    for row_idx in range(1, len(df) + 1):
        for col_idx in range(len(df.columns)):
            worksheet.write(row_idx, col_idx, df.iloc[row_idx - 1, col_idx], data_cell_style)

# Save and close the Excel file and database connection
excel_writer.close()
db_conn.close()

print(f"Successfully created Excel file: {output_filename}")

Key Features Explained: #

• Multi-Sheet Support: We loop through a list of query configurations, each defining a unique sheet name, SQL query, and custom column headers.
• Custom Column Names: The custom_columns list replaces raw SQL column names with user-friendly, readable labels.
• Styling:
  • Headers get a bold, blue background with white text and centered alignment.
  • Data cells have borders and consistent vertical/horizontal alignment.
  • Column widths are set to 18 (tweak this value if your data needs more space).
• Error-Free SQL: Fixed the WHERE clause logic to ensure your queries actually return data.

Quick Setup Notes: #

• Install required packages if you haven't already: pip install pandas mysqlclient xlsxwriter
• Adjust the bg_color hex code in header_style to match your preferred color scheme.
• For very large datasets, consider adding chunking to avoid memory issues—this code works great for standard-sized datasets.

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