java程序excel导入数据库 java将excel文件导入数据库

转载

langrisser 2024-05-31 09:56:57

文章标签 java程序excel导入数据库 python sql mysql 文章分类 Java 后端开发

1 运行环境

开发环境清单:

IDE:Pycharm(已试验,IDLE也可以直接运行)
Python: 3.7
Mysql: 8.0.18
Pymysql:0.9.3
xlrd: 1.2.0
xlwt: 1.3.0

2 代码展示

直接引用别人的代码,在下是不屑的.以下代码纯手敲,跑起来问题不大:

import xlrd
import xlwt
import pymysql


class AddressBook:
    def __init__(self):
        self.contacts = None


# 添加联系人
def add_contact():
    sql_str = '''
    insert into contact ( contact_id, contact_name, contact_sex, contact_telephone, 
    contact_email, contact_address)
    values(%s,%s,%s,%s,%s,%s)
    '''
    contact = input_contact()
    if contact is not None:
        value = (contact.contact_id, contact.contact_name,
                 contact.contact_sex, contact.contact_telephone,
                 contact.contact_email, contact.contact_address)
        mysql_manager = MysqlManager(sql_str, value)
        mysql_manager.update()
        print("新增 ==> 操作完毕")
    else:
        return


# 导入数据的添加方法
def add_for_contact(contact):
    sql_str = '''
    insert into contact ( contact_id, contact_name, contact_sex, contact_telephone, 
    contact_email, contact_address)
    values(%s,%s,%s,%s,%s,%s)
    '''
    if check_repeat(contact.contact_id[0]):
        print("编号 [" + str(int(contact.contact_id[0])) + "]重复 ==> 已取消操作 ")
        return
    value = (contact.contact_id, contact.contact_name,
             contact.contact_sex, contact.contact_telephone,
             contact.contact_email, contact.contact_address)
    mysql_manager = MysqlManager(sql_str, value)
    mysql_manager.update()
    print("新增 ==> 操作完毕")


# 删除联系人
def del_contact():
    contact_id = input("请输入待删除的联系人编号: ")
    del_sql = '''
    delete from contact where CAST(contact_id AS CHAR)=%s
    '''
    mysql_manager = MysqlManager(del_sql, contact_id)
    mysql_manager.update()
    print("删除 ==> 操作完毕")


# 编辑联系人
def edit_contact():
    sql_str = '''update contact set contact_id=%s ,contact_name=%s,contact_sex=%s,contact_telephone=%s ,
    contact_email=%s,contact_address=%s  where contact_id=%s '''
    contact_id = input("请输入要编辑的条目编号: ")
    contact = input_contact()
    if contact is not None:
        value = (contact.contact_id, contact.contact_name,
                 contact.contact_sex, contact.contact_telephone,
                 contact.contact_email, contact.contact_address, contact_id)
        print(value)
        mysql_manager = MysqlManager(sql_str, value)
        mysql_manager.update()
    print("编辑 ==> 操作完毕")


# 搜索联系人
def get_contact():
    value = input("请输入查询条件: ")
    sql_str = '''
      select * from contact where 
      cast(contact_id as CHAR ) = %s 
      or contact_name = %s 
      or contact_sex = %s 
      or contact_telephone = %s 
      or contact_email = %s 
      or contact_address = %s 
      '''
    value = (value, value, value, value, value, value)
    mysql_manager = MysqlManager(sql_str, value)
    print("根据条件[" + value.__getitem__(1) + "],查询到如下结果")
    query_result = mysql_manager.query()
    for result in query_result:
        print(result)
    print("查询 ==> 操作完毕")
    print()
    return query_result


# 展示所有联系人
def all_contact():
    all_sql = '''
    select * from contact
    '''
    mysql_manager = MysqlManager(all_sql, None)
    print("---------------------欢欢通讯录------------------------")
    query_result = mysql_manager.query()
    for result in query_result:
        print(result)
    print("-----------------------------------------------------")
    return query_result


# 导入联系人
def import_contact():
    # 指定工作簿
    book = xlrd.open_workbook("H:/20200621-Python-Excel-Test-V1.xlsx")
    # 指定工作表
    sheet = book.sheet_by_index(0)
    # 读取数据
    for i in range(1, sheet.nrows):
        contact_id, contact_name, contact_sex, contact_telephone, contact_email, contact_address = sheet.row_values(i)
        contact = Contact(contact_id, contact_name, contact_sex, contact_telephone,
                          contact_email, contact_address)
        add_for_contact(contact)
    print("数据导入完毕")


