python操作excel
.获取excel文档内容blue.xlsx内容：1
2
3
4
5
6
7
8
9
# encoding : utf-8
import xlrd
xlsfile = r'/soft2/nba/blue.xlsx'
data = xlrd.open_workbook(xlsfile)
table = data.sheet_by_name(u'Sheet2')
nrows = table.nrows
print nrows
for i in range(nrows):
print table.row_values(i)
打印结果：
……
2.需求很简单：把其它部门整理好的EXCEL文件中的数据，获取出来映射到数据库里去。这里使用了第三包 xlrd 来处理excel。
mms.xlsx内容：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#coding:utf-8
import xlrd
ass ={'100.0':20,'101.0':21,'102.0':22,'103.0':23,'104.0':24,'105.0':25,'106.0':26,'107.0':27,'108.0':28,'109.0':29,'110.0':30,'111.0':31,'112.0':32,'113.0':33}
gss ={'101.0':21,'102.0':22,'103.0':23,'104.0':24,'105.0':25,'106.0':26,'107.0':27,'108.0':28,'109.0':29,'110.0':30,'111.0':31,'112.0':32,'113.0':33,'114.0':34}
#SQL = 'update 业绩表 set `ass` = %d,`score` = %d where `raw` = %d limit 1;'
wb = xlrd.open_workbook('/soft2/nba/mms.xlsx')
wb.sheet_names()
sh = wb.sheet_by_name(u'Sheet1')
sqlfile = open('/soft1/wb.sql','a')
totalsql = 0
ids = sh.col_values(0)
for k,v in enumerate(ids):
if isinstance(v,int) or isinstance(v,float):
v = int(v)
a = sh.cell(k,2).value
g = sh.cell(k,3).value
a = str(a)
g = str(g)
SQL = 'update 业绩表 set `id` = %d,`score` = %d where `raw` = %d limit 1;'
if a!='':
#编码转换(中文问题)
a = a.encode('gbk')
g = g.encode('gbk')
#print('%s-->%d-->%s-->%d--%d'%(a,ass,g,gss,v))
sql = SQL%(ass,gss,v)
totalsql = totalsql + 1
print(sql)
#写入文件
sqlfile.writelines(sql)
sqlfile.flush()
sqlfile.close()
print ('程序运行完毕，一共产生了%d条有效SQL。'%(totalsql))
打印结果：
以上在读取excel文件时，如果有多个文件，可以考虑使用线程来处理。先返回所目录下的所有excel文件：excel_dir = 'D:/tuan_team_back/'
excel_file_array =[]
#返回目录里的excel文件
#os.listdir(excel_dir)
for i in os.listdir(excel_dir):
f = excel_dir+i
if os.path.isfile(f):
t = os.path.splitext(f)
if t in ['.xls','.xlsx','.xlsm','.xltx','.xlsb']:
excel_file_array.append(f)
print(excel_file_array)
页:
[1]