OLE程序开发利用（开发EXCEL)一、首先打开类向导（MFC ClassWizard) 选择Add Class按钮中的 From a type library...找到 Office 目录下的文件 EXCEL9.OLB 并打开。二、在Confirm Class中的类框中选择你所需的类（EXCEL中的对象）后按OK按钮后依次添加 _Application 、Workbooks 、_Workbook 、Worksheets 、_Worksheet 和 Range类。三、添加头文件 #include <comdef.h> #include "excel9.h" 四、代码如下：
void CParameterApp::CreateRepTemp() { _Application ExcelApp; Workbooks wbsMyBooks; _Workbook wbMyBook; Worksheets wssMysheets; _Worksheet wsMysheet; Range rgMyRge; CString strSqlStmt; 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_strSource.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 报表模板设置")); file://得到Workbooks wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true); if( iFileExist) { wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t((CString)strRepTemplate))); file://wbMyBook.SetSaved(true); file://ExcelApp.GetSaveAsFilename (vtMissing,vtMissing,vtMissing,vtMissing,_variant_t("vtMissing")); file://wbMyBook.Save (); file://("xlShared") file://wbMyBook.SaveAs(_variant_t("d://我的工作表.xls"),_variant_t("xlAddIn"),vtMissing,vtMissing,vtMissing,vtMissing,_variant_t(long (1)),_variant_t("xlUserResolution"),vtMissing,vtMissing,vtMissing); file://运行宏(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("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);
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){file://添加所有的表单 _Worksheet worksheettemp; CString strSelRepName; int nCount =m_strRepNameArray.GetSize();
for(int i=1/*0*/;i<nCount;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;}
