fuqimport3xlseditor.pas

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

begin
end;

procedure TfmQImport3XLSEditor.SetFileName(const Value: string);
begin
  if AnsiCompareText(FFileName, Trim(Value)) <> 0 then
  begin
    FFileName := Trim(Value);
    edFileName.Text := FFileName;
    ClearDataSheets;
    if FileExists(FFileName) then
      FillGrid;
    SetCaption;
  end;
  SetEnabledControls;
end;

procedure TfmQImport3XLSEditor.edFileNameChange(Sender: TObject);
begin
  FileName := edFileName.Text;
end;

procedure TfmQImport3XLSEditor.FormDestroy(Sender: TObject);
begin
  FXLSDefinedRanges.Free; 
  FXLSGridSelection.Free;
  FXLSFile.Free;
end;

procedure TfmQImport3XLSEditor.ApplyChanges;
var
  i: integer;
  str: string;
begin
  FImport.Map.BeginUpdate;
  try
    FImport.Map.Clear;
    for i := 0 to lvXLSFields.Items.Count - 1 do begin
      str := TMapRow(lvXLSFields.Items[i].Data).AsString;
      if str <> EmptyStr then
        FImport.Map.Values[lvXLSFields.Items[i].Caption] := str;
    end;
  finally
    FImport.Map.EndUpdate;
  end;
  FImport.FileName := edFileName.Text;
  FImport.SkipFirstRows := SkipFirstRows;
  FImport.SkipFirstCols := SkipFirstCols;
end;

procedure TfmQImport3XLSEditor.bBrowseClick(Sender: TObject);
begin
  odFileName.FileName := FFileName;
  if odFileName.Execute then FileName := odFileName.FileName;
end;

procedure TfmQImport3XLSEditor.SetSkipFirstRows(const Value: integer);
begin
  if FSkipFirstRows <> Value then begin
    FSkipFirstRows := Value;
    edXLSSkipRows.Text := IntToStr(FSkipFirstRows);
    XLSRepaintCurrentGrid;
  end;
end;

procedure TfmQImport3XLSEditor.SetSkipFirstCols(const Value: integer);
begin
  if FSkipFirstCols <> Value then begin
    FSkipFirstCols := Value;
    edXLSSkipCols.Text := IntToStr(FSkipFirstCols);
    XLSRepaintCurrentGrid;
  end;
end;

procedure TfmQImport3XLSEditor.SetEnabledControls;
var
  Condition: boolean;
begin
  Condition := (lvXLSFields.Items.Count > 0) and (FileName <> EmptyStr);
  edXLSSkipRows.Enabled := Condition;
  laXLSSkipRows_01.Enabled := Condition;
  laXLSSkipRows_02.Enabled := Condition;
  edXLSSkipCols.Enabled := Condition;
  laXLSSkipCols_01.Enabled := Condition;
  laXLSSkipCols_02.Enabled := Condition;
  tbtXLSAutoFillCols.Enabled := Condition;
  tbtXLSAutoFillRows.Enabled := Condition;
  tbtXLSClearFieldRanges.Enabled := Condition;
  tbtXLSClearAllRanges.Enabled := Condition;
  tbtXLSAddRange.Enabled := Condition;
  tbtXLSEditRange.Enabled := Assigned(lvXLSFields.ItemFocused) and
                             Assigned(lvXLSRanges.ItemFocused) and
                             (FileName <> EmptyStr);
  tbtXLSDelRange.Enabled := Assigned(lvXLSFields.ItemFocused) and
                            (lvXLSRanges.SelCount > 0) and
                            (FileName <> EmptyStr);
  tbtXLSMoveRangeUp.Enabled := Assigned(lvXLSFields.ItemFocused) and
                               Assigned(lvXLSRanges.ItemFocused) and
                               (lvXLSRanges.ItemFocused.Index > 0) and
                               (FileName <> EmptyStr);
  tbtXLSMoveRangeDown.Enabled := Assigned(lvXLSFields.ItemFocused) and
                                 Assigned(lvXLSRanges.ItemFocused) and
                                 (lvXLSRanges.ItemFocused.Index < lvXLSRanges.Items.Count - 1) and
                                 (FileName <> EmptyStr);
  pcXLSFile.Enabled := Condition;
