[Calc, Python] Expand/shrink list ranges

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Calc, Python] Expand/shrink list ranges

Post by Villeroy »

EDIT 2023-02-20: Added a reduced version for LIbreOffice where merged cells are not handled. LibreOffice changed the way how merged cells are handled on cell insertion which interfered with my macro in strange ways. Scroll down this topic and find "InsertCalcRows_LO.py.odt"
EDIT 2023-02-26: Replaced the zip packages with text documents and installation macros.

One of the drawbacks of list processing in spreadsheets is the risk of losing the right references when you simply append data to the empty cells below the previously used range. For instance, the sum of a column =SUM($A$1:$A$99) the will *not* expand to =SUM($A$1:$A$100) when you simply append new data in row 100. All references expand automatically after insertion of new cells in between the existing rows. The same problem occurs when you refresh sorted and/or filtered ranges. New data are affected by the filter/sort operation only if they are entered into newly inserted cells. Chart ranges, validation lists and conditional formatting are other features where expansion by insertion takes place. (or shrink by removal respectively)

One option related to this issue is Menu:Tools>Options...Calc>General, "Expand references when new columns/rows are inserted". With this option set, you get your references adjusted even when you insert cells at the end of a list (insert columns at the right side or insert rows directly below a range). This can be turned off because this behaviour can be quite annoying when designing a calculation model.

Quite often such a list includes calculated fields:

Code: Select all

Article Count Price Amount
X-08/15  20  9.99  =Count*Price
Whenever you insert a new row into your spreadsheet list, you have to copy the formulas into the new records.
The following pair of Python macros takes care of all this. I use them with shortcuts Ctrl+Ins and Ctrl+Del.

The routine called "insertListRows" works like this:
Get the currently selected range of cells and break silently if the selection is not not a single range.
Get the width of the current range (adjacent non-empty cells around the selection) as width of the list range.
Take the height of the current selection as row count to be inserted.
Since 2008-10-17: Unmerge merged ranges
Turn on the above mentioned option "Expand references when new rows/columns are inserted".
Insert rows according to the detected width and height.
Turn off the above mentioned option if it was not set before.
Detect all formulas in the row above and copy them down into the new rows. This can not work with the topmost row of a list.
Since 2008-10-17: Remerge previously merged ranges
Select the remaining empty range(s) for editing.

The routine called "removeListRows" works like this:
Get the currenly selected range of cells and break silently if the selection is not a single range.
Get the width of the current range (adjacent non-empty cells around the selection) as width of the list range.
Take the height of the current selection as row count to be removed.
Since 2008-10-17: Unmerge merged ranges
Remove rows according to the detected width and height.
Since 2008-10-17: Remerge previously merged ranges
Select the range where the cells have been removed.

Installation
Download the attached file and extract the python script from the zip archive. Feel free to change the script's file name, but keep the .py ending.
Application wide installation for all users:
Get administrator access and copy the Python script to <install_directory>/share/Scripts/python/
Typical path in Windows: C:\Programs\OpenOffice 2.3\share\Scripts\python\
Typical path in Linux: /opt/openoffice2.3/share/Scripts/python/
Single user installation:
Due to a bug in v3.0 Python macros work application wide only (fixed in 3.0.1)
Copy the Python script to <ooo_user_directory>/user/Scripts/python/
You may need to create the python directory below Scripts.
Typical path in Windows:
C:\Documents and Settings\username\Application Data\OpenOffice.org2\user\Scripts\python\
Typical path in Linux: ~/.openoffice2/share/Scripts/python/

When you assign the two routines to shortcuts, buttons or menu entries you'll find them in Openoffice.org Macros>user or shared>InsertCalcRows>"insertListRows" and "removeListRows" ("InsertCalcRows" refers to the file "InsertCalcRows.py")

If you are interested in the source code, open the file in a decent text editor (not Windows Notepad), able to handle UNIX line feeds (ASCII 10). Python macros require this type of line feeds and file name extension *.py
You may copy "insertListRows" and "removeListRows" into your own Python modules. Just append the two symbols to your global list g_exportedScripts.

Usage
Click any cell within your list (or directly below) where you want to insert a new row and hit Ctrl+Ins (if that is the shortcut you assigned to "InsertListRows"). Include more rows in the selection to insert more rows. Same with removal of cells. Formulas will copied down into new rows from adjacent rows above.
2010-02-03: New version, slightly enhanced and streamlined

Icons
2011-09-09: User StephTech created a pair of icons for the two macros. Feel free to use them with your user-defined icons and menues.
Icon for the insertListRow macro (113x121 Pixel)
Icon for the insertListRow macro (113x121 Pixel)
insListRows.png (6.08 KiB) Viewed 27188 times
Icon for the removeListRow macro (113x116 Pixel)
Icon for the removeListRow macro (113x116 Pixel)
removeListRows.png (5.97 KiB) Viewed 27188 times
Thank you very much, StephTec.
Attachments
InsertCalcRows_AOO.odt
Insert rows into current region and delete rows from current region
(18.69 KiB) Downloaded 144 times
Last edited by Villeroy on Sun Feb 26, 2023 3:33 pm, edited 4 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Thell
Posts: 11
Joined: Tue Sep 21, 2010 11:35 pm

Re: [Calc, Python] expand/shrink list ranges

Post by Thell »

Thank you very much for creating and posting this script.

