NAME

DESCRIPTION

The lpOD API doesn't make differences between document types in this area. So, tables are dealed with in the same way for a spreadsheet document (whose content is just a set of tables) as for any other document.

A table is an instance of the lpOD "odf_table" class, knowing that "odf_lpod" is an alias for "ODF::lpOD::Table".

An "odf_table" object is a structured container that holds two sets of objects, a set of rows and a set of columns, and that is optionally associated with a table style.

The basic information unit in a table is the cell. Every cell is contained in a row. Table columns don't contain cells; an ODF column holds information related to the layout of a particular column at the display time, not content data.

A cell can directly contain one or more paragraphs. However, a cell may be used as a container for high level containers, including lists, tables, sections and frames.

Every table is identified by a name (which must be unique for the document) and may own some optional properties.

Table creation and retrieval

A table is created using "odf_create_table" with a mandatory name as its first argument and the following optional parameters:

• "width", "length": the initial size of the new table (rows and columns), knowing that it's zero-sized by default (beware: because cells are contained in rows, no cell is created as long as "width" is less than 1);
• "size": specifies a length and a width (in this order) as a single string (the two values are comma-separated); may replace "length" and "width";
• "style": the name of a table style, already existing or to be defined;
• "cell style": the style to use by default for every cell in the table;
• "protected": a boolean that, if "TRUE", means that the table should be write-protected when the document is edited through a user-oriented, interactive application (of course, such a protection doesn't prevent an lpOD-based tool from modifying the table)(default is "FALSE");
• "protection key": a (supposedly encrypted) string that represents a password; if this parameter is set and if "protected" is "TRUE", a end-user interactive application should ask for a password that matches this string before removing the write-protection (beware, such a protection is not a security feature);
• "print": boolean, tells that the table should be printable; default is "TRUE";
• "print ranges": the cell ranges to be printed, if some areas are not to be printed; the value of this parameter is a space-separated list of cell ranges expressed in spreadsheet-style format (ex: "E6:K12").

Once created, a table may be incorporated somewhere using "insert_element" of "append_element", like any other "odf_element".

Caution: a table should not be inserted in any context. For example, a table should not be inserted within a paragraph. A bad placement may corrupt the document structure. Right contexts are, for example, the document body (in a spreadsheet or text document), a section (in a text document) or a table cell (knowing that the ODF standard allows nested tables).

The style of a table may be retrieved or changed at any time using the generic "get_style()" and "set_style()" accessors.

A table may be retrieved in a document according to its unique name using the context-based "get_table_by_name" method with the name as argument. It may be selected by its sequential position in the list of the tables belonging to the context, using "get_table_by_position", with a zero-based numeric argument (possibly counted back from the end if the argument is negative). A "get_table()" method is provided, that works like "get_table_by_position()" if the argument is numeric or like "get_table_by_name()" otherwise (of course, if the name of the desired table looks like a number, there is no choice but "get_table_by_name()" to retrieve it by name). Without argument, "get_table()" returns the first table in the context (if any). In addition, it's possible to retrieve a table according to its content, through "get_table_by_content"; this method returns the first table (in the order of the document) whose text content matches the given argument, which is regarded as a regular expression.

In addition, an application can get all the tables of a given context using the "get_tables()" method, without argument.

An application may retrieve a table from any element that belongs to it, thanks to the "get_parent_table()" method. This method returns "undef" if the calling element is not in a table. Knowing that a paragraph may be included in a table cell and that a table cell indirectly belongs to a table, the following sequence selects a paragraph matching a given expression and, if the paragraph belongs to a table, displays the table name:

    $p = $context->get_paragraph(content => "xyz");
    die "Content not found\n" unless $p;
    $t = $p->get_parent_table;
    say $t ? $t->get_name : "Not in a table";

Table content retrieval

Every table object selection method returns a null value, without error, when the given address is out of range.

The number of rows and columns may be got using the "odf_table" "get_size" method, that returns the heigth then the width.

An individual cell is selected using "get_cell" with either a pair of numeric arguments corresponding to the row then the column, or an alphanumeric argument whose first character is a letter. The second argument, if provided, is ignored as soon as the first one begins with a letter.

The two following instructions are equivalent and return the second cell of the second row in a table (assuming that $t is a previously selected table):

        $cell = $t->get_cell('B2');
        $cell = $t->get_cell(1, 1);

"get_row()" selects a table row as an ODF element. This method requires a zero-based numeric value.

"get_column()" works according to the same logic and returns a table column ODF element.

The full set of row and column objects may be selected using the table-based "get_rows()" and "get_columns()" methods. By default these methods return respectively the full list of rows or columns. They can be restricted to a specified range of rows or columns. The restriction may be expressed through two numeric, zero-based arguments indicating the positions of the first and the last item of the range. Alternatively, the range may be specified using a more "spreadsheet-like" syntax, in only one alphanumeric argument representing the visible representation of the range through a GUI; this argument is the concatenation of the visible numbers of the starting and ending elements, separated by a ":", knowing that "1" is the visible number of the row zero while "A" is the visible number or the column zero. As a consequence, the two following instructions are equivalent and return a list including the rows from 5 to 10 belonging to the table t:

        @rows = $t->get_rows(5, 10);
        @rows = $t->get_rows('6:11');

According to the same logic, each of the two instructions below returns the columns from 8 to 15:

        @cols = $t->get_columns(8, 15);
        @cols = $t->get_columns('I:P');

Once selected, knowing that cells are contained in rows, a row-based "get_cell()" method is provided. When called from a row object, "get_cell()" requires the same parameter as the table-based "get_column()" method. For example, the following sequence returns the same cell as in the previous example:

        $r = $t->get_row(1);
        $c = $r->get_cell(1);

A column-based "get_cell()" method is provided, too, but it's much less efficient and may produce performance issues. In addition, the column-based "get_cell()" may fail with a warning when used in read optimize mode (see below).

A row set may be selected according to the content of a specified cell, thanks to "get_rows_by_index()". The following example selects all the rows (if any) where the 'C' cell (i.e. the cell at the 3rd position) contains "XYZ":

        @rows = $table->get_rows_by_index(C => "XYZ");

Note that this method allows an alternative syntax; the cell position may be specified by its numeric (zero-based) position:

        @rows = $table->get_rows_by_index(2, "XYZ");

The first argument (or the key in hash notation) specifies the "index" (i.e. the column that must match a given condition) while the second argument is the search value. The result set is selected according to a smart match.

Alternatively, "get_row_by_index()" returns the first matching row, like "get_rows_by_index()" in scalar context.

Remember that there is no real index in a spreadsheet table; this method mimics the use of an arbitrary column as the "key" to select a data set, but the underlying mechanism is not a database engine; the rows are scanned sequentially, so take care of possible performance issues with large tables.

Cell range selection

        @cells = $table->get_cells("B2:D15");
        @cells = $table->get_cells(1,1,14,3);

Note that, after such a selection, $cells[0][0] contains the "B2" cell of the ODF table.

If "get_cells" is called without argument, the selection covers the whole table.

A row object has its own "get_cells()" method. The row based version of "get_cells()" returns, of course, a one-row table of cell objects. When used without argument, it selects all the cells of the row. It may be called with either a pair of numeric arguments that represent the start and the end positions of the cell range, or an alphanumeric argument (whose the numeric content is ignored and should be omitted) corresponding to the start and end columns in conventional spreadsheet notation. The following example shows two ways to select the same cell range (beginning at the 2nd position and ending at the 26th one) in a previously selected row:

        @cells = $r->get_cells('B:Z');
        @cells = $r->get_cells(1, 25);

The elements of the Perl table returned by "get_cells" are references to the cells of the ODF table (not copies); the Perl table just maps an ODF table area, and any cell property change made through this Perl table affects the underlying ODF cell.

A column-based version of "get_cells()" is available, too, but it should be avoided with large tables, and it may explicitly fail in "read optimize" mode.

Named cell ranges

While it appears in a given table, a named range is not a table element; it's defined at the document level, so it's selected or inserted through document- based methods.

An existing named range may be selected using "get_named_range()" from the document. The name of the range is the only one required argument:

        my $range = $doc->get_named_range("MyRange");

The returned object is an "ODF::lpOD::NamedRange", whose description may be got through "get_properties()", that returns a hash (in array context) or a hash ref (in scalar context) that contains the following items:

• "table": the name of the table containing the range;
• "start": the alphanumeric coordinates of the first cell (ex: "C4");
• "end": the alphanumeric coordinates of the last cell (ex: "F12");
• "range": the alphanumeric range expression, including the "start" and "end" values, with a colon as separator (ex: "C4:F12"); this string may be used as the argument of the "get_cells()" table-based method.
• "usage": the range usage, that can be either 'none' or a space-separated list that consists of any of the values 'print-range', 'filter', 'repeat-row' or 'repeat-column' (see the ODF specification for details about the effects of these options).

The "ODF::lpOD::NamedRange" class provides a "get_cells()" method, that returns the same results as the table-based "get_cells()", but that doesn't require any argument. So, the two following sequences produce the same final result:

        # first form
        $p = $doc->get_named_range("RangeName");
        $table = $doc->get_body->get_table($p->{table});
        @cells = $table->get_cells($p->{range});

        # second form
        @cells = $doc->get_named_range("RangeName")->get_cells;

A "get_cell_values()" method is provided, too. It produces the same result as the table-based "get_cell_values()" method, but, of course, it requires only one argument (the data type). Example:

        $r = $doc->get_named_range("RangeName")->get_cell_values('float');
        say "I found $r->[0] float values in the range";
        say "...the min is $r->[1] and the max is $r->[2]";
        say "...and the grand total is $r->[3]";

The possible uses of "get_cell_values()" are documented in the section dedicated to special cell value extractors in the present manual chapter.

The document-based "set_named_range()" method allows the applications to create new named ranges. It requires a unique range name as its first argument. The other parameters must be provides according to the same principles as the return value of "get_properties()", introduced above. As an example, the following instruction registers a new range running from "C4" to "F12", whose name is "MyArea" in the "Sheet1" table:

        $doc->set_named_range(
                "MyArea",
                table   => "Sheet1",
                range   => "C4:F12"
        );

The example below creates a named range whose properties are borrowed from an existing named range belonging to another document:

        my $range_spec = $doc1->get_named_range("R1")->get_properties;
        $doc2->set_named_range("R1", $range_spec);

The 'usage' parameter is optional; it's default value is 'none'.

The return value of "set_named_range()" is the new named range object.

The properties of an existing named range may be changed at any time using "set_properties()".

Be careful: while the uniqueness of the name is checked by "set_named_range()" there is no other consistency check. So the user is allowed to register a named range without table or range specification, or with a non existent table name, or with a range where the starting cell comes after the ending one.

A named range, once retrieved, may be removed using the standard "delete()" element-based method. Example:

        $nr = $doc->get_named_range("MyRange");
        $nr && $nr->delete;

Note that "get_named_range()" and "set_named_range()" work in spreadsheet documents only.

Row and column customization

• "default cell style name": the default style which apply to each cell in the column or row unless this cell has no defined style attribute;
• "visibility": specifies the visibility of the row or column; legal values are 'visible', 'collapse' and 'filter'.

The style may be get or set using "get_style" or "set_style".

Table expansion and shrinking

        $t->add_row(number => 1, after => -1);
        $t->add_row();

        $t->add_row(number => 4, before => 0);

   $to_be_inserted = $t1->get_row(0)->clone;
   $t2->insert_element($to_be_inserted, after => $t2->get_row(5));

        # with delete_row
        $table->delete_row($row_number);

        # without delete_row
        $row = $table->get_row($row_number);
        $row->delete;

Row and column group handling

        $column_group = $table->set_column_group(3, 5);
        $column_group = $table->set_column_group("C:E");

The same idea apply to row groups; however, beware that in range alphanumeric notation, the numbers represents the spreadsheet end-user point of view, so they are one-based; as an example, the two following instructions, that create a row group including the rows 3 to 5, are equivalent:

        $row_group = $table->set_row_group(3, 5);
        $row_group = $table->set_row_group("4:6");

In addition, an optional "display" named boolean parameter may be provided (default="TRUE"), instructing the applications about the visibility of the group.

Both "set_row_group()" and "set_column_group()" return an object which can be used later as a context object for any row, column or cell retrieval or processing. An existing group may be retrieved according to its numeric position using "get_row_group()" or "get_column_group()" with the position as argument, or without argument to get the first (or the only one) group.

A group can't bring a particular style; it's just visible or not. Once created, its visibility may be turned on and off by changing its "display" value through "set_attribute()".

Knowing that cells depends on rows, a row group provides the same "get_cell()" method as a table. It provides a "get_row()" method, while a column group provides a "get_column()" one.

A row group provides a "add_row()" method, while a column group provides a "add_column()" method. These methods work like their table-based versions, and they allow the user to expand the content of a particular group.

Row and column group may be collapsed or expanded using their "collapse()" and "uncollapse()" methods.

It's possible to delete all the cell contents of a group using "clear()". This method doesn't remove any row or column; it just erases the content and, if any, the style and the annotation of every cell. Beware that the column group based version of "clear()" is much slower than the row group based version.

Table headers

Note that a column header is a row or a set of rows containing column titles that should be automatically repeated on every page if the table does not fit on a single page, while a row headers is a column or a set of columns containing row titles. In the present version, row headers are not fully supported.

A table can't directly contain more than one row header and one column header. However, a column group can contain a column header, while a row group can contain a row header. So the header-focused methods above work with groups as well as with tables.

A table header doesn't bring particular properties; it's just a construct allowing the author to designate rows and columns that should be automatically repeated on every page if the table doesn't fit on a single page.

The ``get_xxx()`` table-based retrieval methods ignore the content of the headers. However, it's always possible to select a header, then to used it as the context object to select an object using its coordinates inside the header. For example, the first instruction below gets the first cell of a table body, while the third and third instructions select the first cell of a table header::

   c1 = table.get_cell(0,0)
   header = table.get_header()
   c2 = header.get_cell(0,0)

Individual cell property handling

The cell content is a list of one or more ODF elements. While this content is generally made of a single paragraph, it may contain several paragraphs and various other objects. The user can attach any content element to a cell using the standard "insert_element" method. However, for the simplest (and the most usual) cases, it's possible to use "set_text". The cell-based "set_text" method diffs from the generic "odf_element" "set_text": it removes the previous content elements, if any, then creates a single paragraph with the given text as the new content. In addition, this method accepts an optional "style" named parameter, allowing the user to set a paragraph style for the new content. To insert more content (i.e. additional paragraphs and/or other ODF elements), the needed objects have to be created externally and attached to the cell using "insert_element" or "append_element". Alternatively, it's possible to remove the existing content (if any) and attach a full set of content elements in a single instruction using "set_content"; this last cell method takes a list of arbitrary ODF elements and appends them (in the given order) as the new content.

The generic "group()" method may be used to grab a list of paragraphs in order to move them in the cell. As an example, the following instruction moves all the paragraphs containing a given substring in a given cell:

    $table->get_cell("B4")->group(
        $doc->get_body->get_paragraphs(content => "XYZ")
        );

The "get_content" cell method returns all the content elements as a list. For the simplest cases, the cell-based "get_text" method directly returns the text content as a flat string, without any structural information and whatever the number and the type of the content elements.

The cell properties may be read or changes using "get_xxx" and "set_xxx" methods, where "xxx" stands for one of the following:

• "style": the name of the cell style;
• "type": the cell value type, which may be one of the ODF supported data types, used when the cell have to contain a computable value (may be omitted with text cells, knowing that the default type is 'string');
• "value": the numeric computable value of the cell, used when the "type" is defined (for a string cell, "get_value" and "set_value" are equivalents of "get_text" and "set_text");
• "currency": the international standard currency unit identifier (ex: EUR, USD), used when the "type" is 'currency';
• "formula": a calculation formula whose result is a computable value (the grammar and syntax of the formula is application-specific and not checked by the lpOD API (it's stored as flat text and not interpreted);
• "protect": boolean (default "FALSE"), tells the applications that the cell can't be edited.

If "set_currency" is used with a non-null value, then the "type" of the cell is automatically set to 'currency'. If "set_type" forces a type that is not 'currency', then the cell currency is unset.

A cell may be annotated using "set_annotation()". The cell-based version of this method works like the paragraph-based version, described in ODF::lpOD::TextElement, but the positioning options are ignored. A cell annotation is not linked to a text position and may be attached to an empty cell. A "display" boolean option (whose default is "FALSE") may be provided in order to make the annotation automatically visible in the sheet.

It's possible to remove all the content and the properties of a cell but its style, including any possible formula, annotation, and so on, with the "clear()" method. In addition, "clear()" removes any multi-row or multi- column span.

Note that it's possible to clear the content of all the cells of a row, a column, a row group, a column group, or a table, with the respective "clear()" methods of these objects. These methods don't remove the cells themselves. However, remember that the column and column group based versions of "clear()" are very slow.

The cell coordinates may be retrieved using "get_position()". In scalar context, this method returns the local position in the row. In array context, it returns the table name, the row number and the column number. In addition, "get_parent_table()" returns the table object itself, while "get_parent_row()" returns the including row.

Special cell value extractors

        $value = $table->get_cell($row, $column)->get_value;
        $value = $table->get_cell_value($row, $column);

        @values = $table->get_cell_values('currency', 'E2:G10');

        $row = $table->get_row(3);
        @amounts = $row->get_cell_values('currency', 'C:H');

        $r = $table->get_cell_values('float', 'E2:G10');
        say "I found $r->[0] values";
        say "...from $r->[1] to $r->[2]";
        say "...and the grand total is $r->[3]";

Cell span expansion

        $cell = $table->get_cell('B4');
        $cell->set_span(rows => 3, columns => 4);

The existing span of a cell may be get using "get_span()", which returns the "rows" and "columns" values.

This method changes the previous span of the cell. The default value for each parameter is 1, so a "set_span()" without argument reduces the cell at its minimal span.

When a cell is covered due to the span of another cell, it remains present and holds its content and properties. However, it's possible to know at any time if a given cell is covered or not through the boolean "is_covered()" cell method. In addition, the span values of a covered cell are automatically set to 1, and "set_span()" is forbidden with covered cells.

Note that a cell that spreads over multiple rows and/or columns is reduced to the minimal size by "clear()".

Performance issues

        # table scan, way 1
        my @text = ();
        my ($l, $w) = $table->get_size;
        for (my $i = 0 ; $i < $l ; $i++) {
                for (my $j = 0 ; $j < $w ; $j++) {
                        push @text, $table->get_cell($i, $j)->get_text;
                        }
                }

        # table scan, way 2
        my @text = ();
        my ($l, $w) = $table->get_size;
        for (my $i = 0 ; $i < $l ; $i++) {
                my $row = $table->get_row($i);
                for (my $j = 0 ; $j < $w ; $j++) {
                        push @text, $row->get_cell($j)->get_text;
                        }
                }

        $value = $table->get_cell($row, $column)->get_value;
        $value = $table->get_cell_value($row, $column);

        # first form
        @values = ();
        push @values, scalar $_->get_value
                for $row->get_cells($start, $end);

        # second form
        @values = $row->get_cell_values($start, $end);

        my @cells = $table->get_cells("B4:Z50");

        $text = $table->get_cell("C5")->get_text;
        $text = $cells[1][1]->get_text;

        my $table = $doc->get_body->get_table("Sheet1");
        $table->set_working_area(500, 100);

        my $table = $doc->get_body->get_table("Sheet1");
        $table->read_optimize(TRUE);

        $table->read_optimize(TRUE);
        $cell = $table->get_cell("Z26");
        $table->read_optimize(FALSE);
        $cell->set_value(1234);

AUTHOR/COPYRIGHT

Copyright (c) 2010 Ars Aperta, Itaapy, Pierlis, Talend. Copyright (c) 2011 Jean-Marie Gouarne.

This work was sponsored by the Agence Nationale de la Recherche (<http://www.agence-nationale-recherche.fr>).

License: GPL v3, Apache v2.0 (see LICENSE).