OLE程式開發利用（開發EXCEL) (轉)

OLE程式開發利用（開發EXCEL) (轉)[@more@]

OLE開發利用（開發)
一、首先開啟類嚮導（MFC ClassWizard) 選擇Add Class按鈕中的 From a type library...找到
  目錄下的 EXCEL9.OLB 並開啟。
二、在ConfiClass中的類框中選擇你所需的類（EXCEL中的）後按OK按鈕後依次新增 _Application 、Workbooks 、_Workbook 、Worksheets 、_Worksheet 和　Range類。
三、新增標頭檔案
 #include
 #include "excel9.h" 
四、程式碼如下：

void CParameterApp::CreateRepTemp()
{
 _Application ExcelApp;
 Workbooks wbsMyBooks;
 _Workbook wbMyBook;
 Worksheets wssMysheets;
 _Worksheet wsMysheet;
 Range rgMyRge;
 CString strStmt;
 CString strRepTemplate="c:報表報表.xlt";

///////////////////////////////////////////////////////////////////////////
 CFileFind filefind;
 int iFileExist= filefind.FindFile((LPCTSTR)strRepTemplate);

///////////////////////////////////////////////////////////////////////////
 if(CoInitialize(NULL)!=0)
 {
 AfxMessageBox("初始化COM支援庫失敗！");
 exit(1);
 }

  COleException *e = new COleException;
 
 try
 {
 if(!ExcelApp.CreateDispatch("Excel.Application.9",e)) 
   
  throw e; 
 
 }

  catch (COleDispatchException * e)
  {
  CString cStr;

  if (!e->m_str.IsEmpty())
  cStr = e->m_strSource + " - ";
  if (!e->m_strDescription.IsEmpty())
  cStr += e->m_strDescription;
  else
  cStr += "unknown error";

  AfxMessageBox(cStr, MB_OK,
  (e->m_strHelpFile.IsEmpty())? 0:e->m_dwHelpContext);

  e->Delete();
  }
 
 ExcelApp.SetCaption(_T("FARAD 200D 報表模板設定"));
 到Workbooks
 wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true); 
 
 if( iFileExist)
 {
 wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t((CString)strRepTemplate)));
 (true);
 ilename">file://ExcelApp.GetSaveAsFilename (vtMissing,vtMissing,vtMissing,vtMissing,_variant_t("vtMissing"));
  ();
 "xlShared")
 (_variant_t("d:我的工作表.xls"),_variant_t("xlAddIn"),vtMissing,vtMissing,vtMissing,vtMissing,_variant_t(long (1)),_variant_t("xlUserResolution"),vtMissing,vtMissing,vtMissing);
 行宏(CString)
 ExcelApp.Run(_variant_t("auto_open"),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
 );

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

 }

 else
 {
 CString strSQL=_T(" 報表名稱 FROM 報表設定表 ");
 PrePareRepName(strSQL);

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

 到Worksheets
 wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);

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

 到全部Cells，此時,rgMyRge是cells的集合
 rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
 置單元的值
 strSqlStmt=PrepareSQL("AI歷史表");// 獲得查詢語句
 SetTemplateData(&rgMyRge,strSqlStmt);
 
 到所有的列
 rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);
 置列寬
 rgMyRge.SetColumnWidth(_variant_t((long)15));
 置對齊方式
 rgMyRge.SetHorizontalAlignment(_variant_t(BYTE(3)));//3:居中

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

 到全部Cells，此時,rgMyRge是cells的集合
 rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);

 置單元的值
 strSqlStmt=PrepareSQL("COUNTER歷史表");// 獲得查詢語句
 SetTemplateData(&rgMyRge,strSqlStmt);

 到所有的列
 rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);
 置列寬
 rgMyRge.SetColumnWidth(_variant_t((long)15));
 置對齊方式
 rgMyRge.SetHorizontalAlignment(_variant_t(BYTE(2)));//2:左對齊

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

 加所有的報表表單

  AddSheet(wssMysheets, wsMysheet);
行宏
 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);
 存檔案
 wbMyBook.SaveCopyAs(_variant_t((CString)strRepTemplate));

 放物件
 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",};

 COCDynamic 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 {
 CODBCRecord* pODBCRecord=(CODBCRecord*)pcolarray->GetAt(j);
 
 strColName=pODBCRecord->m_strcolname;//列名
 pODBCRecord->m_pvar->GetStringValue(szValue);//值
 中行列計數是以1為基
 if(j 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);

 pRgMyRge->SetItem(_variant_t((long)irow),_variant_t((long)icol),_variant_t(val));

 }
 else
 {
 pRgMyRge->SetItem(_variant_t((long)irow),_variant_t((long)icol),_variant_t(szValue));
 }
 }
 
 }break; 
 }
 }
 
 catch (CUserException* pe)
 {
 pe->ReportError();
 pe->Delete();
 }

 return ;
}

_Worksheet CParameterApp::AddSheet(Worksheets &worksheets, _Worksheet &worksheet)
{
加所有的表單
 _Worksheet worksheettemp;
 CString strSelRepName;
 int nCount =m_strRepNameArray.GetSize();

 for(int i=1/*0*/;i {
 
 strSelRepName=m_strRepNameArray[i];
 
 worksheettemp.AttachDispatch(worksheets.Add(vtMissing,_variant_t(worksheet),vtMissing,vtMissing),true);
 worksheettemp.SetName(strSelRepName);
 worksheet.AttachDispatch(worksheet.GetNext(),true);

 }
 return worksheettemp;
}