fuqimport3xlseditor.pas

Advanced Data Import Component Suite for Borland Delphi and C++ Builder allows you to import your da

  var
    str, str1: string;
    N: integer;
  begin
    if IsCol
      then str := Format('[%s]%s-%s;', [pcXLSFile.ActivePage.Caption, Col2Letter(Number), COLFINISH])
      else str := Format('[%s]%s-%s;', [pcXLSFile.ActivePage.Caption, Row2Number(Number), ROWFINISH]);

      N := FXLSGridSelection.IndexOfRange(str);
      if N > -1 then FXLSGridSelection.Delete(N);

      if (not IsCtrl) or (N = -1) then
      begin
        str1 := FXLSGridSelection.AsString;
        str1 := str1 + str;
        FXLSGridSelection.AsString := str1;
      end;
  end;

var
  Grid: TqiStringGrid;
  ACol, ARow, SCol, SRow, N, i: integer;
  IsCtrl, IsShift: boolean;

  procedure ChangeCurrentCell(Col, Row: integer);
  var
    Event: TSelectCellEvent;
  begin
    Event := Grid.OnSelectCell;
    Grid.OnSelectCell := nil;
    Grid.Col := Col;
    Grid.Row := Row;
    Grid.OnSelectCell := Event;
  end;

begin
  if not (Sender is TqiStringGrid) then Exit;
  Grid := Sender as TqiStringGrid;

  IsShift := GetKeyState(VK_SHIFT) < 0;
  IsCtrl := GetKeyState(VK_CONTROL) < 0;

  if not (IsShift or IsCtrl) then begin
    Grid.Repaint;
    Exit;
  end;

  Grid.MouseToCell(X, Y, ACol, ARow);

  if not ((ACol = 0) xor (ARow = 0)) then begin
    Grid.Repaint;
    Exit;
  end;

  if not FXLSIsEditingGrid then
    XLSStartEditing;

  if IsCtrl then begin
    if ACol = 0
      then N := ARow
      else N := ACol;

    AddColRowToSelection(ARow = 0, true, N);

    if ACol = 0
      then ChangeCurrentCell(Grid.Col, ARow)
      else ChangeCurrentCell(ACol, Grid.Row);
  end
  else if IsShift then begin
    SCol := Grid.Col;
    SRow := Grid.Row;

    if ACol = 0 then begin
      if SRow <= ARow then
        for i := SRow to ARow do
          AddColRowToSelection(false, false, i)
      else
        for i := SRow downto ARow do
          AddColRowToSelection(false, false, i);
      ChangeCurrentCell(Grid.Col, ARow);
    end
    else begin
      if SCol <= ACol then
        for i := SCol to ACol do
          AddColRowToSelection(true, false, i)
      else
        for i := SCol downto ACol do
          AddColRowToSelection(true, false, i);
      ChangeCurrentCell(ACol, Grid.Row);
    end;
  end;

  XLSFillSelection;
  Grid.Repaint;
end;

procedure TfmQImport3XLSEditor.XLSSelectCell(Sender: TObject; ACol, ARow: Integer;
  var CanSelect: Boolean);
var
  Grid: TqiStringGrid;
  SCol, SRow, i: integer;
  Str: string;
  IsShift, IsCtrl, Cut: boolean;
