import csv
from datetime import datetime
import random
 
# 模擬電商訂單數(shù)據(jù)
def generate_order_data(num_orders):
    """
    生成指定數(shù)量的訂單數(shù)據(jù)。
    參數(shù):
    num_orders (int): 要生成的訂單數(shù)量。
    
    返回:
    list: 包含所有生成訂單數(shù)據(jù)的列表。
    """
    orders = []
    for i in range(1, num_orders + 1):
        order_id = f"ORD{1000 + i}"
        customer_id = f"CUST{random.randint(100, 999)}"
        product_id = f"PROD{random.randint(1000, 9999)}"
        quantity = random.randint(1, 5)
        price = round(random.uniform(10, 100), 2)
        order_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        status = random.choice(["Pending", "Shipped", "Delivered", "Cancelled"])
        orders.append([order_id, customer_id, product_id, quantity, price, order_date, status])
    return orders
 
# 寫(xiě)入CSV文件
def export_orders_to_csv(orders, filename):
    """
    將訂單數(shù)據(jù)導(dǎo)出到CSV文件。
    參數(shù):
    orders (list): 包含訂單數(shù)據(jù)的列表。
    filename (str): 要寫(xiě)入的CSV文件名。
    """
    headers = ["Order ID", "Customer ID", "Product ID", "Quantity", "Price", "Order Date", "Status"]
 
    with open(filename, mode='w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        writer.writerow(headers)
        writer.writerows(orders)
 
    print(f"成功導(dǎo)出 {len(orders)} 條訂單數(shù)據(jù)到 {filename}")
 
# 使用示例
if __name__ == "__main__":
    num_orders = 50
    orders = generate_order_data(num_orders)
    export_orders_to_csv(orders, "orders_export.csv")

# 導(dǎo)入csv模塊，用于讀取和寫(xiě)入csv文件
import csv
# 導(dǎo)入random模塊，用于生成隨機(jī)數(shù)
import random
# 導(dǎo)入Faker模塊，用于生成假數(shù)據(jù)
from faker import Faker
 
# 初始化Faker生成假數(shù)據(jù)
fake = Faker('zh_CN')
 
 
# 生成學(xué)生成績(jī)數(shù)據(jù)
def generate_student_grades(num_students):
    students = []
    for _ in range(num_students):
        student_id = f"STU{fake.unique.random_number(digits=5)}"  # 生成唯一的5位數(shù)字學(xué)號(hào)
        name = fake.name()  # 生成假姓名
        class_name = f"{random.randint(1, 6)}年級(jí){random.randint(1, 12)}班"  # 生成假班級(jí)
        chinese = random.randint(60, 100)  # 生成假語(yǔ)文成績(jī)
        math = random.randint(50, 100)  # 生成假數(shù)學(xué)成績(jī)
        english = random.randint(55, 100)  # 生成假英語(yǔ)成績(jī)
        science = random.randint(65, 100)  # 生成假科學(xué)成績(jī)
        total = chinese + math + english + science  # 計(jì)算總分
        average = round(total / 4, 1)  # 計(jì)算平均分
        students.append([student_id, name, class_name, chinese, math, english, science, total, average])  # 將學(xué)生成績(jī)添加到列表中
    return students
 
 
# 導(dǎo)出成績(jī)到CSV
def export_grades_to_csv(students, filename):
    headers = ["學(xué)號(hào)", "姓名", "班級(jí)", "語(yǔ)文", "數(shù)學(xué)", "英語(yǔ)", "科學(xué)", "總分", "平均分"]  # 定義CSV文件頭
 
    with open(filename, mode='w', newline='', encoding='utf-8-sig') as file:  # utf-8-sig解決Excel中文亂碼
        writer = csv.writer(file)
        writer.writerow(headers)  # 寫(xiě)入文件頭
        writer.writerows(students)  # 寫(xiě)入學(xué)生成績(jī)數(shù)據(jù)
 
    print(f"成功導(dǎo)出 {len(students)} 名學(xué)生成績(jī)到 {filename}")  # 輸出成功導(dǎo)出學(xué)生成績(jī)的提示信息
 
 
# 使用示例
if __name__ == "__main__":
    num_students = 30  # 定義學(xué)生數(shù)量
    students = generate_student_grades(num_students)  # 生成學(xué)生成績(jī)數(shù)據(jù)
    export_grades_to_csv(students, "student_grades.csv")  # 導(dǎo)出成績(jī)到CSV文件

import csv
from collections import defaultdict
 
def analyze_orders(csv_file):
    """
    分析訂單數(shù)據(jù)
    讀取CSV文件中的訂單信息，并進(jìn)行統(tǒng)計(jì)分析
    參數(shù):
    csv_file (str): 訂單數(shù)據(jù)的CSV文件路徑
    返回:
    無(wú)
    """
    # 初始化統(tǒng)計(jì)變量
    total_orders = 0
    total_revenue = 0.0
    status_counts = defaultdict(int)
    product_sales = defaultdict(int)
 
    try:
        # 打開(kāi)CSV文件并讀取訂單數(shù)據(jù)
        with open(csv_file, mode='r', encoding='utf-8') as file:
            reader = csv.DictReader(file)
 
            for row in reader:
                total_orders += 1
                quantity = int(row['Quantity'])
                price = float(row['Price'])
                total_revenue += quantity * price
 
                status = row['Status']
                status_counts[status] += 1
 
                product_id = row['Product ID']
                product_sales[product_id] += quantity
 
        # 打印分析結(jié)果
        print(f"訂單總數(shù): {total_orders}")
        print(f"總銷售額: ￥{total_revenue:.2f}")
        print("\n訂單狀態(tài)分布:")
        for status, count in status_counts.items():
            print(f"{status}: {count} 單 ({count / total_orders:.1%})")
 
        print("\n最暢銷產(chǎn)品TOP3:")
        top_products = sorted(product_sales.items(), key=lambda x: x[1], reverse=True)[:3]
        for product, sales in top_products:
            print(f"{product}: {sales} 件")
 
    except FileNotFoundError:
        print(f"錯(cuò)誤: 文件 {csv_file} 未找到")
    except Exception as e:
        print(f"讀取文件時(shí)出錯(cuò): {e}")
 
 
# 使用示例
if __name__ == "__main__":
    analyze_orders("orders_export.csv")

import csv
import statistics
 
def analyze_student_grades(csv_file):
    grades = {
        '語(yǔ)文': [],
        '數(shù)學(xué)': [],
        '英語(yǔ)': [],
        '科學(xué)': []
    }
    class_stats = defaultdict(lambda: {
        'count': 0,
        'total': 0,
        'scores': []
    })
    
    try:
        with open(csv_file, mode='r', encoding='utf-8-sig') as file:
            reader = csv.DictReader(file)
            
            for row in reader:
                # 收集各科目成績(jī)
                grades['語(yǔ)文'].append(int(row['語(yǔ)文']))
                grades['數(shù)學(xué)'].append(int(row['數(shù)學(xué)']))
                grades['英語(yǔ)'].append(int(row['英語(yǔ)']))
                grades['科學(xué)'].append(int(row['科學(xué)']))
                
                # 按班級(jí)統(tǒng)計(jì)
                class_name = row['班級(jí)']
                class_stats[class_name]['count'] += 1
                total_score = int(row['總分'])
                class_stats[class_name]['total'] += total_score
                class_stats[class_name]['scores'].append(total_score)
        
        # 計(jì)算并打印各科目統(tǒng)計(jì)信息
        print("各科目成績(jī)統(tǒng)計(jì):")
        for subject, scores in grades.items():
            print(f"{subject}:")
            print(f"  平均分: {statistics.mean(scores):.1f}")
            print(f"  最高分: {max(scores)}")
            print(f"  最低分: {min(scores)}")
            print(f"  中位數(shù): {statistics.median(scores)}")
        
        # 計(jì)算并打印班級(jí)統(tǒng)計(jì)信息
        print("\n班級(jí)成績(jī)統(tǒng)計(jì):")
        for class_name, stats in class_stats.items():
            avg = stats['total'] / stats['count']
            print(f"{class_name}:")
            print(f"  平均總分: {avg:.1f}")
            print(f"  最高總分: {max(stats['scores'])}")
            print(f"  最低總分: {min(stats['scores'])}")
            print(f"  人數(shù): {stats['count']}")
            
    except FileNotFoundError:
        print(f"錯(cuò)誤: 文件 {csv_file} 未找到")
    except Exception as e:
        print(f"處理成績(jī)數(shù)據(jù)時(shí)出錯(cuò): {e}")
 
# 使用示例
if __name__ == "__main__":
    analyze_student_grades("student_grades.csv")

pip install xlwt xlrd xlutils

# 導(dǎo)入csv模塊，用于讀取和寫(xiě)入csv文件
import csv
# 導(dǎo)入defaultdict模塊，用于創(chuàng)建一個(gè)默認(rèn)值的字典
from collections import defaultdict
 
def analyze_orders(csv_file):
    """
    分析訂單數(shù)據(jù)
    讀取CSV文件中的訂單信息，并進(jìn)行統(tǒng)計(jì)分析
    參數(shù):
    csv_file (str): 訂單數(shù)據(jù)的CSV文件路徑
    返回:
    無(wú)
    """
    # 初始化統(tǒng)計(jì)變量
    total_orders = 0
    total_revenue = 0.0
    status_counts = defaultdict(int)
    product_sales = defaultdict(int)
 
    try:
        # 打開(kāi)CSV文件并讀取訂單數(shù)據(jù)
        with open(csv_file, mode='r', encoding='utf-8') as file:
            reader = csv.DictReader(file)
 
            for row in reader:
                total_orders += 1
                quantity = int(row['Quantity'])
                price = float(row['Price'])
                total_revenue += quantity * price
 
                status = row['Status']
                status_counts[status] += 1
 
                product_id = row['Product ID']
                product_sales[product_id] += quantity
 
        # 打印分析結(jié)果
        print(f"訂單總數(shù): {total_orders}")
        print(f"總銷售額: ￥{total_revenue:.2f}")
        print("\n訂單狀態(tài)分布:")
        for status, count in status_counts.items():
            print(f"{status}: {count} 單 ({count / total_orders:.1%})")
 
        print("\n最暢銷產(chǎn)品TOP3:")
        top_products = sorted(product_sales.items(), key=lambda x: x[1], reverse=True)[:3]
        for product, sales in top_products:
            print(f"{product}: {sales} 件")
 
    except FileNotFoundError:
        print(f"錯(cuò)誤: 文件 {csv_file} 未找到")
    except Exception as e:
        print(f"讀取文件時(shí)出錯(cuò): {e}")
 
 
# 使用示例
if __name__ == "__main__":
    analyze_orders("orders_export.csv")

import random
 
import xlwt
 
student_names = ['關(guān)羽', '張飛', '趙云', '馬超', '黃忠']
scores = [[random.randrange(50, 101) for _ in range(3)] for _ in range(5)]
# 創(chuàng)建工作簿對(duì)象（Workbook）
wb = xlwt.Workbook()
# 創(chuàng)建工作表對(duì)象（Worksheet）
sheet = wb.add_sheet('一年級(jí)二班')
# 添加表頭數(shù)據(jù)
titles = ('姓名', '語(yǔ)文', '數(shù)學(xué)', '英語(yǔ)')
for index, title in enumerate(titles):
    sheet.write(0, index, title)
# 將學(xué)生姓名和考試成績(jī)寫(xiě)入單元格
for row in range(len(scores)):
    sheet.write(row + 1, 0, student_names[row])
    for col in range(len(scores[row])):
        sheet.write(row + 1, col + 1, scores[row][col])
# 保存Excel工作簿
wb.save('考試成績(jī)表.xls')

header_style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# 0 - 黑色、1 - 白色、2 - 紅色、3 - 綠色、4 - 藍(lán)色、5 - 黃色、6 - 粉色、7 - 青色
pattern.pattern_fore_colour = 5
header_style.pattern = pattern
titles = ('姓名', '語(yǔ)文', '數(shù)學(xué)', '英語(yǔ)')
for index, title in enumerate(titles):
    sheet.write(0, index, title, header_style)

font = xlwt.Font()
# 字體名稱
font.name = '華文楷體'
# 字體大?。?0是基準(zhǔn)單位，18表示18px）
font.height = 20 * 18
# 是否使用粗體
font.bold = True
# 是否使用斜體
font.italic = False
# 字體顏色
font.colour_index = 1
header_style.font = font

align = xlwt.Alignment()
# 垂直方向的對(duì)齊方式
align.vert = xlwt.Alignment.VERT_CENTER
# 水平方向的對(duì)齊方式
align.horz = xlwt.Alignment.HORZ_CENTER
header_style.alignment = align

borders = xlwt.Borders()
props = (
    ('top', 'top_colour'), ('right', 'right_colour'),
    ('bottom', 'bottom_colour'), ('left', 'left_colour')
)
# 通過(guò)循環(huán)對(duì)四個(gè)方向的邊框樣式及顏色進(jìn)行設(shè)定
for position, color in props:
    # 使用setattr內(nèi)置函數(shù)動(dòng)態(tài)給對(duì)象指定的屬性賦值
    setattr(borders, position, xlwt.Borders.DASHED)
    setattr(borders, color, 5)
header_style.borders = borders

# 設(shè)置行高為40px
sheet.row(0).set_style(xlwt.easyxf(f'font:height {20 * 40}'))
titles = ('姓名', '語(yǔ)文', '數(shù)學(xué)', '英語(yǔ)')
for index, title in enumerate(titles):
    # 設(shè)置列寬為200px
    sheet.col(index).width = 20 * 200
    # 設(shè)置單元格的數(shù)據(jù)和樣式
    sheet.write(0, index, title, header_style)

import xlrd
import xlwt
from xlutils.copy import copy
 
wb_for_read = xlrd.open_workbook('阿里巴巴2020年股票數(shù)據(jù).xls')
sheet1 = wb_for_read.sheet_by_index(0)
nrows, ncols = sheet1.nrows, sheet1.ncols
wb_for_write = copy(wb_for_read)
sheet2 = wb_for_write.get_sheet(0)
sheet2.write(nrows, 4, xlwt.Formula(f'average(E2:E{nrows})'))
sheet2.write(nrows, 6, xlwt.Formula(f'sum(G2:G{nrows})'))
wb_for_write.save('阿里巴巴2020年股票數(shù)據(jù)匯總.xls')

pip install openpyxl

import datetime
 
import openpyxl
 
# 加載一個(gè)工作簿 ---> Workbook
wb = openpyxl.load_workbook('阿里巴巴2020年股票數(shù)據(jù).xlsx')
# 獲取工作表的名字
print(wb.sheetnames)
# 獲取工作表 ---> Worksheet
sheet = wb.worksheets[0]
# 獲得單元格的范圍
print(sheet.dimensions)
# 獲得行數(shù)和列數(shù)
print(sheet.max_row, sheet.max_column)
 
# 獲取指定單元格的值
print(sheet.cell(3, 3).value)
print(sheet['C3'].value)
print(sheet['G255'].value)
 
# 獲取多個(gè)單元格（嵌套元組）
print(sheet['A2:C5'])
 
# 讀取所有單元格的數(shù)據(jù)
for row_ch in range(2, sheet.max_row + 1):
    for col_ch in 'ABCDEFG':
        value = sheet[f'{col_ch}{row_ch}'].value
        if type(value) == datetime.datetime:
            print(value.strftime('%Y年%m月%d日'), end='\t')
        elif type(value) == int:
            print(f'{value:<10d}', end='\t')
        elif type(value) == float:
            print(f'{value:.4f}', end='\t')
        else:
            print(value, end='\t')
    print()

import random
 
import openpyxl
 
# 第一步：創(chuàng)建工作簿（Workbook）
wb = openpyxl.Workbook()
 
# 第二步：添加工作表（Worksheet）
sheet = wb.active
sheet.title = '期末成績(jī)'
 
titles = ('姓名', '語(yǔ)文', '數(shù)學(xué)', '英語(yǔ)')
for col_index, title in enumerate(titles):
    sheet.cell(1, col_index + 1, title)
 
names = ('關(guān)羽', '張飛', '趙云', '馬超', '黃忠')
for row_index, name in enumerate(names):
    sheet.cell(row_index + 2, 1, name)
    for col_index in range(2, 5):
        sheet.cell(row_index + 2, col_index, random.randrange(50, 101))
 
# 第四步：保存工作簿
wb.save('考試成績(jī)表.xlsx')

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
 
# 對(duì)齊方式
alignment = Alignment(horizontal='center', vertical='center')
# 邊框線條
side = Side(color='ff7f50', style='mediumDashed')
 
wb = openpyxl.load_workbook('考試成績(jī)表.xlsx')
sheet = wb.worksheets[0]
 
# 調(diào)整行高和列寬
sheet.row_dimensions[1].height = 30
sheet.column_dimensions['E'].width = 120
 
sheet['E1'] = '平均分'
# 設(shè)置字體
sheet.cell(1, 5).font = Font(size=18, bold=True, color='ff1493', name='華文楷體')
# 設(shè)置對(duì)齊方式
sheet.cell(1, 5).alignment = alignment
# 設(shè)置單元格邊框
sheet.cell(1, 5).border = Border(left=side, top=side, right=side, bottom=side)
for i in range(2, 7):
    # 公式計(jì)算每個(gè)學(xué)生的平均分
    sheet[f'E{i}'] = f'=average(B{i}:D{i})'
    sheet.cell(i, 5).font = Font(size=12, color='4169e1', italic=True)
    sheet.cell(i, 5).alignment = alignment
 
wb.save('考試成績(jī)表.xlsx')

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.label import DataLabelList
 
def create_sales_report():
    # 創(chuàng)建一個(gè)只寫(xiě)模式的Workbook
    wb = Workbook(write_only=True)
    sheet = wb.create_sheet(title="季度銷售報(bào)告")
 
    # 準(zhǔn)備銷售數(shù)據(jù) - 2023年各季度家電銷售數(shù)據(jù)(單位：萬(wàn)元)
    header = ["產(chǎn)品類別", "第一季度", "第二季度", "第三季度", "第四季度", "年度總計(jì)"]
    products = [
        ["空調(diào)", 320, 580, 650, 280, 0],
        ["冰箱", 280, 310, 290, 350, 0],
        ["洗衣機(jī)", 190, 210, 230, 300, 0],
        ["電視機(jī)", 450, 380, 420, 550, 0],
        ["小家電", 180, 160, 200, 240, 0]
    ]
 
    # 計(jì)算年度總計(jì)
    for product in products:
        product[5] = sum(product[1:5])
 
    # 向工作表中寫(xiě)入數(shù)據(jù)
    sheet.append(header)
    for row in products:
        sheet.append(row)
 
    # 創(chuàng)建柱狀圖
    chart = BarChart()
    chart.type = "col"  # 柱狀圖
    chart.style = 13    # 樣式
    chart.title = "2023年家電銷售季度對(duì)比(萬(wàn)元)"
    chart.y_axis.title = "銷售額(萬(wàn)元)"
    chart.x_axis.title = "產(chǎn)品類別"
    chart.legend.position = "b"  # 圖例在底部
 
    # 設(shè)置數(shù)據(jù)范圍(不包括總計(jì)列)
    data = Reference(sheet, min_col=2, min_row=1, max_col=5, max_row=6)
    # 設(shè)置分類范圍
    cats = Reference(sheet, min_col=1, min_row=2, max_row=6)
 
    # 添加數(shù)據(jù)到圖表
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
 
    # 添加數(shù)據(jù)標(biāo)簽
    chart.dataLabels = DataLabelList()
    chart.dataLabels.showVal = True  # 顯示數(shù)值
 
    # 將圖表添加到工作表(從A8單元格開(kāi)始)
    sheet.add_chart(chart, "A8")
 
    # 創(chuàng)建第二個(gè)圖表 - 年度總計(jì)對(duì)比
    chart2 = BarChart()
    chart2.type = "bar"  # 條形圖(橫向)
    chart2.style = 11
    chart2.title = "2023年家電銷售年度總計(jì)對(duì)比"
    chart2.x_axis.title = "銷售額(萬(wàn)元)"
    chart2.y_axis.title = "產(chǎn)品類別"
 
    # 設(shè)置數(shù)據(jù)范圍(只包含總計(jì)列)
    data2 = Reference(sheet, min_col=6, min_row=1, max_row=6)
    # 設(shè)置分類范圍(與第一個(gè)圖表相同)
 
    chart2.add_data(data2, titles_from_data=True)
    chart2.set_categories(cats)
    chart2.dataLabels = DataLabelList()
    chart2.dataLabels.showVal = True
 
    # 將第二個(gè)圖表添加到工作表(從A25單元格開(kāi)始)
    sheet.add_chart(chart2, "A25")
 
    # 保存Excel文件
    wb.save("家電銷售報(bào)告_2023.xlsx")
    print("銷售報(bào)告已生成: 家電銷售報(bào)告_2023.xlsx")
 
if __name__ == "__main__":
    create_sales_report()