Synopsis

require 'spreadsheet_manip'

document = Spreadsheet.instance()

document.file=FILE_NAME

sheet = document.sheet(SHEET#)

sheet.remove_line_be(REFERENCE, EXPR)

sheet.remove_line_bv(REFERENCE, VALUE)

DESCRIPTION

At the time of this writing, “spreadsheet_manip” proposes two functions to delete lines from spreadsheet tables. More will follow. Each function is also represented in an executable which is installed with the Ruby-Gem: delete_line_be and delete_line_bv.

Only OOXML is supported, meaning that the spreadsheet file has to be in XLSX (®Microsoft Excel) or PMDX (®SoftMaker PlanMaker) format.

MOTIVATION

Automation of manipulations in spreadsheets is often asked for. At the same time, the unavailability of OLE and COM under Linux necessitate a different approach than that of the VBA environments which are available in ®Microsoft Windows.

While Office Suites may attempt to immitate the behaviour of their Windows-based counterparts, the freely available programming environments under Linux are already amply sufficient and allow an interested person to manipulate as much as the XML-libraries for her/his preferred programming languagee allow.

Ruby is just one programming language of so many. The best that you can do with this spreadsheet_manip gem is take it as an example for your own creations. The second best would be, maybe, to use it as the base for your own extensions.

PARAMETERS

FILE_NAME

The name of the spreadsheet-file. Should be *.pmdx or *.xlsx, but independent of the file-extension, the mime-type of the files will be checked prior action.

SHEET#

The number (position) of a spreadsheet table within the workbook. The first sheet is 1.

REFERENCE

For the two remove_line functions, reference is a column, like A, AC, Y. Lower-case letters are allowed, too.

EXPR

Is an expression which identifies cells. For the two remove_line functions, if the expression evaluates to TRUE, the lines which contain the cell will be entirely removed from the table.

RETURN VALUES

None of the interesting methods does currently return (usable) values. The result of all action must be verified in the altered spreadsheet-file.

Preparatory Action

Manipulations on a spreadsheet necessitate that methods of a sheet object be called. The sheet is one of many in a spreadsheet workbook.

Initially, you must create a spreasheet object, then set the file-name of the spreadsheet file to work on.

document = Spreadsheet.instance()

document.file=FILE_NAME

Next, indicate the position of the table in the workbook (the first being 1).

sheet = document.sheet(SHEET#)

EXECUTABLE FILES

The two remove_line functions come with an executable file, each, meaning that you do not actually have to programm anything, but can right away remove lines from a given spreadsheet file by calling one of delete_line_be or delete_line_bv.

You can call each of these utilities with an argument -h or --help to see the short help text.

NOTE 1 The operators in expressions must be enclosed by spaces, like in 'c = 100'.

NOTE 2 delete_line_bv does basically the same as delete_line_be, if the equation operator ('=') is used in the expression.

NOTE 3 For the time being, the executables will always create a backup-copy of the spreadsheet-file, even if the -b option is not used. The file-name of the copy is prefixed with "BAK_" and stored in the same directory as the original spreadsheet.

NOTE 4 The executables accept sheet-names instead of numbers.

OTHER INFORMATION

Ω