python操作excel表格(数据库读取)

男娘i 2023-07-17 11:15 23阅读 0赞

今天学习了python操作excel表格，感觉很有意思，整理一下笔记

### 文章目录 ###

*   *   *   *  python读取文件
             *  使用python创建excel表格
             *  向excel表中手动写入数据
             *  将excel表中的数据存入数据库
             *   *  python连接mysql数据库
                 *  将excel中的数据写入数据库
             *  读取数据库中的数据将其写入excel表中
             *  完整代码

#### python读取文件 ####

**读取文件的方式1：直接读取，最后需要close操作**

def open_file2(file):
        f = open(file,"r")
        re = f.read()
        print(re)
        f.close()
    
    def open_file3(file):
        try:
            f = open(file,"r")
            re = f.read()
            print(re)
        except:
            pass
        finally:
            f.close()

在读取文件处理异常时，发现异常：

UnboundLocalError: local variable 'f' referenced before assignment

Traceback (most recent call last):
      File "/Users/apple/Downloads/11人脸识别/projects/PyDemo/excel/use_with.py", line 35, in <module>
        open_file_try()
      File "/Users/apple/Downloads/11人脸识别/projects/PyDemo/excel/use_with.py", line 26, in open_file_try
        f.close()
    UnboundLocalError: local variable 'f' referenced before assignment

在处理异常时，更改了一下文件名看是否直接显示except的内容，但是执行后发现抱了另一个错，原因是在open文件时没有找到文件，所以f相当于未定义，最后执行finally的内容的就找不到相应的f

**读取文件方式2:利用with读取，最后不用进行close操作：**

def open_file(file):
        with open(file,"r") as f:
            re = f.read()
            print(re)

使用with时，返回了一个上下文管理器,执行管理器的enter方法，如果with语句设置了目标对象，则把enter方法的返回值赋给目标对象，执行with中的代码块

#### 使用python创建excel表格 ####

下载相关库：

pip install openpyxl

from openpyxl import Workbook
    
    class Client(object):
        def __init__(self):
            #创建excel工作表
            self.wb = Workbook()
            #创建excel表里的不同表单
            self.ws = self.wb.active
            
            #创建表单的三种方式
            self.ws.title = "表单1"
            self.ws_two = self.wb.create_sheet("表单2")
            self.ws_three = self.wb.create_sheet()
    
        def do(self):
      		#存储excel表到相应路径
            self.wb.save("./static/one.xlsx")
    if __name__ == '__main__':
        client  = Client()
        client.do()

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70]  
打开生成的excel文件，可以看出下面有三个表单，其中第三个表单未命名所以显示默认名字

#### 向excel表中手动写入数据 ####

from openpyxl.styles import Font, colors
    from openpyxl.drawing.image import Image

#手动设置内容
            self.ws['A1'] = "学号"
            self.ws['B1'] = "姓名"
            self.ws['C1'] = "成绩"
            self.ws['A2'] = "12345"
    
            #设置字体，颜色
            font = Font(sz = 18,color=colors.RED)
            self.ws['A2'].font = font
    
            #循环赋值
            i = 2
            for row in self.ws['A3':'C5']:
                for cell in row:
                    cell.value = i
                    i +=2
    
            #插入图片
            image = Image("./static/shishi.png")
            self.ws.add_image(image,'E5')
    
            #合并/拆分单元格
            self.ws.merge_cells('A8:D9')
            self.ws.merge_cells('A11:D12')
            self.ws.unmerge_cells('A11:D12')
            #拆分单元格只能拆分之前已经合并过的

效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 2]

完整代码：

