fuqimport3odteditor.pas

EMS Advanced Import Component Suite 允许你把数据从文件导入数据库中

unit fuQImport3ODTEditor;

{$I VerCtrl.inc}

interface

{$IFDEF ODT}
{$IFDEF VCL6}

uses
  {$IFDEF QI_UNICODE} EmsWideStringGrid, {$ENDIF}
  Windows, QImport3ODT, QImport3Common, QImport3StrTypes,
  Messages, SysUtils, {$IFDEF VCL6}Variants, {$ENDIF}Classes, Graphics,
  Controls, Forms, Dialogs, ComCtrls, Buttons, StdCtrls{$IFDEF VCL4},
  ImgList{$ENDIF}, ToolWin, Grids, BaseDocumentFile, XLSUtils3, ExtCtrls;

type
  TfmQImport3ODTEditor = class(TForm)
    pcODTFile: TPageControl;
    lvFields: TListView;
    ToolBar: TToolBar;
    tbtAutoFill: TToolButton;
    tbtClear: TToolButton;
    odFileName: TOpenDialog;
    ilODT: TImageList;
    edODTSkipRows: TEdit;
    laSkipRows: TLabel;
    cbHeaderRow: TCheckBox;
    Panel1: TPanel;
    Bevel1: TBevel;
    laFileName: TLabel;
    edFileName: TEdit;
    bBrowse: TSpeedButton;
    Panel2: TPanel;
    Bevel2: TBevel;
    bCancel: TButton;
    bOk: TButton;
    procedure bBrowseClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure tbtAutoFillClick(Sender: TObject);
    procedure pcODTFileChange(Sender: TObject);
    procedure lvFieldsChange(Sender: TObject; Item: TListItem;
      Change: TItemChange);
    procedure tbtClearClick(Sender: TObject);
    procedure edODTSkipRowsChange(Sender: TObject);
    procedure cbHeaderRowClick(Sender: TObject);
    procedure bOkClick(Sender: TObject);
    procedure edFileNameChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    FSkipLines: Integer;
    FFileName: String;
    FImport: TQImport3ODT;
    FODTFile: TODTFile;
    FSheetName: AnsiString;
    FCurrentStringGrid: TqiStringGrid;
    FIsHeaderUsed: Integer;

    procedure ApplyChanges;
    procedure ClearDataSheets;
    procedure FillFieldList;
    procedure FillGrid;
    procedure FillMap;
    procedure sgrODTMouseDown(Sender: TObject;
      Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
    procedure sgrODTDrawCell(Sender: TObject; ACol,
      ARow: Integer; Rect: TRect; State: TGridDrawState);
    procedure SetFileName(const Value: string);
    procedure SetSheetName(const Value: AnsiString);
    procedure SetSkipLines(const Value: Integer);
    procedure TuneButtons;

    function ODTCol: Integer;
  public
    property Import: TQImport3ODT read FImport write FImport;
    property FileName: String read FFileName write SetFileName;
    property SkipLines: Integer read FSkipLines write SetSkipLines;
    property SheetName: AnsiString read FSheetName write SetSheetName;
    property IsHeaderUsed: Integer read FIsHeaderUsed write FIsHeaderUsed;
  end;

var
  fmQImport3ODTEditor: TfmQImport3ODTEditor;

function RunQImportODTEditor(AImport: TQImport3ODT): boolean;

{$ENDIF}
{$ENDIF}

implementation

{$IFDEF ODT}
{$IFDEF VCL6}

uses
  fuQImport3Loading;

{$R *.dfm}

function RunQImportODTEditor(AImport: TQImport3ODT): boolean;
begin
  with TfmQImport3ODTEditor.Create(nil) do
  try
    Import := AImport;
    SkipLines := AImport.SkipFirstRows;
    if AImport.UseHeader then
      IsHeaderUsed := 0
    else
      IsHeaderUsed := 1;
    if (FileName = AImport.FileName) then
    begin
     FillFieldList;
     FillMap;
    end;
    FileName := AImport.FileName;
    SheetName := AImport.SheetName;
    
    Result := ShowModal = mrOk;
  finally
    Free;
  end;
end;

{ TfmQImport3ODTEditor }

procedure TfmQImport3ODTEditor.FillFieldList;
var
  i: Integer;

  procedure ClearFieldList;
  begin
     lvFields.Items.Clear;
  end;

begin
  if not QImportDestinationAssigned(False, FImport.ImportDestination,
      FImport.DataSet, FImport.DBGrid, FImport.ListView, FImport.StringGrid)
    then Exit;
  ClearFieldList;
  for i := 0 to QImportDestinationColCount(False, FImport.ImportDestination,
    FImport.DataSet, FImport.DBGrid, FImport.ListView, FImport.StringGrid) - 1 do
    with lvFields.Items.Add do
    begin
      Caption := QImportDestinationColName(False, FImport.ImportDestination,
        FImport.DataSet, FImport.DBGrid, FImport.ListView, FImport.StringGrid,
        FImport.GridCaptionRow, i);
      SubItems.Add(EmptyStr);
      ImageIndex := 0;
    end;
    if lvFields.Items.Count > 0 then
    begin
      lvFields.Items[0].Focused := True;
      lvFields.Items[0].Selected := True;
    end;
end;

procedure TfmQImport3ODTEditor.SetFileName(const Value: string);
begin
  if FFileName <> Value then
  begin
    FFileName := Value;
    edFileName.Text := FFileName;
    FillFieldList;
    FillGrid;
    FillMap;
   end;
  TuneButtons;
end;

procedure TfmQImport3ODTEditor.SetSheetName(const Value: AnsiString);
var
  i: Integer;
begin
  if FSheetName <> Value then
  begin
    FSheetName := Value;
     for i := 0 to pcODTFile.PageCount - 1 do
      if pcODTFile.Pages[i].Caption = string(Value) then
      begin
        pcODTFile.ActivePage := pcODTFile.Pages[i];
        Break;
      end;
    if assigned(pcODTFile.ActivePage) then
      for i := 0 to pcODTFile.ActivePage.ComponentCount - 1 do
        if pcODTFile.ActivePage.Components[i].Tag = pcODTFile.ActivePage.Tag then
          FCurrentStringGrid := TqiStringGrid(pcODTFile.ActivePage.Components[i]);
  end;
end;

procedure TfmQImport3ODTEditor.SetSkipLines(const Value: Integer);
begin
  if FSkipLines <> Value then
  begin
    FSkipLines := Value;
    edODTSkipRows.Text := IntToStr(FSkipLines);
  end;
  if (edODTSkipRows.Text = EmptyStr) then
    edODTSkipRows.Text := IntToStr(FSkipLines);
end;

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

procedure TfmQImport3ODTEditor.ClearDataSheets;
var
  i: integer;
begin
  for i := pcODTFile.PageCount - 1 downto 0 do
    pcODTFile.Pages[i].Free;
end;

procedure TfmQImport3ODTEditor.FillGrid;
var
  TabSheet: TTabSheet;
  StringGrid: TqiStringGrid;
  k, i, j: integer;
  F: TForm;
  Start, Finish: TDateTime;
  W: integer;
begin
  if assigned(FODTFile) then
    FODTFile.Free;
  FODTFile := TODTFile.Create;
  if assigned(FCurrentStringGrid) then
    FCurrentStringGrid := nil;
  ClearDataSheets;
  SheetName := '';
  if not FileExists(FFileName) then Exit;
  FODTFile.FileName := FFileName;
  Start := Now;
  F := ShowLoading(Self, FFileName);
  try
    Application.ProcessMessages;
    FODTFile.Load;

    for k := 0 to FODTFile.Workbook.SpreadSheets.Count - 1 do
    begin
      TabSheet := TTabSheet.Create(pcODTFile);
      TabSheet.PageControl := pcODTFile;
      TabSheet.Caption := string(FODTFile.Workbook.SpreadSheets[k].Name);
      TabSheet.Tag := k;

      StringGrid :=
        TqiStringGrid.Create(TabSheet);
      StringGrid.Parent := TabSheet;
      StringGrid.Align := alClient;
      StringGrid.ColCount := 257;
      StringGrid.RowCount := 257;
      StringGrid.FixedCols := 1;
      StringGrid.FixedRows := 1;
      StringGrid.Tag := k;
      StringGrid.DefaultColWidth := 64;
      StringGrid.DefaultRowHeight := 16;
      StringGrid.ColWidths[0] := 30;
      StringGrid.Options := StringGrid.Options - [goRangeSelect];
      GridFillFixedCells(StringGrid);
      if IsHeaderUsed = 0 then
        for i := 0 to StringGrid.ColCount - 1 do
          StringGrid.Cells[i, 0] := EmptyStr;
      StringGrid.OnDrawCell := sgrODTDrawCell;
      StringGrid.OnMouseDown := sgrODTMouseDown;
      for i := 0 to FODTFile.Workbook.SpreadSheets[k].DataCells.RowCount - 1 do
        for j := 0 to FODTFile.Workbook.SpreadSheets[k].DataCells.ColCount - 1 do
        begin
        StringGrid.Cells[j + 1 , i + 1 * IsHeaderUsed] :=
          FODTFile.Workbook.SpreadSheets[k].DataCells.Cells[j, i];
        W := StringGrid.Canvas.TextWidth(StringGrid.Cells[j + 1, i + 1 * IsHeaderUsed]);
        if W + 10 > StringGrid.ColWidths[j + 1] then
          if W + 10 < 130 then
            StringGrid.ColWidths[j + 1] := W + 10
          else
            StringGrid.ColWidths[j + 1] := 130;
        end;

       if (k = 0) and (SheetName = '') then
       begin
         SheetName := AnsiString(TabSheet.Caption);
       end;
    end;
  finally
    Finish := Now;
    while (Finish - Start) < EncodeTime(0, 0, 0, 500) do
      Finish := Now;
    if Assigned(F) then
      F.Free;
    TuneButtons;
  end;
end;


procedure TfmQImport3ODTEditor.FormCreate(Sender: TObject);
var
  TabSheet: TTabSheet;
  StringGrid: TqiStringGrid;
  k: Integer;
begin
  FODTFile := TODTFile.Create;
  for k := 0 to 2 do
  begin
    TabSheet := TTabSheet.Create(pcODTFile);
    TabSheet.PageControl := pcODTFile;
    TabSheet.Caption := 'Table' + IntToStr(k + 1);