large-excel-analysis-and-formatting

Skill Steps

Step 1: Read the Excel file and count the total number of rows across all sheets. If the data volume is too large (e.g., ≥ 10,000 rows), convert it to Parquet format to significantly improve the efficiency of subsequent reading and analysis.

import pandas as pd

file_path = "input.xlsx"
xls = pd.ExcelFile(file_path)
total_rows = 0

# 统计所有 sheet 的总行数
for name in xls.sheet_names:
    df_temp = pd.read_excel(file_path, sheet_name=name, header=None)
    total_rows += len(df_temp)

print(f"总行数: {total_rows}")

Large file processing: convert to Parquet when above the threshold to improve efficiency

if total_rows >= 10000: parquet_path = "/mnt/data/temp.parquet" # Here, reading the first sheet is used as an example; in practice, multiple sheets can be merged as needed df = pd.read_excel(file_path, sheet_name=0) df.to_parquet(engine='pyarrow', path=parquet_path) df = pd.read_parquet(parquet_path) else: df = pd.read_excel(file_path, sheet_name=0)


Step2 提取目标数据进行分组汇总分析，并识别出最大值及其对应的分类项。
```python
# 占位示例：根据实际数据集替换列名
group_col = '分类列名'  # 如 '控股类型'
target_col = '目标数值列'  # 如 '建筑业总产值'

# 假设 df 已清洗并包含所需列，进行汇总分析
summary = df.groupby(group_col)[target_col].sum().reset_index()

# 识别最大值及其对应的分类
max_idx = summary[target_col].idxmax()
max_type = summary.loc[max_idx, group_col]
print(f"最高产值类型: {max_type}")

Step 3: Use openpyxl to write the analysis results into a new Excel file, configure the header style, borders, and column widths, and highlight in green the rows that meet specific conditions (such as the maximum value), then generate a download link.

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side

wb = Workbook()
ws = wb.active
ws.title = "分析报告"

# 样式定义
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_font = Font(name="微软雅黑", bold=True, color="FFFFFF", size=12)
highlight_fill = PatternFill(start_color="00B050", end_color="00B050", fill_type="solid")
highlight_font = Font(name="微软雅黑", bold=True, color="FFFFFF", size=12)
normal_font = Font(name="微软雅黑", size=11)
center_align = Alignment(horizontal="center", vertical="center")
thin_border = Border(
    left=Side(style="thin"), right=Side(style="thin"),
    top=Side(style="thin"), bottom=Side(style="thin")
)

# 写入表头并应用样式
headers = [group_col, target_col]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = center_align
    cell.border = thin_border

# 写入数据并进行条件高亮
for row_idx, row_data in enumerate(summary.itertuples(index=False), 2):
    type_name, value = row_data[0], row_data[1]
    
    cell_type = ws.cell(row=row_idx, column=1, value=type_name)
    cell_value = ws.cell(row=row_idx, column=2, value=value)
    
    # 基础样式
    for cell in [cell_type, cell_value]:
        cell.alignment = center_align
        cell.border = thin_border
        cell.font = normal_font
    
    # 命中最大值条件时高亮整行
    if type_name == max_type:
        cell_type.fill = highlight_fill
        cell_type.font = highlight_font
        cell_value.fill = highlight_fill
        cell_value.font = highlight_font

# 调整列宽
ws.column_dimensions['A'].width = 18
ws.column_dimensions['B'].width = 25

# 保存文件
output_path = "/mnt/data/formatted_analysis_report.xlsx"
wb.save(output_path)
print(f"文件已保存至: {output_path}")

# 提供下载链接
download_link = f"sandbox:{output_path}"
print(f"下载链接: {download_link}")

About this skill

Skill Steps