OLE程序开发EXCEL

最新推荐文章于 2025-03-17 14:06:58 发布

转载最新推荐文章于 2025-03-17 14:06:58 发布 · 1.7k 阅读

文章标签：

#程序开发 #excel #报表 #application #byte #class

VC++程序设计同时被 2 个专栏收录

675 篇文章

订阅专栏

Office编程

136 篇文章

订阅专栏

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 报表模板设置"));
//得到Workbooks
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);

if( iFileExist)
{
  wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t((CString)strRepTemplate)));
  //wbMyBook.SetSaved(true);
  //ExcelApp.GetSaveAsFilename (vtMissing,vtMissing,vtMissing,vtMissing,_variant_t("vtMissing"));
  //wbMyBook.Save ();
  //("xlShared")
  //wbMyBook.SaveAs(_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("SELECT 报表名称 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",};

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)
{
//添加所有的表单
_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;
}