Tutorial on using Python scripts to operate tables under Gnumeric, gnumericpython

Tutorial on using Python scripts to operate tables under Gnumeric, gnumericpython

About Gnumeric

Gnumeric is a powerful and easy-to-use spreadsheet software on the linux platform. It is consistent with other commonly used spreadsheet software such as Excel in terms of style. Gnumeric's current stable version is 1.2.13, and its support for Chinese characters is quite mature. According to official information, Gnumeric not only implements all functions of MS Excel, but also implements functions that do not exist in more than 60 Excel files and basic financial functions, it also provides advanced statistical analysis, scalable random number generator, and linear or nonlinear computing capabilities. What's even more surprising is that Gnumeric has now integrated the powerful Script Programming capabilities of Python, allowing Python users to implement more complex computing functions for Gnumeric.

What is Python

Python is an explanatory, object-oriented programming language with dynamic semantics. The Python code is highly readable, has the concept of modules and packages, supports various mainstream platforms, and has good cross-platform capabilities. Python has been widely used in text processing, Internet programming, database programming, system management, and other fields. At the same time, Python is a successful embedded language. It is very convenient to Package C/C ++ code. More and more heavyweight applications are beginning to support Python script programming, OpenOffice, GIMP, Blender, and so on.

Plug-ins

Any C function call or access to a Python object must follow this framework:

1. C function converts call parameters to Python Data Types

2. Call Python functions using converted Parameters

3. the return value is converted to the C language type and returned to the C function.

Similarly, Calling C functions from Python functions follows similar steps:

1. the Python function converts a parameter to a C language type.

2. Call the C function with the converted Parameter

3. Convert the returned value to the Python language type and return it to the Python function.

Therefore, the key to mutual calls between Python and C functions is the issue of data conversion. These conversions require good development skills in C and Python interpretation languages, fortunately, the Python plug-in of Gnumeric has automatically converted the data type for us. We only need to focus on the implementation of the algorithm.

The interaction between Gnumeric and Python follows a similar process. First, Gnumeric automatically converts the parameter type, calls the Python function, and finally converts the returned value to a suitable type and returns it to Gnumeric. The following table lists Common Data Types of Gnumeric and Python:

For cells, Gnumeric directly converts the data in cells to the corresponding data type and transmits the Python functions called, such as Integer, Float, and String ); however, Gnumeric adopts a roundabout policy for the Range of cells. It only transmits the reference of a cell area (RangeRef) to the called Python function, in this case, Python needs to use the Gnumeric interface to access and operate data in the cell area. Therefore, Gnumeric provides the Gnumeric module for Python, including all Gnumeric functions and thin worksheet objects, the functions and objects in the Gnumeric module are briefly listed here (For details, refer to the Gnumeric py-gnumeric.c source file in the plugins/python-loader directory ).

Sample Analysis

Through the above introduction, we have a preliminary understanding of the Cross-language call framework, next, we will analyze the Python plug-in examples (usually in/usr/lib/Gnumeric/<VERSION>/plugins/py-func/) that come with the gnumeric software package /). This example is composed of plugin. the xml and py_func.py files are composed of plugin. xml is a configuration file in XML format for Gnumeric to read information about python functions. py_func.py contains the definition of Python functions and the function prototype dictionary.

The first analysis is the py_func.py file. This file defines three functions: func_printf, func_capwords, and func_bitand. The functions are output formatting, uppercase letters of words, and summation by bit. Let's compare these three functions:

Taking the func_bitand function as an example, the function accepts two integers and the return value is also an integer. The type conversion between C and Python is automatically completed by Gnumeric. func_bitand only focuses on algorithm implementation, the specific calculation is completed by calling the bitand function of Gnumeric. It is worth mentioning that the document string starting with ''@'' is the document interface provided to Gnumeric, provide function functions, interfaces, instances, and references respectively. The format is fixed. Each field (including line breaks) is enclosed in single quotes and followed "\".
Code 1 func_bitand Function Definition

