第一个问是怎样汇总
我的表是这样的
w1 w2 w4 w5 w6 w7 w8
001 a b c d e f
002 a b d e g k
003 a c d e f g
004 c e f g h
我想汇出这样的结果:
当W2=a时,W4=b的数据个数,并且W4=b的个数占所有W4不等于空的个数的百分比。
第二个问题是把结果如何自动的生成按excel表。
要生成的表式是这样的,包括括号的内容
表1-1.....
总计(一共有多少条数据) w2选择a的个数 W2选择a时w4选择b的个数 w4占所有W4不等于空的个数的百分比
4 3 2 66.66%
我的表是这样的
w1 w2 w4 w5 w6 w7 w8
001 a b c d e f
002 a b d e g k
003 a c d e f g
004 c e f g h
我想汇出这样的结果:
当W2=a时,W4=b的数据个数,并且W4=b的个数占所有W4不等于空的个数的百分比。
第二个问题是把结果如何自动的生成按excel表。
要生成的表式是这样的,包括括号的内容
表1-1.....
总计(一共有多少条数据) w2选择a的个数 W2选择a时w4选择b的个数 w4占所有W4不等于空的个数的百分比
4 3 2 66.66%
select count(*) as cou1 from db
select count(*) as cou2 from db where w2=''a''
select count(*) as cou3 from db where w2=''a'' and w4=''b''
select count(*) as cou4 from db where w4=''b'' 通过cou4/cou1得出比率
var
Template:OleVariant;
begin
try
Self.ExcelApplication1:=TExcelApplication.Create(self);
Self.ExcelApplication1.Connect;
except
messagebox(application.Handle,'无法生成Excel报表,请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);
exit;
end;
Self.ExcelApplication1.Visible[0]:=False;
self.ExcelApplication1.DisplayAlerts[0]:=False;
Self.ExcelApplication1.Caption:='统计报表';
Self.ExcelApplication1.Workbooks.Add(EmptyParam,0);
Self.ExcelWorkbook1.ConnectTo(self.ExcelApplication1.Workbooks[1]);
self.ExcelWorksheet1:=TExcelWorkSheet.Create(self);
self.ExcelWorksheet1.ConnectTo(self.ExcelWorkbook1.Worksheets[1] as _worksheet); self.ExcelWorksheet1.Range['A1','A1'].columnwidth:=length('总计(一共有多少条数据)');
self.ExcelWorksheet1.Range['A1','A1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,1].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,1].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[2,1]:='总计(一共有多少条数据)';
self.ExcelWorksheet1.Cells.Item[3,1].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,1]:='4'; self.ExcelWorksheet1.Cells.Item[2,2].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,2].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Range['B1','B1'].columnwidth:=length('w2选择a的个数');
self.ExcelWorksheet1.Range['B1','B1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,2]:='w2选择a的个数';
self.ExcelWorksheet1.Cells.Item[3,2].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,2]:='3'; self.ExcelWorksheet1.Cells.Item[2,3].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,3].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Range['C1','C1'].columnwidth:=length('W2选择a时w4选择b的个数');
self.ExcelWorksheet1.Range['C1','C1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,3]:='W2选择a时w4选择b的个数';
self.ExcelWorksheet1.Cells.Item[3,3].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,3]:='2'; self.ExcelWorksheet1.Cells.Item[2,4].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,4].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Range['D1','D1'].columnwidth:=length('w4占所有W4不等于空的个数的百分比');
self.ExcelWorksheet1.Range['D1','D1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,4]:='w4占所有W4不等于空的个数的百分比';
self.ExcelWorksheet1.Cells.Item[3,4].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,4]:='66.66%';
self.ExcelWorksheet1.SaveAs('f:\统计报表.xls');
self.ExcelWorksheet1.Disconnect;
self.ExcelWorkbook1.Disconnect;
self.ExcelApplication1.Disconnect;
self.teminate_excel;
end;
procedure TForm1.teminate_excel;
var
lppe: TProcessEntry32;
found : boolean;
Hand : THandle;
hh:hwnd;
s:string;
begin
Hand := CreateToolhelp32Snapshot(TH32CS_SNAPALL,0);
found := Process32First(Hand,lppe);
while found do
begin
s:=strpas(lppe.szExeFile);
if uppercase(s)='EXCEL.EXE' then
begin
hh:=openprocess(PROCESS_ALL_ACCESS,true,lppe.th32ProcessID);
terminateprocess(hh,0); //中止进程
exit;
end;
found := Process32Next(Hand,lppe);
end;
end;
unit Unit1;interface
到
end.
谢谢
unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Excel2000, OleServer, StdCtrls, ComCtrls,TLHelp32, DB, ADODB;type
TForm1 = class(TForm)
Button1: TButton;
ExcelWorksheet1: TExcelWorksheet;
ExcelApplication1: TExcelApplication;
ExcelWorkbook1: TExcelWorkbook;
TreeView1: TTreeView;
Button2: TButton;
Label1: TLabel;
Button3: TButton;
Memo1: TMemo;
Memo2: TMemo;
Button4: TButton;
Button5: TButton;
ListBox1: TListBox;
OpenDialog1: TOpenDialog;
Button6: TButton;
Button7: TButton;
Label2: TLabel;
Label3: TLabel;
ADOQuery1: TADOQuery;
ADOConnection1: TADOConnection;
Button8: TButton;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure Memo2Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
private
{ Private declarations }
procedure onminsize(var msg:TMessage);message WM_syscommand;
public
{ Public declarations }
procedure teminate_excel;
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
var
Template:OleVariant;
cou1,cou2,cou3,cou4:Integer;
r:string;
begin
self.ADOQuery1.Close;
self.ADOQuery1.Connection:=Self.ADOConnection1;
self.ADOQuery1.SQL.Clear;
self.ADOQuery1.SQL.Add('select count(*) as cou1 from db');
self.ADOQuery1.Open;
cou1:=self.ADOQuery1.FieldByname('cou1').AsInteger; self.ADOQuery1.Close;
self.ADOQuery1.Connection:=Self.ADOConnection1;
self.ADOQuery1.SQL.Clear;
self.ADOQuery1.SQL.Add('select count(*) as cou2 from db where w2=''a''');
self.ADOQuery1.Open;
cou2:=self.ADOQuery1.FieldByname('cou2').AsInteger; self.ADOQuery1.Close;
self.ADOQuery1.Connection:=Self.ADOConnection1;
self.ADOQuery1.SQL.Clear;
self.ADOQuery1.SQL.Add('select count(*) as cou3 from db where w2=''a'' and w4=''b''');
self.ADOQuery1.Open;
cou3:=self.ADOQuery1.FieldByname('cou3').AsInteger; self.ADOQuery1.Close;
self.ADOQuery1.Connection:=Self.ADOConnection1;
self.ADOQuery1.SQL.Clear;
self.ADOQuery1.SQL.Add('select count(*) as cou4 from db where w4<>''''');
self.ADOQuery1.Open;
cou4:=self.ADOQuery1.FieldByname('cou4').AsInteger;
{cou1:=4;
cou2:=2;
cou3:=3;
cou4:=3;} r:=FormatFloat('0.00',(cou2/cou3)*100)+'%'; try
Self.ExcelApplication1:=TExcelApplication.Create(self);
Self.ExcelApplication1.Connect;
except
messagebox(application.Handle,'无法生成Excel报表,请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);
exit;
end;
Self.ExcelApplication1.Visible[0]:=False;
self.ExcelApplication1.DisplayAlerts[0]:=False;
Self.ExcelApplication1.Caption:='统计报表';
Self.ExcelApplication1.Workbooks.Add(EmptyParam,0);
Self.ExcelWorkbook1.ConnectTo(self.ExcelApplication1.Workbooks[1]);
self.ExcelWorksheet1:=TExcelWorkSheet.Create(self);
self.ExcelWorksheet1.ConnectTo(self.ExcelWorkbook1.Worksheets[1] as _worksheet); self.ExcelWorksheet1.Range['A1','A1'].columnwidth:=length('总计(一共有多少条数据)');
self.ExcelWorksheet1.Range['A1','A1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,1].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,1].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[2,1]:='总计(一共有多少条数据)';
self.ExcelWorksheet1.Cells.Item[3,1].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,1]:=inttostr(cou1); self.ExcelWorksheet1.Cells.Item[2,2].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,2].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Range['B1','B1'].columnwidth:=length('w2选择a的个数');
self.ExcelWorksheet1.Range['B1','B1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,2]:='w2选择a的个数';
self.ExcelWorksheet1.Cells.Item[3,2].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,2]:=inttostr(cou2); self.ExcelWorksheet1.Cells.Item[2,3].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,3].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Range['C1','C1'].columnwidth:=length('W2选择a时w4选择b的个数');
self.ExcelWorksheet1.Range['C1','C1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,3]:='W2选择a时w4选择b的个数';
self.ExcelWorksheet1.Cells.Item[3,3].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,3]:=inttostr(cou3); self.ExcelWorksheet1.Cells.Item[2,4].Interior.colorindex:=15;
self.ExcelWorksheet1.Cells.Item[2,4].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Range['D1','D1'].columnwidth:=length('w4占所有W4不等于空的个数的百分比');
self.ExcelWorksheet1.Range['D1','D1'].WrapText:=true;
self.ExcelWorksheet1.Cells.Item[2,4]:='w4占所有W4不等于空的个数的百分比';
self.ExcelWorksheet1.Cells.Item[3,4].BorderAround(xlcontinuous,xlthin,xlautomatic,clblack);
self.ExcelWorksheet1.Cells.Item[3,4]:=r;
self.ExcelWorksheet1.SaveAs('f:\统计报表.xls');
self.ExcelWorksheet1.Disconnect;
self.ExcelWorkbook1.Disconnect;
self.ExcelApplication1.Disconnect;
self.teminate_excel;
end;
procedure TForm1.teminate_excel;
var
lppe: TProcessEntry32;
found : boolean;
Hand : THandle;
hh:hwnd;
s:string;
begin
Hand := CreateToolhelp32Snapshot(TH32CS_SNAPALL,0);
found := Process32First(Hand,lppe);
while found do
begin
s:=strpas(lppe.szExeFile);
if uppercase(s)='EXCEL.EXE' then
begin
hh:=openprocess(PROCESS_ALL_ACCESS,true,lppe.th32ProcessID);
terminateprocess(hh,0); //中止进程
exit;
end;
found := Process32Next(Hand,lppe);
end;
end;end.
应该是
r:=FormatFloat('0.00',(cou2/cou4)*100)+'%';
比如我想让d1、d2和d3合并
cells.item[1,4]:-'123'
这个公试中,如果分母为‘0’怎么办?