PyOOCalc - Library for generating reports, Libre / Open Office Calc accounts in Python
Sometimes tasks arise when it is necessary to generate reports and other documents. In my practice, this problem arose more than once.
Projects in which this task arose:
My first experience was with MS Office, but later there was a need to develop cross-platform solutions, therefore, the choice fell on OpenOffice. Since in most cases it was necessary to form the tables, then OpenOffice Calc was chosen.
At first I solved similar problems in C ++, developed a library that I used in different projects. Everything was fine, but almost every minor version of the office required rebuilding the library, and not rarely editing the code. At each distribution kit and its new version, at best, we had to rebuild the packages, edit the paths taking into account the office version, replace OpenOffice with LibreOffice, etc. And this is very tiring and time consuming.
')
All these difficulties led to the search for simpler solutions. I looked at the list of supported languages and saw Python, which I was very interested in, because already quite actively began to use it. Found a simple example, tried it - it works. Checked on different versions and - oh, a miracle! Without the slightest edits, the same script works on different distributions and versions of the office.
Those who have already encountered similar tasks know that picking the API is not a thankful business. Therefore, the first thing I did was start looking for ready-made libraries. The most interesting option for me was PyOO . This library contains quite extensive functionality and is easy to use. It implements functions from creating / saving documents to merging cells and creating charts. But ... None of the libraries I found provided the functionality I needed. A lot of tasty, but too much for me.
Let's return to the initial task: it is necessary to generate reports and accounts. As mentioned above, the easiest way to create templates is in an office suite. After all, they will be able to create and end users without the participation of developers.
With the creation of templates, everything is clear, but how to know where to insert the data. You can use indexes (column, row) or name (for example: "E5").
And what if for one report you need to create several templates for generating a document. For example, one template for a book layout, the second for a landscape. But there is no guarantee that the same field (data) must be inserted by the same name (or index) of the cell in the same template and in the other. And this means that you need to store data on the location of the fields in the template. But there is a simpler, universal solution. This task can be implemented through NamedRange .
NamedRange is the name of a cell or area on a sheet. At the same time NamedRange is unique in the frame of one book (document).
Another necessary function for solving this problem is inserting strings. In this case, the insertion should be taking into account the formatting of the string (font, merged cells, etc.).
Based on the above tasks, a Python module was developed that implements the following functions:
Usage example:
I am sure that this task arose not only from me. Glad if someone come in handy.
An example of a document with these requirements and an example of using the library are in the examples directory.
Download: PyOOCalc
Projects in which this task arose:
- inventory control
- property accounting
- document flow
My first experience was with MS Office, but later there was a need to develop cross-platform solutions, therefore, the choice fell on OpenOffice. Since in most cases it was necessary to form the tables, then OpenOffice Calc was chosen.
At first I solved similar problems in C ++, developed a library that I used in different projects. Everything was fine, but almost every minor version of the office required rebuilding the library, and not rarely editing the code. At each distribution kit and its new version, at best, we had to rebuild the packages, edit the paths taking into account the office version, replace OpenOffice with LibreOffice, etc. And this is very tiring and time consuming.
')
All these difficulties led to the search for simpler solutions. I looked at the list of supported languages and saw Python, which I was very interested in, because already quite actively began to use it. Found a simple example, tried it - it works. Checked on different versions and - oh, a miracle! Without the slightest edits, the same script works on different distributions and versions of the office.
Those who have already encountered similar tasks know that picking the API is not a thankful business. Therefore, the first thing I did was start looking for ready-made libraries. The most interesting option for me was PyOO . This library contains quite extensive functionality and is easy to use. It implements functions from creating / saving documents to merging cells and creating charts. But ... None of the libraries I found provided the functionality I needed. A lot of tasty, but too much for me.
Let's return to the initial task: it is necessary to generate reports and accounts. As mentioned above, the easiest way to create templates is in an office suite. After all, they will be able to create and end users without the participation of developers.
With the creation of templates, everything is clear, but how to know where to insert the data. You can use indexes (column, row) or name (for example: "E5").
And what if for one report you need to create several templates for generating a document. For example, one template for a book layout, the second for a landscape. But there is no guarantee that the same field (data) must be inserted by the same name (or index) of the cell in the same template and in the other. And this means that you need to store data on the location of the fields in the template. But there is a simpler, universal solution. This task can be implemented through NamedRange .
NamedRange is the name of a cell or area on a sheet. At the same time NamedRange is unique in the frame of one book (document).
Another necessary function for solving this problem is inserting strings. In this case, the insertion should be taking into account the formatting of the string (font, merged cells, etc.).
Based on the above tasks, a Python module was developed that implements the following functions:
- Opening and creating documents
- Saving documents in different formats (supported by Libre / Open Office)
- Insert / delete sheets
- Insert lines with copy formatting
- Insert / Get Name Values (NamedRange)
- Insert / get cell values by name (index)
Usage example:
import pyoocalc # open document doc = pyoocalc.Document() file_name = os.getcwd() + "/example.ods" doc.open_document(file_name) # Get document fields fields = doc.fields() # Get field "HEADER" field = fields.field("HEADER") print ("Document header is: " + str(field.is_null())) # Set values field = fields.field("TABLE_NAME") field.set_value("Test table name") print ("New table name is: " + field.value()) # Insert 5 rows field1 = fields.field("FIELD_1") num_rows = 5 step = 2 if num_rows > 0: field1.insert_rows(num_rows=num_rows-1, step=step, columns_to_copy=200) for i in range(1, num_rows + 1): field1.set_value("F1." + str(i), 0, i * step - (step - 1)) # Set value="value1" at column=1, row=1 (B1) sheet = doc.sheets().sheet(0) sheet.set_cell_value_by_index(1, 0, "value1") print (sheet.cell_value_by_index(1, 0)) I am sure that this task arose not only from me. Glad if someone come in handy.
An example of a document with these requirements and an example of using the library are in the examples directory.
Download: PyOOCalc
Source: https://habr.com/ru/post/273131/
All Articles