python自动化办公要学多久-基于python实现自动化办公学习笔记三

Excel

（1）写xls文件

# 有序字典

from collections import OrderedDict

# 存储数据

from pyexcel_xls import save_data

def makeExcelFile(path, data):

dic = OrderedDict()

for sheetNum, sheetValue in data.items():

d = {}

d[sheetNum] = sheetValue

dic.update(d)

save_data(path, dic)

path = r"E:\Python\py17\automatictext\b.xlsx"

makeExcelFile(path, {"表1": [[1, 2, 3], [4, 5, 6], [7, 8, 9]],

"表2": [[11, 22, 33], [44, 55, 66],

[77, 88, 99]]})

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

（2）读xls文件

from openpyxl.reader.excel import load_workbook

def readXlsxFile(path):

file = load_workbook(filename=path)

print(file.get_sheet_names)

sheets = file.get_sheet_names()

sheet = file.get_sheet_by_name(sheets[0])

for lineNum in range(1, sheet.max_row + 1):

lineList = []

print(sheet.max_row, sheet.max_column)

for columnNum in range(1, sheet.max_column + 1):

# 拿数据

value = sheet.cell(row=lineNum,

column=columnNum).value

if value != None:

lineList.append(value)

print(lineList)

path = r"E:\Python\py17\automatictext\001.xlsx"

readXlsxFile(path)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

（3）返回xls数据

from openpyxl.reader.excel import load_workbook

def readXlsxFile(path):

dic = {}

file = load_workbook(filename=path)

sheets = file.get_sheet_names()

print(len(sheets))

for sheetName in sheets:

sheet = file.get_sheet_by_name(sheetName)

# 一张表的所有数据

sheetInfo = []

for lineNum in range(1, sheet.max_row + 1):

lineList = []

for columnNum in range(1, sheet.max_column + 1):

value = sheet.cell(row=lineNum,

column=columnNum).value

lineList.append(value)

sheetInfo.append(lineList)

# 将一张表的数据存到字典

dic[sheetName] = sheetInfo

return dic

path = r"E:\Python\py17\automatictext\001.xlsx"

dic = readXlsxFile(path)

print(dic)

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

# 有序字典

from collections import OrderedDict

# 读取数据

from pyexcel_xls import get_data

def readXlsAndXlsxFile(path):

dic = OrderedDict(http://www.my516.com)

# 抓取数据

xdata = get_data(path)

for sheet in xdata:

dic[sheet] = xdata[sheet]

return dic

path = r"E:\Python\py17\automatictext\001.xlsx"

dic = readXlsAndXlsxFile(path)

print(dic)

print(len(dic))

---------------------