I am generating a number of LibreOffice Calc spreadsheets automatically (programmatically) with the --headless option. The spreadsheets are filled with information from a database. I want the AutoFilter header in them, so this is the code I came up with: Sub SetAutoFilter ( doc As Object ) Dim frame As Object frame = doc.CurrentController.Frame Dim dispatcher As Object dispatcher = createUnoService( "com.sun.star.frame.DispatchHelper" ) Dim args(0) As New com.sun.star.beans.PropertyValue args(0).Name = "ToPoint" args(0).Value = "$A$1" dispatcher.executeDispatch( frame, ".uno:GoToCell", "", 0, args() ) dispatcher.executeDispatch( frame, ".uno:DataFilterAutoFilter", "", 0, Array() ) End Sub That worked fine for a while, but started failing for some spreadsheets. After some investigation, it turns out the reason is that LibreOffice Calc sometimes prompts the following: "The range does not contain column headers. Do you want the first line to be used as column header?" That prompt breaks automation in headless mode. Some people say that this happens when the first row has empty cells, but that wasn't the case. After some more research, I found out that the prompt only comes up if there are less than 4 column headers. This seems like a bug to me. As a work-around, I just added extra column headers with the title "<workaround>", and then the routine above worked fine again. The data underneath can still have less than 4 columns. By the way, where can I find documentation about the parameters for .uno:DataFilterAutoFilter ? I couldn't find any documentation at the usual locations, and I would like to know what arguments [ "", 0, Array() ] mean.
Please ask your question in https://ask.libreoffice.org/c/english/5/l/latest, there are valuable people to help.
No response after the last advice, so probably reporter is happy with it and we can close.
> [...] > so probably reporter is happy For the record, I am not actually happy about this. 8-) In my opinion this bug is not "invalid".
(In reply to R. Diez from comment #3) > > [...] > > so probably reporter is happy > > For the record, I am not actually happy about this. 8-) > > In my opinion this bug is not "invalid". Well, it was a good way to get you to talk after 6 months of silence :) Now you just have to tell us, did you post about your question on Ask LibreOffice forum? The interesting header code is apparently here: https://git.libreoffice.org/core/+/90bf630fa2c612000cdc7128612c13088a22ce34/sc/source/ui/view/dbfunc.cxx#343 I couldn't find information about the arguments given to the dispatch command. It would indeed be good to have it documented in Help.
Thanks for locating the relevant code. The first thing I noticed is the call to CreateMessageDialog() without checking whether we are in --headless mode. I do not know anything about LibreOffice's internals. Is there not some global policy about this? Like I mentioned in this bug report, such UI calls are bound to break in headless mode. The code is complicated, and I could not find the reason why Calc considers there is no header when the number of columns is <= 3. If there is a good reason, it probably should be documented as well. Or perhaps you cannot reproduce this behaviour, and then it is something that only happens on my computer? You have already confirmed the shortcoming that the parameters for .uno:DataFilterAutoFilter are not documented. I wouldn't close this bug report until the documentation is in place. Alternatively, you could create a bug just for the missing documentation and reference it from this one. I am hoping that passing the right arguments would prevent the prompting. Do you know whether there is a way to unconditionally tell Calc that there is a header?
In the Ask forums we find other ways to define the filter: https://ask.libreoffice.org/t/macro-to-manipulate-an-autofilter-without-a-database-range/57846/8 Sub main() sel = ThisComponent.CurrentController.Selection range = get_range(sel) fd = range.createFilterDescriptor(True) range.filter(fd) End Sub Function get_range(cell) cursor = cell.SpreadSheet.createCursorByRange(cell) cursor.collapseToCurrentRegion() get_range = cursor End Function https://ask.libreoffice.org/t/macro-automatic-filter-or-standard-filter-in-calc/57808/13 "The only way I know to create an AutoFilter via the API is to create a DatabaseRange and to set its .Autofilter property to TRUE."
Thanks for the pointers. I do not know enough yet, but it seems that there is a way to create an AutoFilter via the API with a DatabaseRange, and another way with the given script, which does not seem to use a DatabaseRange at all. I'll investigate those alternatives the next time I have to modify my code. At the moment, the workaround I implemented (adding extra dummy column headers) does the trick for me. I created this bug report because I think users shouldn't fight with Calc and search in the forums and puzzle all the information together in order to do this task. I would clearly document whether the ".uno:DataFilterAutoFilter" dispatch should be avoided when programmatically creating filters (especially with --headless), because it may break due to the prompting, and which alternative is the "right" one. And why the number of columns matter. Or maybe it shouldn't really matter, and the LibreOffice code needs to be modified. Note that some of the code in the forums would break (or unnecessarily prompt) with less than 3 columns. Or perhaps you can pass some arguments to ".uno:DataFilterAutoFilter" to always prevent the prompting, and then the number of columns and --headless are then irrelevant. The aim is to prevent other users from having this kind of trouble in the future. If I had the necessary time and skills I would do it myself. In the meantime, I hope that this bug report helps other people save some time if they encounter the same unexpected problem. If you mark it as "invalid" and/or close it, chances are that this kind of trouble remains unfixed forever, and that other users do not find this information easily.
I agree that improvements could be made. Let's set this to NEW, so it doesn't fly under the radar.