cxGrid 明细表数据导出Excel 例子

最新推荐文章于 2024-06-07 01:11:49 发布

zxkid

最新推荐文章于 2024-06-07 01:11:49 发布

阅读量5.6k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.csdn.net/zxkid/article/details/2197117

Delphi 下的QuantumGrid控件不支持主从表结构的直接从表数据导出，目前因为需求做了一个从表数据导出的功能，仅作参考，代码如下：

procedure TFormScheduleQuery.cxButtonExportClick(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, RowIndex, MasterRecordIndex, TitleRows: Integer;

TableView, MainView, DetailView: TcxGridDBTableView;

DetailVisible: Boolean;

RangeBorders: array of Cardinal;

begin

//导出

if Self.tvWorkCenterSchedule.DataController.RecordCount = 0 then

begin

if Language = Chinese then

s := '没有导出数据...'

else

s := 'No data to export!';

Application.MessageBox(PChar(s), PChar(Application.Title),

MB_OK or MB_ICONERROR);

Exit;

end;

if Language = Chinese then

begin

FormMain.SaveDialogExport.Filter := 'Excel文件 (*.xls)';

FormMain.SaveDialogExport.Title := '导出为';

end

else

begin

FormMain.SaveDialogExport.Filter := 'Excel File (*.xls)';

FormMain.SaveDialogExport.Title := 'Export As';

end;

if not FormMain.SaveDialogExport.Execute then

Exit;

FileName := FormMain.SaveDialogExport.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];

xlWorkSheet.Name := 'WorkCenter Plan';

TableView := TcxGridDBTableView(Self.cxGrid1.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;

TitleRows := 3;

RowIndex := TitleRows + 1;

xlWorkSheet.Rows[RowIndex].Font.Name := '宋体';

xlWorkSheet.Rows[RowIndex].Font.Bold := True;

xlWorkSheet.Rows[RowIndex].Font.Size := 9;

xlWorkSheet.Rows[RowIndex].WrapText := True;

for i := 0 to DetailView.VisibleColumnCount - 1 do

xlWorkSheet.Cells[RowIndex, i + 1].Value := VarToStr(DetailView.VisibleColumns[i].Caption);

//设置标题行

xlWorkSheet.Cells[RowIndex, 1].AutoFilter;

xlRange := xlWorkSheet.Range[xlWorkSheet.Cells[RowIndex, 1], xlWorkSheet.Cells[RowIndex, DetailView.VisibleColumnCount]];

xlRange.Interior.ColorIndex := 37;

xlRange.Interior.Pattern := xlSolid;

xlRange.Interior.PatternColorIndex := xlAutomatic;

//设置列格式

xlWorkSheet.Columns[DetailView.GetColumnByFieldName('OrderID').VisibleIndex + 1].NumberFormatLocal := '@';

xlWorkSheet.Columns[DetailView.GetColumnByFieldName('ItemID').VisibleIndex + 1].NumberFormatLocal := '@';

xlWorkSheet.Columns[DetailView.GetColumnByFieldName('StartDate').VisibleIndex + 1].NumberFormatLocal := 'yyyy-mm-dd hh:mm;@'; //yyyy-m-d h:mm;@

xlWorkSheet.Columns[DetailView.GetColumnByFieldName('StopDate').VisibleIndex + 1].NumberFormatLocal := 'yyyy-mm-dd hh:mm;@';

for i := 0 to DetailView.DataController.RecordCount - 1 do

begin

Inc(RowIndex);

xlWorkSheet.Rows[RowIndex].Font.Name := '宋体';

xlWorkSheet.Rows[RowIndex].Font.Size := 9;

for j := 0 to DetailView.VisibleColumnCount - 1 do

xlWorkSheet.Cells[RowIndex, j + 1].Value := VarToStr(DetailView.DataController.Values[i, DetailView.VisibleColumns[j].Index]);

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 := 'WorkCenter Plan';

Inc(RowIndex);

xlWorkSheet.Rows[RowIndex].Font.Name := '宋体';

xlWorkSheet.Rows[RowIndex].Font.Size := 9;

xlRange := xlWorkSheet.Range[xlWorkSheet.Cells[RowIndex, 1], xlWorkSheet.Cells[RowIndex, DetailView.VisibleColumnCount]];

xlRange.HorizontalAlignment := xlRight;

xlRange.VerticalAlignment := xlCenter;

xlRange.ShrinkToFit := False;

xlRange.MergeCells := True;

with MainView.DataController do

xlWorkSheet.Cells[RowIndex, 1].Value := '计划日期: ' + VarToStr(Values[MasterRecordIndex, GetItemByFieldName('StartDate').Index])

+ ' 到 ' + VarToStr(Values[MasterRecordIndex, GetItemByFieldName('StopDate').Index]);

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;

for i := low(RangeBorders) to high(RangeBorders) do

begin

xlRange.Borders[RangeBorders[i]].LineStyle := xlContinuous;

xlRange.Borders[RangeBorders[i]].Weight := xlThin;

xlRange.Borders[RangeBorders[i]].ColorIndex := xlAutomatic;

end;

xlWorkBook.SaveAs(FileName);

xlWorkBook.Close;

xlWorkBook := Unassigned;

xlWorkSheet := Unassigned;

xlRange := Unassigned;

xlApp.Quit;

xlApp := Unassigned;

Application.ProcessMessages;

Screen.Cursor := crDefault;

Application.ProcessMessages;

if Language = Chinese then

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

else

s := 'Export Succeed!';

Application.MessageBox(PChar(s), PChar(Application.Title),

MB_OK or MB_IconInformation);

end;