openpyxl excel读写操作

￡神魔★判官ぃ 2023-02-12 11:22 27阅读 0赞

#http://yumos.gitee.io/openpyxl3.0/index.html#benchmarks
    #coding:utf-8
    from openpyxl import Workbook
    from openpyxl.utils import get_column_letter
    #写工作簿(workbook)
    wb = Workbook()
    
    dest_filename = 'empty_book.xlsx'
    ws1 = wb.active
    ws1.title = "range names"
    for row in range(1, 40):
        ws1.append(range(40))
    ws2 = wb.create_sheet(title="Pi")
    ws2['F5'] = 3.14
    ws3 = wb.create_sheet(title="Data")
    for row in range(1, 20):
        for col in range(1, 20):
            ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
    
    print(ws3['A10'].value)
    wb.save(filename = dest_filename)
    
    from openpyxl import load_workbook
    #读取已存在的工作簿(workbook)
    wb = load_workbook(filename = 'empty_book.xlsx')
    sheet_ranges = wb['range names']
    print(sheet_ranges['D18'].value)
    
    import datetime
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    # set date using a Python datetime
    ws['A1'] = datetime.datetime(2010, 7, 21)
    #Using number formats
    ws['A1'].number_format
    print(ws['A1'].value)
    
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    # 添加一个简单的函数
    ws["A1"] = "=SUM(1, 1)"
    wb.save("formula.xlsx")
    
    from openpyxl.workbook import Workbook
    wb = Workbook()
    ws = wb.active
    #合并/取消合并单元格
    ws.merge_cells('A2:D2')
    ws.unmerge_cells('A2:D2')
    # 等效于以下方法
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
    ws.unmerge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
    
    #插入图片
    from openpyxl import Workbook
    from openpyxl.drawing.image import Image
    #import Pillow
    
    # wb = Workbook()
    # ws = wb.active
    # ws['A1'] = 'You should see three logos below'
    # # create an image
    # img = Image('login.png')
    # # add to worksheet and anchor next to cells
    # ws.addimage(img, 'A1')
    # wb.save('logo.xlsx')
    
    #折叠(outline)
    import openpyxl
    wb = openpyxl.Workbook()
    ws = wb.create_sheet()
    ws.column_dimensions.group('A','D', hidden=True)
    ws.row_dimensions.group(1,10, hidden=True)
    #插入行
    ws.insert_rows(7)
    #删除行和列
    #删除F到H（F:H）列:
    ws.delete_cols(6, 3)
    #移动所选区域的单元格
    ws['E4']='E4'
    ws['F10']='F10'
    #单元格向上移动一行，向右移动两列
    ws.move_range("E4:F10", rows=-1, cols=2)
    wb.save('group.xlsx')
    
    from openpyxl import Workbook
    wb = Workbook(write_only = True)
    ws = wb.create_sheet()
    from openpyxl.cell import WriteOnlyCell
    from openpyxl.comments import Comment
    from openpyxl.styles import Font
    cell = WriteOnlyCell(ws, value="hello world")
    cell.font = Font(name='Courier', size=36)
    cell.comment = Comment(text="A comment", author="Author's Name")
    ws.append([cell, 3.14, None])
    wb.save('write_only_file.xlsx')