2005/10/16 | Delphi凋用Excel 2
类别(语言类学习笔记) | 评论(0) | 阅读(119) | 发表于 16:11

procedure TRwExcel.ExportDataToExcelMTitle(paraDBGrid: TDBGridEh;
MyCaption, lefttop, middletop, righttop, leftbottom, middlebottom,
rightbottom, TaxCode: string);
var
odSaveFile : TSaveDialog;
sFileName : string; //Excel文件名
i,j,k : Integer;
temp : Widestring;
tmp : Widestring;
tmp2 : Widestring;
ExcelApp : Variant;
TitleCapLines: integer; //表头层数
titledetail : array of array of array of string;
qrysm,qrywt : TADOQuery;
begin
odSavefile:= TSaveDialog.Create(nil);
try
odSaveFile.Filter:= 'EXCEL表格文件格式(*.XLS)|*.xls';
odSaveFile.DefaultExt:= '.xls';
odSaveFile.InitialDir:= ExtractFilePath(ParamStr(0));
odSaveFile.FileName:= MyCaption + '.xls';
if odSaveFile.Execute
then sFileName:= odSaveFile.FileName
else Exit;
finally
odSaveFile.Free;
end;
Application.ProcessMessages;

try
ExcelApp := CreateOleObject('Excel.Application');
except
MyShowMsg('调用Excel程序失败，请确认本机是否安装Excel系统！',0 ,'调用失败',0);
Exit;
end;

Screen.Cursor:=crHourGlass;

try
ExcelApp.WorkBooks.Add;
ExcelApp.DisplayAlerts := False ;
ExcelApp.WorkBooks[1].WorkSheets[1].Name := '导出数据';
TitleCapLines:=1; // 确定最大的title的行数 此处为 默认为一行


tmp:=CheckColCount(paraDBgrid.VisibleColumns.count);
temp:='A1'+': '+tmp+'1'; //

ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,1].value:=MyCaption;
ExcelApp.WorkBooks[1].workSheets[1].range[temp].merge;

ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.size:=14 ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.Bold:=True ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].wraptext:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do // 确定最大的title的行数
begin
j:= CountChar(paraDBGrid.VisibleColumns[i].Title.Caption,'|');
if TitleCapLines<j then TitleCapLines:=j;
end;

setLength(titledetail,paraDBGrid.VisibleColumns.Count,TitleCapLines,3);

k:=3; //从第三行开始 数据行默认从 第三行开始
for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
for j:=0 to TitleCapLines-1 do
begin
//取标题单元格的值 以及是否需要合并的标志 这里暂且设置为空
titledetail[i,j,0]:=GetTitleNCaption(paraDBGrid.VisibleColumns[i].title.Caption,'|',j+1);
titledetail[i,j,1]:='';
titledetail[i,j,2]:='';
ExcelApp.WorkBooks[1].WorkSheets[1].cells[k+j,i+1].value:=titledetail[i,j,0];
end;
end;

for j:=0 to TitleCapLines-1 do //设置是否需要向右合并
for i:=0 to paraDBGrid.VisibleColumns.Count-1-1 do
if (titledetail[i,j,0]=titledetail[i+1,j,0]) and (trim(titledetail[i,j,0])<>'') then titledetail[i,j,1]:='right'; //right 代表向右合并一列

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do //设置是否需要向下合并
for j:=0 to TitleCapLines-1-1 do
if (titledetail[i,j,0]=titledetail[i,j+1,0]) and (trim(titledetail[i,j,0])<>'') then titledetail[i,j,2]:='down'; //down 代表向下合并一列

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do //向下合并单元格
begin
j:=3;
for k:=0 to TitleCapLines-1 do
begin
tmp:=CheckColCount(i+1);
tmp2:=CheckColCount(i+1);
temp:=tmp+intTostr(j)+': '+tmp2+intTostr(j+1);
if titledetail[i,k,2]='down' then ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge; //merge;
inc(j)
end;
end;

j:=3;
for i:=0 to TitleCapLines-1 do //向右合并单元格
begin
for k:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
tmp:=CheckColCount(k+1);
tmp2:=CheckColCount(k+2);
temp:=tmp+intTostr(j)+': '+tmp2+intTostr(j);
if titledetail[k,i,1]='right' then ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge; //merge;
end;
inc(j);
end;

paraDBGrid.DataSource.DataSet.DisableControls;

//导出数据部分

