tutorial on using Python script to manipulate tables under Gnumeric _python

About Gnumeric

Gnumeric is a powerful and easy-to-use spreadsheet software under the Linux platform that is very consistent with other common spreadsheet software such as Excel. The current stable version of Gnumeric is 1.2.13, and support for Chinese is more mature. According to official information, Gnumeric in addition to the implementation of MS Excel all functions, but also implemented more than 60 excel in the function and the basic financial aspects of functions, and already has advanced statistical analysis, scalable random number generator, linear or non-linear computing capacity. More surprisingly, now that Gnumeric has integrated Python's powerful scripting capabilities, Python users can implement more complex computing capabilities for Gnumeric.

What is Python

Python is an interpretive, object-oriented, dynamic semantic programming language. Python code has excellent readability, has the concept of modules and packages, supports a variety of mainstream platforms, and has a 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, packaging of C + + code is very convenient, more and more heavyweight applications began to support Python scripting, OpenOffice, GIMP, blender and so on.

Research on Plug-ins

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

1. C function converts call parameters to Python language data types

2. Invoke the Python function with the converted Parameters

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

Similarly, invoking the C function from the Python function follows similar steps:

1. Python function converts parameters to C language type

2. Invoke the C function with the converted parameter

3. Return value converted to Python language type and returned to Python function

So Python functions and C functions to invoke each other is the key to the problem of data conversion, these transformations require a fairly good C and Python interpretation of language development skills, fortunately Gnumeric python plug-ins have been automatically for us to do the conversion of data types, we only need to focus on the implementation of the algorithm.

Gnumeric and Python interactions follow a similar process, first gnumeric the parameter type automatically, then invoke the Python function, and finally convert the return value to the appropriate type to return to Gnumeric. The following tables are common data types for gnumeric and Python:

For cells, Gnumeric converts the data in the cell directly to the corresponding data type, passing the called Python function, such as an integer, floating-point number (float), string, and so on, but for a cell range (range), Gnumeric takes a roundabout strategy, passing a reference to a range of cells (RangeRef) to the called Python function, which Python then needs to access and manipulate the data in the cell range through the Gnumeric interface. As a result, Gnumeric provides Python with Gnumeric modules, including all functions of Gnumeric and Workbook objects, Here is a brief list of the functions and objects in the Gnumeric module (please refer to the Gnumeric py-gnumeric.c source file in the Plugins/python-loader directory for specific details).

Example analysis

Through the above introduction, we have a preliminary understanding of the framework of Cross-language invocation, on this basis to analyze the Gnumeric package from the Python plug-in example (usually located in/usr/lib/gnumeric/<version>/plugins/ py-func/). This example consists of Plugin.xml, py_func.py two files, Plugin.xml is an XML configuration file for Gnumeric to read the information about Python functions; py_func.py contains the definitions of Python functions and the Dictionary of functional prototypes.

The first analysis is the py_func.py file. The file defines three functions: Func_printf,func_capwords,func_bitand, the function is formatted output, the word first letter uppercase, sum by bit. Let's compare these three functions:

Take the Func_bitand function as an example, the function accepts two integers, the return value is also an integer, C and Python type conversion is gnumeric automatically, Func_bitand only focus on the implementation of the algorithm, The specific calculation is done by calling the bitwise SUM function (Bitand) of the Gnumeric, and it is worth mentioning that the document string at the beginning of ' @ ' is a document interface provided to Gnumeric, providing the function, interface, instance, and reference information of functions, and the format is fixed. Each field, including line breaks, is enclosed in single quotes and followed by "\".
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) ' \
    \ \ \ '
    @SEEALSO =bitand '
    
    
    gnm_bitand =functions[' Bitand '] # Gnumeric's bitwise SUM function return
    gnm_bitand (NUM1, num2)

At the end of the py_func.py file, there is also a special-purpose dictionary that provides the gnumeric with Python prototype information, which is called a functional prototype dictionary. The name of the function prototype dictionary is very strict and must be "_functions" as the suffix, "_" The previous name must be consistent with the Plugin.xml file so that Gnumeric can discover the various function information in the plug-in, otherwise gnumeric will appear many function information errors, resulting in the plug-in function can not be used. The function prototype is represented by a "key:value" pair in the Dictionary (Code 2), as Func_bitand,key is the function name that is mapped in Gnumeric Py_bitand,value is a tuple 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, a parameter type is represented by a special character, for example, the two floating-point number parameters of Func_bitand are expressed as "FF". The string representation of common parameter types is summarized as follows:

Another simple structured XML file, Plugins.xml (1), is the configuration information that developers provide to gnumeric. The name and description tags in the Information tab provide the names and descriptions of the plug-ins, and the internationalization of the information is simple, with the need to fill out the internationalization information in the corresponding label with the language tag. The Value property of the attribute tag in the loader label, the id attribute in the service tag, and the name attribute in the function label are most important, respectively, for the Python script file name, the function prototype dictionary name in the script (excluding the suffix), The key of the function prototype function. For this example, the property value is Py_func,test,py_printf,py_capwords,py_bitand, corresponding to the plug-in py_func.py and Test_functions,py_printf,py_capwords respectively , Py_bitand. These correspondences must be consistent, otherwise gnumeric will complain to you.
Code 3 py-func.py plugin.xml configuration file

<?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>

	
  

		
   

	
  

His experiment

Based on the analysis above, we see that writing the Gnumeric function in Python takes three steps:

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

2. Build a functional prototype dictionary, such as Test_functions, based on the function created.

