[libreoffice-users] making self-defined functions available for all spreadsheets
Hi, how can I make a self-written function (i. e. a macro) available for all spreadsheets so that I can use this function for any spreadsheet I create or load and modify? Ideally, this should work with multiple users in that I'd like to deploy the macro(s) somewhere central for others to use. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: making self-defined functions available for all spreadsheets
Am 21.02.2015 um 21:02 schrieb Andreas Säger:
Am 21.02.2015 um 19:17 schrieb hw:
Hi,
how can I make a self-written function (i. e. a macro) available for all
spreadsheets so that I can use this function for any spreadsheet I
create or load and modify?
Ideally, this should work with multiple users in that I'd like to deploy
the macro(s) somewhere central for others to use.
Hi,
You have two choices:
1) Write an add-in package which is installable via the extension
manager. These functoons can have their own localized help pages and
function names. They appear in the function wizard just like a built-in
function. You can not write add-ins Basic code.
2) Quick and drity Basic functions without any integration can be stored
in the "Standard" library of the global macro name space ("My Macros").
This library is loaded automatically so the functions can be found at
any time.
2a) Since it is possible to call macro code from different languages,
you can write or re-use functions written in Java, Python or JavaScript
and write a Basic wrapper. The spreadsheet calls the Basic function
which itself calls the other script.
Thank you very much! For now, I'll just put the macro into the
"Standard" library --- I tried it and it works.
Would it be possible to have one file this "Standard" library is saved
in for multiple users, or would that give problems with several users
accessing the same file? Perhaps I could put it on a network share and
allow only read-only access to it.
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: making self-defined functions available for all spreadsheets
Am 23.02.2015 um 13:20 schrieb Andreas Säger: Am 23.02.2015 um 13:02 schrieb hw: Would it be possible to have one file this "Standard" library is saved in for multiple users, or would that give problems with several users accessing the same file? Perhaps I could put it on a network share and allow only read-only access to it. Yes. Build your own LO installation package with your macros in the user profile. Just in case your macros refer to a specific class of user defined document templates: In this case you store all macros in the template they refer to. And yes, distributeion of such templates requires an install package with modified profile or a user who installs the template into the profile via File>Templates>Save... I can't find the LO administration guide on libreoffice.org. May be someone else can point us to it. How do you build an installation package? LO is installed on a Windows terminal server for all users, yet each user has their own settings. To get macro distributed, I have to save it to a text file which then can be copied and pasted from for each user. Or is there a better way? BTW, is there a way to use an external editor to edit these macros? I'd rather use a decent editor like emacs than the poor thing built into LO. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] making self-defined functions available for all spreadsheets
Am 25.02.2015 um 17:14 schrieb Regina Henschel: Hi, hw schrieb: Hi, how can I make a self-written function (i. e. a macro) available for all spreadsheets so that I can use this function for any spreadsheet I create or load and modify? Ideally, this should work with multiple users in that I'd like to deploy the macro(s) somewhere central for others to use. In addition to the suggestions by Andreas, in the Basic Organizer, you can export a macro library to an extension (file name .oxt). When you then install this extension "for all users" the library goes to /share/uno_packages/... and with the next start of LibreOffice another user will find it in its UI in "LibreOffice Macros&Dialogs". But although this works in general, it will not work for Basic functions, which are written to be used in Calc formulas, see https://bz.apache.org/ooo/show_bug.cgi?id=53615 and likely the same root cause in https://bugs.documentfoundation.org/show_bug.cgi?id=79588. Thank you very much! This is good to know, though currently, it is actually a macro written in BASIC. It provides some functions to be used with spreadsheets. BTW, is there a macro readily available somewhere to genearate EAN-13 codes for spreadsheets? I got something for perl which I could translate, and it might save time if there's already a function for spreadsheets. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] database2spreadsheet
Hi, how would I create a spreadsheet from a table in a database? I know I can copy and paste, and I want to avoid that. Basically, users should be able to click an icon in LO (or on their desktop) and be presented with a spreadsheet which displays an export from a table I have in a mysql database. Fields in the spreadsheet must be in a different order than in the database and be formatted to a pre-defined width. In a future step, I would like to integrate graphics into the spreadsheet which could be created by a perl script with gnuplot from data in the database; the images could reside in a directory and rows in the database would have a field referring to the file name of the image. Is there any functionality in LO to achieve something like this, or is the database part as incompatible to other parts of LO, like spreadsheets, as it seems to be? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 06.03.2015 um 09:08 schrieb Alex Thurgood: Le 04/03/2015 10:59, hw a écrit : Hi, In a future step, I would like to integrate graphics into the spreadsheet which could be created by a perl script with gnuplot from data in the database; the images could reside in a directory and rows in the database would have a field referring to the file name of the image. If you are going to use Perl to manipulate graphic objects to insert into a Calc document, why not use Perl for everything, including querying your db, outputting your data in the desired format, and inserting that array of data into a Perl-created/manipulated Calc file ? Look at the following Perl modules : ODF::lpod OpenOffice::UNO OpenOffice::OODoc Creating LO documents directly would be nice and might have the advantage to be able to specify some formatting --- which I might need to do sooner or later. Unfortunately, it seems to be rather tedious, and it's only one way just like CSV. Perhaps I could use some sort of overlay showing data from a table as a spreadsheet which lets the user edit only some of the fields. Some of the content would need to be filled in "on the fly" from somewhere else because the perl script writes data and forumals into the CSV that aren't in the database. --- Or I'd have to create yet another table instead of the CSV. Some experimentation is required at this point ... Thank you for all the replies so far! -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 06.03.2015 um 22:31 schrieb Andreas Säger: Am 04.03.2015 um 10:59 schrieb hw: In a future step, I would like to integrate graphics into the spreadsheet which could be created by a perl script with gnuplot from data in the database; the images could reside in a directory and rows in the database would have a field referring to the file name of the image. Without any coding it is be possible to create a field of hyperlinks. Just add a HYPERLINK formula next to the import range. The adjacent field expands to the size of the import range. You mean to the size of the image? It doesn't scale the images to achieve a reasonable overall layout? In this case, I could make the images all the same size; in other cases, I might have images in all different sizes ... -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 06.03.2015 um 15:44 schrieb Alexander Thurgood: Le 06/03/2015 10:18, Andreas Säger a écrit : Hi Andreas, In a future step, I would like to integrate graphics into the spreadsheet which could be created by a perl script with gnuplot from data in the database; the images could reside in a directory and rows in the database would have a field referring to the file name of the image. Because all this has been implemented already to be used by anyone without coding. Hit F4 and drag your query into your favourite spreadsheet template. Add spreadsheet formulas, (conditional) formatting, charts and stuff. Finally, you may remove the "store data" flag from the import range so the user is prompted to update the import range when opening this document. All this works reliably out of the box. Including the image link from the Gnuplot trace that the original poster requested ? I think not, at least not without some other form of programming magic to convert the filename to a URL and then insert it in the sheet. The GUI only solution you propose only works within the metes and bounds of what the GUI can offer without programming. My understanding of the original poster's question was that he was already considering a programmable solution, at least for the images. If you need programming within LO to get where you want, and you're already using Perl for some of what you need, you might as well go to whole hog, and do it all in Perl (or Python, or some other language that lets you manipulate the document format). Exactly: What the perl script does is way beyond the capabilities of a query and of formulas in a spread sheet. Even letting aside that using the BASIC dialect of LO is very awkward (just think of the inability to return from a function ...) and that it doesn't even have a decent editor, LO is far too unreliable for anything like this. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 16.03.2015 um 18:04 schrieb Andreas Säger: Am 16.03.2015 um 16:18 schrieb hw: Exactly: What the perl script does is way beyond the capabilities of a query and of formulas in a spread sheet. In which way? You are rather unspecific about your requirements. That's partly because the requirements are still evolving and not fixed yet. The perl script performs some calculations and updates data in various tables based on data from a number of other tables, sometimes intertwining multiple queries to achieve the desired results. It's non-trivial. Even letting aside that using the BASIC dialect of LO is very awkward (just think of the inability to return from a function ...) and that it doesn't even have a decent editor, LO is far too unreliable for anything like this. Basic can not return from a function? I'm no fan of that awkward and almost extinct language of the 90ies but sometimes I use quick&dirty Basic functions in spreadsheets or even Basic functions calling Python functions. It's what you get when you write a macro to provide you with a function you can use in your spread sheets. I don't like it, either, and I'd gladly do it in perl instead. Can I just point LO to the perl script that provides the required functions? I can't even do that with the built-in BASIC. Will it interpret the perl script? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 16.03.2015 um 18:00 schrieb Andreas Säger: Am 16.03.2015 um 16:21 schrieb hw: Without any coding it is be possible to create a field of hyperlinks. Just add a HYPERLINK formula next to the import range. The adjacent field expands to the size of the import range. You mean to the size of the image? It doesn't scale the images to achieve a reasonable overall layout? In this case, I could make the images all the same size; in other cases, I might have images in all different sizes ... Nope. I mean you can add a hyperlink to the spreadsheet which opens the picture in your pic viewing program. Ok, so I want to import data from a CSV file and would like to have some formatting applied automatically, like specifying a particular width and conditional formatting for some of the columns when I open the file which is sent to me by email. It would be possible to write some extra data into the CSV to achieve this. How could I do this? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 17.03.2015 um 13:22 schrieb hw: Am 16.03.2015 um 18:00 schrieb Andreas Säger: Am 16.03.2015 um 16:21 schrieb hw: Without any coding it is be possible to create a field of hyperlinks. Just add a HYPERLINK formula next to the import range. The adjacent field expands to the size of the import range. You mean to the size of the image? It doesn't scale the images to achieve a reasonable overall layout? In this case, I could make the images all the same size; in other cases, I might have images in all different sizes ... Nope. I mean you can add a hyperlink to the spreadsheet which opens the picture in your pic viewing program. Ok, so I want to import data from a CSV file and would like to have some formatting applied automatically, like specifying a particular width and conditional formatting for some of the columns when I open the file which is sent to me by email. It would be possible to write some extra data into the CSV to achieve this. How could I do this? Sorry, this is the wrong answer to the right post --- still a question, though. The answer to this post should have been that I would need the images to appear in the spread sheet. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 16.03.2015 um 15:57 schrieb hw: Some experimentation is required at this point ... Thank you for all the replies so far! Ok so I made a new spreadsheet and dragged a query from the datasources view into it. Apparently that fills rows in the spreadsheet with the data obtained from the query. This can be useful and doesn't exactly seem to be what I would want in each case: What if the data in the underlying tables changes? Will the spreadsheet be updated automatically? What if I edit the data in the spreadsheet? I suppose I cannot update the underlying tables through the query. So I pulled a table into the spreadsheet and after an hour or so, I had to kill LO because it didn't seem to do anything anymore. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 17.03.2015 um 21:41 schrieb Andreas Säger: Am 17.03.2015 um 13:36 schrieb hw: Sorry, this is the wrong answer to the right post --- still a question, though. The answer to this post should have been that I would need the images to appear in the spread sheet. Pictures in Calc are no spreadsheet data. Pictures are decorative, illustrating elements pinned to the cells or to the top-left corner of a sheet. Writing a program to manipulate many calculator cells so they wrap around picture objects is very difficult. Absolutely non-trivial. Hence my idea to resize the pictures outside of LO to suitable dimensions and put links to the resized ones into the CSV (or a databse table) to have them displayed reasonably in the spreadsheet. For a LibreOffice user there is only one built-in tool to do reporting with pictures. It is the report tool. Hm, I'm not too happy with that because the defaults aren't too useful, and when I wanted to make a report to display data from a table, the report didn't display any of the data but the default placeholders instead. You are a Perl programmer. This kind of stuff has always be done with html reports. Then the data cannot be edited as easily as with a spreadsheet ... However, a web page just like a spreadsheet would be very useful. I will have to look for some tool that works for this; there's probably something I could use. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 17.03.2015 um 21:34 schrieb Andreas Säger: Am 17.03.2015 um 13:22 schrieb hw: Ok, so I want to import data from a CSV file and would like to have some formatting applied automatically, like specifying a particular width and conditional formatting for some of the columns when I open the file which is sent to me by email. It would be possible to write some extra data into the CSV to achieve this. How could I do this? Simply use the database engine of your choice. The one you are already connected with. It should provide all the capabilities to export, import and link csv table. In this case you would not need any Office suite at all. Office users can import csv files in the office GUI. Copying table icons from a text connected database into a regular database or copying spreadsheet cells onto the icon of a database table. Copying icons? For the built-in HSQLDB this is described here: [Tutorial] Using csv/text files as editable data source. https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=23260 And it works in the same way with later versions of HSQL. Since you do not mention your database engine, Google is your friend. mysql There are thousands of hits about csv and MySQL. All this works without any office suite and your Perl script can simply issue some SQL command via the dbi module (I think). Yes, it uses DBI to do its work. I'm 100& sure that an office suite can not add anything to your project. It makes life a hell of a lot easier because it provides a very useful frontend. Operate your database any way you want. Finally you may or may not load the results into document templates. [Example] Loading CSV into preformatted spreadsheets https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=23727 how to maintain prepared Calc templates to be filled with raw data from csv (or any other kind of data source provided by the Base component). Awesome, thank you! That might be exactly what I'm looking for, I'll check it out. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 19.03.2015 um 14:07 schrieb Andreas Säger: Am 19.03.2015 um 11:49 schrieb hw: Copying icons? Indeed. In order to copy table or view contents from one DB to another DB you copy the table _icon_ from one database window, select the table icon in the other database window (the DB where you have write access) and paste. A dialog pops up which lets you map the matching fields of both tables. How weird, I'd never think of doing anything like that. And if I would, I wouldn't do it because LO is too likely to crash, leaving me with a mess. BTW, as to reports, no, I don't want to alter data in reports, only in spreadsheets. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 16.03.2015 um 18:07 schrieb Andreas Säger: Am 16.03.2015 um 15:57 schrieb hw: Creating LO documents directly would be nice and might have the advantage to be able to specify some formatting --- which I might need to do sooner or later. Formatting is a matter of style, cell styles in this case. Linked import ranges filled with database data can be prepared with cell styles (I use document templates for this type of database reports). The formatting expands/shrinks with the imported data range. How would I do something like this with CSVs? I guess I'd need some sort of "overlay spreadsheet" which defines the formatting and is then being filled with the data from a CSV file. The fields in the CSV remain the same while the number of rows will vary. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] how to put images into a report
Hi, how can I put images into a report? I have a query on a database that, amongst others, has a field with the file name of an image. All images are stored in a particular directory, so the relevant table in the database holds the file name of the image, for example "abc.jpg". I need the image that has the particular file name displayed in the report. The images are of different sizes, and in the first step, I'd like to have them scaled to fit into the report, and their aspect ratio must not be changed. In a second step, I might use some external tool to make all images the same size eventually. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: database2spreadsheet
Am 23.03.2015 um 18:28 schrieb Brian Barker: At 17:01 23/03/2015 +0100, Honly Wonly wrote: Am 16.03.2015 um 18:07 schrieb Andreas Säger: Am 16.03.2015 um 15:57 schrieb Honly Wonly: Creating LO documents directly would be nice and might have the advantage to be able to specify some formatting --- which I might need to do sooner or later. Formatting is a matter of style, cell styles in this case. Linked import ranges filled with database data can be prepared with cell styles (I use document templates for this type of database reports). The formatting expands/shrinks with the imported data range. How would I do something like this with CSVs? I guess I'd need some sort of "overlay spreadsheet" which defines the formatting and is then being filled with the data from a CSV file. The fields in the CSV remain the same while the number of rows will vary As suggested above, if you already had suitable styling configured as cell styles, it would be very simple to apply these styles to the data after it was positioned. Alternatively, if you have a document with space for the data already formatted - which would indeed sensibly be created from a template - you could add the data without upsetting the formatting. The trick here is to use Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. If you paste from elsewhere in the same or another spreadsheet document, ensure that "Paste all" and Formats are both *not* ticked in the Paste Special dialogue; if you paste from another source, select "Unformatted text" in the Paste Special dialogue. The data is in a CSV file. Opening the CSV creates a new spreadsheet. I don't want to apply formatting or copy and paste anything manually. The formatting should be applied automatically, for example based on the name of the CSV file, using a regexp, when the CSV file is opened. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] include images into spreadsheets, texts or reports
Hi, so far, I have had no success whatsoever with including images into spreadsheets, texts or reports. Such images either reside in a directory on a server and are accessible via a samba share and http, or they are generated on the fly and accessible via http. Putting a hyperlink into a spreadsheet displays the hyperlink rather than the image. Inserting a frame into a text document and assigning a hyperlink to the frame in order to have the image displayed within the frame displays nothing but the empty frame. There doesn't seem to be any way at all to display images in a report. Is it entirely impossible to integrate images? Currently, I need to display EAN-13 barcodes in a document created via mail merge from data in a spreadsheet. Since the barcode extension for LO does not work, I resorted to creating the barcodes on the web server. But there is no way to have them displayed with LO. If it's possible to include images, I need LO to query the web server with an EAN number so that the web server can create the corresponding barcode and insert it into the document created via mail merge. The correct URL to retreive the barcode could be created within the spreadsheet. How would I do this? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] include images into spreadsheets, texts or reports
Am 07.04.2015 um 16:43 schrieb Brad Rogers: On Tue, 07 Apr 2015 16:20:57 +0200 hw wrote: Hello hw, Is it entirely impossible to integrate images? No. Insert menu -> Image... -> From File... Does it for me. The image is created on demand with a cgi-script and is different for each row of the spreadsheet, depending on the EAN number specified in the row. The image is accessible via http, with an URL like http://example.com/cgi-bin/barcode.pl?code=1234567890123 I can use a formula or write a function for the spreadsheet to create the URL from the spreadsheet, and I need the image to appear in the text document created from the spreadsheet via mail merge. The spreadsheet is the data source for the text document. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] zooming to a larger version of an image on mouse-over
Hi, how can I add an image to a spreadsheet cell in such a way that when the mouse pointer is over the image, a larger version of the image is displayed? The larger, original version of the image resides on a web server. The scaled down image is inserted into the spreadsheet with the 'insert_image()' method provided by 'Excel::Writer::XLSX'[1] because it's impossible to use an URL for this. Every image is scaled down before inserting it into the spreadsheet because using the original image and setting a scale of the image in the spreadsheet makes things unusably slow. The scaled down version is too small to be useful when zoomed, hence the original image needs to be used for the large display. When the image is zoomed, it may cover neighbouring rows and cells, i. e. the size of the cell the image is in doesn't need to change. Ideas for both LO and M$ Excel are welcome, especially since LO is rather buggy and slow with displaying the images anyway, whereas Excel performs nicely. These spreadsheets have a few thousand images. [1]: see http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX-0.84/lib/Excel/Writer/XLSX.pm -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] how to make formulas permanently case sensitive?
Hi, I need, of course, formulars to be case sensitive. This can be set in the options for calc. The option is not saved permanently and every time I open a spreadsheet, I need to set the option again. How can I save this option permanently so I don't need to set it over and over again? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] how to make formulas permanently case sensitive?
Am 12.05.2015 um 12:45 schrieb hw: Hi, I need, of course, formulars to be case sensitive. This can be set in the options for calc. The option is not saved permanently and every time I open a spreadsheet, I need to set the option again. How can I save this option permanently so I don't need to set it over and over again? To clarify: I need the string comparisons of IF() to be case sensitive: 'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'. Why would anyone make them not case sensitive unless explicitly specified, and how do I specify case sensitiveness per formula? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] how to make formulas permanently case sensitive?
Am 12.05.2015 um 19:53 schrieb Brian Barker: At 12:45 12/05/2015 +0200, Honly Wonly wrote: I need, of course, formulas to be case sensitive. This can be set in the options for calc. The option is not saved permanently and every time I open a spreadsheet, I need to set the option again. I'm surprised by that. If you mean the options at Tools | Options... | LibreOffice Calc | Calculate | Case sensitive, doesn't this apply per user and thus stick not only between documents but between LibreOffice sessions? No, this option is not remembered. How can I save this option permanently so I don't need to set it over and over again? If it doesn't stick - and if this option is, as I imagine, saved in the user profile - you might want to wonder if your user profile has become corrupt. If you close LibreOffice and delete or rename the profile, LibreOffice will create a new, clean one next time you start it. If you then set the option again, does it now stick? I have seen this on different computers, with both the Linux and the Windoze version of LO. At 12:55 12/05/2015 +0200, Honly Wonly wrote: To clarify: I need the string comparisons of IF() to be case sensitive: 'if(a1 = "x"; ...)' is, of course, different from 'if(a1 = "X"; ...)'. Why would anyone make them not case sensitive unless explicitly specified, ... I suspect those who prefer the other option would ask the opposite question! Things are usually case sensitive, unless you specify otherwise. It is what I expect. If you are looking for references to dogs, you might want to find "Dogs are friendly pets" as well as "My dog's got no nose". I don't like dogs ... ... and how do I specify case sensitiveness per formula? If the option is, as I suspect, per user, your spreadsheet will go wrong if you need to change the option for another document, if a different user handles it on the same computer, or if you exchange it with someone using a different computer. I haven't tried with different users. So you are quite right to identify that building the requirement into your formulae and not relying on options is the reliable way to go. And it's quite easy: the EXACT() function comes to your aid. Instead of using =IF(A1="X";...) use =IF(EXACT(A1;"X");...) This will match "X" but not "x" irrespective of the option setting. Thank you, maybe that works; I'll try it tomorrow. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] how to make formulas permanently case sensitive?
Am 12.05.2015 um 13:16 schrieb Gary Collins: To make a formula that is case sensitive, you should be able to use the string functions (I think) to convert to a known case before performing the comparison, e.g. if(UPPER(A1) = "X";...) That would make the formula case insensitive. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Fwd: Re: [libreoffice-users] how to make formulas permanently case sensitive?
Am 14.05.2015 um 11:55 schrieb Brian Barker: At 20:32 12/05/2015 +0200, Honly Wonly wrote: Am 12.05.2015 um 19:53 schrieb Brian Barker: At 12:45 12/05/2015 +0200, Honly Wonly wrote: I need, of course, formulas to be case sensitive. This can be set in the options for calc. The option is not saved permanently and every time I open a spreadsheet, I need to set the option again. I'm surprised by that. If you mean the options at Tools | Options... | LibreOffice Calc | Calculate | Case sensitive, doesn't this apply per user and thus stick not only between documents but between LibreOffice sessions? No, this option is not remembered. I've done some research, and the setting is stored in registrymodifications.xcu in the profile. So yes: it *will* be saved per user, not per document or per system, and will stick across LibreOffice sessions. (But perhaps not, as I suggested, if you have a corrupt profile.) Ok so it's supposed to be remembered and it isn't for some reason. How can I save this option permanently so I don't need to set it over and over again? If it doesn't stick - and if this option is, as I imagine, saved in the user profile - you might want to wonder if your user profile has become corrupt. If you close LibreOffice and delete or rename the profile, LibreOffice will create a new, clean one next time you start it. If you then set the option again, does it now stick? I have seen this on different computers, with both the Linux and the [Windows] version of LO. If you use different systems, the setting would need to be made in your profile separately on each, of course. Yes, and it should be remembered on both. Things are usually case sensitive, unless you specify otherwise. It is what I expect. I'm sorry you missed my point. I understood what you wanted. Anyone reading your enquiry will have understood what you wanted. My point was that others might prefer the existing arrangement. I think others should be given consideration. If the setting to make formulas case sensitive would be remembered as expected, there would be no problem. If you are looking for references to dogs, you might want to find "Dogs are friendly pets" as well as "My dog's got no nose". I don't like dogs ... Did you imagine I was writing for your benefit alone? There are plenty of dog-lovers on the list. In any case, this is not a list about dogs or for dog lovers. ... and how do I specify case sensitiveness per formula? If the option is, as I suspect, per user, your spreadsheet will go wrong if you need to change the option for another document, if a different user handles it on the same computer, or if you exchange it with someone using a different computer. I haven't tried with different users. Actually, it appears you have. You say you have experience of this matter on different operating systems. Even though you are the same person on each, you will be separate users and have separate profiles on the different systems. True --- I was referring to an LO installation on a terminal server and wasn't clear about that, sorry. So you are quite right to identify that building the requirement into your formulae and not relying on options is the reliable way to go. And it's quite easy: the EXACT() function comes to your aid. Instead of using =IF(A1="X";...) use =IF(EXACT(A1;"X");...) This will match "X" but not "x" irrespective of the option setting. Thank you, maybe that works; I'll try it tomorrow. Oh, it does work for others. I'm not sure how your use if it downgrades this to "maybe". That something works "for others" --- i. e. in some cases --- doesn't mean that it works in all cases or for what I'm doing. It only means that it may work, and I won't know whether it does or not before I tried it. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] how to make formulas permanently case sensitive?
Am 15.05.2015 um 08:58 schrieb Mike Scott: > Now, if I reload doc1 and doc2 simultaneously, showing in 2 windows, both spreadsheets show the same results as before. Furthermore, checking the (expected-to-be-global) Tools option for case sensitivity shows different values when the dialogue is opened from each window. Again, checking the contents.xml for each file shows an extra entry for the 'case insensitive' file. (Presumably the default is 'true'.) So as one would hope, the setting is saved per-file. Thank you for testing this! I wouldn't hope that because this setting appears among others which are (or seem to be) global (or defaults or the desired settings, however you might call it), with no indication that this particular setting is per spreadsheet. My suspicion is that any value in registrymodifications.xcu is simply a default for new documents. How are we supposed to know which settings made at Tools->Options are global and which aren't? I would expect all of them to be global and to be remembered between sessions like every other software does in such context. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] how to make formulas permanently case sensitive?
Am 15.05.2015 um 10:03 schrieb Brian Barker: Another contributor suggested that the original enquirer's problem may have been caused by (unwisely) saving spreadsheets in Microsoft Excel format, and it appears that this may well have been so. My experiments suggest that spreadsheets saved with the option set differently are displayed correctly in the Excel Viewer (and therefore presumably in Excel itself) but do not render correctly when reopened - suggesting that it is LibreOffice's input filter that is at fault. Solution: save in .ods format. The spreadsheets are created with a perl module (Excel::Writer::XLSX) because there doesn't seem to be an equivalent module to create ODS files. Saving them as ODS is not an option because it's annoying having to switch between different formats and to create various copies of the files all the time. Even saving them as CSV to import them is a step that needs to be removed from the workflow. But the bottom line is still the same: because the situation is so complicated and fluid, the reliable way to deal with the problem is to build case sensitivity into formulae, testing similarity using the EXACT() function instead of the "=" operator. I don't understand why or how this is complicated. When I make a general/global setting amongst others, I simply expect this setting to be in effect regardless which document I open. If this setting cannot be made general/global, then it needs to be clear that it is per document --- not considering that some ppl might nonetheless wish that a global/general setting for this exists. And if a setting which is per document overrides a general/global setting, I should optionally receive a warning. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] optimal width always
Hi, how can make it so that when opening files, especially CSVs, with calc, the whole sheet is automatically being set to optimal column width? I have to do that manually every time and that's really annoying, especially since there's no easy way to do that, like right-clicking on the top left empty square every sheet has. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] optimal width always
Nobody has an idea? It should be possible using a macro, shouldn't it? On Sun, 2023-07-09 at 11:50 +0200, hw wrote: > Hi, > > how can make it so that when opening files, especially CSVs, with calc, > the whole sheet is automatically being set to optimal column width? > > I have to do that manually every time and that's really annoying, > especially since there's no easy way to do that, like right-clicking on > the top left empty square every sheet has. > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] optimal width always
On Tue, 2023-07-11 at 15:07 -0400, James wrote: > It's a great idea for a feature. > > I don't know how hard it would be to add a checkbox beside column type > to make the column the automatic width. No checkbox needed, just make it so that a right click on the empty field at the top left sets all columns to optimal width. That field currently has no function at all. > You can record a macro and see if that works but it is probably easier > to manually do it each time. How do I record a macro? There doesn't seem to be an option for that? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] optimal width always
On Wed, 2023-07-12 at 10:18 +0100, Brad Rogers wrote: > On Wed, 12 Jul 2023 11:13:41 +0200 > hw wrote: > > Hello hw, > > > field at the top left sets all columns to optimal width. That field > > currently has no function at all. > > It selects the entire sheet. Of little use, perhaps, but that certainly > isn't 'no function at all.' Ah yes, right --- still that leaves two mouse buttons that don't have any function at all on this field. That's the majority of buttons ;) Why not use them. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] optimal width always
On Wed, 2023-07-12 at 10:35 +0100, Brad Rogers wrote: > On Wed, 12 Jul 2023 11:13:41 +0200 > hw wrote: > > Hello hw, > > And adding to my previous; > > If you select the entire sheet, then right click on a column and select > "Optimal Width...", guess what happens (rhetorical question). Why can't that happen automatically when loading a file, or at least with a single right click on the empty field. Having to click and to find and select the desired function from a more or less long list after making another click is the opposite of user friendly. Not only every time I open a spreadsheet I have to do that --- except for the rare cases where I previously opened one and adjusted the column width and saved it in a format that remembers it --- I also have to do it after altering some of the columns and changing their contents so they require a different width. It is actually the most-used functionality when working with spreadsheets, followed by copying and pasting. Why is there no key binding for it? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] optimal width always
On Wed, 2023-07-12 at 07:51 -0500, Michael H wrote: > "function" here would mean an action somewhat related to the > advertisement > in the menu name. The empty field does not have a menu, or does it? Function would mean that something happens when you right or middle click the empty field. Currently nothing happens. But I wouldn't mind if it had a menu with items like 'Clear Direct Formatting', 'Set Optimal Width', 'Auto Filter', 'Sort ...' and whatever else would be appropriate which all operate on the whole sheet without having to select it first. > What exactly does "select the entire sheet" have to do > with "optimal width"? When setting the optimal column width, it's almost always much easier to select the whole sheet before doing so, instead of painstakingly selecting each of the columns the width of which is not optimal (by going through the whole sheet to find them). If right or middle clicking the empty field would simply set all columns to their optimal widths, it won't even be necessary to select the whole sheet before setting the optimal width. Of course, in case you want to set the width of a single column, it's easier to make the particular column wider and it doesn't always need to be optimal. And you can still do that. > From outside the discussion, it seems like you're confusing > "function" with > "bug". Seems normal for LibreOffice these days. Dunno, it's probably not a bug that the functionality isn't there; only it hasn't been implemented yet. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] optimal width always
On Wed, 2023-07-12 at 11:41 +0100, Brad Rogers wrote: > On Wed, 12 Jul 2023 12:09:29 +0200 > hw wrote: > > Hello hw, > > > Why can't that happen automatically when loading a file, or at > > least > > IME 'optimal width' results in a cell that is too wide. Then don't use this function. > Consequently, > for my use case, a sheet that could otherwise be printed on one piece > of > paper ends up requiring two, or more. Who still prints stuff? :) > Point is; Uses for all of us differ. Your use case differs from > mine. right > Satisfying your itch may well upset other users' work methodology. How would it break someones methodology to right or middle click on the empty field which currently does nothing? If someone feels the need to do that without something happening I can as well argue that nothing must happen no matter where they click because something happening could interrupt them when they click. As to setting optimal width when opening a file, it would of course have to be an option that needs to be enabled first (for different types of files). Nothing of I suggest would upset or break or interrupt anything. > In any case, the 'box' at top left corner of each sheet isn't, in > fact, > a box at all - it's an artifact of the column and row markers. > Nothing > more. That's not true, it selects the whole sheet when you left click it. If were merely an artifact, it would do nothing. > > It is actually the most-used functionality when working with > > By you. I never use it. It's something that's usually done once per > sheet, IME, if at all. "Fit and forget", if you will. Yes, by me and probably by a lot of other users. You would never need to use it. However, my original question is how I could accomplish such functionality, like writing a macro to do it. Are there commands or functions or something in libreoffice BASIC (or python) that select the whole sheet and set the optimal column width? I haven't found anything like that yet. Yet it's basically all that needs to happen, so how hard can it be? Perhaps someone more knowledgeable about this kind of programming could give some hints. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
SOLVED: [libreoffice-users] optimal width always
On Wed, 2023-07-12 at 08:25 -0400, James wrote: > You need to turn on macros in the settings (google how, I forgot) and > the > setting is enabled to record it (google where :-)). I think you can > assign the macro to a key. Thank you very much! I have enabled macro recording, recorded a macro to select the whole sheet and to set the column width, and it works just fine. I've assigned it to F3. I thought I couldn't record macros because there was no option to do that ... Why isn't that enabled by default. This is awesome, it'll make things a lot easier for me :) -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] LO keeps trying to recover files that don't need recovery
Hi, ever since I changed the directory for temporary files in the settings from /tmp to another directory, LO always wants to recover a file, or some files, when it's started despite these files don't need any recovery. The recovery fails. It is a very bad idea to use /tmp as the directory from which files could be recovered because /tmp is volatile, so there will be nothing that could be recovered after a reboot. Why is this the default? And how can I get rid of the unnecessary recovery attempts? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
