C++ Builder 将数据库数据导出到Excel中总结2

void __fastcall TFrmDispatchDataExport::DisplayExcelReport(String _title, String _strSql)
{
 int i=0;
 Variant ExcelApp1,WorkBook1,Sheet1;
 AnsiString FileName=GetCurrentDir();
 FileName=FileName+"\\Template\\DispatchReport.xlt";
 // run excel
 try
 {
  ExcelApp1=CreateOleObject("Excel.Application"); //启动Excel
 }
 catch(...)
 {
  ShowMessage("Can not start excel");
  return;
 }

 // open InOrderTo.xlt
 ExcelApp1.OlePropertySet("Visible",(Variant)false);
 ExcelApp1.OlePropertyGet("WorkBooks").OleFunction("Add",(WideString)FileName.c_str());
 WorkBook1=ExcelApp1.OlePropertyGet("ActiveWorkBook");
 Sheet1=WorkBook1.OlePropertyGet("ActiveSheet");
 String Str_Title="";
 Str_Title=Str_Title+"                        "+_title;

 // print Inordermst info
 Sheet1.OlePropertyGet("Cells",1,6).OlePropertySet("Value",(WideString)Str_Title);
// Sheet1.OlePropertyGet("Cells",3,7).OlePropertySet("Value",FrmMain->UserParameters->GetOperatorID().c_str());
// Sheet1.OlePropertyGet("Cells",3,9).OlePropertySet("Value",FrmMain->GetSysDateTime().DateTimeString().c_str());

 // print InOrderdtl info
 qryExport->Close();
 qryExport->SQL->Clear();
 qryExport->SQL->Add(_strSql);
 qryExport->Open();
 qryExport->First();

 int nPos = 0;
 int nStep  = 0;
 int nRecordCnt = qryExport->RecordCount;
 if ( nRecordCnt > 0 )
 {
  nStep = ceil(nRecordCnt/100.0);
  pbExport->StepBy(1);
 }
 statOARBar->Panels->Items[1]->Text = IntToStr(nRecordCnt) ;
 int iRows = 0;
 for(iRows=6;iRows < qryExport->RecordCount+6;iRows++)
 {
  if( bIsStop ){break;}
  Sheet1.OlePropertyGet("Rows",iRows+1).OleProcedure("Insert");
  Sheet1.OlePropertyGet("Cells",iRows,1).OlePropertySet("Value",iRows-5);
  for(int iCols = 1;iCols < qryExport->FieldCount;iCols++)
  {
   if( bIsStop ){break;}
   String strValue = qryExport->Fields->Fields[iCols-1]->AsString.Trim();
   Sheet1.OlePropertyGet("Cells",iRows,iCols+1).OlePropertySet("Value",(WideString)strValue);
  }
  ++nPos;
  if ( nPos%nStep == 0 )
  {
   pbExport->StepIt();
   Application->ProcessMessages();
  }
  qryExport->Next();
 }
 Sheet1.OlePropertyGet("Rows",iRows).OleProcedure("Delete");
 Sheet1.OlePropertyGet("Rows",iRows).OleProcedure("Delete");
 ExcelApp1.OlePropertySet("Visible",(Variant)true);
}

 

备注：需要一个模板文件：DispatchReport.xlt   ，该段代码是将创建的Excel默认是不显示的，当你操作完成后，会显示，这样你可以手动保存或者不保存。由用户决定，我个人认为这个比较好一些，代码清晰而且简单。希望大家有不清楚的地方和我联系：QQ:1574203887 或者邮箱：chao_song2011@sina.cn