VC中如何操作Excel文件？

Question

希望在VC里建立一个新Excel文件，并对其进行数据添加、修改以及删除操作，该如何实现？我导入Excel9.olb类型库后，不知道该怎么操作，请大家指点，最好给出示列代码，谢谢了

Answer 1

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