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;