python的excel库_Python-excel相关库

1、xlrd库

例1：

#打开工作薄aaa，将aaa中的所有内容读入到列表list，并打印list

import xlrd #导入xlrd

book = xlrd.open_workbook("D:\\OneDrive\\python\\xlrd\\learn_xlrd.xlsx") #打开excel工作薄

print("The number of worksheets is {0}".format(book.nsheets)) #excel工作薄中表的数量

print("Worksheet name(s): {0}".format(book.sheet_names())) #excel工作薄中各表的名称

list = []

for i in range(book.nsheets):

print("-----{0}-----".format(i))

sh = book.sheet_by_index(i)

print("{0} {1} {2}".format(sh.name, sh.nrows, sh.ncols)) #.nrows总行数，.ncols总列数

for rx in range(sh.nrows):

list.append(sh.row(rx))

print(sh.row(rx)) #打印第rx行的内容

print(r"++++++++++")

for n in list:

print(n)

结果--->

The number of worksheets is 2

Worksheet name(s): ['标投资记录', '测试']

-----0-----

标投资记录 5 4

[text:'投资用户', text:'手机号', text:'真实姓名', text:'日期']

[text:'golplaygirl', text:'13867467906', text:'朱小波', xldate:43158.69649328704]

[text:'zhongmei', text:'13570729082', text:'钟肖媚', xldate:43160.69649328704]

[empty:'', empty:'', empty:'', empty:''] #表格中间有的空格不会被忽略！！！

[text:'maggie1112', text:'13537753062', text:'何远均', xldate:43161.69649328704]

++++++++++

-----1-----

测试 5 2

[text:'投标类型', text:'总投资额']

[text:'鸡宝宝1月', number:3200.0]

[text:'鸡宝宝1月', number:14468.0]

[empty:'', empty:'']

[text:'鸡宝宝1月', number:2294.0]

++++++++++

[text:'投资用户', text:'手机号', text:'真实姓名', text:'日期']

[text:'golplaygirl', text:'13867467906', text:'朱小波', xldate:43158.69649328704] #表格中的日期以float格式读取

[text:'zhongmei', text:'13570729082', text:'钟肖媚', xldate:43160.69649328704]

[empty:'', empty:'', empty:'', empty:'']

[text:'maggie1112', text:'13537753062', text:'何远均', xldate:43161.69649328704]

[text:'投标类型', text:'总投资额']

[text:'鸡宝宝1月', number:3200.0]

[text:'鸡宝宝1月', number:14468.0]

[empty:'', empty:'']

[text:'鸡宝宝1月', number:2294.0]

例2：

from xlrd import open_workbook

wb = open_workbook('C:\\Users\\ye\\Downloads\\tutorial-master\\students\\xlrd\\simple.xls')

for s in wb.sheets(): #简化循环条件

print('Sheet:',s.name)

for row in range(s.nrows):

values = []

for col in range(s.ncols):

values.append(s.cell(row,col).value)

print(','.join(values))

print()

结果--->

Sheet: Sheet1

S1R1CA,S1R1CB,S1R1CC

S1R2CA,S1R2CB,S1R2CC

S1R3CA,S1R3CB,S1R3CC

Sheet: Sheet2

S2R1CA,S2R1CB,S2R1CC

S2R2CA,S2R2CB,S2R2CC

S2R3CA,S2R3CB,S2R3CC

例3，循环遍历工作薄的3种方法

from xlrd import open_workbook

book = open_workbook('C:\\Users\\ye\\Downloads\\tutorial-master\\students\\xlrd\\simple.xls')

print("0 book.nsheets is {0}".format(book.nsheets))

for sheet_index in range(book.nsheets): #方法1

print("1",book.sheet_by_index(sheet_index))

print("2",book.sheet_names())

for sheet_name in book.sheet_names(): #方法2

print("3",book.sheet_by_name(sheet_name))

for sheet in book.sheets(): #方法3

print("4",sheet)

结果--->

0 book.nsheets is 2

1 #方法1

2 ['Sheet1', 'Sheet2']

1

2 ['Sheet1', 'Sheet2']

3 #方法2

3

4 #方法3

4

例4：循环读取工作表的每一个单元格

for row_index in range(sheet.nrows): #循环每一行

for col_index in range(sheet.ncols): #循环每一列

print('cellname:',cellname(row_index,col_index)) #读取每一个单元格的位置

print('cellvalue:',sheet.cell(row_index,col_index).value) #读取每一个单元格的值

print("-"*10)

结果--->

cellname: A1

cellvalue: S1R1CA

----------

cellname: B1

cellvalue: S1R1CB

例5：一次性读取整行或整列数据

sheet0 = book.sheet_by_index(0)

print(sheet0.row(0)) ---> [text:'S1R1CA', text:'S1R1CB', text:'S1R1CC']

print(sheet0.row_values(0)) ---> ['S1R1CA', 'S1R1CB', 'S1R1CC']

