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

首先要引入头文件：#include "ComObj.hpp"

下面是具体操作Excel：

 

bool __fastcall TFrmDispatchDataExport::DisExportData(String _strSql, String ExcelFileName)
{
 Variant ExcelApp,Workbook1,Sheet1;
 try
 {
  //建立Excel的Ole对象
  ExcelApp = CreateOleObject("Excel.Application");
  //设置Excel为不可见
//  ExcelApp.OlePropertySet("Visible",false);
  //创建Excel对象
//  ExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Add");
  ExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open", (WideString)ExcelFileName);
  //创建工作簿对象
  Workbook1 = ExcelApp.OlePropertyGet("ActiveWorkBook");
  //选择工作表中第一个工作表
//        Workbook1.OlePropertyGet("Sheets",1).OleProcedure("Select");
  //取当前活动Sheet
  Sheet1 = Workbook1.OlePropertyGet("ActiveSheet");

  //设置第一列的列宽为
  ExcelApp.OlePropertyGet("Columns",1).OlePropertySet("ColumnWidth",22);
  ExcelApp.OlePropertyGet("Columns",2).OlePropertySet("ColumnWidth",22);
  ExcelApp.OlePropertyGet("Columns",3).OlePropertySet("ColumnWidth",22);
  ExcelApp.OlePropertyGet("Columns",4).OlePropertySet("ColumnWidth",40);
  ExcelApp.OlePropertyGet("Columns",5).OlePropertySet("ColumnWidth",22);
  ExcelApp.OlePropertyGet("Columns",6).OlePropertySet("ColumnWidth",22);
  ExcelApp.OlePropertyGet("Columns",7).OlePropertySet("ColumnWidth",22);
  ExcelApp.OlePropertyGet("Columns",8).OlePropertySet("ColumnWidth",50);

  int iRows = 1;
        //使用C++ XE6版本 值要用WideString进行转换，否则会出错
  Sheet1.OlePropertyGet("Cells",iRows,1).OlePropertySet("Value",(WideString)"BUSIBOOKNO");
  Sheet1.OlePropertyGet("Cells",iRows,2).OlePropertySet("Value",(WideString)"附加码");
  Sheet1.OlePropertyGet("Cells",iRows,3).OlePropertySet("Value",(WideString)"付款企业代码");
  Sheet1.OlePropertyGet("Cells",iRows,4).OlePropertySet("Value",(WideString)"付款企业名称");
  Sheet1.OlePropertyGet("Cells",iRows,5).OlePropertySet("Value",(WideString)"OARBU");
  Sheet1.OlePropertyGet("Cells",iRows,6).OlePropertySet("Value",(WideString)"报价单号");
  Sheet1.OlePropertyGet("Cells",iRows,7).OlePropertySet("Value",(WideString)"付款类型");
  Sheet1.OlePropertyGet("Cells",iRows,8).OlePropertySet("Value",(WideString)"说明");

  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);
  }

  while( !qryExport->Eof )
  {
   ++iRows;
   //如果点击取消则终止线程
   if( bIsStop ){break;}
   for(int iCols = 0;iCols < qryExport->FieldCount;iCols++)
   {
    //如果点击取消则终止线程
    if( bIsStop ){break;}
    String strValue = qryExport->Fields->Fields[iCols]->AsString.Trim();
    Sheet1.OlePropertyGet("Cells",iRows,iCols+1).OlePropertySet("Value",(WideString)strValue);
   }
   ++nPos;

   //界面进度条操作
   if ( nPos%nStep == 0 )
   {
    pbExport->StepIt();
    Application->ProcessMessages();
   }
   qryExport->Next();
  }
 }
 __finally
 {
  //保存文件
  Workbook1.OleProcedure("Save");
  //关闭相关对象
  Workbook1.OleProcedure("Close");
  //退出Excel
//  ExcelApp.OlePropertySet("Quit",true);
  Sheet1=NULL;
  Workbook1=NULL;
  ExcelApp=NULL;
  ExcelApp=Unassigned;
  Workbook1=Unassigned;
  Sheet1=Unassigned;
  bFinsh = true;
  qryExport->Close();
 }

 return bFinsh;
}