[Solved] Testing if a sheet is empty in Delphi

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Testing if a sheet is empty in Delphi

Postby Deer » Mon Mar 24, 2008 12:18 pm

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   Expand viewCollapse view
procedure TForm1.FormShow(Sender: TObject);
    Sheet, SearchDescriptor, SheetRanges, v : variant;
    SheetCount, i, j, arrayindex : integer;
  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) + ':' +


Last edited by Deer on Wed Mar 26, 2008 8:19 pm, edited 2 times in total.
Posts: 4
Joined: Sun Mar 23, 2008 2:24 am

Re: Testing if a sheet is empty in Delphi

Postby Villeroy » Mon Mar 24, 2008 12:46 pm

Code: Select all   Expand viewCollapse view
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
end with
REM returns c.s.s.sheet.SheetCellRanges
oRanges = obj.queryContentCells(nWhat)
hasContents = (oRanges.getCount() > 0)
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 28440
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to External Programs

Who is online

Users browsing this forum: No registered users and 1 guest