# 导出联系人
def export_contact():
    print("正在导出数据,loading...")
    # 创建工作簿
    work_book = xlwt.Workbook()
    # 创建工作表
    work_sheet = work_book.add_sheet('通讯录')
    style = xlwt.easyxf('font:bold on')
    work_sheet.col(3).width = 200 * 22
    work_sheet.col(4).width = 200 * 22
    work_sheet.col(5).width = 200 * 22
    # 写入表头
    work_sheet.write(0, 0, "序号", style)
    work_sheet.write(0, 1, "姓名", style)
    work_sheet.write(0, 2, "性别", style)
    work_sheet.write(0, 3, "手机号码", style)
    work_sheet.write(0, 4, "邮箱地址", style)
    work_sheet.write(0, 5, "通讯地址", style)
    # 写入具体数据
    query_result = all_contact()
    count = 1
    for result in query_result:
        work_sheet.write(count, 0, result[0])
        work_sheet.write(count, 1, result[1])
        work_sheet.write(count, 2, result[2])
        work_sheet.write(count, 3, result[3])
        work_sheet.write(count, 4, result[4])
        work_sheet.write(count, 5, result[5])
        count = count + 1
    work_book.save("H:/20200621-Python-Excel-Test-V2.xls")
    print("导出数据成功,请查看...")


def input_contact():
    contact_id = input("请输入编号: ")
    repeat = check_repeat(contact_id)
    if repeat:
        print("编号重复,已取消操作")
        return
    contact_name = input("请输入姓名: ")
    contact_sex = input("请输入性别: ")
    contact_telephone = input("请输入手机号: ")
    contact_address = input("请输入通讯地址: ")
    contact_email = input("请输入邮箱地址: ")
    contact = Contact(contact_id, contact_name, contact_sex, contact_telephone,
                      contact_email, contact_address)
    print("您所要添加的联系人信息如下:")
    print(contact.__dict__)
    return contact


# 如果传入的contact_id存在重复,那么返回True
def check_repeat(contact_id):
    sql_str = '''
    select contact_id from contact
    '''
    mysql_manager = MysqlManager(sql_str, None)
    query_result = mysql_manager.query()
    repeat = False
    for result in query_result:
        if int(result[0]) == int(contact_id):
            repeat = True
    return repeat


def exit_contact():
    print("当前已退出系统,欢迎下次使用")


class Contact:
    def __init__(self, contact_id, contact_name, contact_sex, contact_telephone,
                 contact_email, contact_address):
        self.contact_id = contact_id,
        self.contact_sex = contact_sex,
        self.contact_name = contact_name,
        self.contact_telephone = contact_telephone,
        self.contact_email = contact_email,
        self.contact_address = contact_address


class MysqlManager:
    def __init__(self, sql_str, value):
        self.sql = sql_str
        self.value = value

    def update(self):
        db = pymysql.connect('127.0.0.1', 'root', 'root', 'python_connection')
        cursor = db.cursor()
        cursor.execute(self.sql, self.value)
        execute_result = cursor.fetchall()
        cursor.close()
        db.commit()
        return execute_result

    def query(self):
        db = pymysql.connect('127.0.0.1', 'root', 'root', 'python_connection')
        cursor = db.cursor()
        cursor.execute(self.sql, self.value)
        execute_result = cursor.fetchall()
        cursor.close()
        db.commit()
        return execute_result


while True:
    functions = {'1': add_contact,
                 '2': del_contact,
                 '3': edit_contact,
                 '4': get_contact,
                 '5': all_contact,
                 '6': export_contact,
                 '7': import_contact,
                 '8': exit_contact}

    print("欢迎使用xx通讯录，小鹿等待您的吩咐... ...")
    print("1.增加联系人")
    print("2.删除联系人")
    print("3.编辑联系人")
    print("4.查询联系人")
    print("5.显示所有联系人")
    print("6.导出excel文件")
    print("7.导入excel文件")
    print("8.退出系统")
    command = input("请输入对应操作数字指令: ")
    func = functions[command]
    func()
    if command == str(8):
        break