Recent

Author Topic: Best way to determine if TsWorkSheetGrid has connection to spreadsheet  (Read 419 times)

dodgebros

  • Full Member
  • ***
  • Posts: 110
Please pardon the subject line wording, not sure how to word my quesion.  I have a TsWorkSheetGrid that I run a process on and I need to know if the user has, shall we say, bond an actual spreadsheet file to it before the process is allowed to run.

At the present, I am checking to see if the TsWorkBookSource.filename property is populated.  Is that the best way to determine this?

Hope this makes sense,
TD

wp

  • Hero Member
  • *****
  • Posts: 7555
But a workbook can be created by code and can contain data without being saved...

Checking the sheet count does not work because the visual controls create an empty dummy sheet by default.

I think it's best to check the row and column count of the worksheet. If both are zero, it cannot contain any data:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.FormCreate(Sender: TObject);
  2. begin
  3.   if (sWorkbookSource1.Workbook.GetWorksheetCount <= 1) and
  4.      ((sWorkbookSource1.Worksheet.GetLastRowIndex = 0) and (sWorkbookSource1.Worksheet.GetLastcolIndex = 0))  
  5.   then
  6.     Caption := 'empty';
  7. end;
« Last Edit: July 14, 2020, 07:09:25 pm by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

dodgebros

  • Full Member
  • ***
  • Posts: 110
Thank you wp, I'll try that,
TD

dodgebros

  • Full Member
  • ***
  • Posts: 110
Re: Best way to determine if TsWorkSheetGrid has connection to spreadsheet
« Reply #3 on: August 11, 2020, 05:18:28 pm »
I created this function but I am getting Error: Identifier not found "WkBkSource".  This is the name of the TsWorkbookSource in the project.  If I comment out this function then the app compiles and runs fine.

What am I doing wrong?
TD  :o

Code: Pascal  [Select][+][-]
  1. function fchkSSLoaded(): boolean;
  2. begin
  3.   try
  4.     begin
  5.       if (( WkBkSource.Workbook.GetWorksheetCount <= 1) And
  6.           ((WkBkSource.Worksheet.GetLastRowIndex = 0) And
  7.            (WkBkSource.Worksheet.GetLastcolIndex = 0))) then
  8.         begin
  9.           fchkSSLoaded := false;
  10.         end
  11.       else
  12.         begin
  13.           fchkSSLoaded := true;
  14.       end;
  15.     end;
  16.   except
  17.     on E : Exception do
  18.       begin
  19.         showmessage('Exception message = ' + E.Message);
  20.         showmessage('Spreadsheet, chkSSLoaded');
  21.       end;
  22.   end;
  23. end;
  24.  

dodgebros

  • Full Member
  • ***
  • Posts: 110
Re: Best way to determine if TsWorkSheetGrid has connection to spreadsheet
« Reply #4 on: August 11, 2020, 09:55:25 pm »
Ok, I just added the unit name to the code like this and it now works:

Code: Pascal  [Select][+][-]
  1.       if (( frmSpreadsheet.WkBkSource.Workbook.GetWorksheetCount <= 1) And
  2.           ((frmSpreadsheet.WkBkSource.Worksheet.GetLastRowIndex = 0) And
  3.            (frmSpreadsheet.WkBkSource.Worksheet.GetLastcolIndex = 0))) then
  4.  

I refer to WkBkSource in other parts of my code without having to do this and I do not get errors there.  Why does it throw an error here?

TD  :(
« Last Edit: August 11, 2020, 10:12:56 pm by dodgebros »

wp

  • Hero Member
  • *****
  • Posts: 7555
Re: Best way to determine if TsWorkSheetGrid has connection to spreadsheet
« Reply #5 on: August 11, 2020, 10:37:59 pm »
Ok, I just added the unit name to the code like this and it now works:

Code: Pascal  [Select][+][-]
  1.       if (( frmSpreadsheet.WkBkSource.Workbook.GetWorksheetCount <= 1) And
  2.           ((frmSpreadsheet.WkBkSource.Worksheet.GetLastRowIndex = 0) And
  3.            (frmSpreadsheet.WkBkSource.Worksheet.GetLastcolIndex = 0))) then
  4.  

I refer to WkBkSource in other parts of my code without having to do this and I do not get errors there.  Why does it throw an error here?
I don't know what's in your units, so I am of limited help. But I guess you have a form named frmSpreadsheet and it contains a TsWorkbookSource named WkBkSource. And I guess that fchkSSloaded() is in some other unit. This other unit has frmSpreadsheet in its uses clauses. In this case the other unit sees only the frmSpreadsheet, it does not see WkBkSource because only frmSpreadsheet is declared as a variable there. But WkBkSource is a published variable of frmSpreadsheet. Therefore, you can access the WkBookSource from any other unit with uses frmSpreadsheet by calling frmSpreadsheet.WkBkSource.

As you see this situation is quite complex - and to me hard to explain so that I don't know that you understand my explanation. The problem is that there are too many dependencies. It would be better if you'd pass the workbook to the fckSSLoaded as a parameter:

Code: Pascal  [Select][+][-]
  1. function fchkSSLoaded(AWorkbook: TsWorkbook): boolean;
  2. var
  3.   sheet: TsWorksheet;
  4. begin
  5.   Result := false;
  6.  
  7.   // no workbook --> not loaded
  8.   if AWorkbook = nil then
  9.     exit;
  10.  
  11.   // workbook exists but has not sheets --> not loaded
  12.   if AWorkbook.GetWorksheetCount = 0 then
  13.     exit;
  14.  
  15.   // if workbook exists and has one sheet:
  16.   if AWorkbook.GetWorksheetCount = 1 then
  17.   begin
  18.     // get this only worksheet
  19.     sheet := AWorkbook.GetWorksheetByIndex(0);  
  20.     // this sheet has no cells --> not loaded
  21.     if (sheet.GetlastRowIndex = 0) and (sheet.GetLastColIndex = 0) then
  22.       exit;
  23.   end;
  24.  
  25.   // all other cases --> workbook is loaded.
  26.   Result := true;
  27. end;

This code does not need to know about frmSpreadsheet and that is contains a workbooksource named WkBkSource. Only the calling procedure needs to know that when it wants to do the check.

Code: Pascal  [Select][+][-]
  1.   if fchkSSLoaded(frmSpreadsheet.WkBkSource.Workbook) then ...
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

dodgebros

  • Full Member
  • ***
  • Posts: 110
Re: Best way to determine if TsWorkSheetGrid has connection to spreadsheet
« Reply #6 on: August 12, 2020, 12:20:55 am »
Thank you wp for taking the time to answer my post.  Actually fchkSSLoaded() is in the frmSpreadsheet unit.  I don't know why it just all of a sudden, and only in this function, needed the have the unit name in order to recognize WkBkSource.

I understood your explanation so you did a good job of explaining it.  I really like your idea of passing the workbook as a parameter so i will be implementing it.

Thanks again wp

 

TinyPortal © 2005-2018