Python | Arithmetic operations in an excel file using openpyxl



Openpyxl — is a Python library with which you can perform several operations on Excel files, such as reading, writing, arithmetic, and graphing. Let`s see how to perform various arithmetic operations using openpyxl.

  • = SUM (cell1: cell2): adds all numbers in a range of cells.

    # import openpyxl module

    import openpyxl

     
    # Calling the Workbook () function from openpyxl
    # create a new blank Workbook object

    wb = openpyxl.Workbook ()

     
    # Get a working sheet of the active sheet
    # from the active attribute.

    sheet = wb.active

     
    # write to worksheet cell Excel

    sheet [ `A1` ] = 200

    sheet [ `A2` ] = 300

    sheet [ `A3` ] = 400

    sheet [ `A4` ] = 500

    sheet [ `A5` ] = 600

     
    # The value in cell A7 is set to the formula
    # which sums the values ​​in A1, A2, A3, A4, A5 .

    sheet [ `A7` ] = `= SUM (A1: A5)`

      
    # save file

    wb.save ( "sum.xlsx" )

    Exit:

  • = PRODUCT (cell 1: cell 2): ​​ Multiplies all numbers in a 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 the formula
    # which multiplies the values ​​in A1, A2, A3, A4, A5.

    sheet [ `A7` ] = ` = PRODUCT (A1: A5) `

     

    wb.save ( "product.xlsx" )

    Output:

  • = MEDIUM (cell 1: cell 2): it gives the average (arithmetic mean) of all numbers that are present in the given range of cells.

     

    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 the formula
    # which return average in A1, A2, A3, A4, A5.

    sheet [ `A7` ] = `= AVERAGE (A1: A5)`

     

    wb.save ( "average.xlsx" )

    Output:

  • = QUOTIENT (num1, num2): returns the integer part of the division.

    import openpyxl

     

    wb = openpyxl.Workbook ()

    sheet = wb.active

     
    # The value in the cell is set by the formula
    # which gives the factor value.

    sheet [ `A1` ] = `= QUOTIENT (64, 8)`

    sheet [ `A2 ` ] = ` = QUOTIENT (25, 4) `

     

    wb.save ( " quotient.xlsx " )

    Output:

  • = MOD (num1, num2): returns the remainder after dividing the number by the divisor.

    import openpyxl

     

    wb = openpyxl.Workbook ()

    sheet = wb.active

     
    # The value in the cell is set by the formula
    # which gives the remainder or value module.

    sheet [ `A1` ] = `= MOD (64, 8)`

    sheet [ `A2` ] = `= MOD (25, 4)`

     

    wb.save ( "modulus.xlsx" )

    Output:

  • = COUNT (cell a1: cell2): counts the number of cells in a range that contain a 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 the formula
    # which gives a count of the number present in the cells.

    sheet [ `A7` ] = `= COUNT (A1: A6)`

     

    wb.save ( "count.xlsx" )

    Output: