day16excel和csv文件操作作业

1. 已知一个列表，将列表中的数据写入到’商品.xlsx’文件中的’学生表’中：

import openpyxl
datas = [
         {'姓名': '小明', '年龄': 20, '性别': '男'},
         {'姓名': '小花', '年龄': 30, '性别': '女'},
         {'姓名': '张三', '年龄': 35, '性别': '男'},
         {'姓名': '李四', '年龄': 18, '性别': '女'}
]
sheet_name = '学生表'
wb = openpyxl.load_workbook(r'.\files_operate\商品.xlsx')
if sheet_name not in wb.sheetnames:
    student_sheet = wb.create_sheet(sheet_name)
else:
    student_sheet = wb[sheet_name]

first_student = datas[0]
keys = []
count = 0
for key in first_student:
    count += 1
    keys.append(key)
    student_sheet.cell(1, count).value = key
row = 1
for dict1 in datas:
    col = 1
    for value in keys:
        student_sheet.cell(1+row, col).value = dict1[value]
        col += 1
    row += 1

wb.save(r'.\files_operate\商品.xlsx')

2. 将一个csv文件中的内容读出来写到一个excel文件中；将一个excel文件内容读出来，写入csv文件

import csv
import openpyxl

new_list = list(csv.reader(open(r'.\files_operate\北京高档酒店价格分析.csv', encoding='utf-8')))
print(new_list)
sheet_name = '酒店数据'
wb = openpyxl.load_workbook(r'.\files_operate\new_北京高档酒店价格分析.xlsx')
if sheet_name not in wb.sheetnames:
    hotel_sheet = wb.create_sheet(sheet_name)
else:
    hotel_sheet = wb[sheet_name]

row = 0
for list1 in new_list:
    col = 1
    for item in list1:
        hotel_sheet.cell(1+row, col).value = item
        col += 1
    row += 1

wb = openpyxl.load_workbook(r'.\files_operate\new_北京高档酒店价格分析.xlsx')
m_row = hotel_sheet.max_row
m_col = hotel_sheet.max_column
result = []
for row in range(1, m_row+1):
    aad_list = []
    for col in range(1, m_col+1):
        cell = hotel_sheet.cell(row, col)
        aad_list.append(cell.value)
    result.append(aad_list)
list_writer = csv.writer(open(r'.\files_operate\new_北京高档酒店价格分析.csv', 'w', encoding='utf-8', newline=''))
list_writer.writerows(result)
wb.save(r'.\files_operate\new_北京高档酒店价格分析.xlsx')