begin
  if not (Sender is TqiStringGrid) then Exit;
  Grid := Sender as TqiStringGrid;

  IsShift := GetKeyState(VK_SHIFT) < 0;
  IsCtrl := GetKeyState(VK_CONTROL) < 0;

  if not (IsShift or IsCtrl) then begin
    XLSFinishEditing;
    Exit;
  end;

  SCol := Grid.Col;
  SRow := Grid.Row;

  if IsShift and not ((SCol = ACol) or (SRow = ARow)) then begin
    XLSFinishEditing;
    Exit;
  end;

  if not FXLSIsEditingGrid then
    XLSStartEditing;

  Cut := false;
  if (SCol = ACol) and (SRow = ARow) then begin
  end
  else begin
    if IsShift then begin
      if FXLSGridSelection.Count > 0 then begin

        if SCol <> ACol then begin
          if FXLSGridSelection[FXLSGridSelection.Count - 1].RangeType = rtRow then begin
            if SCol > ACol then begin
              for i := SCol downto ACol + 1 do
                if CellInRange(FXLSGridSelection[FXLSGridSelection.Count - 1],
                     EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, i, ARow) then begin
                  Cut := true;
                  FXLSGridSelection[FXLSGridSelection.Count - 1].Col2 :=
                    FXLSGridSelection[FXLSGridSelection.Count - 1].Col2 - 1;
                end;
            end
            else begin
              for i := SCol to ACol - 1 do
                if (FXLSGridSelection[FXLSGridSelection.Count - 1].RangeType = rtRow) and
                   CellInRange(FXLSGridSelection[FXLSGridSelection.Count - 1],
                     EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, i, ARow) then begin
                  Cut := true;
                  FXLSGridSelection[FXLSGridSelection.Count - 1].Col2 :=
                    FXLSGridSelection[FXLSGridSelection.Count - 1].Col2 + 1;
                end;
            end;
          end
        end
        else if SRow <> ARow then begin
          if (FXLSGridSelection[FXLSGridSelection.Count - 1].RangeType = rtCol) then begin
            if SRow > ARow then begin
              for i := SRow downto ARow + 1 do
                if CellInRange(FXLSGridSelection[FXLSGridSelection.Count - 1],
                     EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, ACol, i) then begin
                  Cut := true;
                  FXLSGridSelection[FXLSGridSelection.Count - 1].Row2 :=
                    FXLSGridSelection[FXLSGridSelection.Count - 1].Row2 - 1;
                end;
            end
            else begin
              for i := SRow to ARow - 1 do
                if CellInRange(FXLSGridSelection[FXLSGridSelection.Count - 1],
                     EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, ACol, i) then begin
                  Cut := true;
                  FXLSGridSelection[FXLSGridSelection.Count - 1].Row2 :=
                    FXLSGridSelection[FXLSGridSelection.Count - 1].Row2 + 1;
                end;
            end;
          end;
        end;

        if (FXLSGridSelection[FXLSGridSelection.Count - 1].Col2 = SCol) and
           (FXLSGridSelection[FXLSGridSelection.Count - 1].Row2 = SRow) then begin
          if FXLSGridSelection[FXLSGridSelection.Count - 1].Col2 = ACol then begin
            if ARow > SRow
              then SRow := SRow + 1
              else SRow := SRow - 1;
          end
          else if FXLSGridSelection[FXLSGridSelection.Count - 1].Row2 = ARow then begin
            if ACol > SCol
              then SCol := SCol + 1
              else SCol := SCol - 1;
          end;
        end;

      end;
      if not Cut then begin
        str := FXLSGridSelection.AsString;
        if SCol = ACol then begin
          if SRow > ARow then begin
            for i := SRow downto ARow do
              if not CellInRow(FXLSGridSelection, EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, ACol, i) then
                str := str + Format('[%s]%s%d;', [pcXLSFile.ActivePage.Caption, Col2Letter(ACol), i]);
          end
          else begin
            for i := SRow to ARow do
              if not CellInRow(FXLSGridSelection, EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, ACol, i) then
                str := str + Format('[%s]%s%d;', [pcXLSFile.ActivePage.Caption, Col2Letter(ACol), i]);
          end
        end
        else if SRow = ARow then begin
          if SCol > ACol then begin
            for i := SCol downto ACol do
              if not CellInRow(FXLSGridSelection, EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, i, ARow) then
                str := str + Format('[%s]%s%d;', [pcXLSFile.ActivePage.Caption, Col2Letter(i), ARow]);
          end
          else begin
            for i := SCol to ACol do
              if not CellInRow(FXLSGridSelection, EmptyStr, pcXLSFile.ActivePage.PageIndex + 1, i, ARow) then
                str := str + Format('[%s]%s%d;', [pcXLSFile.ActivePage.Caption, Col2Letter(i), ARow]);
          end
        end;
        FXLSGridSelection.AsString := str;
      end;
      XLSFillSelection;
    end
    else if IsCtrl then begin
      if not CellInRow(FXLSGridSelection, EmptyStr,
           pcXLSFile.ActivePage.PageIndex + 1, ACol, ARow) then begin
        str := FXLSGridSelection.AsString;
        str := str + Format('[%s]%s%d;', [pcXLSFile.ActivePage.Caption, Col2Letter(ACol), ARow]);
        FXLSGridSelection.AsString := str;
      end
      else begin
        RemoveCellFromRow(FXLSGridSelection, EmptyStr,
          pcXLSFile.ActivePage.PageIndex + 1, ACol, ARow);
      end;
      XLSFillSelection;
    end;
  end;
  Grid.Repaint;
end;

procedure TfmQImport3XLSEditor.XLSGridExit(Sender: TObject);
begin
  XLSFinishEditing;
end;

procedure TfmQImport3XLSEditor.XLSGridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Shift = [] then
    case Key of
      VK_RETURN: XLSApplyEditing;
      VK_ESCAPE: XLSFinishEditing;
    end
end;

procedure TfmQImport3XLSEditor.XLSStartEditing;
begin
  FXLSIsEditingGrid := true;
  lvXLSRanges.Visible := false;
  lvXLSSelection.Visible := true;
  tbXLSRanges.Visible := false;
end;

procedure TfmQImport3XLSEditor.XLSFinishEditing;
begin
  FXLSIsEditingGrid := false;
  FXLSGridSelection.Clear;
  XLSRepaintCurrentGrid;

  lvXLSSelection.Visible:= false;

  lvXLSRanges.Visible := true;
  tbXLSRanges.Visible := true;;
