AI技术智启未来—办公大模型赋能工作

import pandas as pd
# 读取上传的Excel文件
file_path = '/mnt/data/库存盘点表.xlsx'
# 由于Excel文件包含多个工作表，我们需要分别读取每个工作表
sheets = pd.read_excel(file_path, sheet_name=None)
# 检查读取到的工作表名称
sheet_names = sheets.keys()
sheet_names

# 定义一个函数来计算每个工作表的采购总花费
def calculate_total_cost(sheet):
    # 检查是否存在单价和采购数量列
    if '单价' in sheet.columns and '采购数量' in sheet.columns:
        # 计算总花费
        return (sheet['单价'] * sheet['采购数量']).sum()
    else:
        return 0
# 计算每个工作表的总花费
total_costs = {sheet_name: calculate_total_cost(sheet) for sheet_name, sheet in sheets.items()}
# 计算所有工作表的总花费
grand_total_cost = sum(total_costs.values())
grand_total_cost

# 检查每个工作表的"单价"和"采购数量"列的数据类型，并转换为数值类型（如果需要）
for sheet_name, sheet in sheets.items():
    if '单价' in sheet.columns and '采购数量' in sheet.columns:
        sheet['单价'] = pd.to_numeric(sheet['单价'], errors='coerce')
        sheet['采购数量'] = pd.to_numeric(sheet['采购数量'], errors='coerce')
# 再次计算每个工作表的总花费
total_costs = {sheet_name: calculate_total_cost(sheet) for sheet_name, sheet in sheets.items()}
# 再次计算所有工作表的总花费
grand_total_cost = sum(total_costs.values())
grand_total_cost

# First, let's take a look at the structure of the Orders table to understand the available columns.
columns = cursor.execute("PRAGMA table_info(Orders);").fetchall()
columns = [(column[1], column[2]) for column in columns]
columns

# Extract sales data for iPhones from the past quarters
# We'll consider a quarter as 3 months for simplicity
# Get the current date to determine the last quarters
from datetime import datetime, timedelta
current_date = datetime.now()
quarters = []
# Going back 4 quarters (1 year) from the current date
for i in range(4):
    start_date = current_date - timedelta(days=i*90)
    end_date = start_date + timedelta(days=90)
    if end_date > current_date:
        end_date = current_date
    quarters.append((start_date, end_date))
# Extract iPhone sales data for each quarter
iphone_sales_data = []
for start_date, end_date in quarters:
    query = """
    SELECT SUM(o.quantity) as total_sales
    FROM Orders o
    JOIN Users u ON o.user_id = u.user_id
    JOIN Products p ON o.product_id = p.product_id
    WHERE p.product_name LIKE 'iPhone%'
    AND o.order_date BETWEEN ? AND ?
    """
    total_sales = cursor.execute(query, (start_date, end_date)).fetchone()[0]
    iphone_sales_data.append((start_date, end_date, total_sales))
iphone_sales_data

# 绘制图表
fig, ax1 = plt.subplots(figsize=(12, 6))
# 绘制平均价格的折线图
ax1.plot(df['月份'], df['样本平均价格(元/㎡)'], color='b', label='样本平均价格(元/㎡)')
ax1.set_xlabel('月份')
ax1.set_ylabel('样本平均价格(元/㎡)', color='b')
ax1.tick_params(axis='y', labelcolor='b')
ax1.set_title('平均价格和环比涨跌的走势图')
# 创建第二个Y轴
ax2 = ax1.twinx()
# 绘制环比涨跌的柱状图
ax2.bar(df['月份'], df['环比涨跌(%)'], color='g', alpha=0.6, label='环比涨跌(%)')
ax2.set_ylabel('环比涨跌(%)', color='g')
ax2.tick_params(axis='y', labelcolor='g')
# 显示图例
ax1.legend(loc='upper left', bbox_to_anchor=(0.2, 1))
ax2.legend(loc='upper left', bbox_to_anchor=(0.2, 0.9))
plt.show()