• python3-常用模块之openpyxl(1)



    1、创建工作簿

    from openpyxl import Workbook
    # 创建excel对象
    wb = Workbook()
    # 获取第一个sheet = wb.active
    # 单元格写入内容
    ws.append(['单元1','单元2','钉钉'])
    ws['A1'] = 'A1'
    ws['A2'] = '你好'+'A2'
    # 新行写入多个单元格,默认会在新的一行里写入内容
    ws.append(['单元11','单元22','钉钉1'])
    # 插入自定义时间对象
    import time
    ws['B2'] = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
    # 保存表格
    wb.save("sample_demo.xlsx")
    

     2、创建sheet

    from openpyxl import Workbook
    wb = Workbook()
    #创建sheet,如果已经存在多个sheet,默认在最后位置插入
    ws1 = wb.create_sheet("sheet1")
    #设置sheet的名字,注意'sheet1'为sheet对象的名字
    ws1.title = '表1'
    #指定位置创建sheet,第1个插入
    ws2 = wb.create_sheet('sheet2',0)
    ws2.title = '表0'
    wb.save('sample_demo.xlsx')
    
    #获取某个sheet对象,两种方式
    print(wb.get_sheet_by_name('表1'))
    print(wb['表1'] == wb.get_sheet_by_name('表1'))  #True
    
    #获取全部sheet名字
    print(wb.sheetnames)
    for i in wb:
        print(i.title)
    
    #复制sheet
    wb['表1']['A1'] = '中国'
    source = wb['表1']
    target = wb.copy_worksheet(source)
    target.title = '拷贝表格'
    wb.save('sample_demo.xlsx')

    3、操作单元格

    wb = Workbook()
    ws = wb.create_sheet('my_sheet1')
    # 单元格写入数据,两种方式
    ws['A1'] = '100'
    ws['A2'] = '你好200'
    v = ws.cell(3,1,300)
    print(ws['A1'].value)
    print(ws['A2'].value)
    print(v.value)
    #单元格输入百分号
    ws['a1'] = "12%"
    #打印出的是str类型的‘12%’
    print(ws['a1'].value)
    print(type(ws['a1'].value))

    4、批量操作单元格

    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.create_sheet('my_sheet')
    ws["A1"]=1
    ws["A2"]=2
    ws["A3"]=3
    ws["B1"]=4
    ws["B2"]=5
    ws["B3"]=6
    ws["C1"]=7
    ws["C2"]=8
    ws["C3"]=9
    # 操作单列
    print(ws['A'])
    for cell in ws['A']:
        print(cell.value)
    print('===========')
    # 操作多列
    for column in ws['A:C']:
        for cell in column:
            print(cell.value)
    print('===========')
    # 操作多行
    print(ws[1:3])
    for row in ws[1:3]:
        for cell in row:
            print(cell.value)
    print('===========')
    print("===指定行列====")
    for row in ws.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3):
        for cell in row:
            print(cell.value)
    print('===========')
    # 操作所有行
    print(ws.rows)
    for row in ws.rows:
        print(row)
    #操作所有列
    print(ws.columns)
    for col in ws.columns:
        print(col)

    5、操作存在的文件

    from openpyxl import Workbook
    from openpyxl import load_workbook
    wb = load_workbook("sample_demo.xlsx")
    ws = wb.active
    ws['a1'] = '333'  # ws['a1'].value = '333'效果一样
    print(ws['a1'].value)
  • 相关阅读:
    选择排序
    插入排序
    冒泡排序
    linux 常用命令全集
    Boost简介
    postgresql命令行
    Ncurses 命令行图形库
    rsync ssh文件同步
    BusyBox参考
    screen命令
  • 原文地址:https://www.cnblogs.com/tester-star/p/11643771.html
Copyright © 2020-2023  润新知