openpyxl处理数据放入excel，并取出

 第一种：

import datetime

from flask import Flask,request
import openpyxl
import os

app = Flask(__name__)

filename = '测试.xlsx'
if os.path.exists(filename):
    print(f"文件： {filename} 已存在，无需再次创建文件")
else:
    wb = openpyxl.Workbook()  # 创建excel
    ws = wb.active
    wb.save(filename)


def to_Excel(excel_data, excel_filename):  
    to_wb = openpyxl.load_workbook(excel_filename)  # 文件已存在，不会自动创建文件，可追加
    to_ws = to_wb['Sheet']  # 创建子表
    max_row_num = to_ws.max_row    # 获得最大行数
    to_ws._current_row = max_row_num    # 将当前行设置为最大行数
    if max_row_num == 1:        # 添加表头,由于创建时自动增加第一行为空白，所以强制更改内容
        to_ws['A1'] = '用户名'
        to_ws['B1'] = '密码'
        to_ws['C1'] = '导入时间'
    to_ws.append([excel_data[0]["username"], 
                excel_data[0]["password"], 
                datetime.datetime.now()])
    to_wb.save(filename)


@app.route('/', methods=['post'])
def test_post():  
    # 获取表单上传的数据
    form = request.form
    username = form.get('username')
    password = form.get('password')
    data = [
        {"username": username, "password": password},
    ]
    to_Excel(data, filename)  # 运行时不可打开excel文件
    return 'None'


if __name__ == '__main__':
    app.run()

openpyxl从excel里拿出数据

wb = openpyxl.load_workbook('测试.xlsx')
ws = wb['Sheet']
max_row_num = ws.max_row
ws._current_row = max_row_num    # 最大行数
message_data_data = []

for row in ws.iter_rows(min_row=2, values_only=True):# 从第二行开始，且只返回单元格数据
    username = row[0]
    password = row[1]
    print(row)
    # row代表循环每行的数据，row[0]代表该行内第一列元素

第二种：

import openpyxl
import os
import json


# 将数据放入excel中，key作为表头传入，再循环导入相value的值
def data_to_Excel(excel_data, excel_filename):
    to_wb = openpyxl.load_workbook(excel_filename)
    to_ws = to_wb['Sheet']
    max_row_num = to_ws.max_row
    if max_row_num == 1:
        first_data_excel = []
        for i in excel_data:
            first_data_excel.append(i)
        for j in range(1, len(first_data_excel) + 1):
            to_ws.cell(row=1, column=j).value = first_data_excel[j - 1]
    main_data_excel = []
    for k in excel_data:
        main_data_excel.append(excel_data[k])
    to_ws.append(main_data_excel)
    to_wb.save(excel_filename)
    return 0


# 将数据从excel中取出，将表头取出作为key，将表头外的其他值取出作为value，组成字典并放入value,组成一条含有多个字典的列表，并将其转化为json形式输出
def message_data_Excel_to(excel_filename):
    to_wb = openpyxl.load_workbook(excel_filename)
    to_ws = to_wb['Sheet']
    max_row_num = to_ws.max_row
    max_col_num = to_ws.max_column
    to_ws._current_row = max_row_num
    message_data = []
    for row in to_ws.iter_rows(min_row=2, values_only=True):
        message_dict = {}
        for i in range(1, max_col_num + 1):
            first_excel_data = to_ws.cell(row=1, column=i).value
            message_dict.update({first_excel_data: row[i - 1]})
        message_data.append(message_dict)

    to_wb.save(excel_filename)
    return json.dumps(message_data)


# 创建excel文件，并检查文件是否存在
def create_file(file_name):
    file_path = os.path.join('D:\\PycharmProjects\\flask\\data', file_name)
    if os.path.exists(file_path):
        print(f"文件 {file_name} 已存在，无需再次创建文件。")
    else:
        to_wb = openpyxl.Workbook()
        to_wb.save(file_path)
        print(f"文件 {file_name} 已成功创建。")
    return file_path