How to use Python openpyxl

Time:2022-7-29
catalogue
  • Generate new EXCEL
    • Three ways to create a sheet
    • Assign a value to a cell in the sheet
    • Assign the value of cell
  • Read Excel
    • load file
    • Get sheet
  • An example
    • First create a tab page
    • collecting data
    • Save total score
  • summary

    Generate new EXCEL

    
    from openpyxl import Workbook
    from openpyxl.utils import get_column_letter
    
    wb = Workbook()
    
    dest_filename = 'empty_book.xlsx'
    
    ws1 = wb.active
    ws1.title = "range names"
    
    for row in range(1, 40):
        ws1.append(range(600))
    ws2 = wb.create_sheet(title="Pi")
    ws2['F5'] = 3.14
    
    ws3 = wb.create_sheet(title="Data")
    for row in range(10, 20):
        for col in range(27, 54):
            _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
    print(ws3['AA10'].value)
    
    wb.save(filename=dest_filename)
    

    A total of three sheets have been created

    Three ways to create a sheet

    ws1 = wb. create_ Sheet ("mysheet") # add at the end
    # or
    ws2 = wb. create_ Sheet ("mysheet", 0) # add at the beginning
    # or
    ws3 = wb. create_ Sheet ("mysheet", -1) # added in the penultimate place

    Assign a value to a cell in the sheet

    
    ws2['F5'] = 3.14
    

    Assign the value of cell

    
    ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
    

    Read Excel

    
    from openpyxl import load_workbook
    wb = load_workbook(filename = 'empty_book.xlsx')
    sheet_ranges = wb['range names']
    print(sheet_ranges['D18'].value)
    

    load file

    
    wb = load_workbook(filename = 'empty_book.xlsx')
    

    Get sheet

    
    sheet_ranges = wb['range names']
    

    An example

    Our goal is to put the data of the same test number into one line and calculate the total score

    First create a tab page

    Attention should be paid to the directness here

    Ws = wb['total score']

    Definitely not, because at this time, without this tab, you will directly report keyerror:’worksheet total score does not exist.’ So you need to check whether the tab page exists first

    If not 'total score' in wb.sheetnames:
        wb.create_ Sheet ('total score ')
    Ws = wb['total score']

    It should be noted that you must save at last, otherwise wb.save (‘first. Xlsx’) is useless

    collecting data

    #Collect the score data in each tab and put it into the corresponding data row
    
    for pos, tabName in enumerate(wb.sheetnames):
        if tabName != ' Total score ':
            wstt = wb[tabName]
            for row in wstt.iter_rows(min_row=2, values_only=True):
                for ind, code in enumerate(ws['A']):
                    if code.value == row[0]:
                        ws.cell(ind + 1, 4 + pos, row[2])
                        break

    Save total score

    Start by adding multiple fields directly

    
    for po, row in enumerate(ws.iter_rows(min_row=2, values_only=True)):
        ws.cell(po + 1, 3, 0 + row[3] + row[4] + row[5] + row[6] + row[7] + row[8] + row[9])
    

    Errors are reported as follows

    
    ws.cell(po + 1, 3, 0 + row[3] + row[4] + row[5] + row[6] + row[7] + row[8] + row[9])
    TypeError: unsupported operand type(s) for +: 'int' and 'NoneType'
    

    The reason is that there is a nonetype, and int and nonetype cannot be added. Int (value or 0) can be used to convert the corresponding none, 0, [], “” which Python considers to be false to 1.

    summary

    Life is short. I use python. With the help of powerful python, we can generate the excel we want with just a few lines of code.

    The above is the details of how to use Python openpyxl. For more information about the use of Python openpyxl, please pay attention to other relevant articles of developeppaer!

    Recommended Today

    JS generate guid method

    JS generate guid method https://blog.csdn.net/Alive_tree/article/details/87942348 Globally unique identification(GUID) is an algorithm generatedBinaryCount Reg128 bitsNumber ofidentifier , GUID is mainly used in networks or systems with multiple nodes and computers. Ideally, any computational geometry computer cluster will not generate two identical guids, and the total number of guids is2^128In theory, it is difficult to make two […]