from openpyxl import Workbook
    from openpyxl.styles import Font, colors
    from openpyxl.drawing.image import Image
    class Client(object):
    
        def __init__(self):
    
            #创建excel工作表
            self.wb = Workbook()
            #创建excel表里的不同表单
            self.ws = self.wb.active
            self.ws.title = "表单1"
            self.ws_two = self.wb.create_sheet("表单2")
            self.ws_three = self.wb.create_sheet()
    
        def do(self):
    
            #手动设置内容
            self.ws['A1'] = "学号"
            self.ws['B1'] = "姓名"
            self.ws['C1'] = "成绩"
            self.ws['A2'] = "12345"
    
            #设置字体，颜色
            font = Font(sz = 18,color=colors.RED)
            self.ws['A2'].font = font
    
            #循环赋值
            i = 2
            for row in self.ws['A3':'C5']:
                for cell in row:
                    cell.value = i
                    i +=2
    
            #插入图片
            image = Image("./static/shishi.png")
            self.ws.add_image(image,'E5')
    
            #合并/拆分单元格
            self.ws.merge_cells('A8:D9')
            self.ws.merge_cells('A11:D12')
            self.ws.unmerge_cells('A11:D12')
    
            self.wb.save("./static/one.xlsx")
    
    if __name__ == '__main__':
        client  = Client()
        client.do()

#### 将excel表中的数据存入数据库 ####

表的数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 3]

在数据库中设定好相应参数：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 4]

##### python连接mysql数据库 #####

from openpyxl import Workbook, load_workbook
    import MySQLdb
    
    #数据库连接
        def get_conn(self):
            conn = MySQLdb.connect(
                db = "user_grade",#数据库名称
                host = "localhost",#主机地址
                port = 3306,#端口号
                user = "root",#db登录用户名
                password = "root",#db登录密码
                charset = "utf8"
            )
            return conn

##### 将excel中的数据写入数据库 #####

#写数据到数据库
        def read_excel_to_db(self):
            #获取要读的excel表的表
            wb = load_workbook("./static/two.xlsx")
            name = wb.get_sheet_names()#获取所有的表单名，这里只有一个
            print(name)#成绩统计
    
            ws = wb.active
            ws = wb[name[0]]
    
            for (i, row) in enumerate(ws.rows):
                if i < 1:
                    continue
                com_org = ws['A{0}'.format(i+1)].value #数据从A2开始
                os = ws['B{0}'.format(i+1)].value
                micro_com = ws['C{0}'.format(i+1)].value
                mobile = ws['D{0}'.format(i+1)].value
    
                conn = self.get_conn()
                cursor = conn.cursor()
                sql = 'insert into `computer`(`com_org`,`os`,`micro_com`,`mobile`) VALUES ({com_org},{os},{micro_com},{mobile})'\
                    .format(com_org=com_org,
                            os = os,
                            micro_com =  micro_com,
                            mobile = mobile)
                cursor.execute(sql)#执行插入的sql语句
                conn.autocommit(True)#提交

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 5]

#### 读取数据库中的数据将其写入excel表中 ####

def export_from_db_to_excel(self):
            #查询数据
            conn = self.get_conn()
            cursor = conn.cursor()
            sql = 'select `com_org`,`os`,`micro_com`,`mobile` from `computer`'
            cursor.execute(sql)
            rows = cursor.fetchall()
            #建立导出数据的excel表
            wb = Workbook()
            ws = wb.active
            ws['A1'] = "计算机组成原理"
            ws['B1'] = '操作系统'
            ws['C1'] = "微机原理"
            ws['D1'] = "移动设备开发应用"
    
            #将数据写入表中
            row_id = 1
            for (i,row) in enumerate(rows):
                ws['A{0}'.format(row_id+1)] = row[0]
                ws['B{0}'.format(row_id+1)] = row[1]
                ws['C{0}'.format(row_id+1)] = row[2]
                ws['D{0}'.format(row_id+1)] = row[3]
                row_id += 1
                ''' ( ws['A{0}'.format(row_id+1)], ws['B{0}'.format(row_id+1)], ws['C{0}'.format(row_id+1)], ws['D{0}'.format(row_id+1)] ) = row '''
                
            wb.save("./static/export_data4.xlsx")

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 6]

#### 完整代码 ####

