Delphi 使用笔记

1、 Q：ACCESS “数据库无可更新的查询”   

A：数据库为只读

 

2、 cxGrid明细只显示一行

主表：KeyFieldName  iVID 

从表：DetailKeyFieldName  iVID

     MasterKeyFieldName  iVID

     KeyFieldName  Iid       (从表主键)

 

3、 conLinkMdb = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Jet OLEDB:Database Password=gasfp123'; //带密码 ACCESS

 

  conLinkMdb = 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=%s;Persist Security Info=True;Jet OLEDB:Database Password=gasfp123'; //连数据库没问题,但若是作为压缩数据时的连接串时如有红色标注部分则会报”多步OLE DB操作错误……”

 

 conLinkDbf = 'Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceType=DBF;SourceDB=%s';

 

4、压缩ACCESS数据库，conLinkMdb即为3.中的ConLinkMdb

function TDM.CompactMdb(sTempFileName, sFileName: string): Boolean;

var

 JE:OleVariant;

begin

 

 if FileExists( sTempFileName ) then

   DeleteFile(pchar(sTempFileName));

 try

   JE := CreateOleObject('JRO.JetEngine');  // DAO.DBEngine.36

   JE.CompactDatabase(format(conLinkMdb,[sFileName]),format(conLinkMdb,[sTempFileName]));

   if DeleteFile(pchar(sFileName)) then

     RenameFile(sTempFileName, sFileName);

 

 except

   result := false;

 end;

end;

 

5.获取显示的值:

cxtxtedCustName.Text := tvPayList.DataController.Controller.FocusedRecord.DisplayTexts[0];

 

6.直接将CXGRID明细表导出到EXCEL(兼顾过滤情况)

procedure TfrmMain.ToolButton4Click(Sender: TObject);
const
  xlNone = $FFFFEFD2;
  xlAutomatic = $FFFFEFF7;
  xlSolid = $00000001;

  xlInsideHorizontal = $0000000C;
  xlInsideVertical = $0000000B;
  xlDiagonalDown = $00000005;
  xlDiagonalUp = $00000006;
  xlEdgeBottom = $00000009;
  xlEdgeLeft = $00000007;
  xlEdgeRight = $0000000A;
  xlEdgeTop = $00000008;

  xlContinuous = $00000001;
  xlDash = $FFFFEFED;
  xlDashDot = $00000004;
  xlDashDotDot = $00000005;
  xlDot = $FFFFEFEA;
  xlDouble = $FFFFEFE9;
  xlSlantDashDot = $0000000D;
  xlLineStyleNone = $FFFFEFD2;

  xlHairline = $00000001;
  xlMedium = $FFFFEFD6;
  xlThick = $00000004;
  xlThin = $00000002;

  xlCenter = $FFFFEFF4;
  xlBottom = $FFFFEFF5;
  xlLeft = $FFFFEFDD;
  xlRight = $FFFFEFC8;
  xlTop = $FFFFEFC0;
var
  FileName, s: String;
  xlApp, xlWorkbook, xlWorkSheet, xlRange: OleVariant;
  i, j, k,RowIndex, MasterRecordIndex, TitleRows: Integer;
  TableView, MainView, DetailView: TcxGridDBTableView;
  DetailVisible: Boolean;
  RangeBorders: array of Cardinal;
begin

  if Self.tvInv.DataController.RowCount = 0 then
  begin
    s := '没有导出数据...' ;
    Application.MessageBox(PChar(s), PChar(Application.Title),MB_OK or MB_ICONERROR);
    Exit;
  end;

  SaveDlgExport.Filter := 'Excel文件 (*.xls)';
  SaveDlgExport.Title := '导出为';

  if not SaveDlgExport.Execute then
    Exit;
 
  FileName := SaveDlgExport.FileName;
  Application.ProcessMessages;
 
  try
    xlApp := CreateOleObject('Excel.Application');
  except
    Application.MessageBox('无法创建Excel文件, 请确认是否安装了Excel软件',
     PChar(Application.Title), MB_OK + MB_ICONWarning);
    Exit;
  end;
  Screen.Cursor := crHourGlass;
  Application.ProcessMessages;

  xlApp.Visible := False;
  xlApp.DisplayAlerts := False;
  xlApp.ScreenUpdating := False;

  xlWorkBook := xlApp.WorkBooks.Add;
  xlWorkSheet := xlWorkBook.WorkSheets[1];

  if rbtnUnInv.Checked then
    s := rbtnUnInv.Caption
  else if rbtnInved.Checked then
    s := rbtnInved.Caption
  else if rbtnAll.Checked then
    s := rbtnAll.Caption;
  xlWorkSheet.Name := '发票数据(' + s + ')';

 

  TitleRows := 3;
  RowIndex := TitleRows + 1;
  xlWorkSheet.Rows[RowIndex].Font.Name := '宋体';
  xlWorkSheet.Rows[RowIndex].Font.Bold := True;
  xlWorkSheet.Rows[RowIndex].Font.Size := 10;
  xlWorkSheet.Rows[RowIndex].WrapText := True;

  //设置标题行
