222222

import openpyxl
import statistics
import pandas as pd
import matplotlib.pyplot as plt
import time

wb=openpyxl.load_workbook('data.xlsx')

worksheet = wb.active

max_row = worksheet.max_row

def get_max_column_with_data(worksheet):
    max_column = 0
    for col in worksheet.columns:
        if any(cell.value for cell in col):
            max_column = col[0].column
    return max_column

max_column = get_max_column_with_data(worksheet)
print(f'有数据的最大列数是: {max_column}')
print(f'有数据的行数: {max_row}')


dates=[]
scores=worksheet['D']
for score in scores:
    dates.append(score.value)
dates.remove(dates[0])
dotes=[]
for datrs in dates:
    dotes.append(float(datrs))
max_value = max(dotes)
min_value = min(dotes)
mean_value = statistics.mean(dotes)
variance = statistics.pvariance(dotes)
record_count = len(dotes)
print(f"最大值: {max_value}")
print(f"最小值: {min_value}")
print(f"平均值: {mean_value}")
print(f"方差: {variance}")
print(f"记录数: {record_count}")
rows_to_delete = []
for row in worksheet.iter_rows(min_row=1, max_row=251, min_col=1, max_col=9):
    for cell in row:
        if cell.value == '空':
            rows_to_delete.append(cell.row)
            break
for row_number in reversed(rows_to_delete):
    worksheet.delete_rows(row_number)
wb.save('clean.xlsx')
def turtle():
    plt.rcParams['font.sans-serif'] = 'SimHei'
    df = pd.read_excel('clean.xlsx', sheet_name='Sheet')
    mq=df.head(11)
    x = mq['中文标题']
    y = mq['电影评分']
    plt.figure(figsize=(10,3), dpi=150)
    plt.title('202209427李志豪  top250前十电影评分')
    plt.xlabel('中文标题')
    plt.ylabel('电影评分')
    plt.bar(x, y, width=0.6)
    for a, b in zip(x, y):
        plt.text(x=a, y=b, s=b, color='black',fontsize=8, ha='center')
    plt.xticks(range(len(x))[::2])
    plt.legend(['202209427李志豪  top250评分'])
    time.sleep(1)

    plt.rcParams['font.sans-serif'] = 'SimHei'
    x = ['陈凯歌','宫崎骏','克里斯托夫','加布里尔']
    y = [1,8,1,1]
    plt.figure(figsize=(10, 3), dpi=150)
    plt.title('202209427李志豪  导演和作品数量')
    plt.xlabel('导演')
    plt.ylabel('数量')
    plt.bar(x, y, width=0.6)
    for a, b in zip(x, y):
        plt.text(x=a, y=b, s=b, color='black', fontsize=8, ha='center')
    plt.xticks(range(len(x))[::2])
    plt.legend(['导演—数量'])
    time.sleep(1)

    mq=df.head(10)
    x = mq['中文标题']
    y = mq['电影评分']
    plt.figure(figsize=(5, 3), dpi=150)
    plt.title('202209427李志豪  前十的电影评分')
    plt.stackplot(x, y)
    plt.xticks([])
    cellText = mq[['中文标题']].T.values.tolist()
    rowLabels = ['中文标题']
    colLabels = mq['电影评分'].values.tolist()
    plt.table(cellText=cellText, rowLabels=rowLabels, colLabels=colLabels)
    time.sleep(1)
    plt.figure(figsize=(5, 3), dpi=150)
    plt.pie(mq['电影评分'],labels= mq['中文标题'])
    plt.title('202209427李志豪  前十名对应评分占比情况')
    time.sleep(1)
    x = mq['中文标题']
    y = mq['电影评分']
    plt.figure(figsize=(5, 3), dpi=150)
    plt.xlabel('202209427李志豪  中文标题')
    plt.ylabel('电影评分')
    plt.scatter(x, y)
    plt.show()
if __name__ == '__main__':
    turtle()