delphi 导出EXCEL XLSX

最新推荐文章于 2024-10-19 11:18:46 发布

zxf668

最新推荐文章于 2024-10-19 11:18:46 发布

阅读量940

点赞数 8

文章标签： excel 人工智能大数据

本文链接：https://blog.csdn.net/zxf668/article/details/135267619

版权

uses comobj;

procedure TFrm_AllRecord.EXCEL1Click(Sender: TObject);
var
xlApp, xlBook, xlSheet, xlQuery: Variant;
str1,str2,str3,fn,p_sconn:string;
i,j,k,m,n,i2,j2,k2,m2,n2:Integer;
begin
//Dbgrid2excel.DBGridToExcel(dbgrid1);

if qry1.RecordCount >0 then
begin

dlgSave1.Filter:= 'Excel文件(*.xlsx)|*.xlsx';
dlgSave1.DefaultExt :='.xlsx';
if dlgSave1.Execute then
begin
fn:=dlgSave1.FileName ;
xlApp := CreateOleObject('Excel.Application');
xlBook := xlApp.Workbooks.Add;
xlSheet := xlBook.Worksheets['sheet1'];
xlApp.Visible := false;

//nall:=Adoquery5.RecordCount; //保存记录的数量
//row1:=0; //初始化行，定位在第一行
xlSheet.Cells.item[1, 1] := dbgrd1.Columns[0].Title.Caption ;
xlSheet.Cells.item[1, 2] := dbgrd1.Columns[1].Title.Caption ;
xlSheet.Cells.item[1, 3] := dbgrd1.Columns[2].Title.Caption ;
xlSheet.Cells.item[1, 4] := dbgrd1.Columns[3].Title.Caption;
xlSheet.Cells.item[1, 5] := dbgrd1.Columns[4].Title.Caption;
xlSheet.Cells.item[1, 6] := dbgrd1.Columns[5].Title.Caption;
xlSheet.Cells.item[1, 7] := dbgrd1.Columns[6].Title.Caption;
xlSheet.Cells.item[1, 8] := dbgrd1.Columns[7].Title.Caption;
xlSheet.Cells.item[1, 9] := dbgrd1.Columns[8].Title.Caption;
xlSheet.Cells.item[1, 10] := dbgrd1.Columns[9].Title.Caption;
xlSheet.Cells.item[1, 11] := dbgrd1.Columns[10].Title.Caption;
xlSheet.Cells.item[1, 12] := dbgrd1.Columns[11].Title.Caption;
xlSheet.Cells.item[1, 13] := dbgrd1.Columns[12].Title.Caption;
xlSheet.Cells.item[1, 14] := dbgrd1.Columns[13].Title.Caption;
xlSheet.Cells.item[1, 15] := dbgrd1.Columns[14].Title.Caption;
xlSheet.Cells.item[1, 16] := dbgrd1.Columns[15].Title.Caption;
xlSheet.Cells.item[1, 17] := dbgrd1.Columns[16].Title.Caption;
xlSheet.Cells.item[1, 18] := dbgrd1.Columns[17].Title.Caption;
xlSheet.Cells.item[1, 19] := dbgrd1.Columns[18].Title.Caption;
xlSheet.Cells.item[1, 20] := dbgrd1.Columns[19].Title.Caption;
xlSheet.Cells.item[1, 21] := dbgrd1.Columns[20].Title.Caption;
xlSheet.Cells.item[1, 22] := dbgrd1.Columns[21].Title.Caption;
xlSheet.Cells.item[1, 23] := dbgrd1.Columns[22].Title.Caption;
xlSheet.Cells.item[1, 24] := dbgrd1.Columns[23].Title.Caption;
xlSheet.Cells.item[1, 25] := dbgrd1.Columns[24].Title.Caption;
xlSheet.Cells.item[1, 26] := dbgrd1.Columns[25].Title.Caption;
xlSheet.Cells.item[1, 27] := dbgrd1.Columns[26].Title.Caption;

xlQuery := xlSheet.QueryTables.Add(qry1.Recordset ,xlSheet.Range['A2']);
xlQuery.FieldNames := false;
xlQuery.RowNumbers := False;
xlQuery.FillAdjacentFormulas := False;
xlQuery.PreserveFormatting := True;
xlQuery.RefreshOnFileOpen := False;
xlQuery.BackgroundQuery := True;
//xlQuery.RefreshStyle := xlOverwriteCells;
xlQuery.SavePassword := True;
xlQuery.SaveData := True;
xlQuery.AdjustColumnWidth := True;
xlQuery.RefreshPeriod := 0;
xlQuery.PreserveColumnInfo := True;
xlQuery.Refresh;
xlApp.DisplayAlerts := False; //取消报警
//xlSheet.range['B2:B8'].Merge; //合并单元格
//xlSheet.range[xlSheet.Cells.item[2,4],xlSheet.Cells.item[4,4]].Merge;
//xlSheet.range[xlSheet.Cells.item[5,4],xlSheet.Cells.item[7,4]].Merge;

//清除不必要的字符
str1:=datetimetostr(now());
str1:=stringreplace(str1,'-','',[rfReplaceAll,rfIgnoreCase]);
str1:=stringreplace(str1,':','',[rfReplaceAll,rfIgnoreCase]);
str1:=stringreplace(str1,' ','',[rfReplaceAll,rfIgnoreCase]);
//str_memo:= stringreplace(str_memo,'-','',[rfReplaceAll,rfIgnoreCase]);
//str2:=str3 + '\' + batch + '_' + trim(dbcb2.Text ) + '_' + trim(edit1.Text ) + trim(edit3.Text ) + '_' + str1;

//以下是导出完毕后的动作，

xlBook.SavecopyAs(fn); //加后缀名，确保最后是EXCEL文件
xlBook.Close(false);
//xlApp.Disconnect; //断开与EXCEL的连接
Screen.Cursor:=crDefault; //指针随记录而动

ShowMessage('导出完毕');
end;
end
else
begin
ShowMessage('没有数据');
end;

end;