//  xlWorkSheet.Cells[RowIndex, 1].AutoFilter;
  xlWorkSheet.Cells[3, 1].Value := '起止日期：'  + datetostr(cxdtedFrom.Date) + ' -- '  +  datetostr(cxdtedTO.Date);
  tvInv.DataController.GotoFirst;
//  for k := 0 to tvInv.DataController.RecordCount - 1 do
  for k := 0 to tvInv.DataController.RowCount - 1 do
  begin
//    tvInv.DataController.RecNo := K + 1;
//    tvInv.DataController.FocusedRowIndex := k + 1;
    TableView := TcxGridDBTableView(Self.gridInv.FocusedView);

    if TableView.IsMaster then
    begin
      MainView := TableView;
      MasterRecordIndex := MainView.DataController.FocusedRecordIndex;
      with MainView.DataController do
        DetailView := TcxGridDBTableView(TcxGridDataController(GetDetailDataController(FocusedRecordIndex, 0)).GridView);
    end
    else
    begin
      DetailView := TableView;
      MainView := TcxGridDBTableView(TcxGridDataController(DetailView.DataController.GetMasterDataController).GridView);
      MasterRecordIndex := DetailView.DataController.GetMasterRecordIndex;
    end;

    if k = 0 then
      for i := 0 to DetailView.VisibleColumnCount - 1 do
        xlWorkSheet.Cells[4, i + 1].Value := VarToStr(DetailView.VisibleColumns[i].Caption);

    Inc(RowIndex);
    with MainView.DataController do
    begin
      xlWorkSheet.Cells[RowIndex, 1].Value := VarToStr(Values[MasterRecordIndex, GetItemByFieldName('FBillNo').Index]);
      xlWorkSheet.Cells[RowIndex, 2].Value := VarToStr(Values[MasterRecordIndex, GetItemByFieldName('KFMC').Index]);
      xlWorkSheet.Cells[RowIndex, 3].Value := VarToStr(Values[MasterRecordIndex, GetItemByFieldName('DJRQ').Index]);
    end;

    for i := 0 to DetailView.DataController.RecordCount - 1 do
    begin
     Inc(RowIndex);
     xlWorkSheet.Rows[RowIndex].Font.Name := '宋体';
     xlWorkSheet.Rows[RowIndex].Font.Size := 10;

     for j := 0 to DetailView.VisibleColumnCount - 1 do
       xlWorkSheet.Cells[RowIndex, j + 1].Value := VarToStr(DetailView.DataController.Values[i, DetailView.VisibleColumns[j].Index]);
    end;

    tvInv.DataController.GotoNext;
  end;

  for j := 0 to DetailView.VisibleColumnCount - 1 do
   xlWorkSheet.Columns[j + 1].EntireColumn.AutoFit;

  RowIndex := 1;
  xlWorkSheet.Rows[RowIndex].Font.Name := '宋体';
  xlWorkSheet.Rows[RowIndex].Font.Bold := True;
  xlWorkSheet.Rows[RowIndex].Font.Size := 18;

  xlRange := xlWorkSheet.Range[xlWorkSheet.Cells[RowIndex, 1], xlWorkSheet.Cells[RowIndex, DetailView.VisibleColumnCount]];
  xlRange.HorizontalAlignment := xlCenter;
  xlRange.VerticalAlignment := xlCenter;
  xlRange.ShrinkToFit := False;
  xlRange.MergeCells := True;
  xlWorkSheet.Cells[RowIndex, 1].Value := '数据清单';

  Inc(RowIndex);
  xlWorkSheet.Rows[RowIndex].Font.Name := '宋体';
  xlWorkSheet.Rows[RowIndex].Font.Size := 9;

  xlApp.ActiveWindow.SplitRow := TitleRows + 1;
  //xlApp.ActiveWindow.SplitColumn = 1
  xlApp.ActiveWindow.FreezePanes := True;
  xlApp.ActiveWindow.DisplayGridlines := False;

  xlRange := xlWorkSheet.Range[xlWorkSheet.Cells[TitleRows + 1, 1],
   xlWorkSheet.Cells[TitleRows + 1 + DetailView.DataController.RecordCount, DetailView.VisibleColumnCount]];
  xlRange.Borders[xlDiagonalDown].LineStyle := xlNone;
  xlRange.Borders[xlDiagonalUp].LineStyle := xlNone;
  SetLength(RangeBorders, 6);
  RangeBorders[0] := xlEdgeBottom;
  RangeBorders[1] := xlEdgeLeft;
  RangeBorders[2] := xlEdgeRight;
  RangeBorders[3] := xlEdgeTop;
  RangeBorders[4] := xlInsideHorizontal;
  RangeBorders[5] := xlInsideVertical;

  xlWorkBook.SaveAs(FileName);
  xlWorkBook.Close;
  xlWorkBook := Unassigned;
  xlWorkSheet := Unassigned;
  xlRange := Unassigned;
  xlApp.Quit;
  xlApp := Unassigned;
  Application.ProcessMessages;

  Screen.Cursor := crDefault;
  Application.ProcessMessages;

   s := '导出完成...';

  Application.MessageBox(PChar(s), PChar(Application.Title),
   MB_OK or MB_IconInformation);

end;