Qrysm:=TADOQuery.Create(nil);
qrywt:=TADOQuery.Create(nil);
qrysm.Connection:=dmCmn_DM_Dsr.DsrConnect;
qrywt.Connection:=dmCmn_DM_Dsr.DsrConnect;
MyOpenExecSQL(qrysm,format('select * from %s ',[Gettablename(31)]),0);
MyOpenExecSQL(qrywt,format('select * from %s ',[Gettablename(108)]),0);
try
i:=0;
k:=3+TitleCapLines;
paraDBGrid.DataSource.DataSet.First;
while not paraDBGrid.DataSource.DataSet.eof do //读取表格数据 并写入 Excel
begin
for j := 0 to paraDBGrid.VisibleColumns.Count-1 do
begin
if UpperCase(Trim(paraDbGrid.VisibleColumns.Items[j].FieldName))='STATE' then
begin
if paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).AsInteger=Pc_State_Qd
then ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:='确定'
else ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:='取消';
end else
if UpperCase(Trim(paraDbGrid.VisibleColumns.Items[j].FieldName))='计税依据' then
begin //个人所得税
if paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).AsCurrency >0
then ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).Asstring
else ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=0;
end else //其他所得
if (UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='PROCODE') then
begin //违法事项
if qrywt.Locate('事项代码;TaxCode',VarArrayOf([paraDBGrid.VisibleColumns.Items[j].Field.value,self.TaxCode]),[]) then
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=qrywt.FieldByName('问题事项').AsString;
end else //其他所得
if (UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='TIMCODE') then
begin
if qrysm.Locate('TimCode;TaxCode',VarArrayOf([paraDBGrid.VisibleColumns.Items[j].Field.value,self.TaxCode]),[]) then
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=qrysm.FieldByName('Timname').AsString;
end else // 违规费用
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).Asstring;
end;
Inc(i);
paraDBGrid.DataSource.DataSet.Next;
end;
finally
qrysm.Free;
qrywt.Free;
end;


for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
case paraDBGrid.VisibleColumns[i].Footer.valueType of
fvtNon: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.value;
fvtSum: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.Sumvalue;
fvtAvg: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.Sumvalue;
fvtCount: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.Sumvalue;
fvtFieldvalue: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.value;
fvtStaticText: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.value;
end;
tmp:=CheckColCount(paraDBGrid.VisibleColumns.Count);
temp:=chr(ord('A'))+intTostr(3)+': '+tmp+intTostr(3+TitleCapLines-1+paraDBGrid.DataSource.DataSet.recordcount+1); //增加了一行合计行
for i:=1 to 4 do
begin
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].Weight:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].LineStyle:=1;
end;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.size:=9 ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].wraptext:=True;

tmp:=checkColCount(paraDBGrid.VisibleColumns.Count);
temp:=chr(ord('A'))+intTostr(3)+': '+tmp+intTostr(3+TitleCapLines-1);
for i:=1 to 4 do
begin
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].Weight:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].LineStyle:=1;
end;

ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.size:=12 ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].wraptext:=True;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,1]:=lefttop;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,paraDBGrid.VisibleColumns.Count]:=righttop;
if paraDBGrid.VisibleColumns.Count>=7 then //>=6 也可以 //合并 右边单元格
begin
tmp:=CheckColCount(paraDBGrid.VisibleColumns.Count-1);
tmp2:=CheckColCount(paraDBGrid.VisibleColumns.Count);
temp:=tmp+intTostr(2)+': '+tmp2+intTostr(2);
ExcelApp.WorkBooks[1].WorkSheets[1].Range[temp].merge;
end;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4,1]:=leftbottom;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4,paraDBGrid.VisibleColumns.Count]:=rightbottom;

if paraDBGrid.VisibleColumns.Count>=7 then //>=6 也可以 //合并 右边单元格
begin
tmp:=CheckColCount(paraDBGrid.VisibleColumns.Count-1);
tmp2:=CheckColCount(paraDBGrid.VisibleColumns.Count);
temp:=tmp+intTostr(paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4)+': '+tmp2+intTostr(paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4);
ExcelApp.WorkBooks[1].WorkSheets[1].Range[temp].merge;
end;

if paraDBGrid.VisibleColumns.Count>=3 then
begin
if (paraDBGrid.VisibleColumns.Count mod 2)=0 then
begin

i:=paraDBGrid.VisibleColumns.Count;
i:=Trunc(i/2);

ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i]:=middletop;
tmp :=checkColCount(i);
tmp2 :=CheckColCount(i+1);
temp :=tmp+intTostr(2)+': '+tmp2+intTostr(2);
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+TitleCapLines+paraDBGrid.datasource.DataSet.RecordCount,i]:=middlebottom;
tmp :=CheckColCount(i);
tmp2 :=CheckColCount(i+1);
temp:=tmp+intTostr(4+TitleCapLines+paraDBGrid.datasource.DataSet.RecordCount)+': '+tmp2+intTostr(paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4);
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;

end
else
begin
i:=paraDBGrid.VisibleColumns.Count;
i:=Trunc(i/2)+1;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i]:=middletop;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i].horizontalalignment:=3;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+titleCapLines+paraDBGrid.DataSource.DataSet.RecordCount,i]:=middlebottom;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+titleCapLines+paraDBGrid.DataSource.DataSet.RecordCount,i].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+titleCapLines+paraDBGrid.DataSource.DataSet.RecordCount,i].horizontalalignment:=3;

end;
end;

ExcelApp.Workbooks[1].SaveAs(sFileName); //保存

paraDBGrid.DataSource.DataSet.EnableControls;

myShowMsg('数据导出已成功完成！', 0,'',0);

finally
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
Screen.Cursor:=crDefault;
end;
end;

0

评论Comments

日志分类
首页[289]
教程习题[21]
数据库学习笔记[60]
语言类学习笔记[75]
网页类学习笔记[93]
其他相关笔记[40]