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)
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.
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.
None of the interesting methods does currently return (usable) values. The result of all action must be verified in the altered spreadsheet-file.
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#)
document = Spreadsheet.instance()
document.file = /home/user/plan/figures_2019/proposal.pmdx
sheet = document.sheet(2)
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.
Remove from the second table in file test.xlsx all lines where the cell in column 'E' is empty:
:~$ delete_line_be -d"E empty" -s2 test.xlsx
Remove from the table "new preview" in the file simmulation.pmdx all lines which have the string 'approximation' in column 'B':
:~$ delete_line_bv simmulation.pmdx -s"new preview" -d"B = approximation"
Source-code: | "Spreadsheet_Manip" had been developed in Ruby and is distributed as a ruby-gem. If you installed the program from the gem-file (with gem install), then you find all the source-files in the gem-folder for your Ruby-version. Otherwise, you can also unpack the gem-file (with tar), then uncompress the data-archive and take a look at the files in the resulting, new folders bin and lib. |
---|---|
License: | "Spreadsheet_Manip" is released under the conditions of the GNU General Public License, version 3.0. |
Version: | 0.1 of 2019/12/10 |
Author: | "Spreadsheet_Manip" has been programmed by Michael Uplawski <michael.uplawski@uplawski.eu>. All testing has been done with ®SoftMaker PlanMaker (®SoftMaker Office 2018). |
Ω