VC++中调用EXCEL方法

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

Range类操作集合

[cpp]  view plain copy

1. range =sheet.GetRange(COleVariant("A1"), COleVariant("D1"));    // 获得A1:D1的Range  
2.   
3. range =sheet.GetRange(COleVariant("D2"),covOptional);                //获得D2 Range  
4.   
5. range =range.GetResize(COleVariant((long)NUMROWS), COleVariant((long)1)); //重新设置D2的大小  
6. range.SetFormula(COleVariant("=C2*0.07"));                         // 给D2：D21设置公式  
7.   
8. range.AutoFit();                                                                           //自动适合尺寸  

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

Font类操作集合

[cpp]  view plain copy

1. oFont.SetBold(COleVariant((short)TRUE));                                   //设置是否粗体  
2. oFont.SetColor(COleVariant((long)RGB(255, 0,0)));                    // 设置字体颜色  
3.   
4. oFont.SetName(COleVariant("黑体"));                                      // 设置字体类型<span style="color:#333333;BACKGROUND: white"> </span>  

[cpp]  view plain copy

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

[cpp]  view plain copy

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

//

 // 向单元格中添加公式

[cpp]  view plain copy

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

[cpp]  view plain copy

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

3、合并单元格

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

[cpp]  view plain copy

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

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

[cpp]  view plain copy

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

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

[cpp]  view plain copy

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

6、关闭Excel服务

[cpp]  view plain copy

1. COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);  
2.     book.SetSaved(TRUE);     // 将Workbook的保存状态设置为已保存，即不让系统提示是否人工保存  
3.     range.ReleaseDispatch();    // 释放Range对象  
4.     sheet.ReleaseDispatch();    // 释放Sheet对象  
5.     sheets.ReleaseDispatch();    // 释放Sheets对象  
6.    
7.     book.ReleaseDispatch();     // 释放Workbook对象  
8.     books.ReleaseDispatch();    // 释放Workbooks对象  
9.   
10.     book.Close(covOptional, covOptional,covOptional);//关闭Workbook对象  
11.     books.Close();           // 关闭Workbooks对象  
12.     app.Quit();          // 退出_Application  
13.    app.ReleaseDispatch();       // 释放_Application  

//得到所有的列    

[cpp]  view plain copy

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

//得到第一列    

[cpp]  view plain copy

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

//打印预览

[cpp]  view plain copy

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

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

[cpp]  view plain copy

1. ExcelApp.Run(_variant_t("CopyRow"),_variant_t((long)10),vtMissing,vtMissing,  
2. vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,   
3. vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,   
4. vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,   
5. vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);  

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

[cpp]  view plain copy

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

//获取总共有多少个SHEET

[cpp]  view plain copy

1. sheets.get_Count();