python3 openpyxl 基础知识

from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
#加载execl工作簿
wb = load_workbook('F:\ks\ks.xlsx')

#打印使用wb的工作表名（方式一）
print(wb.sheetnames)
#方式二
for sheet in wb:
    print(sheet.title)
#创建一个工作表名为mysheet
mysheet = wb.create_sheet('mysheet')
print(wb.sheetnames)

#操作工作簿里的mysheet工作表(方式一)
#sheet3 = wb.get_sheet_by_name('mysheet')
#方式二
sheet4 = wb['mysheet']

#激活使用默认工作表
ws = wb.active
#打印表单对象
print(ws)
print(ws['A1'])
print(ws['A1'].value)

c = ws['B1']
#打印B1单元格行列值出来
print('Row{},Column{} is {}'.format(c.row,c.column,c.value))
#打印对应B1而且输出对应的值
print('Cel {} is {}'.format(c.coordinate,c.value))
#引用单元格方式3
print(ws.cell(row=1,column=2))
print(ws.cell(row=1,column=2).value)

#打印1到8行第2列的值步数为2
for i in range(1,8,2):
    print(i,ws.cell(row=i,column=2).value)

#取C列的单元格
colC = ws['C']
print(colC)
#打印取C3的值
print(colC[2].value)
#打印C3列对应的值
num=len(colC)
print(num)
for i in range(1,num+1):
    print(colC[i],colC[i].value)
#打印行的单元格
row3 = ws[3]
print(row3)

#输出1到2行的数据
# row_ragne = ws[1:2]
# for row in row_ragne:
#     for cell in row:
#         print(cell.value,end="\t")

#取C和D列的数据
col_range = ws['C:D']
for col in col_range:
    for cell in col:
       print(cell.value)

#获取1,2行前两项数据
for row in ws.iter_rows(min_row=1,max_row=2,max_col=2):
    for cell in row:
        print(cell.value)
#每一行都是元组中的元组
print(tuple(ws.rows))

#打印A1:C3区间每个单元格的值，先行后列
cell_range = ws['A1:C3']
for rowcell in cell_range:
    for cell in rowcell:
        print(cell.coordinate,cell.value)
    print('-----end of row--------')
打印工作表有多少行多少列
print('{}*{}'.format(ws.max_row,ws.max_column))
#可以字母转换为数字，数字转换为字母
from openpyxl.utils import get_column_letter,column_index_from_string
print(get_column_letter(2),get_column_letter(47),get_column_letter(900))
print(column_index_from_string('AAH'))