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

最新推荐文章于 2016-01-18 18:06:59 发布

东北熊孩子

最新推荐文章于 2016-01-18 18:06:59 发布

阅读量2.1k

点赞数

分类专栏： Delphi区 C++区文章标签： C++ builder Excel

本文链接：https://blog.csdn.net/lovescjinbao/article/details/48055179

版权

Delphi区同时被 2 个专栏收录

26 篇文章 0 订阅

订阅专栏

C++区

17 篇文章 0 订阅

订阅专栏

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