[Solved] Testing if a sheet is empty in Delphi

[Deer]

I want to search in a workbook with 3 sheets. For example only the first sheet contains cells, the second and the third are empty (every cells are deleted, not just the cells' content).
I don't know why it is good, but I have to work with this, and similar spreadsheetdocs.

As I declare 'SearchDescriptor' for an empty sheet, Delphi throws exception: 'Variant does not reference an automation object'.
So I want to test that the sheet is empty or not before I declare a 'SearchDescriptor' for it.
I tried it with 'SupportsService()', but the every sheets support the same services.

How do I know if a sheet is empty or not? (I don't want to use Delphi's exception-handling)

Can you help me?

The code:

Code: Select all

procedure TForm1.FormShow(Sender: TObject);
var
    Sheet, SearchDescriptor, SheetRanges, v : variant;
    SheetCount, i, j, arrayindex : integer;
begin
  App     := CreateOleObject('com.sun.star.ServiceManager');
  Desktop := App.CreateInstance('com.sun.star.frame.Desktop');
  p    := VarArrayCreate([0, 0], varVariant);
  p[0] := CreateValue('Hidden', true);
  Document := Desktop.LoadComponentFromURL('file:///c:/test.xls', '_blank', 0, P);

  SheetCount := Document.Sheets.Count;

  for i := 0 to SheetCount - 1 do begin
    Sheet := Document.Sheets.getByIndex(i);

    SearchDescriptor := Sheet.createSearchDescriptor;
    SearchDescriptor.SearchString := '133';

    v := Sheet.SupportedServiceNames;
    for arrayindex := 1 to VarArrayHighBound(v,1) do ListBox1.Items.Add(IntToStr(i) +  v[arrayindex]);

    // this is the place where I want to test

    SheetRanges := Sheet.FindAll(SearchDescriptor).RangeAddresses;
    // Delphi throws an exception here if sheet has no cells

    for j := 0 to varArrayHighBound(SheetRanges,1) do
      ListBox1.Items.Add((IntToStr(SheetRanges[j].Sheet) + '->'+
                          IntToStr(SheetRanges[j].StartColumn) + ':' +
                          IntToStr(SheetRanges[j].StartRow)));

  end;

end;

[Villeroy]

Basic:

Code: Select all

REM test formulas, values, strings, disregard annotations
REM sheet, range, ranges have interface c.s.s.sheet.XCellRangesQuery
Function hasContents(obj)
with com.sun.star.sheet.CellFlags
  nWhat = .VALUE + .STRING + .DATETIME + .FORMULA
end with
REM returns c.s.s.sheet.SheetCellRanges
oRanges = obj.queryContentCells(nWhat)
hasContents = (oRanges.getCount() > 0)
End Function