3. Create plugin.xml configuration file, configure file name, function classification, name, prototype dictionary and other related information.

In order to demonstrate the process of creating a Python function in Gnumeric, the author has written a small function based on the automatic marking grade, consisting of two files of Plugin.xml and exam.py.

First create the script file exam.py, the entire file is only mark and CStr two functions: The mark function parameters and return values are strings, the function is based on their size to return the grade of the score; CStr is used to convert strings into utf-8 encodings so that Gnumeric can display Chinese (2). The annotation in the Mark function is the function information provided to the Gnumeric, and the reader can develop it simply by pressing the template.
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 ('illegal score')
   elif score <60:
     level = cstr ('Failed')
   elif score <80:
     level = cstr ('Pass')
   elif score <90:
     level = cstr ('good')
   elif score <= 100:
     level = cstr ('Excellent')
   else:
     level = cstr ('illegal score')
   return level
def 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 the registration function, the Exam_functions function prototype dictionary at the end of the exam.py file reveals the prototype information of the Mark function to Gnumeric, the dictionary's key ' Mark_ Score ' is Mark's name mapping in Gnumeric, F indicates that the parameter type is an integer and score is the parameter name. Plugin.xml (3) is based on a simple rewrite of the template, the main attention is the above mentioned several attributes, must and plug-ins corresponding, otherwise plug-ins are invalid; Other attributes, such as category also added Chinese information to facilitate the use.
Code 5 exam.py plugin.xml configuration file

<?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! now starts Gnumeric (4), according to the icon in column A, enter a column of results, and then enter the formula in the B1 cell: ' =mark_score (A1) ', and then use the mouse to drag and copy the formula, copy the formula to the corresponding column B, You will find that all the flags are automatically generated in column B.
Illustration 1 Classification of grades

Further

If it's just a simple calculation of cell data, Python is a fun toy at best in Gnumeric, but Python plug-ins are much more than that, and Python can control the data that reads and writes the range of cells, accesses all the functions of the Gnumeric, Control the creation of worksheets, and so on, these functions can be combined organically to complete complex tasks. This section further deals with the results of the class, using the summary function of Rpy (5) to perform a simple statistic on all fractions, calculating the maximum, mean, median, and two four-digit digits, and printing the resulting results to a new worksheet.

The first task to count the scores in the class is to get the data from the Gnumeric. For large amounts of data, the Gnumeric is represented by a range of cells, whereas a cell range reference (RANGEREF) is passed to Python during the invocation, so a cell range reference (RANGEREF) needs to be converted to extract the bulk data. Unfortunately, the Gnumeric API is in the process of development and there is no direct conversion method. Therefore, the author uses the function of Gnumeric itself to construct a Pygnmrange class. The Pygnmrange object takes a cell range reference (RANGEREF) as the initialization parameter, an index that builds all the cells in the range of cells, the "_table" property, and provides several methods for easy access, This allows us to manipulate cell data with the sheet object in the Gnumeric module.
Definition of code 6 class Pygnmrange

   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 needs to be noted for two points:

1. The cell subscript takes a column-first representation, counting from zero, such as B3 representation (1,2), so that it is consistent with the Gnumeric specification to facilitate manipulation of cell data.

2. The class initialization function uses four Gnumeric functions, column, columns, row, rows, and functions as follows:

With the prior preparation, we can concretely implement the summary function. The summary function obtains the current cell range reference through the Gnm_scores parameter, creates the Pygnmrange object using the parameter, calculates the subscript for all cells, and obtains the object of worksheet 1 through the workbooks and sheets functions of the Gnumeric module. To manipulate cell data in conjunction with Worksheet objects and cell subscripts. And the real Computing R language is complete, and the Rpy module is a bridge linking Python and R languages (6). Finally, the summary function obtains the results of the R language calculation and prints it to a newly created worksheet through the Gnumeric module.
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 written, the function is registered, the function prototype dictionary has only one line, the only thing to note is that the cell range reference data type needs to be represented by "R". The Plugin.xml file only needs to join the following line:
Code 8 plugin.xml configuration file for 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 is a plug-in function of the operation of the graph, the input data is randomly generated within 80 100 floating-point number, function inserted in the B1 cell, because the purpose of the function is to generate a simple report rather than return value, so after the run ended B1 cell is still blank, All of the data is printed in the new worksheet 4 (Figure 2 and Figure 3).
Illustration 2 class scores and input of functions

Illustration 3 Report of the class performance statistics

Plug-in deployment

Gnumeric plug-in deployment and its simplicity, users only need to be in their home directory to create a new Gnumeric directory, put the plug-in function can be, such as exam.py and Plugin.xml is located in

Conclusion

The Gnumeric python development process needs to be aware of several issues:

1. Gumeric python plug-ins are still actively developing, and some of the code is likely to change significantly in future releases; The Gnumeric module interface provided by the plugin is not complete, such as the lack of functions to get the active worksheet, which requires careful processing when writing Python functions.

2. Python function configuration is simple, but debugging is not very convenient, often appear gnumeric not get the Python information correctly, the reason is manifold, For example, the name of the Plugin.xml file is inconsistent with the script file, the function prototype dictionary naming is not standard, function document string format error, script file syntax error, etc.

Even so, for programmers who are familiar with Python, this doesn't affect the fun of writing gnumeric functions, it's not hard to deal with carefully. Hope this article can play a role, interested readers can refer to the Gnumeric source code in the developer documentation and Python plug-in source code, will find a lot of valuable information, write more valuable applications.