The most detailed Excel module Openpyxl tutorial (3) - Using formulas

1, Number and types of formulas .

Let's first take a look at the formulas that can be used in openpyxl. Let's take a look at the code:

from openpyxl.utils import FORMULAE
print(len(FORMULAE))
print(FORMULAE)

The output content is as follows: .

It can be seen that the supported formulas include ROW, ABS, MAX, etc. The number of supported formulas may vary depending on the version. Currently, I am using OpenPyXL version 3.0.5, which supports 352 formulas.

So how do we determine whether the formula we want to use supports it? We can make a simple judgment:

print('MID' in FORMULAE)
print('minddd' in FORMULAE)

The above code output True And False That is to say, the formula "MID" is supported, while the formula "minddd" is not.

2, Using formulas in cells .

Assuming we now have a data table "formulae1. xlsx", the data is displayed as follows:

We will use a formula to calculate a total value, with the following code:

wbook = load_workbook(filename='formula_1.xlsx')
wsheet = wbook['Sheet1']
wsheet["C2"] = "=SUM(A2,B2)"
print(wsheet['C2'].value)
wbook.save("formula_1.xlsx")

The output in the code is: = SUM (A2, B2) The table after operation is displayed as follows, which happens to be the sum of the two. Of course, we can also write the fourth line of code as follows: wssheet [C2]"= SUM (10,20)", We can also obtain the results shown in the following figure:

Using the for loop, we can convert the values of all rows in the above table:

from openpyxl import load_workbook
wbook = load_workbook(filename='formula_1.xlsx')
wsheet = wbook['Sheet1']
for j in range(2,4):
cell_a = 'A' + str(j)
cell_b = 'B' + str(j)
cell_c = 'C' + str(j)
wsheet[cell_c] = "=SUM({},{})".format(cell_a,cell_b)
wbook.save("formula_1.xlsx")

Let's take a look at the data in the table. By selecting cell C2, we can see that it displays a formula:

What happens when we reload this table and read this cell?.

from openpyxl import load_workbook
wbook = load_workbook(filename='formula_1.xlsx')
wsheet = wbook['Sheet1']
cell_C2 = wsheet.cell(2,3).value
print(cell_C2)
wbook.save("formula_1.xlsx")

The printout in the code is: = SUM (A2, B2) So there's a question here, how do we get the calculated value in this cell? That is to say, how to print out 30. Here we need to mention a parameter in openpyxl when opening a file. We will change the second line of code to:

wbook = load_workbook(filename='formula_1.xlsx',data_only=True)

Load_The workbook method involves many attributes, including read_Only, keep_VBA, guess_Types, data_Only, etc Data_Only is used to read the value in the cell. When the value in the cell is a formula, it will return the calculated result. data_Only Control whether cells with formulas have formulas (default values) or the values stored when Excel last read the worksheet .

After we run the replaced code, print the cell_When the C2 value is [insert value], it is [insert value], but [insert value] is [insert value] None Why is this? The explanation for querying relevant information can refer to:

https://blog.51cto.com/antidarkness/1974684.

So how should this problem be solved? We can use the Win32 library for opening and closing operations, and then use openpyxl to open the file and pass it in Data_Only; True Okay, use Win32 to open the saved code as follows: (Note that filname needs to pass a full path).

from win32com.client import Dispatch
filename = r'C:UsersLEGIONDesktoptweets_codeformula_1.xlsx'
xlApp = Dispatch('Excel.Application')
xlApp.Visible = False
xlBook = xlApp.Workbooks.Open()
xlBook.Save()
xlBook.Close()

Of course, when calculating multi row values such as the sum of A1 to A5, you can use:"= SUM (A1: A5)".

3, Convert Formula Position .

Converting formulas from one position to another is also very common in daily work, so how should it be implemented? Let's first take a look at the code:

from openpyxl import load_workbook
from openpyxl.formula.translate import Translator
wbook = load_workbook(filename='formula_1.xlsx')
wsheet = wbook['Sheet1']
wsheet['C3'] = Translator("=SUM(A2,B2)", origin="C2").translate_formula("C3")
print(wsheet['C3'].value)
wbook.save("formula_1.xlsx")

The above code will"= SUM (A2, B2)" The displayed formula has been copied to cell C3 through transformation, and the printed result is:= SUM (A3, B3) The value in cell C3 becomes A3; The value of B3 is not very interesting. The official documentation explains the purpose and parameters of this Translator (object) class as follows:

Modifications A formula so that it can be translated from one cell to another
Formula: The str string to translate Must include the leading'=' Character
Origin: The cell address (in A1 notation) where this formula was defined (excluding the worksheet name)

Here is a supplementary knowledge point from the previous cell:

What should we do when we don't have the format we want when setting it? It's very simple. We can first use Excel to set the style of the corresponding cells, and then use the following code to print the format:

cell_style = wsheet.cell(i,j).number_format
print(cell_style)

4, Summary .