python(67)-excel-openpyxl-baseuse--读取

python读写excel有若干中方法，本篇用的是openpyxl库
三个层次：文件(workbook),表(sheet),单元格（row,colum）
1.读取文件
2.读取表格
3.读取单元格
4.转换索引，字母->数字，数字->字母
上代码

import openpyxl
from openpyxl.utils import get_column_letter,column_index_from_string
#数字变字母  字母变数字

def getworkbook():
    excelname="D:\\PythonPrj\\PyQt5\\pretest\\Excel\\src.xlsx"
    wb=openpyxl.load_workbook(excelname)
    print(wb.sheetnames)
def getsheet():  #读取表单名
     excelname = "D:\\PythonPrj\\PyQt5\\pretest\\Excel\\src.xlsx"
     wb = openpyxl.load_workbook(excelname)
     for sheet in wb:
         print(sheet.title)
def addsheet():    #添加表单
     excelname = "D:\\PythonPrj\\PyQt5\\pretest\\Excel\\src.xlsx"
     wb = openpyxl.load_workbook(excelname)
     mysheet =wb.create_sheet('mysheet')
def getrow_col_cell():
    excelname = "D:\\PythonPrj\\PyQt5\\pretest\\Excel\\src.xlsx"
    wb = openpyxl.load_workbook(excelname)
#    sheet0 = wb.get_sheet_by_name('遥测')  # 获取sheet对象‘遥测’
#    print(sheet0)

    ws = wb.active                                         #获取当前活跃的表单
    print(ws)
    #1.获取单个
    print(ws['A1'],ws['A1'].value)
    c = ws['B2']                                                  # 1.1单元格 cell的获取 通过索引
    print('Row{},Colums{} is{}'.format(c.row,c.column,c.value))
    print('Cell{} is {}'.format(c.coordinate,c.value))
  #  row6 = ws['6']  # 行
    # 3.excel 行列都是从1开始的 引用单元格
    print(ws.cell(row=1,column=2))                            #1.2 单元格 行列索引
    print(ws.cell(row=1,column=2).value)

    # 2.获取多个 两种方式：
    #2.1 指定行列的范围
    # for i in range(1,8,2):  #打印1-8  步数为2  即为1,3,5,7
    col_range = ws['B:C']   #指定列的范围
    row_range = ws[2:6]     #指定行的范围
    for col in col_range:   #列的访问
        for cell in col:
            print(cell.value)
    for row in row_range:   #行的访问
        for cell in row:
            print(cell.value)
    #2.2指定行列的范围
    for row in ws.iter_rows(min_row=1, max_row=2, max_col=2):
        for cell in row:
            print(cell)
    print(tuple(ws.rows))  #元组与列表的区别：元组里是常量不可变

    #3.读取总行数总列数
    print('{} *{}'.format(ws.max_row,ws.max_column))

    #4.行列坐标 数字->字母，字母->数字
    print(get_column_letter(2),get_column_letter(47),get_column_letter(900))
    print(column_index_from_string('AAH'))