python读取xlsx文件

发布时间：2019-09-07 08:08:10编辑：auto阅读（1736）

    我是在win7下读取的。 python版本是：3.5

    import xlrd
import re
import sqlite3

def read_xlsx():
    workbook = xlrd.open_workbook('E:\20160322.xlsx')
    booksheet = workbook.sheet_by_name('Sheet1')
    p = list()
    for row in range(booksheet.nrows):
            row_data = []
            for col in range(booksheet.ncols):
                    cel = booksheet.cell(row, col)
                    val = cel.value
                    try:
                            val = cel.value
                            val = re.sub(r'\s+', '', val)
                    except:
                            pass

                    if type(val) == float:
                        val = int(val)
                    else:
                        val = str( val )
                    row_data.append(val)
            p.append(row_data)

    return  p

def operat_sqlite(*data):
    # print(type(data))
    # print(data)
    print(data[0])
    try:
        conn = sqlite3.connect('E:\list.db')
    except:
        print('open sqlite3 failed.')
        return
    else:  #操作数据库
         c = conn.cursor()
         for item in data:
             for i in range(len(item)):
                 DLDMv = item[i][1]
                 LDDMv = item[i][3]
                 LDMCv = item[i][2]
                 FHSSLXv = item[i][5]
                 XZQHv = item[i][6]
                 try:
                    #creat sql
                     c.execute("insert into roadkey (DLDM, LDDM, LDMC, FHSSLX, XZQH) values (?, ?, ?, ?, ?)", (DLDMv, LDDMv, LDMCv, FHSSLXv, XZQHv))
                     conn.commit()
                 except:
                     print('insert roadky failed ')
                     pass
                 print(i)
                 print(item[i])
         conn.close()

    return

if __name__ == '__main__':
   data_list =  list()
   data_list = read_xlsx()
   operat_sqlite(data_list)

    1. 代码功能：读取xlsx文件内容，插入到sqlite3数据库里面。

    2. 安装xlrd： pip install xlrd。 

    sqlite3已经有库了。。不用安装，直接使用。


    参考网址：Python xlsx读取

                        python 操作Excel读写

                       python开发_sqlite3_完整


关键字：

上一篇： python实现一个完整的blog网站

下一篇： python中动态加载模块和类方法实现