end;

procedure TfmQImport3XLSEditor.FormCreate(Sender: TObject);
begin
  FXLSFile := TXLSFile.Create;
  FXLSIsEditingGrid := false;
  FXLSGridSelection := TMapRow.Create(nil);
  FXLSDefinedRanges := TMapRow.Create(nil);
end;

procedure TfmQImport3XLSEditor.FormShow(Sender: TObject);
begin
  Caption := Import.Name + ' - Component Editor';
end;

procedure TfmQImport3XLSEditor.tbtXLSAutoFillColsClick(Sender: TObject);
var
  i, j: integer;
  MapRow: TMapRow;
  MR: TMapRange;
begin
  j := pcXLSFile.ActivePage.TabIndex;

  for i := 0 to lvXLSFields.Items.Count - 1 do begin
    MapRow := TMapRow(lvXLSFields.Items[i].Data);
    MapRow.Clear;
    if i <= FXLSFile.Workbook.WorkSheets[j].ColCount - 1 then begin
      MR := TMapRange.Create(MapRow);
      MR.Col1 := FXLSFile.Workbook.WorkSheets[j].Cols[i].ColNumber + 1;
      MR.Col2 := MR.Col1;
      MR.Row1 := 0;
      MR.Row2 := 0;
      MR.SheetIDType := sitName;
      MR.SheetName := FXLSFile.Workbook.WorkSheets[j].Name;
      MR.SheetNumber := 0;
      MR.Direction := rdDown;
      MapRow.Add(MR);
    end;
  end;
  if Assigned(lvXLSFields.ItemFocused) then
    lvXLSFieldsChange(lvXLSFields, lvXLSFields.ItemFocused, ctState);
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.tbtXLSAutoFillRowsClick(Sender: TObject);
var
  i, j: integer;
  MapRow: TMapRow;
  MR: TMapRange;
begin
  j := pcXLSFile.ActivePage.TabIndex;

  for i := 0 to lvXLSFields.Items.Count - 1 do begin
    MapRow := TMapRow(lvXLSFields.Items[i].Data);
    MapRow.Clear;
    if i <= FXLSFile.Workbook.WorkSheets[j].RowCount - 1 then begin
      MR := TMapRange.Create(MapRow);
      MR.Row1 := FXLSFile.Workbook.WorkSheets[j].Rows[i].RowNumber + 1;
      MR.Row2 := MR.Row1;
      MR.Col1 := 0;
      MR.Col2 := 0;
      MR.SheetIDType := sitName;
      MR.SheetName := FXLSFile.Workbook.WorkSheets[j].Name;
      MR.SheetNumber := 0;
      MR.Direction := rdDown;
      MapRow.Add(MR);
    end;
  end;

  if Assigned(lvXLSFields.ItemFocused) then
    lvXLSFieldsChange(lvXLSFields, lvXLSFields.ItemFocused, ctState);
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.lvXLSFieldsChange(Sender: TObject;
  Item: TListItem; Change: TItemChange);
var
  i: integer;
  Row: TMapRow;
begin
  if not Assigned(Item) or not Assigned(Item.Data) then Exit;
//  lvXLSRanges.Items.BeginUpdate;
  try
    lvXLSRanges.Items.Clear;
    Row := TMapRow(Item.Data);
    for i := 0 to Row.Count - 1 do
      with lvXLSRanges.Items.Add do begin
        Caption := Row[i].AsString;
        ImageIndex := 3;
        Data := Row[i];
      end;
    if lvXLSRanges.Items.Count > 0 then begin
      lvXLSRanges.Items[0].Focused := true;
      lvXLSRanges.Items[0].Selected := true;
    end
    else lvXLSRangesChange(lvXLSRanges, nil, ctState);

    XLSRepaintCurrentGrid;
  finally
  //  lvXLSRanges.Items.EndUpdate;
  end;
  TuneButtons;
