1

2

3

4

5

6

7

8

import xlrd

data = xlrd.open_workbook('abcd.xls') # 打开xls文件

table = data.sheets()[0] # 打开第一张表

nrows = table.nrows # 获取表的行数

for i in range(nrows): # 循环逐行打印

    if i == 0:# 跳过第一行

        continue

    print (table.row_values(i)[:13]) # 取前十三列

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

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

#coding=utf-8

#######################################################

#filename:test_xlrd.py

#author:defias

#date:xxxx-xx-xx

#function：读excel文件中的数据

#######################################################

import xlrd

#打开一个workbook

workbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')

#抓取所有sheet页的名称

worksheets = workbook.sheet_names()

print('worksheets is %s' %worksheets)

#定位到sheet1

worksheet1 = workbook.sheet_by_name(u'Sheet1')

"""

#通过索引顺序获取

worksheet1 = workbook.sheets()[0]

#或

worksheet1 = workbook.sheet_by_index(0)

"""

"""

#遍历所有sheet对象

for worksheet_name in worksheets:

worksheet = workbook.sheet_by_name(worksheet_name)

"""

#遍历sheet1中所有行row

num_rows = worksheet1.nrows

for curr_row in range(num_rows):

row = worksheet1.row_values(curr_row)

print('row%s is %s' %(curr_row,row))

#遍历sheet1中所有列col

num_cols = worksheet1.ncols

for curr_col in range(num_cols):

col = worksheet1.col_values(curr_col)

print('col%s is %s' %(curr_col,col))

#遍历sheet1中所有单元格cell

for rown in range(num_rows):

for coln in range(num_cols):

cell = worksheet1.cell_value(rown,coln)

print cell

"""

#其他写法：

cell = worksheet1.cell(rown,coln).value

print cell

#或

cell = worksheet1.row(rown)[coln].value

print cell

#或

cell = worksheet1.col(coln)[rown].value

print cell

#获取单元格中值的类型，类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

cell_type = worksheet1.cell_type(rown,coln)

print cell_type

"""

1

2

3

4

5

import xlwt

wbk = xlwt.Workbook()

sheet = wbk.add_sheet('sheet 1')

sheet.write(0,1,'test text')#第0行第一列写入内容

wbk.save('test.xls')

1

2

3

4

5

6

7

import xlrd

from xlutils.copy import copy

workbook = xlrd.open_workbook(u'有趣装逼每日数据及趋势.xls')

workbooknew = copy(workbook)

ws = workbooknew.get_sheet(0)

ws.write(3, 0, 'changed!')

workbooknew.save(u'有趣装逼每日数据及趋势copy.xls')

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

from openpyxl import Workbook

from openpyxl import load_workbook

from openpyxl.writer.excel import ExcelWriter 

workbook_ = load_workbook(u"新歌检索失败1477881109469.xlsx")

sheetnames =workbook_.get_sheet_names() #获得表单名字

print sheetnames

sheet = workbook_.get_sheet_by_name(sheetnames[0])

print sheet.cell(row=3,column=3).value

sheet['A1'] = '47'

workbook_.save(u"新歌检索失败1477881109469_new.xlsx") 

wb = Workbook()

ws = wb.active

ws['A1'] = 4

wb.save("新歌检索失败.xlsx")

1

2

3

4

5

6

7

import openpyxl

# 新建文件

workbook = openpyxl.Workbook()

# 写入文件

sheet = workbook.activesheet['A1']='A1'

# 保存文件

workbook.save('test.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

import xlsxwriter

def get_chart(series):

    chart = workbook.add_chart({'type': 'line'})

    for ses in series:

        name = ses["name"]

        values = ses["values"]

        chart.add_series({

            'name': name,

            'categories': 'A2:A10',

            'values':values

        })

    chart.set_size({'width': 700, 'height': 350})

    return chart

if __name__ == '__main__':

    workbook = xlsxwriter.Workbook(u'H5应用中心关键数据及趋势.xlsx')

    worksheet = workbook.add_worksheet(u"每日PV,UV")

    headings = ['日期', '平均值']

    worksheet.write_row('A1', headings)

    index=0

    for row in range(1,10):

        for com in [0,1]:

            worksheet.write(row,com,index)

            index+=1

    series = [{"name":"平均值","values":"B2:B10"}]

    chart = get_chart(series)

    chart.set_title ({'name': '每日页面分享数据'}) 

    worksheet.insert_chart('H7', chart)

    workbook.close()

openpyxl

1

2

3

4

5

6

7

8

9

import xlsxwriter as xw

#新建excel

workbook  = xw.Workbook('myexcel.xlsx')

#新建工作薄

worksheet = workbook.add_worksheet()

#写入数据

worksheet.wirte('A1',1)

#关闭保存

workbook.close()

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 xlsxwriter

import xlrd

#新建excel

workbook  = xlsxwriter.Workbook('广东.xlsx')

#新建工作薄

worksheet = workbook.add_worksheet()

count = 1

worksheet.write("A%s"%count,"公司名称")

worksheet.write("B%s"%count,"法人")

worksheet.write("C%s"%count,"电话")

worksheet.write("D%s"%count,"注册资金")

worksheet.write("E%s"%count,"注册时间")

count+=1

for i in range(1,153):

    data = xlrd.open_workbook('ah (%s).xls'%i) # 打开xls文件

    table = data.sheets()[0] # 打开第一张表

    nrows = table.nrows # 获取表的行数

    for i in range(nrows): # 循环逐行打印

        if i == 0:# 跳过第一行

            continue

        # print (table.row_values(i)[:5]) # 取前十三列

        print(count,table.row_values(i)[:5][0])

        #写入数据

        #设定第一列（A）宽度为20像素 A:E表示从A到E

        worksheet.set_column('A:A',30)

        worksheet.set_column('B:E',20)

        worksheet.write("A%s"%count,table.row_values(i)[:5][0])

        worksheet.write("B%s"%count,table.row_values(i)[:5][1])

        worksheet.write("C%s"%count,table.row_values(i)[:5][2])

        worksheet.write("D%s"%count,table.row_values(i)[:5][3])

        worksheet.write("E%s"%count,table.row_values(i)[:5][4])

        count+=1

#关闭保存

workbook.close()