【Python模块】os, time, openpyxl

from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook

import os
import time
import random

"""
openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，
xls和xlsx之间转换容易 注意：如果文字编码是“gb2312” 读取后就会显示乱码，
请先转成Unicode
"""

def writeExcel():
    print(os.getcwd())
    excelPath = os.path.join(os.getcwd(), 'ExcelData')
    print("****"*20)
    print(excelPath)
    nameTime = time.strftime('%Y-%m-%d_%H-%M-%S')
    excelName = 'Excel' + nameTime + '.xlsx'
    ExcelFullName = os.path.join(excelPath, excelName)
    print(ExcelFullName)

    wb = Workbook()#Workbook()构建器创建一个workbook(wb)

    ws = wb.active #ws应该是工作薄wb(workbook)的当前工作ws(worksheet)
    #表头
    tableTitle = ['userName', 'Phone', 'age', 'Remark']

    for col in range(len(tableTitle)):
        c = col + 1
        ws.cell(row=1, column=c).value = tableTitle[col]#cell应该是单元格

    tableValues = [['张学友', 15201062100, 18, '测试数据！'],
                   ['李雷', 15201062598, 19, '测试数据！'],
                   ['Marry', 15201062191, 28, '测试数据！']]

    for row in range(len(tableValues)):
        ws.append(tableValues[row])
    """
    ws.append(iterable)
    #添加一行到当前sheet的最底部（即逐行追加从第一行开始） iterable必须是:
    list,tuple,dict,range,generator类型的。 1,如果是list,将list从头到
    尾顺序添加。 2，如果是dict,按照相应的键添加相应的键值。
    """
    wb.save(filename=ExcelFullName)#workbook 调用save()方法保存
    return ExcelFullName

def readExcel(ExcelFullName):
    wb = load_workbook(ExcelFullName)

    # sheets = wb.get_sheet_names()
    sheets = wb.sheetnames
    print(sheets)
    sheet_first = sheets[0]

    # ws = wb.get_sheet_by_name(sheet_first)
    ws = wb[sheet_first]#相当于通过字典的键获取值
    print("***")
    print(sheet_first)
    print(ws.title)
    print("^^^")

    rows = ws.rows
    print(rows)
    # print(f"rows = {list(rows)}")
    list1 = []
    for row in list(rows):
        case = []
        for c in row:
            case.append(c.value)
        print(f"case= {case}")
        list1.append(case)

    print(list1)
        #combine the lists into one nested list


    columns = ws.columns

    # for row in rows:
    #     line = [col.value for col in row]
    #     print("line is: *** ")
    #     print(line)
    # print("ws[A1]: ")
    # print(ws['A1'].value)
    # print("cell(row=1, column=1): ")
    # print(ws.cell(row=1, column=1).value)
    # print(ws.max_row)
    # print(ws.max_column)
"""
通俗的理解__name__ == '__main__'：假如你叫小明.py，在朋友眼中，你是小明(__name__ == '小明')；在你自己眼中，你是你自己(__name__ == '__main__')。
if __name__ == '__main__'的意思是：当.py文件被直接运行时，if __name__ == '__main__'之下的代码块将被运行；当.py文件以模块形式被导入时，if __name__ == '__main__'之下的代码块不被运行。
"""
if __name__ == '__main__':
    ExcelFullName = writeExcel()
    readExcel(ExcelFullName)