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
Re: [libreoffice-users] Re: database2spreadsheet
At 16:06 07/04/2015 +0200, Honly Wonly wrote: 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. As you already indicated. Opening the CSV creates a new spreadsheet. Not necessarily: you can import a CSV file as a new sheet in an existing spreadsheet - and you can very simply copy and paste from there to wherever you want it. I don't want to apply formatting ... The previous suggestion - pasting as Unformatted text into a previously formatted sheet, probably derived from a template - avoids this. You will need to indicate what formatting you want at some point, of course, and you can easily do this by creating a template. ... 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. Then you may well need not Calc but SuperCalc. You could volunteer to help create it at www.iwanttohelpwritesupercalc.org . With luck it may be developed to read your mind as well. Or you could employ an assistant. Brian Barker -- 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
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
[libreoffice-users] Re: database2spreadsheet
Am 23.03.2015 um 17:01 schrieb hw: 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. Exactly like with any other database. For the formatted output of database data does not matte if your Base document is connected to csv, dBase, spreadsheets, Oracle server, MySQL, embedde HSQL or any kind of ODBC data source. [Example] Loading CSV into preformatted spreadsheets https://forum.openoffice.org/en/forum/viewtopic.php?f=100t=23727 File #1 is a csv file. File #3 is a Base document connected to the csv file, more precisely to its directory which may contain more csv files. File #4 is a pre-formatted spreadsheet template with a preformatted import range. -- 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
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. I trust this helps. Brian Barker -- 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=83t=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=100t=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
[libreoffice-users] Re: database2spreadsheet
Am 19.03.2015 um 11:37 schrieb hw: 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. I'm pretty sure this can be fixed one way or the other. Sorry, I don't know about this problem since I do not use that reporting tool (in fact I do not even use LibreOffice). Assuming that you get that report with pictures running and you really want to manipulate the data on that report and you know that this is NOT how things should be done because your reports would differ from your database content, then all you need to do is calling menu:FileSaveAs... and save a new writable copy of that Writer document. All reports are read-only Writer documents in the first place. -- 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] Re: database2spreadsheet
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. -- 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
[libreoffice-users] Re: database2spreadsheet
Am 17.03.2015 um 13:14 schrieb hw: 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. Not trivial, but this is what SQL does so you simply link the result of one or more SQL queries to a spreadsheet. Having the data in a spreadsheet you may perform spreadsheet calculations record wise and you can lookup values from other record sets. 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? No, but there are Python, JavaScript and Java as alternatives to the Basic lingo. Just because this happens to be an office suite, nobody forces you to work with Basic. Being a Perl monger, why don't you dump all your results into csv files and then link those files to a spreadsheet? (if it really has to be a spreadsheet ... many users insist in using spreadsheets for no reason). -- 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
Hi :) Ahh, i thought Writer, Calc etc could only look-up data and could not input or edit data. I thought that was where the Base's internal Forms and Reports were really useful? So normal users could be kept in their safe familiar environment with no fear of accidentally breaking anything much. Meanwhile progressively more skilled/knowledgeable workers could easily be given more and more abilities. Regards from Tom :) On 17 March 2015 at 17:37, Andreas Säger ville...@t-online.de wrote: Am 17.03.2015 um 16:37 schrieb hw: 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? Click any single cell in that import range and call DataRefresh A one-line macro can update this on file open. You can also set a flag to that import range to not store the data within the spreadsheet. In this case you hit Enter when are prompted to update the unsaved import data on file open. You find this option under menu:DataDefine... pick your import range, [More Options] You can add a timer to the import range so it updates every x seconds. [Tutorial] Using registered datasources in Calc: https://forum.openoffice.org/en/forum/viewtopic.php?f=75t=18511 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. Your edits will be lost on next refresh. For database input you can add a true input form to this spreadsheet document or any other office document (Writer, Calc Draw). In some cases you may use the data source window for data entry into updatable record sets. -- 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 -- 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] Re: database2spreadsheet
Am 17.03.2015 um 16:37 schrieb hw: 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? Click any single cell in that import range and call DataRefresh A one-line macro can update this on file open. You can also set a flag to that import range to not store the data within the spreadsheet. In this case you hit Enter when are prompted to update the unsaved import data on file open. You find this option under menu:DataDefine... pick your import range, [More Options] You can add a timer to the import range so it updates every x seconds. [Tutorial] Using registered datasources in Calc: https://forum.openoffice.org/en/forum/viewtopic.php?f=75t=18511 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. Your edits will be lost on next refresh. For database input you can add a true input form to this spreadsheet document or any other office document (Writer, Calc Draw). In some cases you may use the data source window for data entry into updatable record sets. -- 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] Re: database2spreadsheet
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. For a LibreOffice user there is only one built-in tool to do reporting with pictures. It is the report tool. You are a Perl programmer. This kind of stuff has always be done with html reports. -- 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] 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? 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. 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=83t=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. 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). I'm 100 sure that an office suite can not add anything to your project. 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=100t=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). -- 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 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 quickdirty 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 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
[libreoffice-users] Re: database2spreadsheet
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. -- 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 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 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
[libreoffice-users] Re: database2spreadsheet
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. 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 quickdirty Basic functions in spreadsheets or even Basic functions calling Python functions. -- 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] Re: database2spreadsheet
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. -- 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] Re: database2spreadsheet
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). Alex -- 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] 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 ? 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. Look at the following Perl modules : ODF::lpod OpenOffice::UNO OpenOffice::OODoc How much time does it take to re-implemented the built-in functionality? And why? -- 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] Re: database2spreadsheet
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. -- 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] Re: database2spreadsheet
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 Alex -- 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] Re: database2spreadsheet
Am 04.03.2015 um 10:59 schrieb hw: Hi, how would I create a spreadsheet from a table in a database? [Tutorial] Using registered datasources in Calc https://forum.openoffice.org/en/forum/viewtopic.php?f=75t=18511 -- 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