end;

{procedure TfmQImportXLSEditor.lvXLSFieldsSelectItem(Sender: TObject;
  Item: TListItem; Selected: Boolean);
var
  i: integer;
  Row: TMapRow;
begin
  if not Assigned(Item) or not Assigned(Item.Data) then Exit;
//  lvXLSRanges.Items.BeginUpdate;
  try
    lvXLSRanges.Items.Clear;
    Row := TMapRow(Item.Data);
    for i := 0 to Row.Count - 1 do
      with lvXLSRanges.Items.Add do begin
        Caption := Row[i].AsString;
        ImageIndex := 3;
        Data := Row[i];
      end;
    if lvXLSRanges.Items.Count > 0 then begin
      lvXLSRanges.Items[0].Focused := true;
      lvXLSRanges.Items[0].Selected := true;
    end
    else lvXLSRangesSelectItem(lvXLSRanges, nil, false);

    XLSRepaintCurrentGrid;
  finally
  //  lvXLSRanges.Items.EndUpdate;
  end;
  TuneButtons;
end;}

procedure TfmQImport3XLSEditor.tbtXLSAddRangeClick(Sender: TObject);
var
  Range: TMapRange;
  MapRow: TMapRow;
  i: integer;
  Item: TListItem;
begin
  MapRow := TMapRow(lvXLSFields.ItemFocused.Data);
  Range := TMapRange.Create(MapRow);
  Range.Col1 := 1;
  Range.Row1 := 0;
  Range.Col2 := 1;
  Range.Row2 := 0;
  Range.Direction := rdDown;
  Range.SheetIDType := sitName;
  Range.SheetName := pcXLSFile.ActivePage.Caption;
  Range.SheetNumber := pcXLSFile.ActivePage.PageIndex + 1;
  Range.Update;

  if EditRange(Range, FXLSFile) then begin
    MapRow.Add(Range);
    lvXLSRanges.Items.BeginUpdate;
    try
      Item := lvXLSRanges.Items.Add;
      with Item do begin
        Caption := Range.AsString;
        ImageIndex := 3;
        Data := Range;
      end;
      for i := 0 to lvXLSRanges.Items.Count - 1 do begin
        lvXLSRanges.Items[i].Focused := lvXLSRanges.Items[i] = Item;
        lvXLSRanges.Items[i].Selected := lvXLSRanges.Items[i] = Item;
      end;
    finally
      lvXLSRanges.Items.EndUpdate;
    end;
  end
  else Range.Free;
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.tbtXLSEditRangeClick(Sender: TObject);
begin
  if not Assigned(lvXLSFields.ItemFocused) and
     not Assigned(lvXLSRanges.ItemFocused) then Exit;

  if EditRange(TMapRange(lvXLSRanges.ItemFocused.Data), FXLSFile) then
    lvXLSRanges.ItemFocused.Caption := TMapRange(lvXLSRanges.ItemFocused.Data).AsString;
  XLSRepaintCurrentGrid;
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.lvXLSRangesChange(Sender: TObject;
  Item: TListItem; Change: TItemChange);
begin
  XLSRepaintCurrentGrid;
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.tbtXLSMoveRangeUpClick(Sender: TObject);
var
  Index, i: integer;
