# ❌ 错误的做法：用 split(",")
line = 'Alice, "Bob, Inc.", 30'
fields = line.split(",")
# 结果: ['Alice', ' "Bob', ' Inc."', ' 30']  ← 错误！

# ✅ 正确的做法：使用 csv 模块
import csv
import io
reader = csv.reader(io.StringIO(line))
fields = next(reader)
# 结果: ['Alice', 'Bob, Inc.', ' 30']  ← 正确！

import csv

# 预定义方言
csv.excel          # Excel 风格（逗号分隔，双引号转义）
csv.excel_tab      # Excel 制表符分隔
csv.unix_dialect   # Unix 风格（换行符 \n）

# 自定义方言
csv.register_dialect('mydialect',
    delimiter='|',          # 分隔符
    quotechar='"',          # 引号字符
    quoting=csv.QUOTE_MINIMAL,  # 引用策略
    lineterminator='\n',    # 行终止符
    skipinitialspace=True,  # 跳过字段前导空格
    doublequote=True,       # 引号内双写转义
    escapechar=None         # 转义字符
)

import csv

# ─── 写 CSV ───

# 方式 1: csv.writer (逐行写入)
with open('data.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['name', 'age', 'city'])      # 写表头
    writer.writerows([                             # 批量写内容
        ['Alice', 30, 'Beijing'],
        ['Bob', 25, 'Shanghai'],
    ])

# 方式 2: DictWriter (写字典)
with open('data.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.DictWriter(f, fieldnames=['name', 'age', 'city'])
    writer.writeheader()                          # 写表头
    writer.writerows([
        {'name': 'Alice', 'age': 30, 'city': 'Beijing'},
        {'name': 'Bob', 'age': 25, 'city': 'Shanghai'},
    ])

# ─── 读 CSV ───

# 方式 1: csv.reader (列表)
with open('data.csv', 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)  # ['name', 'age', 'city'] → ['Alice', '30', 'Beijing']

# 方式 2: DictReader (字典)
with open('data.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row['name'], row['age'])  # Alice 30

# ─── 自动检测 ───
with open('unknown.csv', 'r') as f:
    sample = f.read(1024)
    dialect = csv.Sniffer().sniff(sample)
    has_header = csv.Sniffer().has_header(sample)
    f.seek(0)
    reader = csv.reader(f, dialect)

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference
from openpyxl.utils import get_column_letter

# ─── 创建工作簿 ───
wb = Workbook()
ws = wb.active
ws.title = "销售数据"

# ─── 写入数据 ───
ws['A1'] = '产品名称'
ws['B1'] = '销量'
ws['C1'] = '金额'

# 追加数据
ws.append(['iPhone', 100, 899900])
ws.append(['MacBook', 50, 999800])

# ─── 单元格样式 ───
header_font = Font(name='微软雅黑', bold=True, size=12, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

for cell in ws[1]:  # 第一行 (表头)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center')

# ─── 合并单元格 ───
ws.merge_cells('A1:C1')
ws['A1'].alignment = Alignment(horizontal='center')

# ─── 列宽自动调整 ───
for col in ws.columns:
    max_length = max(len(str(cell.value or '')) for cell in col)
    ws.column_dimensions[get_column_letter(col[0].column)].width = max_length + 2

# ─── 图表 ───
chart = BarChart()
chart.title = "产品销售统计"
chart.y_axis.title = "金额 (元)"
data = Reference(ws, min_col=3, min_row=1, max_row=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=3)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "E2")

# ─── 保存 ───
wb.save('report.xlsx')

# ─── 读取 ───
wb = load_workbook('report.xlsx', data_only=True)  # data_only=True 获取计算结果而非公式
ws = wb.active
for row in ws.iter_rows(min_row=1, values_only=True):
    print(row)  # 元组形式输出

import pandas as pd

# ─── 读取 CSV ───
df = pd.read_csv('data.csv')
df = pd.read_csv('data.csv', encoding='utf-8')       # 指定编码
df = pd.read_csv('data.csv', delimiter='\t')          # 制表符分隔
df = pd.read_csv('data.csv', header=None)             # 无表头
df = pd.read_csv('data.csv', names=['a','b','c'])     # 自定义列名
df = pd.read_csv('data.csv', index_col='id')          # 指定索引列
df = pd.read_csv('data.csv', parse_dates=['date'])    # 解析日期
df = pd.read_csv('data.csv', dtype={'age': int})      # 指定数据类型
df = pd.read_csv('data.csv', na_values=['', 'NA'])    # 指定缺失值标记

# ─── 读取 Excel ───
df = pd.read_excel('report.xlsx')
df = pd.read_excel('report.xlsx', sheet_name='Sheet1')     # 指定工作表
df = pd.read_excel('report.xlsx', sheet_name=None)          # 读取所有工作表
df = pd.read_excel('report.xlsx', skiprows=2)               # 跳过前 2 行
df = pd.read_excel('report.xlsx', usecols='A:C')            # 只读 A-C 列

# ─── 写入 ───
df.to_csv('output.csv', index=False, encoding='utf-8-sig')  # utf-8-sig 含 BOM，Excel 兼容
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)