It was a little frustrating at first :crazy: since I read your comment:
Due to a bug in v3.0 Python macros work application wide only (fixed in 3.0.1)
I tried using the User script path and it indeed did not work yet after moving the script into the shared path everything was wonderful.

Thanks again!
OOo3.2.1 Win 7, XP and various Linux distros.
User avatar
StephTech
Posts: 6
Joined: Thu Sep 01, 2011 4:56 am
Location: Houston, Texas, United States

Re: [Calc, Python] Expand/shrink list ranges

Post by StephTech »

Thank you for sharing your script! I can see myself using it frequently. Just a note for more recent readers, on OOo 3.3 (Win7) I found the correct install location to be:
...\OpenOffice.org 3\Basis\share\Scripts\python
Thanks again :)
OpenOffice 3.3
Win7 SP1 64-bit
HP dv7 laptop
FrogFan
Posts: 27
Joined: Tue Aug 30, 2016 6:51 pm

Re: [Calc, Python] Expand/shrink list ranges

Post by FrogFan »

I know this is an old thread, but I wanted to add my thanks for this handy script. It works great for me.

By the way, my installation directory is ...\OpenOffice 4\share\Scripts\python
OpenOffice 4.1.3 on Windows 7
FrogFan
Posts: 27
Joined: Tue Aug 30, 2016 6:51 pm

Re: [Calc, Python] Expand/shrink list ranges

Post by FrogFan »

I noticed that this script doesn't work if the sheet I'm trying to insert or delete from is protected. I understand how to unprotect and protect a sheet with a macro in OpenOffice.org basic. Can I call this python script from a basic macro? I'd like to unprotect, insert or delete, then reprotect.

I'm a rookie with this OpenOffice macro stuff, so apologies if this is a stupid question.

The idea behind the protect is to simply avoid inadvertent input of data into a cell that contains a formula.

Thanks for any help.
OpenOffice 4.1.3 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Python] Expand/shrink list ranges

Post by Villeroy »

Open the Python script in Writer and turn on View>Non-printing characters (Ctrl+F10)
Find this block:

Code: Select all

    def unmerge(self,):
        self.merge(False)

    def remerge(self,):
        self.merge(True)
Insert new lines with 8 leading spaces just like the other lines in the 2 routine blocks. Spaces mean something in Python.

Code: Select all

    def unmerge(self,):
        self.Sheet.unprotect("password")
        self.merge(False)

    def remerge(self,):
        self.merge(True)
        self.Sheet.protect("password")
Modifying the Python code in this way makes it dysfunctional for sheets that are not protected with the hard-coded password. Save the modified Python code under some other name with .py suffix and use the new version with your particular spreadsheet. To be honest: I would not use any sheet protection because it's pointless.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
FrogFan
Posts: 27
Joined: Tue Aug 30, 2016 6:51 pm

Re: [Calc, Python] Expand/shrink list ranges

Post by FrogFan »

Thanks very much, Villeroy. I appreciate your patience.

I understand your point about sheet protection. I've read a few of your posts as I lurked about here <smiles>. I'm preparing this spreadsheet for my 70 year old sister-in-law, who has to track attendance for a women's club she belongs to. She knows little about computers and spreadsheets, but as long as instructions are clear, she can manage. I've put some formulas in the spreadsheet to auto-populate some fields to make it easier for her to account for some quirky thinks the club does. All I'm really trying to do is safeguard those fields against inadvertent data entry. I can tell her not to enter anything in these fields and she won't, deliberately, but someday she will and things will "break".

On your suggestion, I'll try to do this differently. Off the top of my head, I'm thinking of doing a validation, allowing only a character string she'd never enter inadvertently. In that case, any data she'd conceivably enter would not be allowed, and the effect would be the same but no sheet protection would be required. What do you think of that approach? Is there a better achieve what I'm trying to accomplish?

Thanks again very much for your help.
OpenOffice 4.1.3 on Windows 7
Herman753
Posts: 3
Joined: Tue Mar 06, 2018 12:51 pm

Re: [Calc, Python] Expand/shrink list ranges

Post by Herman753 »

Hi, Im using LibraOffice 6, and I have problem with instalation this script (LibraOffice Calc 6, Windows 7).
I found patch to python scripts, and I pasted script there: C:\Program Files\LibreOffice\share\Scripts\python
What I need to do next?
Thank you for your help in advance :)
Regards
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Python] Expand/shrink list ranges

Post by Villeroy »

Tools>Customize... tab [Keyboard]
Click the shortcut list and hit Ctrl+Insert
You find the macro under "LibreOffice Macros">"LibreOffice">InsertCalcRows, Function:InsertListRows
Select it and then click the [Modify] button.
Then select Ctrl+Delete in the shortcut list, "RemoveListRows" in the functions list and click the [Modify] button.
Confirm all changes with [OK]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Herman753
Posts: 3
Joined: Tue Mar 06, 2018 12:51 pm

Re: [Calc, Python] Expand/shrink list ranges

Post by Herman753 »

Wow, this works perfectly!
Thank you very much for your quick answer, you are great :)
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Python] Expand/shrink list ranges

Post by Villeroy »

Reduced version for LibreOffice. It does not handle merge cells because this feature interfered with LO Calc's handling of merged cells.
Attachments
InsertCalcRows_LO.odt
Insert rows into current region and delete rows from current region (LibreOffice version)
(23.33 KiB) Downloaded 140 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply