Excel在vc中怎么操作单元格的字体、颜色、加线框(和异常的抛出)

这篇博客详细介绍了如何在VC++环境中使用MFC和Automation来操作Excel,包括创建Excel应用、打开工作簿、选择工作表、设置单元格的字体、颜色以及边框样式。通过示例代码展示了如何处理异常,如COleException和COleDispatchException。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  ///////////////////////////////////////////////////////////////////////////////////////  
  //change   cell   font   of   Excel  
  ///////////////////////////////////////////////////////////////////////////////////////  
  void   CC2Dlg::OnOK()  
  {//HOWTO:   Create   Automation   Project   Using   MFC   and   a   Type   Library     Q178749  
  //change   font   of   a   Cell   in   excel  
  try  
  {  
  _Application   app;           //   app   is   an   _Application   object.  
  _Workbook   book;               //   More   object   declarations.  
  _Worksheet   sheet;  
  Workbooks   books;  
  Worksheets   sheets;  
  Range   range;                     //   Used   for   Microsoft   Excel   97   components.  
  LPDISPATCH   lpDisp;         //   Often   reused   variable.  
  COleVariant  
  covTrue((short)TRUE),  
  covFalse((short)FALSE),  
  covOptional((long)DISP_E_PARAMNOTFOUND,   VT_ERROR);  
  if(!app.CreateDispatch("Excel.Application"))  
  {  
  AfxMessageBox("Couldn't   CreateDispatch()   for   Excel");  
  return;  
  }  
  app.SetVisible(TRUE);  
  lpDisp   =   app.GetWorkbooks();           //   Get   an   IDispatch   pointer.  
  ASSERT(lpDisp);  
  books.AttachDispatch(lpDisp);         //   Attach   the   IDispatch   pointer  
  //   to   the   books   object.  
  lpDisp   =   books.Open("C://temp//book1.xls",           //   Test.xls   is   a   workbook.  
  covOptional,   covOptional,   covOptional,   covOptional,   covOptional,  
  covOptional,   covOptional,   covOptional,   covOptional,   covOptional,  
  covOptional,   covOptional,   covOptional,   covOptional   );       //   Return   Workbook's   IDispatch  
  //   pointer.  
  book.AttachDispatch(   lpDisp   );  
  lpDisp   =   book.GetSheets();  
  ASSERT(lpDisp);  
  sheets.AttachDispatch(lpDisp);  
  //   Get   sheet   #1   and   attach   the   IDispatch   pointer   to   your   sheet  
  //   object.  
  lpDisp   =   sheets.GetItem(   COleVariant((short)(1))   );  
  ASSERT(lpDisp);  
  sheet.AttachDispatch(lpDisp);  
  lpDisp   =   sheet.GetRange(COleVariant("B3"),   COleVariant("b3"));  
  range.AttachDispatch(lpDisp);  
  range.SetNumberFormat(COleVariant("@"));  
  range.SetItem(COleVariant((long)(1)),COleVariant((long)(1)),COleVariant(LPCTSTR("000666")));  
  Font   newfont;  
  lpDisp=range.GetFont();  
  newfont.AttachDispatch(lpDisp);  
  newfont.SetName(COleVariant("宋体"));  
  newfont.SetSize(COleVariant((long)24));  
  newfont.ReleaseDispatch();  
  //   Release   dispatch   pointers.  
  range.ReleaseDispatch();  
  sheet.ReleaseDispatch();  
  //   This   is   not   really   necessary   because  
  //   the   default   second   parameter   of   AttachDispatch   releases  
  //   when   the   current   scope   is   lost.  
   
  }   //   End   of   processing.  
   
  catch(COleException   *e)  
  {  
                  char   buf[1024];           //   For   the   Try...Catch   error   message.  
                  sprintf(buf,   "COleException.   SCODE:   %08lx.",   (long)e->m_sc);  
                  ::MessageBox(NULL,   buf,   "COleException",   MB_SETFOREGROUND   |   MB_OK);  
  }  
  catch(COleDispatchException   *e)  
  {  
                  char   buf[1024];           //   For   the   Try...Catch   error   message.  
                  sprintf(buf,  
  "COleDispatchException.   SCODE:   %08lx,   Description:   /"%s/".",  
  (long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));  
                  ::MessageBox(NULL,   buf,   "COleDispatchException",  
  MB_SETFOREGROUND   |   MB_OK);  
  }  
  catch(...)  
  {  
                  ::MessageBox(NULL,   "General   Exception   caught.",   "Catch-All",  
  MB_SETFOREGROUND   |   MB_OK);  
  }  
  CDialog::OnOK();  
  }  
  /////////////////////////////////////////////////////////////////////  
  //change   font   color   of   a   Cell   in   excel,   change   cell   background   color,   set   border  
  /////////////////////////////////////////////////////////////////////  
  void   CCellcolorDlg::OnOK()  
  {//HOWTO:   Create   Automation   Project   Using   MFC   and   a   Type   Library     Q178749  
  //change   font   color   of   a   Cell   in   excel,   change   cell   background   color,   set   border  
  //Do   not   forget   to   call   AfxOleInit();  
  try  
  {  
  _Application   app;           //   app   is   an   _Application   object.  
  _Workbook   book;               //   More   object   declarations.  
  _Worksheet   sheet;  
  Workbooks   books;  
  Worksheets   sheets;  
  Range   range;                     //   Used   for   Microsoft   Excel   97   components.  
  LPDISPATCH   lpDisp;         //   Often   reused   variable.  
  COleVariant  
  covTrue((short)TRUE),  
  covFalse((short)FALSE),  
  covOptional((long)DISP_E_PARAMNOTFOUND,   VT_ERROR);  
  if(!app.CreateDispatch("Excel.Application"))  
  {  
  AfxMessageBox("Couldn't   CreateDispatch()   for   Excel");  
  return;  
  }  
  app.SetVisible(TRUE);  
  lpDisp   =   app.GetWorkbooks();           //   Get   an   IDispatch   pointer.  
  ASSERT(lpDisp);  
  books.AttachDispatch(lpDisp);         //   Attach   the   IDispatch   pointer  
  //   to   the   books   object.  
  lpDisp   =   books.Open("C://temp//book1.xls",           //   Test.xls   is   a   workbook.  
  covOptional,   covOptional,   covOptional,   covOptional,   covOptional,  
  covOptional,   covOptional,   covOptional,   covOptional,   covOptional,  
  covOptional,   covOptional,   covOptional,   covOptional   );       //   Return   Workbook's   IDispatch  
  //   pointer.  
  book.AttachDispatch(   lpDisp   );  
  lpDisp   =   book.GetSheets();  
  ASSERT(lpDisp);  
  sheets.AttachDispatch(lpDisp);  
  //   Get   sheet   #1   and   attach   the   IDispatch   pointer   to   your   sheet  
  //   object.  
  lpDisp   =   sheets.GetItem(   COleVariant((short)(1))   );  
  ASSERT(lpDisp);  
  sheet.AttachDispatch(lpDisp);  
  lpDisp   =   sheet.GetRange(COleVariant("B3"),   COleVariant("b3"));  
  range.AttachDispatch(lpDisp);  
  // set   number   format  
  // range.SetNumberFormat(COleVariant("@"));  
  // range.SetItem(COleVariant((long)(1)),COleVariant((long)(1)),COleVariant(LPCTSTR("000666")));  
  // set   cell   font   here  
  Font   newfont;  
  lpDisp=range.GetFont();  
  newfont.AttachDispatch(lpDisp);  
  //newfont.SetName(COleVariant("宋体"));  
  //newfont.SetSize(COleVariant((long)24));  
  newfont.SetColorIndex(COleVariant((short)3));//use   VBA   to   see   index   value   of   a   certain   color  
  newfont.ReleaseDispatch();  
   
  //set   background   color  
  lpDisp=range.GetInterior();  
  Interior   cellinterior;  
  cellinterior.AttachDispatch(lpDisp);  
  cellinterior.SetColorIndex(COleVariant((short)6));//use   VBA   to   see   index   value   of   a   certain   color  
  cellinterior.ReleaseDispatch();  
  // Set   border  
  //xlDiagonalDown   =5   xlDiagonalUp=6   xlEdgeLeft=7  
  //xlNone   =   -4142   xlContinuous=1   xlThin=2   xlAutomatic=-4105  
  //xlEdgeTop   =8   xlEdgeBottom   =9   xlEdgeRight=10  
  lpDisp   =   range.GetBorders();  
  Borders   bds;  
  bds.AttachDispatch(lpDisp);  
  Border   bd;  
  lpDisp   =   bds.GetItem(8);  
  bd.AttachDispatch(lpDisp);  
  bd.SetLineStyle(COleVariant((short)1));  
   
  // //   Release   dispatch   pointers.  
  range.ReleaseDispatch();  
  sheet.ReleaseDispatch();  
  //   This   is   not   really   necessary   because  
  //   the   default   second   parameter   of   AttachDispatch   releases  
  //   when   the   current   scope   is   lost.  
   
  }   //   End   of   processing.  
   
  catch(COleException   *e)  
  {  
                  char   buf[1024];           //   For   the   Try...Catch   error   message.  
                  sprintf(buf,   "COleException.   SCODE:   %08lx.",   (long)e->m_sc);  
                  ::MessageBox(NULL,   buf,   "COleException",   MB_SETFOREGROUND   |   MB_OK);  
  }  
  catch(COleDispatchException   *e)  
  {  
                  char   buf[1024];           //   For   the   Try...Catch   error   message.  
                  sprintf(buf,  
  "COleDispatchException.   SCODE:   %08lx,   Description:   /"%s/".",  
  (long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));  
                  ::MessageBox(NULL,   buf,   "COleDispatchException",  
  MB_SETFOREGROUND   |   MB_OK);  
  }  
  catch(...)  
  {  
                  ::MessageBox(NULL,   "General   Exception   caught.",   "Catch-All",  
  MB_SETFOREGROUND   |   MB_OK);  
  }  
  CDialog::OnOK();  
  }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值