如题,怎么实现?
解决方案 »
- cxDBNavigator 如何显示文字
- 一道小学算数题
- 【本公司因业务需要,现紧急招聘delphi程序员】
- 新手提问(2) 感谢windindance(风舞轻扬)
- WebConnection1+ADO是不是有BUG?
- 一个关于ADO的奇怪问题!ADO有BUG?
- Delphi中有关ADO的几个Bug.
- 请教一个简单问题!!!!!!!!!!!!
- 请各位大侠,帮小弟一下关于active form的问题,多谢,多谢
- 我用ADO组件做多线程数据库查询程序,怎么有时会出现这样的错误‘Connection is busy with results For another command’;
- 关于update的语句错误,请各位高手帮忙!(在线等待)
- 这样在tcpserver中接收数据为什么程序会停止响应?大家救急,在线等待。多谢了!!!
vDataSet: TDataSet): Boolean;
var
I,VL_I,j: integer;
S,SysPath: string;
MsExcel:Variant;
begin
Result:=true;
if Application.MessageBox('您确信将数据导入到Excel吗?','提示!',MB_OKCANCEL + MB_DEFBUTTON1) = IDOK then
begin
SysPath:=ExtractFilePath(application.exename);
with TStringList.Create do
try
vDataSet.First ;
S:=S+Header;
// system.Delete(s,1,1);
add(s);
s:='';
For I:=0 to vDataSet.fieldcount-1 do
begin
If vDataSet.fields[I].visible=true then
S:=S+#9+vDataSet.fields[I].displaylabel;
end;
system.Delete(s,1,1);
add(s);
while not vDataSet.Eof do
begin
S := '';
for I := 0 to vDataSet.FieldCount -1 do
begin
If vDataSet.fields[I].visible=true then
S := S + #9 + vDataSet.Fields[I].AsString;
end;
System.Delete(S, 1, 1);
Add(S);
vDataSet.Next;
end;
Try
SaveToFile(SysPath+'\Tem.xls');
Except
ShowMessage('写文件时发生保护性错误,Excel 如在运行,请先关闭!');
Result:=false;
exit;
end;
finally
Free;
end;
Try
MSExcel:=CreateOleObject('Excel.Application');
Except
ShowMessage('Excel 没有安装,请先安装!');
Result:=false;
exit;
end;
Try
MSExcel.workbooks.open(SysPath+'\Tem.xls');
Except
ShowMessage('打开临时文件时出错,请检查'+SysPath+'\Tem.xls');
Result:=false;
exit;
end;
MSExcel.visible:=True;
for VL_I :=1 to 4 do
MSExcel.Selection.Borders[VL_I].LineStyle := 0;
MSExcel.cells.select;
MSExcel.Selection.HorizontalAlignment :=3;
MSExcel.Selection.Borders[1].LineStyle := 0; MSExcel.Range['A1'].Select;
MSExcel.Selection.Font.Size :=24; J:=0 ;
for i:=0 to vdataset.fieldcount-1 do
if vDataSet.fields[I].visible then
J:=J+1; VL_I :=J;
MSExcel.Range['A1:'+F_ColumnName(VL_I)+'1'].Select;
MSExcel.Range['A1:'+F_ColumnName(VL_I)+'1'].Merge;
end
else
Result:=false;
end;
sqlText=sqlText + " group by tblowedetail.orgid ,tblowedetail.insuranceid ) is null ) then 0 else ";
sqlText=sqlText + " (select aaowe= sum(owecurtotal) from tblowedetail where tblowedetail.insuranceid in (select insuranceid from tblinsurancedetail where "+ strTemp1 +" ) and tblowedetail.orgid in (select orgid from tblorgdetail where "+ strTemp2 +" ) and tblowedetail.orgid in (select orgid from tblZoneManagerDetail where "+ strTemp3 +" ) and (datepart(yyyy,oweMonth)*12+datepart(m,oweMonth)) < " + FromMonth ;
sqlText=sqlText + " group by tblowedetail.orgid ,tblowedetail.insuranceid ) end as startowe,";
sqlText=sqlText + " owe=(case when ((select aaowe= sum(owecurtotal) from tblowedetail where tblowedetail.insuranceid in (select insuranceid from tblinsurancedetail where "+ strTemp1 +" ) and tblowedetail.orgid in (select orgid from tblorgdetail where "+ strTemp2 +" ) and tblowedetail.orgid in (select orgid from tblZoneManagerDetail where "+ strTemp3 +" ) and (datepart(yyyy,oweMonth)*12+datepart(m,oweMonth)) < " + FromMonth ;
sqlText=sqlText + " group by tblowedetail.orgid ,tblowedetail.insuranceid ) is null ) then 0 else ";
sqlText=sqlText + " (select aaowe= sum(owecurtotal) from tblowedetail where tblowedetail.insuranceid in (select insuranceid from tblinsurancedetail where "+ strTemp1 +" ) and tblowedetail.orgid in (select orgid from tblorgdetail where "+ strTemp2 +" ) and tblowedetail.orgid in (select orgid from tblZoneManagerDetail where "+ strTemp3 +" ) and (datepart(yyyy,oweMonth)*12+datepart(m,oweMonth)) < " + FromMonth ;
sqlText=sqlText + " group by tblowedetail.orgid ,tblowedetail.insuranceid ) end as startowe +sum(owecurtotal) )";
void DataSetToExcel(TDataSet* DataSet)
{
Variant excel_app; //excel applicatian对象
Variant excel_book; //book
Variant excel_sheet; //工作簿
Variant excel_range; //范围
Variant my_worksheet; //当前页
Variant my_range; //当前范围
///***************输出****************
excel_app=Variant::CreateObject("excel.application"); //创建app对象
excel_book=excel_app.OlePropertyGet("workbooks"); //创建book
excel_app.OlePropertySet("SheetsInNewWorkbook",(Variant)1);
excel_sheet=excel_book.OleFunction("add"); //添加一页
my_worksheet=excel_sheet.OlePropertyGet("ActiveSheet");
excel_app.OlePropertySet("Visible",(Variant)true); //可见
//int j(1);
PropertyGet Range("Range");
PropertySet SetValue("Value");
PropertySet SetFormula("Formula");
PropertyGet GetValue("Value");
PropertyGet GetFormula("Formula");
AnsiString mytmp;
for(int j=1;j<=DataSet->FieldCount;++j)
{
Range.ClearArgs();
SetValue.ClearArgs();
mytmp=String(char(64+j))+IntToStr(1);
//Range.ClearArgs();
// SetValue.ClearArgs();
my_range = my_worksheet.Exec(Range << mytmp );
my_range.Exec(SetValue << DataSet->Fields->Fields[j-1]->FieldName);
Range.ClearArgs();
}
DataSet->Bof;
for (int i=2;i<=DataSet->RecordCount+1;++i)
{ for(int j=1;j<=DataSet->FieldCount;++j)
{
Range.ClearArgs();
SetValue.ClearArgs();
mytmp=String(char(64+j))+IntToStr(i);
//Range.ClearArgs();
//SetValue.ClearArgs();
my_range = my_worksheet.Exec(Range << mytmp );
my_range.Exec(SetValue << DataSet->Fields->Fields[j-1]->AsString);
Range.ClearArgs();
}
DataSet->Next();
}
}
i,j :integer;
ea1 :texcelapplication;
ew1 :texcelworkbook;
ews1 :texcelworksheet;
begin
ea1 := texcelapplication.Create(self); //excelapplication1
ew1 := texcelworkbook.Create(self); //excelworkbook1
ews1 := texcelworksheet.Create(self); //excelworksheet1
ea1.Connect;
ea1.Workbooks.Add(null,0);
ew1.ConnectTo(ea1.Workbooks[1]);
ews1.ConnectTo(ew1.Sheets[1] as _worksheet);
......
AQgrsdstj.Open;
if AQgrsdstj.RecordCount > 0 then
begin
i:= 0;
while not AQgrsdstj.Eof do
begin
for j:=0 to AQgrsdstj.FieldCount-1 do
begin
ews1.Cells.Item[i+1,j+1]:= AQgrsdstj.Fields[j].AsString;
end;
i:= i+1;
AQgrsdstj.Next;
end;
end;
ew1.SaveCopyAs('C:\temp.xls');
ShowMessage('已成功导出到EXCEL!');
ea1.Free;
ew1.Free;
ews1.Free;
end;
OleServer, Excel97, DBGrids, dbtables, Dialogs, Sysutils, db, windows,
graphics, FileCtrl, classes;procedure CreateExcel(DBGrid: TDBGrid; Title: string);
procedure HandleData(Worksheet: _Worksheet; DBGrid: TDBGrid; Title: string);
function textformat(str: string; count: integer): string;implementationvar
XLApp: TExcelApplication;
//nizhigang's gbgridtoexcel .function textformat(str: string; count: integer): string;
var
mystr: string;
begin
mystr := copy(' ', 1, count
- length(str));
mystr := mystr + str;
result := mystr;
end;procedure CreateExcel(DBGrid: TDBGrid; Title: string);
var
WorkBks: WorkBooks;
Workbk: _Workbook;
WorkSheets: Sheets;
Worksheet: _WorkSheet;
begin
try
begin
try
XLApp := TExcelApplication.Create(nil);
except
ShowMessage('打开EXCEL失败,请检查系统!');
exit;
end;
XLApp.Visible[0] := True;
WorkBks := XLApp.Workbooks as WorkBooks;
WorkBks.Add(XLWBatWorkSheet, 0);
Workbk := WorkBks.Item[1];
WorkSheets := Workbk.Worksheets;
Worksheet := Worksheets.Get_Item(1) as _WorkSheet;
WorkSheet.Name := Title;
HandleData(Worksheet, DBGrid, Title);
end
finally
if xlapp <> nil then
xlapp.Free;
end;
end;procedure HandleData(Worksheet: _Worksheet; DBGrid: TDBGrid; Title: string);
var
i, j: integer;
ARange: Range;
max: integer;
begin
try
begin
WorKSheet.Cells.Item[1, 3] := Title;
WorKSheet.Cells.Item[2, 2] := '报表单位:' +
'四川成都国腾集团(GTCCL)';
for i := 1 to DBGrid.Columns.Count do
WorkSheet.Cells.Item[3, i + 1] := DBGrid.Columns.Items[i -
1].Title.caption;
dbgrid.DataSource.DataSet.First;
i := 0;
while not dbgrid.DataSource.DataSet.Eof do
begin
for j := 0 to DBGrid.Columns.Count - 1 do
case dbgrid.Fields[j].DataType of
//ftstring:
// WorkSheet.Cells.Item[4+i,j+2]:=''''+dbgrid.Fields[j].Text;
ftBytes:
WorkSheet.Cells.Item[4 + i, j + 2] := '''' +
dbgrid.Fields[j].Text;
else
WorkSheet.Cells.Item[4 + i, j + 2] :=
dbgrid.Fields[j].Text;
end;
dbgrid.DataSource.DataSet.Next;
i := i + 1;
end;
max := dbgrid.DataSource.DataSet.RecordCount;
ARange := WorkSheet.Range[WorkSheet.Cells.Item[3, 2],
WorkSheet.Cells.Item[3, DBGrid.Columns.Count + 1]];
ARange.Columns.Interior.ColorIndex := 24;
ARange := WorkSheet.Range[WorkSheet.Cells.Item[3, 2],
WorkSheet.Cells.Item[3 + Max, DBGrid.Columns.Count + 1]];
ARange.Borders.LineStyle := xlContinuous;
end
except
begin
ShowMessage('EXCEL异常,请重新启动系统!');
end;
end;
end;end.
procedure CreateExcel(DBGrid: TDBGrid; Title: string) 即可 title 是导入后的标题
小弟还用这段代码一样的原理进修打印,数据表。打印是再后台操作,慢也没理他。希望那位高闪指点小弟!