VC操作EXCEL

来源：互联网发布：达内大数据培训可靠吗编辑：程序博客网时间：2024/05/20 10:53

1.新建MFC工程，

2.选择Menu->View->ClassWizade，打开ClassWizade窗口，选择Add Class->From a type library，选择D:Program FilesMicrosoft OfficeofficeExcel9.OLB(D:Program FilesMicrosoft Office是本机上Microsoft Office 2000的安装目录，可根据个人机器上的实际安装目录修改)。

3.选择_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range，加入新类，分别为_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range，头文件Excel9.h，源文件Excel9.cpp。

注意：如果是Excel2003,其OLB的文件是：xl5chs32.olb

4.包含头文件#include "comdef.h" 、#include "Excel9.h"

5.在应用程序::InitInstance()启动主窗口之前增加代码：
if (CoInitialize(NULL)!=0)
{
AfxMessageBox("初始化COM支持库失败!");
exit(1);
}
在return FALSE; 语句前，加入：
CoUninitialize();

6.变量定义
_Application app;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
Range range;
Range iCell;
LPDISPATCH lpDisp;
COleVariant vResult;
COleVariant
        covTrue((short)TRUE),
        covFalse((short)FALSE),
        covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
//*****
//初始化COM的动态连接库
if(!AfxOleInit())
{
        AfxMessageBox("无法初始化COM的动态连接库!");
        return ;
     }

//*****
//创建Excel 2000服务器(启动Excel)
if(!app.CreateDispatch("Excel.Application"))
{
  AfxMessageBox("无法启动Excel服务器!");
    return;
}

app.SetVisible(TRUE);          //使Excel可见
app.SetUserControl(TRUE);      //允许其它用户控制Excel

//*****
//打开c:/1.xls
books.AttachDispatch(app.GetWorkbooks());
lpDisp = books.Open("C://1.xls",
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional );


//*****
//得到Workbook
book.AttachDispatch(lpDisp);

//*****
//得到Worksheets
sheets.AttachDispatch(book.GetWorksheets());

//*****
//得到当前活跃sheet
//如果有单元格正处于编辑状态中，此操作不能返回，会一直等待
lpDisp=book.GetActiveSheet();
sheet.AttachDispatch(lpDisp);


//*****
//读取已经使用区域的信息，包括已经使用的行数、列数、起始行、起始列
Range usedRange;
usedRange.AttachDispatch(sheet.GetUsedRange());
range.AttachDispatch(usedRange.GetRows());
long iRowNum=range.GetCount();                   //已经使用的行数

range.AttachDispatch(usedRange.GetColumns());
long iColNum=range.GetCount();                   //已经使用的列数

long iStartRow=usedRange.GetRow();               //已使用区域的起始行，从1开始
long iStartCol=usedRange.GetColumn();            //已使用区域的起始列，从1开始


//*****
//读取第一个单元格的值
range.AttachDispatch(sheet.GetCells());
range.AttachDispatch(range.GetItem (COleVariant((long)1),COleVariant((long)1)).pdispVal );
COleVariant vResult =range.GetValue();
CString str;
if(vResult.vt == VT_BSTR)       //字符串
{
  str=vResult.bstrVal;
}
else if (vResult.vt==VT_R8)     //8字节的数字
{
  str.Format("%f",vResult.dblVal);
}
else if(vResult.vt==VT_DATE)    //时间格式
{
  SYSTEMTIME st;
     VariantTimeToSystemTime(&vResult.date, &st);
}
else if(vResult.vt==VT_EMPTY)   //单元格空的
{
  str="";
}

//*****
//读取第一个单元格的对齐方式，数据类型:VT_I4
//读取水平对齐方式
range.AttachDispatch(sheet.GetCells());
iCell.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);
vResult.lVal=0;
vResult=iCell.GetHorizontalAlignment();
if(vResult.lVal!=0)
{
  switch (vResult.lVal)
  {
  case 1:      //默认
   break;
  case -4108:  //居中
   break;
  case -4131 : //*左
   break;
  case -4152 : //*右
   break;
  }

}

//垂直对齐方式
iCell.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);
vResult.lVal=0;
vResult=iCell.GetVerticalAlignment();
if(vResult.lVal!=0)
{
  switch (vResult.lVal)
  {
  case -4160 :  //*上
   break;
  case -4108 :  //居中
   break;
  case -4107 :  //*下
   break;
  }

}


//*****
//设置第一个单元格的值"HI,EXCEL!"
range.SetItem(COleVariant(1),COleVariant(1),COleVariant("HI,EXCEL!"));

//*****
//设置第一个单元格字体颜色:红色
Font font;
range.AttachDispatch(sheet.GetCells());
range.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);
font.SetColor(COleVariant((long)0xFF0000));

//*****
//合并单元格的处理
//包括判断第一个单元格是否为合并单元格，以及将第一个单元格进行合并
Range unionRange;
range.AttachDispatch(sheet.GetCells());
unionRange.AttachDispatch(range.GetItem (COleVariant((long)1),COleVariant((long)1)).pdispVal );

vResult=unionRange.GetMergeCells();
if(vResult.boolVal==-1)             //是合并的单元格
{
  //合并单元格的行数
  range.AttachDispatch (unionRange.GetRows ());
  long iUnionRowNum=range.GetCount ();

  //合并单元格的列数
  range.AttachDispatch (unionRange.GetColumns ());
  long iUnionColumnNum=range.GetCount ();

  //合并区域的起始行，列
  long iUnionStartRow=unionRange.GetRow();       //起始行，从1开始
  long iUnionStartCol=unionRange.GetColumn();    //起始列，从1开始

}
else if(vResult.boolVal==0)
{//不是合并的单元格}

//将第一个单元格合并成2行，3列
range.AttachDispatch(sheet.GetCells());
unionRange.AttachDispatch(range.GetItem (COleVariant((long)1),COleVariant((long)1)).pdispVal );
unionRange.AttachDispatch(unionRange.GetResize(COleVariant((long)2),COleVariant((long)3)));
unionRange.Merge(COleVariant((long)0));   //合并单元格

//*****
//将文件保存为2.xls
book.SaveAs(COleVariant("C://2.xls"),covOptional,covOptional, /
  covOptional,covOptional,covOptional,0,/
  covOptional,covOptional,covOptional,covOptional);


//*****
//关闭所有的book，退出Excel
book.Close (covOptional,COleVariant(OutFilename),covOptional);
books.Close();
app.Quit();

//利用模板文件建立新文档
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);
wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t("g:\exceltest\MyTemplate.xlt")));
//得到Worksheets
wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);
//得到sheet1
wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true);
//得到全部Cells，此时,rgMyRge是cells的集合
rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
//设置1行1列的单元的值
rgMyRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("This Is A Excel Test Program!"));
//得到所有的列
rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);
//得到第一列
rgMyRge.AttachDispatch(rgMyRge.GetItem(_variant_t((long)1),vtMissing).pdispVal,true);
//设置列宽
rgMyRge.SetColumnWidth(_variant_t((long)200));
//调用模板中预先存放的宏
ExcelApp.Run(_variant_t("CopyRow"),_variant_t((long)10),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.PrintPreview(_variant_t(false));
//释放对象
rgMyRge.ReleaseDispatch();
wsMysheet.ReleaseDispatch();
wssMysheets.ReleaseDispatch();
wbMyBook.ReleaseDispatch();
wbsMyBooks.ReleaseDispatch();
ExcelApp.ReleaseDispatch();