Recent

Author Topic: Sigsegv on reading an empty cell  (Read 803 times)

cov

  • Full Member
  • ***
  • Posts: 241
Sigsegv on reading an empty cell
« on: April 11, 2021, 08:54:44 am »
My program was working perfectly but all of a sudden I started getting "External SIGSEGV" errors.

It turns out that a row had been deleted in the spreadsheet using LibreOffice which meant that GetLastRowIndex() was overreading and ReadAsNumber(i,1) was attempting to read an empty cell.

At least I think that was the problem: Reading another cell in the same row which is expected to have text in it with textcell:=ReadAsText(i,3) and checking whether the textcell variable is an empty string solved it.

Now, however, I am reading a sheet which all the rows have number values with no text cells and I am again getting the External SIGSEGV errors.

What is the best way to check if a cell is empty? Particularly if the cell should contain a numerical entity?

Edit: Just noticed that the latest error is actually a 'Range Check Error' 'ERangeError' not a SIGSEGV. In any case, I need a check  on whether the cell is empty.

~ Dave
« Last Edit: April 11, 2021, 09:02:21 am by cov »

wp

  • Hero Member
  • *****
  • Posts: 8372
Re: Sigsegv on reading an empty cell
« Reply #1 on: April 11, 2021, 10:45:56 am »
Sorry, I cannot help much because I don't know what you are doing. How can LibreOffice and fpspreadsheet simultaneously work on the same file?

There are two kinds of "empty" cells:
  • Cells which do not exist. In this case the function TsWorksheet.FindCell(row, col) returns nil.
  • Cells which do not have visible content. In this case the function TsWorksheet.FindCell(row, col) returns the pointer to the cell record, but its ContentType is cctEmpty
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

cov

  • Full Member
  • ***
  • Posts: 241
Re: Sigsegv on reading an empty cell
« Reply #2 on: April 11, 2021, 11:26:48 am »
Yes, FPSpreadsheet can't write to the spreadsheet if it's being used by Libreoffice.

What happened was that the spreadsheet was accessed without error initially.

Then a row was deleted in an external program (Libreoffice).

When my program subsequently accessed the spreadsheet, I got the error. I can't say specifically that the misreporting GetLastRowIndex() was to blame, but when I checked for an empty string when the cell was accessed, I was able to eliminate the error.

Your suggestion of using findCell(row,col) sounds like what I need: Thanks, I'll try that.

~Dave

wp

  • Hero Member
  • *****
  • Posts: 8372
Re: Sigsegv on reading an empty cell
« Reply #3 on: April 11, 2021, 11:50:36 am »
You should not do this. fpspreadsheet does not contain code to detect whether a spreadsheet has been modified by another program. Finding an empty cell is not a solution to your issue because LibreOffice can do other changes which do not lead to empty cells. When you save the file from fpspreadsheet, even to a different name, the saved file will not contain the changes made by LibreOffice.

Since I do not plan to implement simultaneous access to a workbook from several applications I probably should go back one step and inhibit opening of a file by fpspreadsheet when the file already has been opened by another application.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

cov

  • Full Member
  • ***
  • Posts: 241
Re: Sigsegv on reading an empty cell
« Reply #4 on: April 11, 2021, 12:31:57 pm »
Fair enough, but if you are writing to a spreadsheet, you have to be prepared for that spreadsheet to be opened and changed by another program; otherwise a better option would be to use a proprietary format that other applications can't use and abuse.

In this particular case I need to prepare data that will be subsequently used to generate reports (most likely using Excel), so I need to use an accessible format, but I also have to be aware of the possibility of the spreadsheet being altered by the external program.

Your suggestion worked, by the way: Thanks for that!

~Dave

wp

  • Hero Member
  • *****
  • Posts: 8372
Re: Sigsegv on reading an empty cell
« Reply #5 on: April 11, 2021, 12:59:57 pm »
Fair enough, but if you are writing to a spreadsheet, you have to be prepared for that spreadsheet to be opened and changed by another program
You cannot save that workbook under its original name when the other application locks it, and LibreOffice does that. It is possible, though, to save the workbook under a different name.

In this particular case I need to prepare data that will be subsequently used to generate reports (most likely using Excel), so I need to use an accessible format, but I also have to be aware of the possibility of the spreadsheet being altered by the external program.
fpspreadsheet initially was intended to be a small library for reading and writing simple spreadsheet files. It has become fatter and fatter during the years, and some users are applying it for things which would be done more easily by the native office applications themselves. No, fpspreadsheet was not designed for multi-user access, and it will not be extended in this direction as long as I am taking care of it.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

cov

  • Full Member
  • ***
  • Posts: 241
Re: Sigsegv on reading an empty cell
« Reply #6 on: April 11, 2021, 01:59:14 pm »
That's perfectly reasonable. I don't think anyone would expect that.

My application will (hopefully) be used by multiple users in multiple situations and I want to be able to generate the information in a format that the user will be able to integrate into their workflow.

I need to make sure my program doesn't fall over if the spreadsheet has been modified.

Obviously, any modifications cannot alter the spreadsheet too drastically, but the removal or addition of Rows could be something that a user might do which it would be easy enough to accommodate.

A thought:
Maybe I should place a note in the spreadsheet in the first cell of the last row along the lines of "This information is generated programatically and any customisation should be confined to alternative sheets in the Workbook". That would allow me to know exactly where the end of the sheet was!

No need for that, though, your suggested remedy worked perfectly.

~ Dave
« Last Edit: April 11, 2021, 02:02:45 pm by cov »

 

TinyPortal © 2005-2018