https://bugs.documentfoundation.org/show_bug.cgi?id=172190
Bug ID: 172190
Summary: Calc: Multi-cell content clearing via Basic macro
removes inner cell borders in xlsx files (works
correctly with physical keyboard Delete) +
Enhancement: no programmatic way to dismiss "marching
ants" clipboard indicator
Product: LibreOffice
Version: 26.2.3.2 release
Hardware: All
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
SYMPTOM:
When running a Basic macro that clears content of a multi-cell range in an xlsx
file (using any of the documented methods: oSel.clearContents(N),
.uno:ClearContents dispatch, setString("") per cell, getDataArray/setDataArray,
or external SendKeys via WScript.Shell), all inner cell borders within the
selection are removed. Only the outer perimeter remains, as if the range had
collapsed into a single cell.
The same operation performed via physical keyboard Delete key preserves all
inner borders correctly.
STEPS TO REPRODUCE:
1. Open an xlsx file (originally created/edited in Excel or imported from
external source).
2. Apply both inner and outer borders to a multi-cell range (e.g., A1:C3
with Format > Cells > Borders > "Box and inner lines").
3. Add content to each cell.
4. Create a simple Basic macro in My Macros > Standard > Module1:
Sub TestClear
Dim oSel As Object
oSel = ThisComponent.getCurrentSelection()
oSel.clearContents(31) ' value+date+string+annotation+formula
End Sub
5. Select the bordered range.
6. Run the macro.
EXPECTED BEHAVIOR:
Content cleared from all cells. All inner and outer cell borders preserved
(flag 31 excludes HARDATTR=32, so formatting including borders should not
be touched).
ACTUAL BEHAVIOR:
Content cleared. Inner cell borders DISAPPEAR. Only the outer 4-line
bounding box of the range remains. The range appears as one merged block,
even though no actual merging occurred.
ENVIRONMENT:
- LibreOffice 26.2 (Windows 11 Pro x64)
- File format: .xlsx (originally from external source)
- Conditional formatting: none
- Merged cells: none
- Cell style: Default
WORKAROUND:
Physical keyboard Delete (selecting range and pressing Del) preserves all
borders correctly. The bug is specific to programmatic/macro-triggered
content clearing.
ADDITIONAL TESTING:
The following alternative approaches all exhibit the same bug:
- oSel.clearContents(31) -- direct API
- .uno:ClearContents dispatch (with or without "Flags" property)
- For loop with oCell.setString("") per cell
- DataArray bypass (getDataArray + modify + setDataArray)
- com.sun.star.awt.Robot keyPress with Key.DELETE
- CreateObject("WScript.Shell").SendKeys "{DEL}"
- External wscript.exe process spawned via Shell, sending Ctrl+C and Delete
via SendKeys after 200-400ms delay (intended to bypass SolarMutex contention)
NONE of the programmatic approaches preserve inner borders. Only physical
hardware-originated keyboard Delete works.
The bug appears to occur in the xlsx import filter's XF (Extended Format)
border pool handling. When the LibreOffice cell topology is modified via
any API/dispatch/synthetic input path, an "XF pool optimization" is
triggered that collapses shared inner borders into a single outer frame.
In a brand new (blank) Calc spreadsheet without xlsx import, the same
macro works correctly and preserves all borders. The bug is specific to
files imported from xlsx format with the original border structure.
This makes Calc unusable for any automation/macro workflow on xlsx files
that have user-applied multi-cell borders.
==============================================================
RELATED ENHANCEMENT REQUEST: Programmatic dismissal of clipboard
"marching ants" indicator
==============================================================
CURRENT BEHAVIOR:
After .uno:Copy or .uno:Cut (or any operation that fills the Calc
clipboard), Calc displays an animated dashed border ("marching ants")
around the source range to indicate the clipboard is active. This
indicator only disappears when:
- Another Cut/Copy is performed, OR
- The user manually presses the physical Escape key
There is no documented programmatic way to dismiss this indicator from
a Basic macro while preserving the Calc internal clipboard (ScTransferObj)
state required for rich paste (formulas, formatting).
USE CASE:
A macro that implements custom Cut/Copy behavior (e.g., "cut content but
preserve formatting" — see main bug above) leaves the marching ants
animation persistently visible after the macro completes. This is
visually distracting and creates an inconsistent user experience compared
to the manual Ctrl+X behavior, where the indicator behaves predictably.
Microsoft Excel has Application.CutCopyMode = False which dismisses
the equivalent indicator while preserving clipboard state. LibreOffice
Calc has no equivalent.
ATTEMPTED PROGRAMMATIC DISMISSAL METHODS (none work reliably):
- oDispatcher.executeDispatch(oFrame, ".uno:Escape", "", 0, Array())
- oDispatcher.executeDispatch(oFrame, ".uno:Cancel", "", 0, Array())
- oDispatcher.executeDispatch(oFrame, ".uno:Deselect", "", 0, Array())
- Programmatic cell selection change via controller.select(...)
- .uno:SetInputMode (F2) immediately followed by .uno:Cancel
(only partially works, side-effects depend on cell content state)
The only thing that reliably dismisses marching ants is the physical
Escape key press — but that also wipes the rich Calc clipboard, leaving
only plain text. So even the "manual Escape" path is not a clean solution.
REQUEST:
Add one of:
(a) A new UNO command like .uno:ClearClipboardIndicator that dismisses
only the visual marching ants, preserving ScTransferObj for rich paste.
(b) An overload of existing dispatch (e.g., a parameter for .uno:Cancel)
that does the equivalent.
(c) A documented "magic incantation" of existing API calls if one already
exists in the codebase but is not user-discoverable.
This would make programmatic Cut workflows in Calc match the polish
of equivalent Excel automation.
Steps to Reproduce:
1. Open an xlsx file imported from Excel or saved in xlsx format with
inner+outer cell borders applied to a multi-cell range (e.g., A1:C3
with Format > Cells > Borders > "Box and inner lines").
2. Add content to each cell.
3. Create a Basic macro in My Macros > Standard > Module1:
Sub TestClear
Dim oSel As Object
oSel = ThisComponent.getCurrentSelection()
oSel.clearContents(31)
End Sub
4. Select the bordered multi-cell range.
5. Run the macro (Tools > Macros > Run Macro, or via assigned keyboard
shortcut).
Actual Results:
Content is cleared from all cells (expected). However, inner cell borders
within the selection DISAPPEAR. Only the outer 4-line bounding box of
the range remains. The range visually collapses as if it were a single
merged cell — even though no actual cell merging occurred.
The same behavior is observed with every alternative programmatic approach
tested:
- oSel.clearContents(31) direct API
- .uno:ClearContents dispatch (with/without "Flags" PropertyValue)
- per-cell oCell.setString("") loop
- DataArray bypass (getDataArray + modify in-memory + setDataArray)
- com.sun.star.awt.Robot keyPress with Key.DELETE
- CreateObject("WScript.Shell").SendKeys "{DEL}" from Basic
- External wscript.exe spawned via Shell, sending Ctrl+C and Delete via
SendKeys with 200-400ms delay (intended to escape SolarMutex contention)
- Pure-launcher pattern: macro contains NO UNO commands, only spawns VBS
that does both Ctrl+C and Delete via SendKeys
NONE of the programmatic methods preserve inner borders. The behavior
appears to be a Calc xlsx-import XF border pool optimization that
triggers on any macro/synthetic-input modification.
Expected Results:
Content should be cleared from all cells with all borders (inner AND outer)
preserved intact. The flag value 31 explicitly excludes HARDATTR=32, so
formatting/borders should not be affected per documented API behavior.
The expected behavior matches what happens when the user presses the
physical Delete key on the same selection: content cleared, all borders
(inner + outer) preserved.
Programmatic clearing should produce identical results to physical
keyboard Delete. The current divergence makes Calc unsuitable for any
macro-based workflow that touches xlsx files with multi-cell borders.
Reproducible: Always
User Profile Reset: No
Additional Info:
ENVIRONMENT:
- LibreOffice 26.2.3.2 (Windows 11 Pro x64)
- File format: .xlsx (originally imported from external source / Excel)
- Conditional formatting: none
- Merged cells: none
- Cell style: Default
- Reproducible on multiple xlsx files imported externally; NOT reproducible
in fresh blank Calc spreadsheets (the bug is xlsx-import-specific).
WORKAROUND:
Physical keyboard Delete (selecting range and pressing Del manually)
preserves all borders correctly. The bug occurs only with programmatic
content clearing.
LIKELY ROOT CAUSE (HYPOTHESIS):
The xlsx import filter creates a "shared XF border pool" where adjacent
cells share border ownership. When the cell topology is modified via
any API/dispatch/synthetic input path, an "XF pool optimization" is
triggered that collapses inner shared borders into a single outer frame.
Physical hardware keyboard events appear to bypass this optimization
(likely going through ScTabView::CmdDelete or similar direct path
without invoking the format re-evaluation engine).
==============================================================
RELATED ENHANCEMENT REQUEST: Programmatic dismissal of "marching ants"
==============================================================
After .uno:Copy or .uno:Cut, Calc displays an animated dashed border
("marching ants") around the source range. There is no documented
programmatic way to dismiss this indicator from Basic while preserving
the Calc internal clipboard (ScTransferObj) state.
Excel's equivalent: Application.CutCopyMode = False (dismisses indicator,
preserves clipboard). LibreOffice Calc has no equivalent.
Tested (all fail to dismiss while preserving rich clipboard):
- .uno:Escape, .uno:Cancel, .uno:Deselect dispatch
- Programmatic cell selection change via controller.select(...)
- .uno:SetInputMode (F2) immediately followed by .uno:Cancel
Only the physical Escape key dismisses marching ants — but it also wipes
the rich Calc clipboard.
Request: Add a UNO command (e.g., .uno:ClearClipboardIndicator) that
dismisses only the visual indicator while preserving ScTransferObj for
rich paste.
==============================================================
ABOUT LIBREOFFICE (Help > About LibreOffice — Copy Version Info):
Version: 26.2.3.2 (X86_64)
Build ID: 70e089b17412e4cb7773e41413306b17a2328c34
CPU threads: 12; OS: Windows 11 X86_64 (build 26200); UI render: Skia/Raster;
VCL: win
Locale: tr-TR (tr_TR); UI: tr-TR
Calc: CL threaded
--
You are receiving this mail because:
You are the assignee for the bug.
