python读取xlsx文件

来源：互联网发布：淘宝女装店铺起名编辑：程序博客网时间：2024/05/15 22:54

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

import xlrdimport reimport sqlite3def 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  pdef 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()    returnif __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_完整

0 0