from openpyxl import Workbook, load_workbook
    import MySQLdb
    
    from openpyxl.styles import Font, colors
    from openpyxl.drawing.image import Image
    
    class Client(object):
    
        def __init__(self):
    
            #创建excel工作表
            self.wb = Workbook()
            #创建excel表里的不同表单
            self.ws = self.wb.active
            self.ws.title = "表单1"
            self.ws_two = self.wb.create_sheet("表单2")
            self.ws_three = self.wb.create_sheet()
    
    
        #数据库连接
        def get_conn(self):
            conn = MySQLdb.connect(
                db = "user_grade",#数据库名称
                host = "localhost",#主机地址
                port = 3306,#端口号
                user = "root",#db登录用户名
                password = "root",#db登录密码
                charset = "utf8"
            )
            return conn
    
        def export_from_db_to_excel(self):
            #查询数据
            conn = self.get_conn()
            cursor = conn.cursor()
            sql = 'select `com_org`,`os`,`micro_com`,`mobile` from `computer`'
            cursor.execute(sql)
            rows = cursor.fetchall()
            #建立导出数据的excel表
            wb = Workbook()
            ws = wb.active
            ws['A1'] = "计算机组成原理"
            ws['B1'] = '操作系统'
            ws['C1'] = "微机原理"
            ws['D1'] = "移动设备开发应用"
    
            #将数据写入表中
            row_id = 1
            for (i,row) in enumerate(rows):
                ws['A{0}'.format(row_id+1)] = row[0]
                ws['B{0}'.format(row_id+1)] = row[1]
                ws['C{0}'.format(row_id+1)] = row[2]
                ws['D{0}'.format(row_id+1)] = row[3]
                row_id += 1
                ''' ( ws['A{0}'.format(row_id+1)], ws['B{0}'.format(row_id+1)], ws['C{0}'.format(row_id+1)], ws['D{0}'.format(row_id+1)] ) = row '''
    
            wb.save("./static/export_data4.xlsx")
    
    
        #写数据到数据库
        def read_excel_to_db(self):
            #获取要读的excel表的表
            wb = load_workbook("./static/two.xlsx")
            name = wb.get_sheet_names()#获取所有的表单名，这里只有一个
            print(name)#成绩统计
    
            ws = wb.active
            ws = wb[name[0]]
    
            for (i, row) in enumerate(ws.rows):
                if i < 1:
                    continue
                com_org = ws['A{0}'.format(i+1)].value #数据从A2开始
                os = ws['B{0}'.format(i+1)].value
                micro_com = ws['C{0}'.format(i+1)].value
                mobile = ws['D{0}'.format(i+1)].value
    
                conn = self.get_conn()
                cursor = conn.cursor()
                sql = 'insert into `computer`(`com_org`,`os`,`micro_com`,`mobile`) VALUES ({com_org},{os},{micro_com},{mobile})'\
                    .format(com_org=com_org,
                            os = os,
                            micro_com =  micro_com,
                            mobile = mobile)
                cursor.execute(sql)#执行插入的sql语句
                conn.autocommit(True)#提交
    
        def do(self):
    
            #手动设置内容
            self.ws['A1'] = "学号"
            self.ws['B1'] = "姓名"
            self.ws['C1'] = "成绩"
            self.ws['A2'] = "12345"
    
            #设置字体，颜色
            font = Font(sz = 18,color=colors.RED)
            self.ws['A2'].font = font
    
            #循环赋值
            i = 2
            for row in self.ws['A3':'C5']:
                for cell in row:
                    cell.value = i
                    i +=2
    
            #插入图片
            image = Image("./static/shishi.png")
            self.ws.add_image(image,'E5')
    
            #合并/拆分单元格
            self.ws.merge_cells('A8:D9')
            self.ws.merge_cells('A11:D12')
            self.ws.unmerge_cells('A11:D12')
    
            self.wb.save("./static/one.xlsx")
    
    
    
    if __name__ == '__main__':
        client  = Client()
        #client.do()
        #client.read_excel_to_db()
        client.export_from_db_to_excel()

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70]: /images/20230528/fad6c2afbc274cf1bda74f243dc08fda.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 1]: /images/20230528/8b60015f2e7f40d6accca62927416a28.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 2]: /images/20230528/a30d608adbf844de9d6ada7fb81cb20e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 3]: /images/20230528/51cf15214a8d4d1c997b64e48611f528.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 4]: /images/20230528/6e4d9dcb5ab94a5aab0284488c0e4f79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 5]: /images/20230528/b7627556ade746b38e47753acc49a74e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2F5YW5nYW5uOTE1_size_16_color_FFFFFF_t_70 6]: /images/20230528/8f6de32c5b1e4a7f9084fb4dced28506.png