From Gnumeric import * def func_bitand (num1, num2): '@ FUNCTION = PY_BITAND \ n' \' @ SYNTAX = PY_BITAND (num) \ n' \ '@ DESCRIPTION = The BITAND function returns bitwise' \ 'and-ing of its arguments. '\ n' \' @ EXAMPLES = \ n' \ 'py _ BITAND (6, 2) equals 2) '\ n' \' @ SEEALSO = BITAND 'gnm_bitand = functions ['bitand'] # return gnm_bitand (num1, num2)

There is also a special dictionary at the end of the py_func.py file, which provides the Python function prototype information to Gnumeric. The name of the function prototype dictionary is very strict. It must be suffixed with "_ functions", and the name before "_" must be the same as that of plugin. the xml file is consistent so that Gnumeric can discover various function information in the plug-in. Otherwise, Gnumeric may encounter many function information errors, resulting in Plug-in function unavailability. The function prototype is represented by the "key: value" pair in the dictionary (Code 2). For example, func_bitand, the key is the function name py_bitand mapped in Gnumeric, value is a tuple consisting of parameter types, parameter names, and function names.
Code 2 test_functions function prototype dictionary

test_functions = {    'py_printf': func_printf,    'py_capwords': ('s', 'sentence', func_capwords),            'py_bitand':  ('ff', 'num1, num2', func_bitand)}

In the function prototype dictionary, the parameter type is represented by special characters. For example, the two floating-point parameters of func_bitand are represented as "ff ". String Representation of common parameter types is summarized as follows:

Another simple XML file, plugins. xml (1), is the configuration information provided by the developer to Gnumeric. The name and description labels in the information tag provide the plug-in name and description information, and the internationalization of the information is also very simple. You only need to enter the international information in the corresponding labels marked by language. The value attribute of the attribute label in the loader tag, the id attribute in the service tag, and the name attribute in the function tag are the most important, it corresponds to the Python script file name, the function prototype dictionary name (excluding the suffix) in the script, and the key of the function prototype function. In this example, if the attribute values are py_func, test, py_printf, py_capwords, and py_bitand, The INS are py_func.py, test_functions, py_printf, py_capwords, and py_bitand. These mappings must be consistent, otherwise Gnumeric will complain to you.
Code 3 plug-in. xml configuration file for the py-func.py

<?xml version="1.0" encoding="UTF-8"?><plugin id="Gnumeric_PyFunc">    <information>        <name>Python functions</name>        <description>Sample Python plugin providing               some (useless) functions.        </description>        <require_explicit_enabling/>    </information>    <loader type="Gnumeric_PythonLoader:python">        <attribute value="py_func" name="module_name"/>    </loader>    <services>        <service type="function_group" id="test">            <category>Python</category>            <category xml:lang="zh_CN">                Python            </category>            <functions>                <function name="py_printf"/>                <function name="py_capwords"/>                <function name="py_bitand"/>            </functions>        </service>    </services></plugin>

Knife Test

According to the above analysis, we can see that it takes three steps to compile the Gnumeric function in Python:

1. Create a Python function source file, such as py_func.py.

2. Create a function prototype Dictionary Based on the created function, such as test_functions.

3. Create a plugin. xml configuration file and configure the file name, function category, name, and prototype dictionary.

To demonstrate the Python function creation process in Gnumeric, I wrote a small function that automatically marks the score level, which consists of two files: plugin. xml and exam. py.

First create the script file exam. py: the entire file only has two functions: mark and cstr. The parameters and return values of the mark function are strings, and the function is to return the score level based on the size of the mark function. cstr is used to convert the string to UTF-8 encoding, enable Gnumeric to display Chinese (2 ). Comments in the mark function are the function information provided to Gnumeric. You only need to simply modify the template during development.
Code 4 exam. py file

