Tutorialspoint.dev

Python | Arithmetic operations in excel file using openpyxl

Prerequisite: Reading & Writing to excel sheet using openpyxl

Openpyxl is a Python library using which one can perform multiple operations on excel files like reading, writing, arithmatic operations and plotting graphs. Let’s see how to perform different arithmatic operations using openpyxl.

  • =SUM(cell1:cell2) : Adds all the numbers in a range of cells.
    # import openpyxl module
    import openpyxl
      
    # Call a Workbook() function of openpyxl 
    # to create a new blank Workbook object
    wb = openpyxl.Workbook()
      
    # Get workbook active sheet  
    # from the active attribute.
    sheet = wb.active
      
    # writing to the cell of an excel sheet
    sheet['A1'] = 200
    sheet['A2'] = 300
    sheet['A3'] = 400
    sheet['A4'] = 500
    sheet['A5'] = 600
      
    # The value in cell A7 is set to a formula 
    # that sums the values in A1, A2, A3, A4, A5 .
    sheet['A7'] = '= SUM(A1:A5)'
      
    # save the file
    wb.save("sum.xlsx")

    Output:

  • =PRODUCT(cell1:cell2) : Multiplies all the numbers in the range of cells.
    import openpyxl
      
    wb = openpyxl.Workbook()
    sheet = wb.active
      
    sheet['A1'] = 2
    sheet['A2'] = 3
    sheet['A3'] = 4
    sheet['A4'] = 5
    sheet['A5'] = 6
      
    # The value in cell A7 is set to a formula 
    # that multiplies the values in A1, A2, A3, A4, A5 .
    sheet['A7'] = '= PRODUCT(A1:A5)'
      
    wb.save("product.xlsx")

    Output:

  • =AVERAGE(cell1:cell2) : It gives the average (arithmetical mean) of all the numbers which is present in the given cell range.
    import openpyxl
      
    wb = openpyxl.Workbook()
    sheet = wb.active
      
    sheet['A1'] = 200
    sheet['A2'] = 300
    sheet['A3'] = 400
    sheet['A4'] = 500
    sheet['A5'] = 600
      
    # The value in cell A7 is set to a formula 
    # that return average of the values in A1, A2, A3, A4, A5 .
    sheet['A7'] = '= AVERAGE(A1:A5)'
      
    wb.save("average.xlsx")

    Output:
    average

  • =QUOTIENT(num1, num2) : It returns the integer portion of a division.
    import openpyxl
      
    wb = openpyxl.Workbook()
    sheet = wb.active
      
    # The value in cell is set to a formula 
    # that gives quotient value .
    sheet['A1'] = '= QUOTIENT(64, 8)'
    sheet['A2'] = '= QUOTIENT(25, 4)'
      
    wb.save("quotient.xlsx")

    Output:
    quotient

  • =MOD(num1, num2) : Returns the remainder after a number is divided by the divisor.
    import openpyxl
      
    wb = openpyxl.Workbook()
    sheet = wb.active
      
    # The value in cell is set to a formula 
    # that gives remainder or modulus value.
    sheet['A1'] = '= MOD(64, 8)'
    sheet['A2'] = '= MOD(25, 4)'
      
    wb.save("modulus.xlsx")

    Output:
    modulus

  • =COUNT(cell1:cell2) : It counts the number of cells in a range that contain the number.
    import openpyxl
      
    wb = openpyxl.Workbook()
    sheet = wb.active
      
    sheet['A1'] = 200
    sheet['A2'] = 300
    sheet['A3'] = 400
    sheet['A4'] = 500
    sheet['A5'] = 600
      
    # The value in cell A7 is set to a formula 
    # that gives counting of number present in the cells.
    sheet['A7'] = '= COUNT(A1:A6)'
      
    wb.save("count.xlsx")

    Output:



This article is attributed to GeeksforGeeks.org

leave a comment

code

0 Comments

load comments

Subscribe to Our Newsletter