end;

procedure TfmQImport3XLSEditor.XLSApplyEditing;
var
  i: integer;
begin
  if FXLSGridSelection.Count = 0 then Exit;
    if not Assigned(lvXLSFields.Selected) then Exit;

  XLSDeleteSelectedRanges;

  for i := 0 to FXLSGridSelection.Count - 1 do begin
    FXLSGridSelection[i].MapRow := TMapRow(lvXLSFields.Selected.Data);
    TMapRow(lvXLSFields.ItemFocused.Data).Add(FXLSGridSelection[i]);
    with lvXLSRanges.Items.Add do begin
      Caption := FXLSGridSelection[i].AsString;
      Data := FXLSGridSelection[i];
      ImageIndex := 3;
    end;
  end;
  if (lvXLSRanges.Items.Count > 0) and
     not Assigned(lvXLSRanges.ItemFocused) then begin
    lvXLSRanges.Items[0].Focused := true;
    lvXLSRanges.Items[0].Selected := true;
  end;

  XLSFinishEditing;
end;

procedure TfmQImport3XLSEditor.XLSDeleteSelectedRanges;
var
  List: TList;
  i: integer;
begin
  if lvXLSRanges.SelCount = 0 then Exit;

  lvXLSRanges.OnChange := nil;
  try
    List := TList.Create;
    try
      for i := 0 to lvXLSRanges.Items.Count - 1 do
        if lvXLSRanges.Items[i].Selected then
          List.Add(Pointer(i));
      for i := List.Count - 1 downto 0 do begin
        TMapRow(lvXLSFields.ItemFocused.Data).Delete(Integer(List[i]));
        lvXLSRanges.Items[Integer(List[i])].Delete;
        List.Delete(i);
      end;

      if (lvXLSRanges.Items.Count > 0) and Assigned(lvXLSRanges.ItemFocused) and
         not lvXLSRanges.ItemFocused.Selected then
        lvXLSRanges.ItemFocused.Selected := true;

    finally
      List.Free;
    end;
    TuneButtons;
  finally
    lvXLSRanges.OnChange := lvXLSRangesChange;
  end;
end;

procedure TfmQImport3XLSEditor.XLSFillSelection;
var
  i: integer;
begin
//  lvXLSSelection.Items.BeginUpdate;
  try
    lvXLSSelection.Items.Clear;
    FXLSGridSelection.Optimize;
    for i := 0 to FXLSGridSelection.Count - 1 do
      with lvXLSSelection.Items.Add do begin
        Caption := FXLSGridSelection[i].AsString;
        ImageIndex := 3;
      end;
    if lvXLSSelection.Items.Count > 0 then begin
      lvXLSSelection.Items[0].Focused := true;
      lvXLSSelection.Items[0].Selected := true;
    end
  finally
  //  lvXLSSelection.Items.EndUpdate;
  end;
end;

function TfmQImport3XLSEditor.XLSGetCurrentGrid: TqiStringGrid;
var
  i: integer;
begin
  Result := nil;
  if not Assigned(pcXLSFile.ActivePage) then Exit;
  for i := 0 to pcXLSFile.ActivePage.ComponentCount - 1 do
    if (pcXLSFile.ActivePage.Components[i] is TqiStringGrid) and
       ((pcXLSFile.ActivePage.Components[i] as TqiStringGrid).Tag  = 1000) then
    begin
      Result := pcXLSFile.ActivePage.Components[i] as TqiStringGrid;
      Break;
    end;
end;

procedure TfmQImport3XLSEditor.XLSRepaintCurrentGrid;
var
  Grid: TqiStringGrid;
begin
  Grid := XLSGetCurrentGrid;
  if Assigned(Grid) then Grid.Repaint;
end;

procedure TfmQImport3XLSEditor.TuneButtons;
begin
  tbtXLSAddRange.Enabled := Assigned(lvXLSFields.ItemFocused) and
                            (FileName <> EmptyStr);
  tbtXLSEditRange.Enabled := Assigned(lvXLSFields.ItemFocused) and
                            Assigned(lvXLSRanges.ItemFocused);
  tbtXLSDelRange.Enabled := Assigned(lvXLSFields.ItemFocused) and
                           (lvXLSRanges.SelCount > 0);
  tbtXLSMoveRangeUp.Enabled := Assigned(lvXLSFields.ItemFocused) and
                              Assigned(lvXLSRanges.ItemFocused) and
                              (lvXLSRanges.ItemFocused.Index > 0);
  tbtXLSMoveRangeDown.Enabled := Assigned(lvXLSFields.ItemFocused) and
                                Assigned(lvXLSRanges.ItemFocused) and
                                (lvXLSRanges.ItemFocused.Index < lvXLSRanges.Items.Count - 1);
end;

procedure TfmQImport3XLSEditor.SetCaption;