VC++中调用EXCEL方法

我是调用EXCEL2003的基础上写的代码，事后发现2007和2010版都可以兼容，微软的程序接口在更新软件时没有改变还是很值得表扬的

Range类操作集合

range =sheet.GetRange(COleVariant("A1"), COleVariant("D1"));    // 获得A1:D1的Range

range =sheet.GetRange(COleVariant("D2"),covOptional);                //获得D2 Range

range =range.GetResize(COleVariant((long)NUMROWS), COleVariant((long)1)); //重新设置D2的大小
range.SetFormula(COleVariant("=C2*0.07"));                         // 给D2：D21设置公式

range.AutoFit();                                                                           //自动适合尺寸

oFont =range.GetFont(); //获得Range的字体

Font类操作集合

oFont.SetBold(COleVariant((short)TRUE));                                   //设置是否粗体
oFont.SetColor(COleVariant((long)RGB(255, 0,0)));                    // 设置字体颜色

oFont.SetName(COleVariant("黑体"));                                      // 设置字体类型

range  = sheet.GetRange(COleVariant("A1"),covOptional);  //获取A1 Rangerange.SetValue2(COleVariant("Date"));                              // 添加数据

range = sheet.GetRange(COleVariant("A1"),COleVariant("D1"));    //获得A1:D1的RangeoFont =range.GetFont();                                                             // 获得Range的字体oFont.SetBold(COleVariant((short)TRUE));                                   // 设置是否粗体oFont.SetColor(COleVariant((long)RGB(255, 0, 0)));                    // 设置字体颜色oFont.SetName(COleVariant("黑体"));                                      // 设置字体类型range =range.GetEntireColumn();                                               //获得全部的单元格range.AutoFit();                                                                           //自动适合尺寸

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// 向单元格中添加公式

range= sheet.GetRange(COleVariant("D2"),covOptional);      //获得D2 Range

range = range.GetResize(COleVariant((long)NUMROWS),COleVariant((long)1)); // 重新设置D2的大小range.SetFormula(COleVariant("=C2*0.07"));                        // 给D2：D21设置公式

3、合并单元格

      //思路：1.先获取A1：C1的Range范围，然后重新定义此范围，最后合并
      //         2.直接获得A1:C2的Range范围，直接合并。结果和第一种方法一样

 Range unionRange;
unionRange =sheet.GetRange(COleVariant("A1"), COleVariant("C1")); 
vResult = unionRange.GetMergeCells();
unionRange =unionRange.GetResize(COleVariant((long)2), COleVariant((long)3));
unionRange.Merge(COleVariant((long)0));      //合并单元格
unionRange.SetRowHeight(COleVariant((short)30));   //设置单元格的高度
unionRange.SetHorizontalAlignment(COleVariant((long)-4108));// 水平居中对齐

4、向单元格中插入图片（支持BMP、JPG格式，其他没试）

Shapes   shapes  =   sheet.GetShapes();       // 从Sheet对象上获得一个Shapes    
range   =sheet.GetRange(COleVariant("B16"),COleVariant("J22"));   // 获得Range对象，用来插入图片
rgMyRge1 = range;
shapes.AddPicture("D://Test1.jpg"  ,   false   ,   true   ,   
                 (float)range .GetLeft().dblVal, (float)range .GetTop().dblVal,    //从本地添加一个图片
                    (float)range .GetWidth().dblVal, (float)range .GetHeight().dblVal);
ShapeRange  sRange   =   shapes.GetRange(_variant_t(long(1)));
sRange.SetHeight(float(30));   
sRange.SetWidth(float(30));

5、将已建的.xls文件另存为

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   book.SaveAs(COleVariant("D://3.xls"),covOptional,covOptional,
                               covOptional,covOptional,covOptional,0,
                               covOptional,covOptional,covOptional,covOptional,covOptional);

6、关闭Excel服务

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    book.SetSaved(TRUE);     // 将Workbook的保存状态设置为已保存，即不让系统提示是否人工保存
    range.ReleaseDispatch();    // 释放Range对象
    sheet.ReleaseDispatch();    // 释放Sheet对象
    sheets.ReleaseDispatch();    // 释放Sheets对象

    book.ReleaseDispatch();     // 释放Workbook对象
    books.ReleaseDispatch();    // 释放Workbooks对象

    book.Close(covOptional, covOptional,covOptional);//关闭Workbook对象
    books.Close();           // 关闭Workbooks对象
    app.Quit();          // 退出_Application
   app.ReleaseDispatch();       // 释放_Application

//得到所有的列

rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);

//得到第一列

rgMyRge.AttachDispatch(rgMyRge.GetItem(_variant_t((long)1),vtMissing).pdispVal,true);

//打印预览

wbMyBook.SetSaved(true); 
ExcelApp.SetVisible(true); 
wbMyBook.PrintPreview(_variant_t(false));

//调用模板中预先存放的宏

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

//设置1行1列的单元的值

rgMyRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("ThisIs A Excel Test Program!"));

//获取总共有多少个SHEET

sheets.get_Count();

秒客网

VC++中调用EXCEL方法

相关文章