begin
  Index := lvXLSRanges.ItemFocused.Index;
  TMapRow(lvXLSFields.ItemFocused.Data).Exchange(Index, Index - 1);
  lvXLSRanges.Items.BeginUpdate;
  try
    lvXLSRanges.Items[Index].Data := TMapRow(lvXLSFields.ItemFocused.Data)[Index];
    lvXLSRanges.Items[Index].Caption := TMapRange(lvXLSRanges.Items[Index].Data).AsString;
    lvXLSRanges.Items[Index - 1].Data := TMapRow(lvXLSFields.ItemFocused.Data)[Index - 1];
    lvXLSRanges.Items[Index - 1].Caption := TMapRange(lvXLSRanges.Items[Index - 1].Data).AsString;
    for i := 0 to lvXLSRanges.Items.Count - 1 do begin
      lvXLSRanges.Items[i].Focused := lvXLSRanges.Items[i] = lvXLSRanges.Items[Index - 1];
      lvXLSRanges.Items[i].Selected := lvXLSRanges.Items[i] = lvXLSRanges.Items[Index - 1];
    end;
  finally
    lvXLSRanges.Items.EndUpdate;
  end;
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.tbtXLSMoveRangeDownClick(Sender: TObject);
var
  Index, i: integer;
begin
  lvXLSRanges.Visible := false;
  lvXLSRanges.Visible := true;
  //Exit;
  
  Index := lvXLSRanges.ItemFocused.Index;
  TMapRow(lvXLSFields.ItemFocused.Data).Exchange(Index, Index + 1);
  lvXLSRanges.Items.BeginUpdate;
  try
    lvXLSRanges.Items[Index].Data := TMapRow(lvXLSFields.ItemFocused.Data)[Index];
    lvXLSRanges.Items[Index].Caption := TMapRange(lvXLSRanges.Items[Index].Data).AsString;
    lvXLSRanges.Items[Index + 1].Data := TMapRow(lvXLSFields.ItemFocused.Data)[Index + 1];
    lvXLSRanges.Items[Index + 1].Caption := TMapRange(lvXLSRanges.Items[Index + 1].Data).AsString;
    for i := 0 to lvXLSRanges.Items.Count - 1 do begin
      lvXLSRanges.Items[i].Focused := lvXLSRanges.Items[i] = lvXLSRanges.Items[Index + 1];
      lvXLSRanges.Items[i].Selected := lvXLSRanges.Items[i] = lvXLSRanges.Items[Index + 1];
    end;
  finally
    lvXLSRanges.Items.EndUpdate;
  end;
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.tbtXLSDelRangeClick(Sender: TObject);
begin
  XLSDeleteSelectedRanges;
  XLSRepaintCurrentGrid;
end;

procedure TfmQImport3XLSEditor.tbtXLSClearFieldRangesClick(Sender: TObject);
begin
  if not (Assigned(lvXLSFields.ItemFocused) and
          Assigned(lvXLSFields.ItemFocused.Data)) then Exit;
  TMapRow(lvXLSFields.ItemFocused.Data).Clear;
  lvXLSFieldsChange(lvXLSFields, lvXLSFields.ItemFocused, ctState);
end;

procedure TfmQImport3XLSEditor.lvXLSRangesDblClick(Sender: TObject);
begin
  if tbtXLSEditRange.Enabled then
    tbtXLSEditRange.Click;
end;

procedure TfmQImport3XLSEditor.tbtXLSClearAllRangesClick(Sender: TObject);
var
  i: integer;
begin
  for i := 0 to lvXLSFields.Items.Count - 1 do
    TMapRow(lvXLSFields.Items[i].Data).Clear;
  lvXLSFieldsChange(lvXLSFields, lvXLSFields.ItemFocused, ctState);
  TuneButtons;
end;

procedure TfmQImport3XLSEditor.edXLSSkipColsChange(Sender: TObject);
begin
  SkipFirstCols := StrToIntDef(edXLSSkipCols.Text, 0);
end;

procedure TfmQImport3XLSEditor.edXLSSkipRowsChange(Sender: TObject);
begin
  SkipFirstRows := StrToIntDef(edXLSSkipRows.Text, 0);
end;

procedure TfmQImport3XLSEditor.lvXLSFieldsEnter(Sender: TObject);
begin
  XLSRepaintCurrentGrid;
end;

procedure TfmQImport3XLSEditor.lvXLSFieldsExit(Sender: TObject);
begin
  XLSRepaintCurrentGrid;
end;

end.