TsWorksheetIndicator lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- abcthing · 2020-04-13 02:37 I wrote TsWorksheetIndicator based on TsWorkbookTabControl  (slightly modified), changed from TabControl to TComboBox. I hope it is helpful.   { TsWorksheetIndicator }   {@@ TsWorksheetIndicator is a combobox control which displays the sheets of the     workbook currently loaded by the WorkbookSource. }   TsWorksheetIndicator = class(TComboBox, IsSpreadsheetControl)   private     FWorkbookSource: TsWorkbookSource;     FLockCount: Integer;     FShowAllSheets: Boolean;     procedure SetShowAllSheets(AValue: Boolean);     procedure SetWorkbookSource(AValue: TsWorkbookSource);   protected     procedure Change; override;     procedure GetSheetList(AList: TStrings);     function GetWorkbook: TsWorkbook;     function GetWorksheet: TsWorksheet;     procedure Notification(AComponent: TComponent; Operation: TOperation); override;   public     constructor Create(AOwner: TComponent); override;     destructor Destroy; override;     procedure ListenerNotification(AChangedItems: TsNotificationItems;       AData: Pointer = nil);     procedure RemoveWorkbookSource;     property Workbook: TsWorkbook read GetWorkbook;     property Worksheet: TsWorksheet read GetWorksheet;   published     property ShowAllSheets: boolean read FShowAllSheets write SetShowAllSheets default true;     property WorkbookSource: TsWorkbookSource read FWorkbookSource write SetWorkbookSource;   end; {--------------------------------------------------------------------- ---------} {                            TsWorksheetIndicator                            } {--------------------------------------------------------------------- ---------} constructor TsWorksheetIndicator.Create(AOwner: TComponent); begin   inherited Create(AOwner);   FShowAllSheets := true; end; destructor TsWorksheetIndicator.Destroy; begin   if FWorkbookSource <> nil then FWorkbookSource.RemoveListener(self);   inherited Destroy; end; procedure TsWorksheetIndicator.Change; var   sheetName: String; begin   if (FWorkbookSource <> nil) and (FLockCount = 0) then begin     sheetName := Items[ItemIndex];     FWorkbookSource.SelectWorksheet(Workbook.GetWorksheetByName(sheetN ame));   end;   inherited; end; procedure TsWorksheetIndicator.GetSheetList(AList: TStrings); var   i: Integer;   sheet: TsWorksheet; begin   AList.Clear;   if Workbook <> nil then     for i:=0 to Workbook.GetWorksheetCount-1 do     begin       sheet := Workbook.GetWorksheetByIndex(i);       if FShowAllSheets or not (soHidden in sheet.Options) then         AList.Add(sheet.Name);     end; end; function TsWorksheetIndicator.GetWorkbook: TsWorkbook; begin   if FWorkbookSource <> nil then     Result := FWorkbookSource.Workbook   else     Result := nil; end; function TsWorksheetIndicator.GetWorksheet: TsWorksheet; begin   if FWorkbookSource <> nil then     Result := FWorkbookSource.Worksheet   else     Result := nil; end; procedure TsWorksheetIndicator.ListenerNotification(   AChangedItems: TsNotificationItems; AData: Pointer = nil); var   i: Integer;   sheet: TsWorksheet; begin   Unused(AData);   // Workbook changed: new workbook, worksheet added/renamed/deleted   if (AChangedItems * [lniWorkbook, lniWorksheetAdd, lniWorksheetRemove, lniWorksheetRename] <> []) then   begin     inc(FLockCount);    // avoid WorkbookSelect message when adding each tab     GetSheetList(Items);     if (lniWorkbook in AChangedItems) and (Workbook <> nil) then     begin       i := Items.IndexOf(Workbook.ActiveWorksheet.Name);       if i > -1 then ItemIndex := i else ItemIndex := 0     end else     if (lniWorksheetAdd in AChangedItems) then       ItemIndex := Items.Count-1     else     if (lniWorksheetRename in AChangedItems) then begin       sheet := TsWorksheet(AData);       ItemIndex := Items.IndexOf(sheet.Name);     end;     dec(FLockCount);   end;   // Worksheet selected   if (lniWorksheet in AChangedItems) and (Worksheet <> nil) then   begin     i := Items.IndexOf(Worksheet.Name);     if i <> ItemIndex then       ItemIndex := i;   end; end; procedure TsWorksheetIndicator.Notification(AComponent: TComponent;   Operation: TOperation); begin   inherited Notification(AComponent, Operation);   if (Operation = opRemove) and (AComponent = FWorkbookSource) then     SetWorkbookSource(nil); end; procedure TsWorksheetIndicator.RemoveWorkbookSource; begin   SetWorkbookSource(nil); end; procedure TsWorksheetIndicator.SetShowAllSheets(AValue: Boolean); var   idx, i: Integer;   sheet: TsWorksheet; begin   if AValue = FShowAllSheets then     exit;   FShowAllSheets := AValue;   idx := -1;   // Find ItemIndex of next visible sheet   if not FShowAllSheets and (Workbook <> nil) then begin     for i:=0 to Workbook.GetWorksheetCount-1 do begin       sheet := Workbook.GetWorksheetByIndex(i);       if sheet = Worksheet then         break;       if not (soHidden in sheet.Options) then inc(idx);     end;     i := idx;     while (sheet <> nil) and (soHidden in sheet.Options) do begin       inc(i);       sheet := Workbook.GetWorksheetByIndex(i);     end;     if sheet = nil then begin       i := idx;       while (sheet <> nil) and (soHidden in sheet.Options) do begin         dec(i);         sheet := Workbook.GetWorksheetByIndex(i);       end;       if sheet = nil then idx := -1;     end;   end;   Change;   if (not FShowAllSheets) then     ItemIndex := idx; end; procedure TsWorksheetIndicator.SetWorkbookSource(AValue: TsWorkbookSource); begin   if AValue = FWorkbookSource then     exit;   if FWorkbookSource <> nil then     FWorkbookSource.RemoveListener(self);   FWorkbookSource := AValue;   if FWorkbookSource <> nil then     FWorkbookSource.AddListener(self);   ListenerNotification([lniWorkbook, lniWorksheet]); end; ---------------------------------------------------------------------- Ñuño_Martínez · 2020-04-13 10:42 Please, use the [ code ] labels to show your sources (the # button over the smile line).  Quote me to see how I did it: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   { TsWorksheetIndicator }   {@@ TsWorksheetIndicator is a combobox control which displays the sheets of the    workbook currently loaded by the WorkbookSource. }  TsWorksheetIndicator = class(TComboBox, IsSpreadsheetControl)  private    FWorkbookSource: TsWorkbookSource;  FLockCount: Integer;    FShowAllSheets: Boolean;    procedure SetShowAllSheets(AValue: Boolean);    procedure SetWorkbookSource(AValue: TsWorkbookSource);  protected    procedure Change; override;    procedure GetSheetList(AList: TStrings);    function GetWorkbook: TsWorkbook;    function GetWorksheet: TsWorksheet;    procedure Notification(AComponent: TComponent; Operation: TOperation); override;  public    constructor Create(AOwner: TComponent); override;    destructor Destroy; override;    procedure ListenerNotification(AChangedItems: TsNotificationItems;      AData: Pointer = nil);    procedure RemoveWorkbookSource;    property Workbook: TsWorkbook read GetWorkbook;    property Worksheet: TsWorksheet read GetWorksheet;  published    property ShowAllSheets: boolean read FShowAllSheets write SetShowAllSheets default true;    property WorkbookSource: TsWorkbookSource read FWorkbookSource write SetWorkbookSource;  end; { ---------------------------------------------------------------------- --------}{                            TsWorksheetIndicator                            }{--------------------------------------------------- ---------------------------} constructor TsWorksheetIndicator.Create(AOwner: TComponent);begin  inherited Create(AOwner);  FShowAllSheets := true;end; destructor TsWorksheetIndicator.Destroy;begin  if FWorkbookSource <> nil then FWorkbookSource.RemoveListener(self);  inherited Destroy;end; procedure TsWorksheetIndicator.Change;var  sheetName: String;begin  if (FWorkbookSource <> nil) and (FLockCount = 0) then begin    sheetName := Items[ItemIndex];    FWorkbookSource.SelectWorks heet(Workbook.GetWorksheetByName(sheetName));  end;  inherited;end; procedure TsWorksheetIndicator.GetSheetList(AList: TStrings);var  i: Integer;  sheet: TsWorksheet;begin  AList.Clear;  if Workbook <> nil then    for i:=0 to Workbook.GetWorksheetCount-1 do  begin      sheet := Workbook.GetWorksheetByIndex(i);      if FShowAllSheets or not (soHidden in sheet.Options) then        AList.Add(sheet.Name);    end;end; function TsWorksheetIndicator.GetWorkbook: TsWorkbook;begin  if FWorkbookSource <> nil then    Result := FWorkbookSource.Workbook  else    Result := nil;end; function TsWorksheetIndicator.GetWorksheet: TsWorksheet;begin  if FWorkbookSource <> nil then    Result := FWorkbookSource.Worksheet  else    Result := nil;end; procedure TsWorksheetIndicator.ListenerNotification(  AChangedItems: TsNotificationItems; AData: Pointer = nil);var  i: Integer;  sheet: TsWorksheet;begin  Unused(AData);   // Workbook changed: new workbook, worksheet added/renamed/deleted  if (AChangedItems * [lniWorkbook, lniWorksheetAdd, lniWorksheetRemove, lniWorksheetRename] <> []) then  begin    inc(FLockCount);    // avoid WorkbookSelect message when adding each tab    GetSheetList(Items);    if (lniWorkbook in AChangedItems) and (Workbook <> nil) then    begin      i := Items.IndexOf(Workbook.ActiveWorksheet.Name);      if i > -1 then ItemIndex := i else ItemIndex := 0    end else    if (lniWorksheetAdd in AChangedItems) then      ItemIndex := Items.Count-1    else    if (lniWorksheetRename in AChangedItems) then begin      sheet := TsWorksheet(AData);      ItemIndex := Items.IndexOf(sheet.Name);    end;    dec(FLockCount);  end;   // Worksheet selected  if (lniWorksheet in AChangedItems) and (Worksheet <> nil) then  begin    i := Items.IndexOf(Worksheet.Name);    if i <> ItemIndex then      ItemIndex := i;  end;end; procedure TsWorksheetIndicator.Notification(AComponent: TComponent;  Operation: TOperation);begin  inherited Notification(AComponent, Operation);  if (Operation = opRemove) and (AComponent = FWorkbookSource) then    SetWorkbookSource(nil);end; procedure TsWorksheetIndicator.RemoveWorkbookSource;begin  SetWorkbookSource(nil);end; procedure TsWorksheetIndicator.SetShowAllSheets(AValue: Boolean);var  idx, i: Integer;  sheet: TsWorksheet;begin  if AValue = FShowAllSheets then  exit;  FShowAllSheets := AValue;  idx := -1;  // Find ItemIndex of next visible sheet  if not FShowAllSheets and (Workbook <> nil) then begin    for i:=0 to Workbook.GetWorksheetCount-1 do begin      sheet := Workbook.GetWorksheetByIndex(i);      if sheet = Worksheet then      break;      if not (soHidden in sheet.Options) then inc(idx);    end;    i := idx;    while (sheet <> nil) and (soHidden in sheet.Options) do begin      inc(i);      sheet := Workbook.GetWorksheetByIndex(i);    end;    if sheet = nil then begin      i := idx;      while (sheet <> nil) and (soHidden in sheet.Options) do begin        dec(i);        sheet := Workbook.GetWorksheetByIndex(i);      end;      if sheet = nil then idx := -1;    end;  end;  Change;  if (not FShowAllSheets) then    ItemIndex := idx;end; procedure TsWorksheetIndicator.SetWorkbookSource(AValue: TsWorkbookSource);begin  if AValue = FWorkbookSource then    exit;  if FWorkbookSource <> nil then    FWorkbookSource.RemoveListener(self);  FWorkbookSource := AValue;  if FWorkbookSource <> nil then    FWorkbookSource.AddListener(self);  ListenerNotification([lniWorkbook, lniWorksheet]);end; ---------------------------------------------------------------------- abcthing · 2020-04-13 14:26 Thank you! I'll try it next. ---------------------------------------------------------------------- wp · 2020-04-13 15:32 Thank you for the addition. I have a concern, however, with the control being derived from TCombobox, not from TCustomCombobox. For example, the default value of Style, csDropDown, allows the user to type anything into the combo's edit field - but this is not necessarily an existing worksheet name. Also, the property Items can be filled by the user with anything, but in this particular context only existing Worksheet names are allowed. Probably, there are more examples of this kind. I'd prefer to derive the control from TCustomCombobox and publish only the inherited properties neede, i.e. without Styles and without Items and maybe some others. ---------------------------------------------------------------------- abcthing · 2020-04-14 00:24 Yes, you are right!  I found another problem. Put the TCombobox to the toolbar,  can't be moved, must modify the align property to alCustom. ---------------------------------------------------------------------- wp · 2020-04-14 23:17 Any control added to a toolbar cannot be moved freely. It is always left-align and can only change its place with other buttons or controls in the toolbar. Or do you mean something else? ---------------------------------------------------------------------- abcthing · 2020-04-15 00:09 I see. Thank you! ---------------------------------------------------------------------- wp · 2020-04-15 22:20 Added your TsWorksheetIndicator as new visual component to fpspreadsheet. Usage is demonstrated in updated demo_ctrls. ---------------------------------------------------------------------- abcthing · 2020-04-16 03:05 Nice going! I have downloaded r7386 and used it.