|
1 |
$ pip install openpyxl |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
from openpyxl import Workbook wb = Workbook() # grab the active worksheet ws = wb.active # Data can be assigned directly to cells ws['A1'] = 42 # Rows can also be appended ws.append([1, 2, 3]) # Python types will automatically be converted import datetime ws['A2'] = datetime.datetime.now() # Save the file wb.save("sample.xlsx") |
openpyxl特点
openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，xls和xlsx之间转换容易 注意：如果文字编码是“gb2312” 读取后就会显示乱码，请先转成Unicode
1、openpyxl 读写单元格时，单元格的坐标位置起始值是（1,1），即下标最小值为1，否则报错！
|
1 2 3 4 5 6 7 8 9 |
tableTitle = ['userName', 'Phone', 'age', 'Remark'] # 维护表头 # if row < 1 or column < 1: # raise ValueError("Row or column values must be at least 1") # 如上，openpyxl 的首行、首列 是 （1,1）而不是（0,0），如果坐标输入含有小于1的值，提示 ：Row or column values must be at least 1，即最小值为1. for col in range(len(tableTitle)): c = col + 1 ws.cell(row=1, column=c).value = tableTitle[col] |
2、openpyxl 支持直接横纵坐标访问，如A1,B2...
|
1 |
ws['A4'] = 4 #直接赋值 |
openpyxl操作excel
Excel文件三个对象
workbook： 工作簿，一个excel文件包含多个sheet。
sheet：工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。
cell： 单元格，存储数据对象
1、新建表
A workbook至少创建一个worksheet.
通过openpyxl.workbook.Workbook.active()得到worksheet
wb = Workbook(encoding='UTF-8')
ws = wb.active
注意：
该方法使用_active_sheet_index属性, 默认会设置0，也就是第一个worksheet。除非手动修改，否则使用active方法得到都是第一个worksheet。
你也可以创建worksheets，通过 openpyxl.workbook.Workbook.create_sheet() 方法：
|
1 2 3 |
ws = wb.create_sheet("Mysheet") #插入到最后(default) #或者 ws = wb.create_sheet("Mysheet", 0) #插入到最开始的位置 |
创建的sheet的名称会自动创建，按照sheet，sheet1，sheet2自动增长，通过title属性可以修改其名称。
|
1 |
ws.title = "New Title" |
|
1 |
ws = wb.create_sheet(title="Pip") |
默认的sheet的tab是白色的，可以通过 RRGGBB颜色来修改sheet_properties.tabColor属性从而修改sheet tab按钮的颜色:
|
1 |
ws.sheet_properties.tabColor = "1072BA" |
当你设置了sheet的名称，可以将其看成workbook中的一个key。也可以使用openpyxl.workbook.Workbook.get_sheet_by_name() 方法
单元格赋值
|
1 2 3 4 |
#设定单元格的值，三种方式 sheet.cell(row=2,column=5).value=99 sheet.cell(row=3,column=5,value=100) ws['A4'] = 4 #write |
逐行写
|
1 2 3 4 5 |
ws.append(iterable) #添加一行到当前sheet的最底部（即逐行追加从第一行开始） iterable必须是list,tuple,dict,range,generator类型的。 1,如果是list,将list从头到尾顺序添加。 2，如果是dict,按照相应的键添加相应的键值。 ws.append([‘This is A1’, ‘This is B1’, ‘This is C1’]) ws.append({‘A’ : ‘This is A1’, ‘C’ : ‘This is C1’}) ws.append({1 : ‘This is A1’, 3 : ‘This is C1’}) |
2、读表操作
|
1 2 3 4 5 6 7 8 9 10 |
#打开文件： from openpyxl import load_workbook excel=load_workbook('E:/test.xlsx') #获取sheet： table = excel.get_sheet_by_name('Sheet1') #通过表名获取 #获取行数和列数： rows=table.max_row #获取行数 cols=table.max_column #获取列数 #获取单元格值： Data=table.cell(row=row,column=col).value #获取表格内容，是从第一行第一列是从1开始的，注意不要丢掉 .value |
|
1 2 3 4 5 6 7 8 9 10 11 |
#通过名字 ws = wb["frequency"] #等同于 ws2 = wb.get_sheet_by_name('frequency') #不知道名字用index sheet_names = wb.get_sheet_names() ws = wb.get_sheet_by_name(sheet_names[index])# index为0为第一张表 #或者 ws =wb.active # 等同于 ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表，默认0读第一个表 #活动表表名 wb.get_active_sheet().title |
单元格使用
|
1 2 3 4 |
c = ws['A4'] d = ws.cell(row = 4, column = 2) #行列读写 |
逐行读取
|
1 2 3 4 5 6 7 8 9 10 11 12 |
#逐行读 ws.iter_rows(range_string=None, row_offset=0, column_offset=0): range-string(string)-单元格的范围：例如('A1:C4') row_offset-添加行 column_offset-添加列 # 返回一个生成器, 注意取值时要用value,例如： for row in ws.iter_rows('A1:C2'): for cell in row: print cell #读指定行、指定列: rows=ws.rows#row是可迭代的 columns=ws.columns#column是可迭代的 #打印第n行数据 print rows[n]#不需要用.value print columns[n]#不需要用.value |
显示有多少张表
|
1 2 3 4 5 |
wb.get_sheet_names() #显示表名，表行数，表列数 print ws.title print ws.max_row print ws.max_column |
公式计算formulae
|
1 2 |
ws["A1"] = "=SUM(1, 1)" ws["A1"] = "=SUM(B1:C1)" |
3、操作实例
|
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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
#-*-coding:utf-8-*- # Time:2017/9/21 19:02 # Author:YangYangJun from openpyxl import Workbook from openpyxl.reader.excel import load_workbook import os import time def writeExcel(): # 获取文件路径 excelPath = os.path.join(os.getcwd(), 'ExcelData') print "****" print excelPath # 定义文件名称 # invalid mode ('wb') or filename: 'Excel2017-09-21_20:15:57.xlsx' 这种方式明明文件，会提示保存失败，无效的文件名。 # nameTime = time.strftime('%Y-%m-%d_%H:%M:%S') nameTime = time.strftime('%Y-%m-%d_%H-%M-%S') excelName = 'Excel' + nameTime + '.xlsx' ExcelFullName= os.path.join(excelPath,excelName) print ExcelFullName wb = Workbook() ws = wb.active tableTitle = ['userName', 'Phone', 'age', 'Remark'] # 维护表头 # if row < 1 or column < 1: # raise ValueError("Row or column values must be at least 1") # 如上，openpyxl 的首行、首列 是 （1,1）而不是（0,0），如果坐标输入含有小于1的值，提示 ：Row or column values must be at least 1，即最小值为1. for col in range(len(tableTitle)): c = col + 1 ws.cell(row=1, column=c).value = tableTitle[col] # 数据表基本信息 tableValues = [['张学友', 15201062100, 18, '测试数据！'], ['李雷', 15201062598, 19, '测试数据！'],['Marry', 15201062191, 28, '测试数据！']] for row in range(len(tableValues)): ws.append(tableValues[row]) #wb.save(ExcelFullName) wb.save(filename=ExcelFullName) return ExcelFullName def readExcel(ExcelFullName): wb = load_workbook(ExcelFullName) #wb = load_workbook(filename=ExcelFullName) # 获取当前活跃的worksheet,默认就是第一个worksheet #ws = wb.active # 当然也可以使用下面的方法 # 获取所有表格(worksheet)的名字 sheets = wb.get_sheet_names() print sheets # # 第一个表格的名称 sheet_first = sheets[0] # # 获取特定的worksheet # ws = wb.get_sheet_by_name(sheet_first) print "***" print sheet_first print ws.title print "^^^" # 获取表格所有行和列，两者都是可迭代的 rows = ws.rows print rows columns = ws.columns # 迭代所有的行 for row in rows: line = [col.value for col in row] print line # 通过坐标读取值 print ws['A1'].value # A表示列,1表示行 print ws.cell(row=1, column=1).value if __name__ == '__main__': ExcelFullName = writeExcel() readExcel(ExcelFullName) |
|
1 2 3 4 |
# 显示表名，表行数，表列数 print "Work Sheet Titile:", ws.title print "Work Sheet Rows:", ws.max_row print "Work Sheet Cols:", ws.max_column |
执行结果