print(sheet0.col(0)) ---> [text:'S1R1CA', text:'S1R2CA']

print(sheet0.col_values(0)) ---> ['S1R1CA', 'S1R2CA']

例6：读取指定单元格的位置

from xlrd import cellname, cellnameabs, colname

print(cellname(0,0),cellname(10,10),cellname(100,100)) ---> A1 K11 CW101

print(cellnameabs(3,1),cellnameabs(41,59),cellnameabs(265,358)) ---> $B$4 $BH$42 $MU$266

print(colname(0),colname(10),colname(100)) ---> A K CW

例7：单元格中时间类型数值的读取

from datetime import date,datetime,time

from xlrd import open_workbook,xldate_as_tuple

book = open_workbook('C:\\Users\\ye\\Downloads\\tutorial-master\\students\\xlrd\\types.xls')

sheet = book.sheets()[0]

date_value = xldate_as_tuple(sheet.cell(3,2).value,book.datemode)

print(sheet.cell(3,2).value) ---> 39890.0

print(date_value) ---> (2009, 3, 18, 21, 6, 44)

print(datetime(*date_value)) ---> 2009-03-18 21:06:44

print(date(*date_value[:3])) ---> 2009-03-18

print(time(*time_value[3:])) ---> 21:06:44

2、xlwt库

例8：新建工作薄+工作表，写入数据并保存

from tempfile import TemporaryFile

from xlwt import Workbook #导入xlwt库

book = Workbook() #新建工作薄

sheet1 = book.add_sheet('Sheet 1') #新建工作表sheet1

book.add_sheet('Sheet 2')

sheet1.write(0,0,'A1') #在sheet1的（0，0）单元格写入'A1'

row1 = sheet1.row(1) #指定第2行

row1.write(0,'A2') #在第2行第一格写入'A2'，=sheet1.row(1).write(0,'A2')

sheet1.col(0).width = 10000 #设定第1列宽度为10000

sheet2 = book.get_sheet(1)

sheet2.row(0).write(0,'Sheet 2 A1')

sheet2.flush_row_data()

sheet2.col(0).hidden = True #隐藏第1列

book.save('simple.xls') #保存

book.save(TemporaryFile())

例9：在同一个单元格内覆盖写入数据

from xlwt import Workbook

book = Workbook()

sheet1 = book.add_sheet('sheet 1',cell_overwrite_ok=True) #在同一个单元格需要重复写入，必须把cell_overwrite_ok的默认值改为True,否则系统报错

sheet1.write(0,0,'original')

sheet = book.get_sheet(0)

sheet.write(0,0,'new')

book.save('1.xls')

例10：设置单元格格式

from datetime import date

from xlwt import Workbook, easyxf #关键导入easyxf

book = Workbook()

sheet = book.add_sheet('A Date')

sheet.write(1,1,date(2009,3,18),easyxf(

'font: name Arial;' #字体

'borders: left thick, right thick, top thick, bottom thick;' #单元格边框

'pattern: pattern solid, fore_colour red;', #单元格背景

num_format_str='YYYY-MM-DD' #数据显示为日期

))

book.save('date.xls') #保存

例11：总结

import xlwt

from datetime import datetime

style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',num_format_str='#,##0.00') #预先设置格式

style1 = xlwt.easyxf(num_format_str='yyyy/m/d h:mm;@')

wb = xlwt.Workbook()

ws = wb.add_sheet('A Test Sheet')

ws.write(0, 0, 1234.56, style0) #写入数据并设置格式

ws.write(1, 0, datetime.now(),style1) #写入时间

ws.write(2, 0, 1)

ws.write(2, 1, 1)

ws.write(2, 2, xlwt.Formula("A3+B3")) #写入公式

wb.save('example.xls')

result

3、案例

例1：将表格2的内容复制到表格1

#导入模块

import xlrd

#import xlwt #不导入也没什么影响

from xlutils.copy import copy

#将工作薄2的内容复制到列表

copy_wb = xlrd.open_workbook("2.xls",formatting_info=True) #打开工作薄2 ，注意一定要有formatting_info=True

copy_ws = copy_wb.sheets()[0]

list1 = []

for i in range(copy_ws.nrows):

list1.append(copy_ws.row_values(i)) #利用for循环将内容依次写入列表

#查询工作薄1已有内容的行数

to_wb = xlrd.open_workbook("1.xls",formatting_info=True) #打开工作薄1

to_ws = to_wb.sheet_by_index(0)

row_num = to_ws.nrows

#关键，利用xlutils的copy行数

new_wb = copy(to_wb) #将工作薄1复制到内存

new_ws = new_wb.get_sheet(0)

#将列表的内容复制到工作薄1

for j in range(len(list1)):

for k in range(len(list1[j])): #循环列表，把值写入目标工作薄

new_ws.write(row_num,k,list1[j][k])

row_num += 1 #工作表行数+1

new_wb.save("1.xls") #保存工作薄

print("SAVE SUCCESS!!!") 成功！