function ExportExcel(DBGridEh1: TDBGridEh;aTitle: string): Boolean;
var
Book: IXLSWorkbook;
ws: IXLSWorksheet;
iSel,iCol,iCol1, iRow: Integer;
SaveDialog1: TSaveDialog;
function GetDbgridEHColumnsCount: Integer;
var
i: Integer;
begin
Result := 0;
for i := 0 to DBGridEh1.Columns.Count - 1 do
begin
if DBGridEh1.Columns.Items[i].Visible then
begin
Inc(Result);
end;
end;
end;
begin
SaveDialog1 := TSaveDialog.Create(nil);
SaveDialog1.Filter := 'Excel文件(*.xls)|*.xls';
try
DBGridEh1.DataSource.DataSet.DisableControls;
iSel := DBGridEh1.DataSource.DataSet.RecNo;
if SaveDialog1.Execute then
begin
try
Book := TXLSWorkbook.Create;
ws := Book.Sheets.Add;
ws.Name := '导出数据';
iCol := GetDbgridEHColumnsCount;
iRow := DBGridEh1.DataSource.DataSet.RecordCount;
//单据头
with ws.RCRange[2, 2, 2, iCol + 1] do
begin
Interior.ColorIndex := 9;
Font.ColorIndex := 2;
VerticalAlignment := xlVAlignCenter;
HorizontalAlignment := xlHAlignCenter;
RowHeight := 22;
end;
//表格样式
with ws.RCRange[2, 2,iRow+2, iCol +1] do
begin
Borders.Color := 9;
end;
//标题
with ws.RCRange[1, 2, 1, iCol+ 1] do
begin
Merge(false);
HorizontalAlignment := xlHAlignCenter;
VerticalAlignment := xlVAlignCenter;
RowHeight := 30;
Font.Bold := True;
Font.Size := Font.Size + 3;
end;
with DBGridEh1 do
begin
iCol1 := 0;
for iCol := 0 to DBGridEh1.Columns.Count - 1 do
begin
if DBGridEh1.Columns.Items[iCol].Visible then
begin
ws.Cells.Item[2, iCol1+2].Value := DBGridEh1.Columns.Items[iCol].Title.Caption;
ws.RCRange[2,iCol1+2,2,iCol1 + 2].ColumnWidth := Trunc(DBGridEh1.Columns.Items[iCol].Width* 0.1433);
Inc(iCol1);
end;
end;
end;
iCol := 0;
DBGridEh1.DataSource.DataSet.First;
with DBGridEh1.DataSource.DataSet do
begin
for iRow := 0 to RecordCount - 1 do
begin
iCol1 := 0;
for iCol := 0 to DBGridEh1.Columns.Count - 1 do
begin
if DBGridEh1.Columns.Items[iCol].Visible then
begin
ws.Cells.Item[iRow+3, iCol1+2].Value := FieldByName(DBGridEh1.Columns.Items[iCol].FieldName).Text;
Inc(iCol1);
end;
end;
Next;
end;
end;
ws.Cells.Item[1, 2].Value := aTitle;
Book.SaveAs(SaveDialog1.FileName + '.xls');
Book := nil;
if MessageBox(0,'导出成功,是否要打开此文档?','询问',MB_YESNO + MB_ICONQUESTION) = IDYES then
begin
ShellExecute(0,'open',PAnsiChar(SaveDialog1.FileName + '.xls'),nil,nil,SW_NORMAL);
end;
except
end;
end;
finally
DBGridEh1.DataSource.DataSet.RecNo := iSel;
DBGridEh1.DataSource.DataSet.EnableControls;
FreeAndNil(SaveDialog1);
end;
end;
function ExportExcel(aAdoquery: TADOQuery;aTitle: string): Boolean;
var
Book: IXLSWorkbook;
ws: IXLSWorksheet;
iCol, iRow: Integer;
SaveDialog1: TSaveDialog;
begin
SaveDialog1 := TSaveDialog.Create(nil);
SaveDialog1.Filter := 'Excel文件(*.xls)|*.xls';
try
if SaveDialog1.Execute then
begin
Result := False;
try
Book := TXLSWorkbook.Create;
ws := Book.Sheets.Add;
ws.Name := '导出数据';
iCol := aAdoquery.FieldCount;
iRow := aAdoquery.RecordCount;
//单据头
with ws.RCRange[2, 2, 2, iCol + 1] do
begin
Interior.ColorIndex := 9;
Font.ColorIndex := 2;
VerticalAlignment := xlVAlignCenter;
HorizontalAlignment := xlHAlignCenter;
RowHeight := 22;
end;
//表格样式
with ws.RCRange[2, 2,iRow+2, iCol +1] do
begin
Borders.Color := 9;
end;
//标题
with ws.RCRange[1, 2, 1, iCol+ 1] do
begin
Merge(false);
HorizontalAlignment := xlHAlignCenter;
VerticalAlignment := xlVAlignCenter;
RowHeight := 30;
Font.Bold := True;
Font.Size := Font.Size + 3;
end;
with aAdoquery do
begin
for iCol := 0 to FieldCount - 1 do
begin
ws.Cells.Item[2, iCol+2].Value := Fields[iCol].DisplayName;
// ws.RCRange[2,icol+2,2,iCol + 2].ColumnWidth := Fields[iCol].DisplayWidth;
end;
end;
with aAdoquery do
begin
for iRow := 0 to RecordCount - 1 do
begin
for iCol := 0 to FieldCount - 1 do
begin
ws.Cells.Item[iRow+3, iCol+2].Value :=Fields[iCol].Text;
end;
Next;
end;
end;
ws.Cells.Item[1, 2].Value := aTitle;
Book.SaveAs(SaveDialog1.FileName + '.xls');
Book := nil;
Result := True;
if MessageBox(0,'导出成功,是否要打开此文档?','询问',MB_YESNO + MB_ICONQUESTION) = IDYES then
begin
ShellExecute(0,'open',PAnsiChar(SaveDialog1.FileName + '.xls'),nil,nil,SW_NORMAL);
end;
except
end;
end;
finally
FreeAndNil(SaveDialog1);
end;
end;