Status of fpSpreadsheet lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Arbee · 2011-09-07 14:32 In earlier topics on  the internet it was mentioned that fpSpreadsheet can only reliably read spreadsheets that have been created by fpSpreadsheet itself.  That - alas - kind of defeats the purpose of the package.  At least for me, because I need to process sheets that are invariably created and maintained with the original applications, either Excel, OOo or Lotus Symphony. And indeed it turns out that - although I can open the workbooks - I can not read data from them in a consistent way.   So the question is, is this limitation still there?  Or do I somehow use a backlevel release?  It dates back to 2009, so I wonder ... is there still development in fpSpreadsheet? ---------------------------------------------------------------------- felipemdc · 2011-09-07 16:19 Quote from: Arbee on September 07, 2011, 04:32:25 pmSo the question is, is this limitation still there? I don't know, why don't you test and tell us? Quote  Or do I somehow use a backlevel release?  It dates back to 2009, so I wonder ... is there still development in fpSpreadsheet? Yes, a lot has changed. You should use the subversion version: http://wiki.lazarus.freepascal.org/FPSpreadsheet#Subversion ---------------------------------------------------------------------- bonmario · 2014-05-26 09:48 Hi, i have added this bug on mantis: http://mantis.freepascal.org/view.php?id=26221 Same project with a older version works fine. Thanks, Mario ---------------------------------------------------------------------- BigChimp · 2014-05-26 10:09 Moved bug to lazarus ccr as specified in http://wiki.lazarus.freepasca l.org/FPSpreadsheet#Support_and_Bug_Reporting Haven't looked at the bug itself yet though ---------------------------------------------------------------------- wp · 2014-05-26 11:02 This is not a bug. fpSpreadsheet exposes the count of columns and rows as GetLastColNumber and GetLastRowNumber which means the index of the last column and row. Therefore you should not subtract 1 from these numbers as you would do if the were the "count". @BigChimp: Maybe we should rename these quantities to GetLastColIndex and GetLastRowIndex to avoid this kind of confusion? @bonmario: You seem to duplicate the behavior of TsWorksheetGrid. Did you have a look at the current version of this grid component? There has been considerable improvement. It also reads and writes formatting information now (unfortunately not for ods, but this will be implemented shortly). ---------------------------------------------------------------------- bonmario · 2014-05-26 11:24 Hi, if you open the file "CATAPGPB.ods", all cells with "K" is in the column "K". If you execute the program, this is not. With an older version of fpspreadsheet it's alla ok. Thanks, Mario ---------------------------------------------------------------------- BigChimp · 2014-05-26 12:07 Quote from: wp on May 26, 2014, 01:02:46 pmThis is not a bug. fpSpreadsheet exposes the count of columns and rows as GetLastColNumber and GetLastRowNumber which means the index of the last column and row. Therefore you should not subtract 1 from these numbers as you would do if the were the "count". @BigChimp: Maybe we should rename these quantities to GetLastColIndex and GetLastRowIndex to avoid this kind of confusion? I would mark GetLastRowNumber/GetLastColNumber as deprecated, and add the new functions in order to keep backward compatibility, but yes, that makes sense. ---------------------------------------------------------------------- wp · 2014-05-26 12:41 Quote from: BonmarioHi, if you open the file "CATAPGPB.ods", all cells with "K" is in the column "K". If you execute the program, this is not.Thanks for clarifying. This seems to be an .ods issue because if I store the file as xls it loads correctly. I'll look into this issue in the evening. ---------------------------------------------------------------------- wp · 2014-05-26 15:04 Fixed in rev 3100. The attached screenshot shows your file in the output of the fpsgrid demo ---------------------------------------------------------------------- wp · 2014-05-26 15:29 Quote from: BigChimpI would mark GetLastRowNumber/GetLastColNumber as deprecated, and add the new functions in order to keep backward compatibility, but yes, that makes sense. Done in rev 3101 ---------------------------------------------------------------------- BigChimp · 2014-05-26 15:35 Thanks for the huge amount of work you put in, wp ---------------------------------------------------------------------- bonmario · 2014-05-26 16:13 Quote from: BigChimp on May 26, 2014, 05:35:56 pmThanks for the huge amount of work you put in, wp Thanks !!!!!!  Hi, Mario ---------------------------------------------------------------------- Caravelle · 2014-05-28 21:51 Lazarus 1.0.12 FPC 2.6.2 Please bear with me, I'm a comparative beginner at Lazarus and fpSpreadsheet is the first add-on I've attempted to add on.  Firstly I did not know that the zip download at SourceForge was an unwise choice, secondly it took me an age to work out how to install it into Lazarus and I'm still not convinced I managed it properly, third there were several failed attempts to open a small Excel 2003 .xls file, fourth an attempt to run the write demo produced a message that some required component could not be found... So I came looking in this Forum and found this thread which suggests that a more advanced package has to be downloaded from this mysterious SVN thing (it may mean something to you guys, but absolutely nothing to me). I eventually downloaded TortoiseSVN but have to say the Help is over my head.  I tried "svn checkout svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet fpspreadsheet" at the command prompt as recommended by the wiki at the URL in a message below, and after a while... nothing happened.   But then I don't know what was supposed to happen.  Nothing seems to be any different, I get the same message from excel5write.dpr that fpsconvencoding cannot be found.  Please, where do I start to sort all this out ?  Do I have to uninstall something, and if so what and how? Where can I find help for beginners with no knowledge of or even interest in the internal workings of the Lazarus IDE, that covers all this ?   I used to use XLSMini in Delphi 7 and never had any problem with that.   All I want to do is read plain text from the cells of simple Excel worksheets, nothing complicated. Looks like it might be quicker to just export as a csv file from Excel, I've probably spent more time today trying to install fpSpreadsheet than it can possibly save me through not having to do work with csv files. Many thanks Caravelle ---------------------------------------------------------------------- wp · 2014-05-29 11:02 Assuming you are on Windows (since you mention Delphi) I'd give you these step-by-step instructions: Download tortoisesvn and install it (as you already did). The only thing that you see of it at the moment is an extended context menu.Create a folder for the svn files ("working folder"). Navigate into that folder.Right-click on that folder, select "SVN Checkout" - this one of new menu items of TortoiseSVN.Paste "svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet" into the field "URL of repository". The field "Checkout directory" should containt the current path to the destination folder, the other settings should be correct as well."OK" copies all the files from the svn repository (the svn://svn.code.. thing) into your working folder. This may take some timeIf you want to update your working folder later to the current version in the repository, right-click onto the working folder in the Explorer and select "SVN Update". OK will keept your working folder in sync with the repository, i.e. copy changed files, remove deleted ones. etc.Run LazarusSelect "Package"/"Open package file (.lpk)". Navigate into the working directory and load "laz_fpspreadsheet.lpk".In the Package dialog select "Compile"If you want to use also visual controls of fpspreadsheet (the grid or the TAChart datasource) you also have to load "laz_fpspreadsheet_visual.lpk". In this case you also have to select "Use" and "Install". This recompiles Lazarus and adds these visual controls to the component palette. Again, may take some time. Lazarus closes and automatically opens the new version.That's all for the preparations.To write your own programs using fpspreadsheet, try one of the examples first. The "excel*demo" folders contains very short commandline sample how to write to or read from Excel files of various versions. Excel2003 that you mention is covered by the excel8demo. Or you can try the fpsgrid demo which shows the spreadsheet grid - I must confess, though, that this example may be a bit too overloaded for a beginner. And you MUST have installed the visual package for the grid example to run. laz_fpspreadsheet.lpk is sufficient for the other excel*demo examples.There is also a wiki giving the basic documentation http://wiki.lazarus.freepascal.org/FPSpreadsheetAnd please, you must be aware that you are using a development version of the software. So, things may change easily. We are doing our best to avoid regressions and to be compatible with previous versions, but sometime we see too late that one development lead into a dead end and we have to revert. Just ask here if something does not work any more. ---------------------------------------------------------------------- Caravelle · 2014-06-08 14:49 Many thanks for this comprehensive reply.  I apologise for the delay in responding, I have been unwell.  I shall follow your advice, see what happens and let you know the result.  It may take some time I'm afraid, I have a lot to catch up on  . ---------------------------------------------------------------------- Caravelle · 2014-06-08 19:27 Hi again.   I had a bit of spare time and couldn't wait... I followed your clear instructions and everything went well until I tried to install the visual package and was told "one or more required packages were not found".  But the required packages are listed in the package graph as far as I can see.  Please see attached screenshot. For reference I am working on a Vista 32 laptop, Lazarus 1.0.12, FPC 2.6.2, revision 42478.  I am fairly confident that I can do what I need with your component once I have it installed - I just need to read simple text from one worksheet and add it to a table in an existing sqlite database.  I am not a great fan of Excel BUT it is a good tool for repetitive data entry.  I don't need the visual components just yet, but I have an old Delphi program to convert one day and that will need the grid. Thanks Caravelle ---------------------------------------------------------------------- fpcoder · 2014-06-13 00:35 The utf8tools package is missing in the screenshot, it has an exclamation mark in the package graph. You can get it from here: http://wiki.lazarus.freepascal.org/Theodp ---------------------------------------------------------------------- wp · 2014-06-13 07:18 This can't be the reason since we're not using this package, it is not installed on any of my computers. I just installed fpspreadsheet on a fresh Lazarus and it compiles and runs fine. @Caravelle: Reading your first posting I suppose your issues are due to crashed package installations that you had tried before. If kapabira's hint to utf8tools package does not work out I'd propose that you start with a clean lazarus. Close Lazarus, rename the folder "c:\users\\AppData\local\Lazarus" , and start Lazarus again. Since it does not find its configuration any more it starts with its "out-of-the-box" settings. Try to install fpspreadsheet. It must work now from all that I know. Then you can begin to add all the packages that you had been using before. (If you want to redo these experiments, delete the newly created config folder and rename the old one back). ---------------------------------------------------------------------- Caravelle · 2014-06-17 17:39 Many thanks wp.   I think you were right, because I had similar issues with a failed installation of Zeos recently.  That got me in such a muddle that I ended up re-installing Lazarus completely (twice).  Kapibara was one of the kind folk who helped me then. I followed the guide above once more, and this time it has completely succeeded  Now to get to work at last.  Thanks again everyone Caravelle ---------------------------------------------------------------------- Caravelle · 2014-06-17 20:02 Can I report a problem please ?  It's very easy to fix. I tried the Excel8read demo.  It compiled OK but when I ran it I got an "EFOpenEror" report  "...can't open file fpspreadsheet\examples\excel8demo\test.xls".  There is no such file in the svn working folder, so that might explain why the demo cannot open it !    There is no test file in the excel2demo or excel5demo folders either.  I saved an existing Excel 2003 xls file as "fpspreadsheet\examples\excel8demo\test.xls" and initially I got the same message.  Then I realised that the file was still open in Excel itself.  When I closed Excel, the demo worked just fine (at last !).  This was only a minor hiccup, but obviously you don't want to create a bad impression at the very start. Thanks Caravelle ---------------------------------------------------------------------- wp · 2014-06-17 22:10 The test.xls file is created by executing the excel8write demo. Same for the demos of the other file formats. Yes, when Excel or Open/LibreOffice has opened a file, the file is locked for access by other programs. To my knowledge there nothing we do against that. ---------------------------------------------------------------------- Caravelle · 2014-06-17 22:44 Then can I suggest you put a note at the top of the read demos code to the effect that they won't work unless the equivalent write demo is executed first ?   I have no interest in writing to Excel, my only requirement is reading.  I have no reason to try the write demo unless instructed to do so. Thanks Caravelle ---------------------------------------------------------------------- taazz · 2014-06-17 22:59 why can't you see where the problem is exactly and change the demo to use one of your own files? Everything has to be spelled out for you before hand? ---------------------------------------------------------------------- BigChimp · 2014-06-18 07:38 Quote from: Caravelle on June 18, 2014, 12:44:53 amI have no interest in writing to Excel, my only requirement is reading.  I have no reason to try the write demo unless instructed to do so. Well, if the read demo aborts with a file not found error the problem is obvious, isn't it? Even though you don't have any interest in writing to Excel, is it too much to ask to compile 2 programs and run them one after the other? Otherwise, you could change the demo to catch the exception and show an error message to the user with the hint you're describing. I'd be happy to merge patches or if need be the entire .lpr file as long as it's based on trunk. I'm less than ecstatic about 1. adding a readme/hint in source code. People generally tend not to read (and complain on forums that things are unclear ) 2. just adding precooked xls/ods files that will have to be kept in sync with any changes, take up useless space anyway - we have the test cases for checking functionality against file formats already. ---------------------------------------------------------------------- BigChimp · 2014-06-18 08:53 Quote from: wp on June 18, 2014, 12:10:08 amYes, when Excel or Open/LibreOffice has opened a file, the file is locked for access by other programs. To my knowledge there nothing we do against that. And I don't think there's anything we can do either... ---------------------------------------------------------------------- wp · 2014-06-18 09:00 QuoteI'm less than ecstatic about (...) adding a readme/hint in source code. We're having users at various levels of expertise, and the less- experienced ones may not catch the point to run the other demo. Therefore I did add a two-liner to all the *read examples: Code: [Select]  if not FileExists(InputFileName) then     WriteLn('File ', InputFileName, ' does not exist. Please run the the write demo first.'); I totally agree not to keep the sample files in the repository. BTW I stuffed a lot of topics into the writing demos, I fear it may get difficult to catch the point. BigChimp, do you think it would be better to return to the original demos showing only how to write a few values and formats? The fpsgrid demo in particular has become very, very complex. Maybe I should rename it to something like "MiniSpread" or "SpreadView", or "MiniCalc" and re-establish the functionality of the original fpsgrid in another demo project. ---------------------------------------------------------------------- BigChimp · 2014-06-18 09:08 Quote from: wp on June 18, 2014, 11:00:47 amQuoteI'm less than ecstatic about (...) adding a readme/hint in source code. We're having users at various levels of expertise, and the less- experienced ones may not catch the point to run the other demo. Therefore I did add a two-liner to all the *read examples: Code: [Select]  if not FileExists(InputFileName) then     WriteLn('File ', InputFileName, ' does not exist. Please run the the write demo first.'); No problem - as it shows up in run time and the user is almost forced to read it. My point is that adding a remark in the source code alone would be useless.. Quote from: wp on June 18, 2014, 11:00:47 amBTW I stuffed a lot of topics into the writing demos, I fear it may get difficult to catch the point. BigChimp, do you think it would be better to return to the original demos showing only how to write a few values and formats? Well... splitting it up in some procedures and uncommenting the stuff that works would help. The resulting xls file would perhaps be a bit complex but you can easily examine it using your favourite spreadsheet Quote from: wp on June 18, 2014, 11:00:47 amThe fpsgrid demo in particular has become very, very complex. Maybe I should rename it to something like "MiniSpread" or "SpreadView", or "MiniCalc" and re- establish the functionality of the original fpsgrid in another demo project. Yes, I thought it would become complex I agree it may be a good idea to have e.g. a simplegrid demo with the more basic stuff