fuqimport3odteditor.pas

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

    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);
  end;
  TuneButtons;
end;

procedure TfmQImport3ODTEditor.FormDestroy(Sender: TObject);
begin
  if assigned(FODTFile) then
    FODTFile.Free;
  if assigned(FCurrentStringGrid) then
    FCurrentStringGrid := nil;
  ClearDataSheets;    
end;

procedure TfmQImport3ODTEditor.sgrODTMouseDown(Sender: TObject;
  Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
var
  ACol, ARow: integer;
  grid: TqiStringGrid;
begin
  if not (Sender is TqiStringGrid) then Exit;
  grid := Sender as TqiStringGrid;

  grid.MouseToCell(X, Y, ACol, ARow);
  if not Assigned(lvFields.Selected) then Exit;
  if ACol = 0 then Exit;
  if ODTCol = ACol
    then lvFields.Selected.SubItems[0] := EmptyStr
    else lvFields.Selected.SubItems[0] := Col2Letter(ACol);
  lvFieldsChange(lvFields, lvFields.Selected, ctState);
end;

function TfmQImport3ODTEditor.ODTCol: Integer;
begin
  Result := 0;
  if Assigned(lvFields.Selected) then
    if lvFields.Selected.SubItems[0] <> EmptyStr then
      Result := GetColIdFromColIndex(lvFields.Selected.SubItems[0]);
end;

procedure TfmQImport3ODTEditor.sgrODTDrawCell(Sender: TObject; ACol,
  ARow: Integer; Rect: TRect; State: TGridDrawState);
var
  X, Y: integer;
  grid: TqiStringGrid;
begin
  if not (Sender is TqiStringGrid) then Exit;
  grid := Sender as TqiStringGrid;

  X := Rect.Left + (Rect.Right - Rect.Left -
   grid.Canvas.TextWidth(grid.Cells[ACol, ARow])) div 2;
  Y := Rect.Top + (Rect.Bottom - Rect.Top -
    grid.Canvas.TextHeight(grid.Cells[ACol, ARow])) div 2;
  if gdFixed in State then
  begin
    if (ACol = ODTCol) and (ARow = 0)then
      grid.Canvas.Font.Style := grid.Canvas.Font.Style + [fsBold]
    else
      grid.Canvas.Font.Style := grid.Canvas.Font.Style - [fsBold];
    grid.Canvas.FillRect(Rect);
    grid.Canvas.TextOut(X - 1, Y + 1, grid.Cells[ACol, ARow]);
  end
  else begin
    grid.DefaultDrawing := false;
    grid.Canvas.Brush.Color := clWindow;
    grid.Canvas.FillRect(Rect);
    grid.Canvas.Font.Color := clWindowText;
    grid.Canvas.TextRect(Rect, Rect.Left + 2, Rect.Top + 2, grid.Cells[ACol, ARow]);

    if (ACol = ODTCol) and (ARow > 0) then
    begin
      grid.Canvas.Font.Color := clHighLightText;
      grid.Canvas.Brush.Color := clHighLight;
      Rect.Bottom := Rect.Bottom + 1;
      grid.Canvas.FillRect(Rect);
      grid.Canvas.TextOut(Rect.Left + 2, Y, grid.Cells[ACol, ARow]);
      Rect.Bottom := Rect.Bottom - 1;
    end;
  end;
  if gdFocused in State
    then DrawFocusRect(grid.Canvas.Handle, Rect);
  grid.DefaultDrawing := true;
end;

procedure TfmQImport3ODTEditor.FillMap;
var
  i, j: Integer;
begin
  for i := 0 to Import.Map.Count - 1 do
    for j := 0 to lvFields.Items.Count - 1 do
      if AnsiCompareText(lvFields.Items[j].Caption, Import.Map.Names[i]) = 0 then
      begin
        lvFields.Items[j].SubItems[0] := Import.Map.Values[Import.Map.Names[i]];
        Break;
      end;
end;

procedure TfmQImport3ODTEditor.ApplyChanges;
var
  i: Integer;
begin
  Import.Map.BeginUpdate;
  try
    Import.Map.Clear;
    for i := 0 to lvFields.Items.Count - 1 do
      if lvFields.Items[i].SubItems[0] <> EmptyStr then
        Import.Map.Values[lvFields.Items[i].Caption] :=
          lvFields.Items[i].SubItems[0];
  finally
    Import.Map.EndUpdate;
  end;
  Import.FileName := FileName;
  Import.SheetName := AnsiString(SheetName);
  Import.SkipFirstRows := SkipLines;
  if IsHeaderUsed = 0 then
    Import.UseHeader := true
  else
    Import.UseHeader := false;
end;

procedure TfmQImport3ODTEditor.tbtAutoFillClick(Sender: TObject);
var
  i: Integer;
begin
  if Assigned(FCurrentStringGrid) then
  begin
    for i := 0 to lvFields.Items.Count - 1 do
      if (i <= FCurrentStringGrid.ColCount - 2) then
        lvFields.Items[i].SubItems[0] := Col2Letter(i + 1)
      else
        lvFields.Items[i].SubItems[0] := EmptyStr;
    lvFieldsChange(lvFields, lvFields.Selected, ctState);
  end;
end;

procedure TfmQImport3ODTEditor.pcODTFileChange(Sender: TObject);
begin
    SheetName := AnsiString(pcODTFile.ActivePage.Caption);
end;

procedure TfmQImport3ODTEditor.lvFieldsChange(Sender: TObject;
  Item: TListItem; Change: TItemChange);
begin
  if not Assigned(Item) then Exit;
  if Assigned(FCurrentStringGrid) then
    FCurrentStringGrid.Repaint;
end;

procedure TfmQImport3ODTEditor.tbtClearClick(Sender: TObject);
var
  i: Integer;
begin
  for i := 0 to lvFields.Items.Count - 1 do
    lvFields.Items[i].SubItems[0] := EmptyStr;
  lvFieldsChange(lvFields, lvFields.Selected, ctState);
end;

procedure TfmQImport3ODTEditor.TuneButtons;
var
  Condition: boolean;
begin
  Condition := (lvFields.Items.Count > 0) and FileExists(FileName)
    and (pcODTFile.PageCount > 0);
  tbtAutoFill.Enabled := Condition;
  tbtClear.Enabled := Condition;
  laSkipRows.Enabled := FileExists(FileName) and (pcODTFile.PageCount > 0);
  edODTSkipRows.Enabled := FileExists(FileName) and (pcODTFile.PageCount > 0);
  cbHeaderRow.Enabled := FileExists(FileName) and (pcODTFile.PageCount > 0);
  pcODTFile.Enabled := FileExists(FileName) and (pcODTFile.PageCount > 0);
  if cbHeaderRow.Enabled then
    if IsHeaderUsed = 0 then
      cbHeaderRow.Checked := true
    else
      cbHeaderRow.Checked := false;
end;

procedure TfmQImport3ODTEditor.edODTSkipRowsChange(Sender: TObject);
begin
  SkipLines := StrToIntDef(edODTSkipRows.Text, 0);
end;

procedure TfmQImport3ODTEditor.cbHeaderRowClick(Sender: TObject);
var
  i, k, j, W, border: Integer;
  TabSheet: TTabSheet;
  StringGrid: TqiStringGrid;
  TempName: AnsiString;
  F: TForm;
  Start, Finish: TDateTime;
begin
  if not FileExists(FFileName) or (FODTFile.FileName = '') then Exit;
  if cbHeaderRow.Checked then
    IsHeaderUsed := 0
  else
    IsHeaderUsed := 1;
  border := pcODTFile.PageCount - 1;
  TempName := SheetName;
  SheetName := '';
  ClearDataSheets;
  Start := Now;
  F := ShowLoading(Self, 'Applying settings');
  try
    Application.ProcessMessages;
    for k := 0 to border 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;
      end;
    SheetName := TempName;
  finally
    Finish := Now;
    while (Finish - Start) < EncodeTime(0, 0, 0, 500) do
      Finish := Now;
    if Assigned(F) then
      F.Free;
  end;
end;

procedure TfmQImport3ODTEditor.bOkClick(Sender: TObject);
begin
  if assigned(FODTFile) then
    if assigned(FODTFile.Workbook.SpreadSheets.GetSheetByName(qiString(SheetName))) then
      if FODTFile.Workbook.SpreadSheets.GetSheetByName(qiString(SheetName)).IsComplexTable then
      begin
        if MessageDlg('Selected table has a complex structure and could be improperly converted' + #10 +
          '(it contains vertically merged cells and/or subtables).'
            + #10 + 'Do you want to convert this table anyway?' + #10 +
            'You could examine internal structure of selected table by pressing No.',
              mtInformation, [mbYes, mbNo], 0) = mrYes then
        begin
          ApplyChanges;
          Self.ModalResult := mrOk;
          exit;
        end;
        exit;
      end;
  ApplyChanges;
  Self.ModalResult := mrOk;
end;

procedure TfmQImport3ODTEditor.edFileNameChange(Sender: TObject);
begin
  if FFileName <> edFileName.Text then
  begin
    FFileName := edFileName.Text;
    FillGrid;
  end;
  TuneButtons;
end;

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

{$ENDIF}
{$ENDIF}

end.