OLE程序开发利用（开发EXCEL) 之二

ExcelApp.SetVisible(true);
  ExcelApp.ReleaseDispatch();

 }

 else
 {
 CString strSQL=_T("SELECT 报表名称 FROM 报表设置表 ");
 PrePareRepName(strSQL);

 wbMyBook.AttachDispatch(wbsMyBooks.Add(vtMissing));

 file://得到Worksheets
 wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);

 file://得到sheet1
// wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true);
 wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t((long)1)),true);
 wsMysheet.SetName(_T("AI"));

 file://得到全部Cells，此时,rgMyRge是cells的集合
 rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
 file://设置单元的值
  strSqlStmt=PrepareSQL("AI历史表");// 获得查询语句
 SetTemplateData(&rgMyRge,strSqlStmt);
 
 file://得到所有的列
 rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);
 file://设置列宽
 rgMyRge.SetColumnWidth(_variant_t((long)15));
 file://设置对齐方式
 rgMyRge.SetHorizontalAlignment(_variant_t(BYTE(3)));//3:居中

 file://得到sheet2
 wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t((long)2)),true);
 wsMysheet.SetName(_T("COUNTER"));

 file://得到全部Cells，此时,rgMyRge是cells的集合
 rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);

 file://设置单元的值
 strSqlStmt=PrepareSQL("COUNTER历史表");// 获得查询语句
 SetTemplateData(&rgMyRge,strSqlStmt);

 file://得到所有的列
 rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);
 file://设置列宽
 rgMyRge.SetColumnWidth(_variant_t((long)15));
 file://设置对齐方式
 rgMyRge.SetHorizontalAlignment(_variant_t(BYTE(2)));//2:左对齐

 file://得到sheet3
 wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t((long)3)),true);
 wsMysheet.SetName(m_strRepNameArray[0]);
 wsMysheet.Activate();
// wsMysheet.SetVisible((long)0);//使sheet3不可视
 ExcelApp.SetVisible(true);

 file://添加所有的报表表单

  AddSheet(wssMysheets, wsMysheet);
file://运行宏
  ExcelApp.Run(_variant_t((CString)"宏2"),vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing
   ,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing
   ,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing
   );
 
 wbMyBook.SetSaved(true);
 ExcelApp.SetVisible(true);
 file://保存文件
 wbMyBook.SaveCopyAs(_variant_t((CString)strRepTemplate));

 file://释放对象
 if(m_strRepNameArray.GetSize()>0)
  m_strRepNameArray.RemoveAll();

 rgMyRge.ReleaseDispatch();
 wsMysheet.ReleaseDispatch();
 wssMysheets.ReleaseDispatch();
 wbMyBook.ReleaseDispatch();
 wbsMyBooks.ReleaseDispatch();
 ExcelApp.ReleaseDispatch();
 }
 
 CoUninitialize();
 
}
void CParameterApp::SetTemplateData(Range *pRgMyRge, CString strSQL)
{
 char prefixion[34][3]={"C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T",
 "U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ",};

 CODBCDynamic odbcDynamic(_T("Report"),"sa","");// 动态连接数据源
    try
  { 
   // 执行查询
   odbcDynamic.ExecuteSQL( strSQL );
  if(odbcDynamic.m_bError)
  {
   AfxMessageBox("对不起，无此数据",MB_OK);
   return;
  }
  
   // 显示查询结果集
   int irecordnum=odbcDynamic.m_ODBCRecordArray.GetSize();//当前记录数
 
   // 添加结果集记录
  
   for (int iRecord = 0; iRecord < irecordnum; iRecord++)//此for循环只执行了irecordnum=0
   {   
    CString strColName="";
    CDBVariantEx* pvarValue=NULL;
    char szValue[255];
   CObArray *pcolarray =(CObArray*)odbcDynamic.m_ODBCRecordArray.GetAt(0);
    
    int num=pcolarray->GetSize();//num为表的行数与列数的乘积
    
     for(int j=0;j<num;j++)//用j进行计数
     {
      CODBCRecord* pODBCRecord=(CODBCRecord*)pcolarray->GetAt(j);
      
      strColName=pODBCRecord->m_strcolname;//列名
      pODBCRecord->m_pvar->GetStringValue(szValue);//值
     file://Excel中行列计数是以1为基
     if(j<num/irecordnum)
      pRgMyRge->SetItem(_variant_t((long)1),_variant_t((long)(j+1)),_variant_t(strColName));
       
      if (0 <= strlen(szValue))
      {
      int irow=j/(num/irecordnum)+2;//行号
      int icol=j%(num/irecordnum)+1;//列号
      if((irow>=2)&&(icol>=3))
      {
       char val[8];
       char postfixion[8];
       _itoa(irow,postfixion,10);
       strcpy(val,prefixion[icol-3]);
       strcat(val,postfixion);