#-*-Coding: GB2312-*-def mark (score): '@ FUNCTION = MARK_SCORE \ n' \' @ SYNTAX = mark_score (score) \ n' \ '@ DESCRIPTION = determine the level for a score \ n' \' @ EXAMPLES = To determine a score in A1: \ n' \ 'mark_score (a1) \ n' \ '@ SEEALSO = 'level = 'n'/a' if score <0: level = cstr ('invalid score') elif score <60: level = cstr ('failed') elif score <80: level = cstr ('failed') elif score <90: level = cstr ('liang ') elif score <= 100: level = cstr ('excellent ') else: level = cstr ('invalid score') return leveldef cstr (str ): "translate a chinese string into UTF-8 string for GTK +" "return unicode (str, 'gbk '). encode ('utf8') exam_functions = {'Mark _ score ': ('F', 'score', mark )}

The next step is to register the function, exam. the prototype Dictionary of the exam_functions function at the end of The py file reveals the prototype information of the mark Function to Gnumeric. The dictionary key 'Mark _ score 'is the name ing of mark in Gnumeric, f indicates that the parameter type is an integer, and score indicates the parameter name. Plugin. xml (3) is simply rewritten based on the template. The main note is that the attributes mentioned above must correspond to the plug-in. Otherwise, the plug-in is invalid. Other attributes, for example, category also adds Chinese information for ease of use.
Code 5: plug-in. xml configuration file of exam. py

<?xml version="1.0" encoding="UTF-8"?><plugin id="Gnumeric_PyFunc"> <information>  <name>Exam functions</name>  <description> Determine rank for exam score </description>  <require_explicit_enabling/> </information> <loader type="Gnumeric_PythonLoader:python">  <attribute value="exam" name="module_name"/> </loader> <services>  <service type="function_group" id=        "exam">   <category>        Exam</category>   <category xml:lang="zh_CN">Exam</category>   <functions>    <function name=        "mark_score"/>   </functions>  </service> </services></plugin>

OK! Start Gnumeric (4), enter A score column in column A as shown in the figure, and enter the formula '= mark_score (A1)' in cell B1 )', then, copy the formula by dragging the mouse to copy the formula to the corresponding column B, and you will find that all the marks have been automatically generated in column B.
Illustration 1 score Classification

Further steps

If it is just a simple computing of cell data, Python is a fun toy in Gnumeric, but the Python plug-in has far more functions than that. Python can control the read/write cell area (Range) to access all the functions of Gnumeric and control the creation of worksheets. By organically combining these functions, you can complete complex tasks. This section further processes the scores of the entire class, and uses the summary function of RPy (5) to perform simple statistics on all scores to calculate the maximum value, average value, median, and two quartile values, and print the calculation result to the new worksheet.

To calculate the overall score, the first task is to obtain data from Gnumeric. Gnumeric indicates a large volume of data in the Range field. However, during the call process, it passes to Python the Range field reference (RangeRef ), therefore, RangeRef needs to be converted to extract batch data. Unfortunately, Gnumeric's API is in the development stage and there is no direct conversion method. Therefore, the author uses Gnumeric's own functions to construct a PyGnmRange class. The PyGnmRange object uses RangeRef as the initialization parameter to construct indexes for all cells in the cell area, that is, the "_ table" attribute. It also provides several methods for convenient access, in this way, we can use the Sheet object in the Gnumeric module to manipulate cell data.
Code 6 class PyGnmRange Definition

    class PyGnmRange:  def __init__(self, gnm_range_ref):    get_cols = Gnumeric.functions['column']    get_rows = Gnumeric.functions['row']    get_col_num = Gnumeric.functions['columns']    get_row_num = Gnumeric.functions['rows']    cols = get_cols(gnm_range_ref)    rows = get_rows(gnm_range_ref)    # column first table    self._table = []    self._col_num = get_col_num(gnm_range_ref)    self._row_num = get_row_num(gnm_range_ref)    for i in range(self._col_num):      for j in range(self._row_num):        self._table.append((cols[i][j]-1, rows[i][j]-1))  def col_num(self):    return self._col_num  def row_num(self):    return self._row_num  def get_col(self,col):    start = (col-1) * self._row_num    end = col * self._row_num    return self._table[start:end]  def get_row(self,row):    indexes = [(i*self._row_num)+(row-1) for i in range(self._col_num)]    return [self._table[i] for i in indexes]  def __iter__(self):    return iter(self._table)

