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 <xlrd.sheet.Sheet object at 0x00000000155DA048> #方法1
2 ['Sheet1', 'Sheet2']
1 <xlrd.sheet.Sheet object at 0x00000000155DA080>
2 ['Sheet1', 'Sheet2']
3 <xlrd.sheet.Sheet object at 0x00000000155DA048> #方法2
3 <xlrd.sheet.Sheet object at 0x00000000155DA080>
4 <xlrd.sheet.Sheet object at 0x00000000155DA048> #方法3
4 <xlrd.sheet.Sheet object at 0x00000000155DA080>
例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!!!") 成功！
网友评论