In addition, the PyGnmRange class definition requires two points:

1. The cell subscript adopts the column-first expression method and starts counting from scratch. For example, B3 indicates (1, 2). This is also used to maintain consistency with Gnumeric specifications and facilitate cell data manipulation.

2. class initialization functions use four Gnumeric functions: column, columns, row, and rows. Their functions are as follows:

With the preparations above, we can implement the summary function. The summary function obtains the current cell region reference using the gnm_scores parameter, creates a PyGnmRange object using this parameter, computes the subscript of all cells, and uses the workbooks and sheets functions of the Gnumeric module, obtains the object of Worksheet 1, and combines the worksheet object with the cell subscript to operate the cell data. While the real computing R language is complete, the RPy module is a bridge connecting Python and R language (6 ). Finally, the summary function obtains the R language computing result and prints it to a new work table through the Gnumeric module.
The summary function definition in code 7 exam. py

<?xml version="1.0" encoding="UTF-8"?><plugin id="Gnumeric_PyFunc"> <information>  <name>Exam functions</name>  <description>Sample Python plugin providing some (useless) functions.</description>  <require_explicit_enabling/> </information> <loader type="Gnumeric_PythonLoader:python">  <attribute value="exam" name="module_name"/> </loader> <services>  <service type="function_group" id="exam">   <category>Exam</category>   <category xml:lang="zh_CN">Exam</category>   <functions> <function name="mark_score"/> <function name="summerize_scores"/>   </functions>  </service> </services></plugin>

After the function is compiled, the function is registered. The function prototype dictionary has only one row. Note that the data type referenced in the cell area must be represented by "r. You only need to add the following line to the plugin. xml file:
Code 8: plug-in. xml configuration file of the summay Function

<?xml version="1.0" encoding="UTF-8"?><plugin id="Gnumeric_PyFunc"> <information>  <name>Exam functions</name>  <description>Sample Python plugin providing some (useless) functions.</description>  <require_explicit_enabling/> </information> <loader type="Gnumeric_PythonLoader:python">  <attribute value="exam" name="module_name"/> </loader> <services>  <service type="function_group" id="exam">   <category>Exam</category>   <category xml:lang="zh_CN">Exam</category>   <functions> <function name="mark_score"/> <function name="summerize_scores"/>   </functions>  </service> </services></plugin>

The following two pieces are the operation of the plug-in function. The input data is a randomly generated 80 floating point numbers less than 100. The function is inserted into the B1 cell, this function is used to generate a simple report instead of a return value. Therefore, after the operation, cell B1 is still blank, all the data is printed in worksheet 4 (figure 2 and Figure 3 ).
Illustration 2 class score and function input

Illustration 3 class score Statistical Report

Plug-in deployment

The Gnumeric plug-in is easy to deploy. You only need to create it in your home directory. add the gnumeric directory to the plug-in function, for example, exam. py and plugin. xml is located in <HOME> /. gnumeric/<version> (7)/plugins/exam/. Restart the Gnumeric plug-in to take effect (8 ).

Conclusion

Pay attention to the following issues during the Python development process of Gnumeric:

1. gumeric's Python plug-in is still under active development, and some code may change significantly in future versions. The Gnumeric module interface provided by the plug-in is not complete, for example, if a function lacks the function to obtain the active worksheet, it must be carefully processed when writing Python functions.

2. although Python function configuration is simple, debugging is not very convenient, and Gnumeric may often fail to correctly obtain Python information. There are many reasons for this, such as plugin. the xml file name is inconsistent with the script file, the function prototype dictionary name is not standardized, the function document string format is incorrect, and The Script File Syntax is incorrect.

Even so, for programmers familiar with Python, this does not affect the fun of writing Gnumeric functions, and it is not difficult to handle it carefully. I hope this article will serve as an example. If you are interested, you can refer to the developer documentation in Gnumeric source code and the source code of the Python plug-in to find a lot of valuable information, write more valuable applications.