OLE方式操作EXCEL（很全面，供参考）

一、新建EXCEL表

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);

app.CreateDispatch(“EXCEL.Application”);

app.SetVisible(false); //设置表可见性

app.SetDisplayFullScreen(false); //设置不全屏显示

app.SetDisplayAlerts(false); //屏蔽警告

二、打开EXCEL表

char path[MAX_PATH];

GetCurrentDirectory(MAX_PATH,path); //获取当前目录

CString strPath = path;

strPath += “\\计算结果”;

books.AttachDispatch(app.GetWorkbooks(),true);

book.AttachDispatch(books.Add(_variant_t(strPath)));

三、获取工作表

sheets = book.GetWorksheets();

sheet = sheets.GetItem(COleVariant((short)1));

四、新建工作表

sheets = book.GetWorksheets();

sheets.Add(vtMissing,vtMissing,_variant_t((long)5[u1] ),vtMissing);

五、选择工作表某个区域

1、range = sheet.GetRange(COleVariant(“A1”),COleVariant(“A1”));

2、range.AttachDispatch(sheet.GetRange(_variant_t(“K39”),_variant_t(“K39”)),TRUE);

六、合并单元格

range = sheet.GetRange(COleVariant(“A1”),COleVariant(“A1”)); //先选择区域

range.Merge(_variant_t((long)0)); //再合并

七、设置单元格内容

1、单个单元格赋值

range = sheet.GetRange(COleVariant(“A1”),COleVariant(“A1”)); //先选择区域

range.SetValue2(COleVariant(“××”)); //再赋值

2、批量赋值

CString sCell; //定义字符串变量

for(int i=0; i<360; i++)

{

sCell.Format(“A%d”,i+2); //选择单元格

range = sheet.GetRange(COleVariant(sCell[u2] ),COleVariant(sCell[u3] ));

range.SetValue2(COleVariant((short)(1.5*i))); //再赋值

}

八、设定单元格字体，对齐方式，颜色，粗体，斜体，背景色，列宽，行高

Font ft； //定义字体变量

Interior it; //定义背景色变量

range = sheet.GetRange(COleVariant(“A1”),COleVariant(“A1”)); //先选择区域

range.SetHorizontalAlignment(_variant_t((long)-4131)); //水平居中对齐

range.SetVerticalAlignment(_variant_t((long)-4108)); //竖直居中对齐

range.SetColumnWidth(COleVariant(“20”)); //列宽

range.SetRowHeight(COleVariant(“25”)); //行高

ft.AttachDispatch(range.GetFont()); //匹配

ft.SetBold(_variant_t((long)0)); //设置粗体，0-不加粗；1-加粗

ft.SetItalic(_variant_t((long)0)); //设置斜体，0-不斜；1-斜

ft.SetSize(_variant_t((long)11)); //字大小

ft.SetColorIndex(_variant_t((long)5)); //字颜色

it.AttachDispatch(range.GetInterior());

it.SetColorIndex(_variant_t((long)8)); //背景色

 

说明：

对齐方式：（水平）居中：-4108 靠左：-4131 靠右：-4152

（竖直）居中：-4108 靠上：-4160 靠下：-4107

线颜色值：1-黑；2-白；3-红；5蓝；6黄；7-粉红；8-浅蓝；9-灰；10-绿；

九、获取单元格数据

COleVariant rValue,gValue; //定义COleVariant变量

CString quzhi; //定义字符串变量

range = sheet.GetRange(COleVariant(“A1”),COleVariant(“A1”))；

rValue=COleVariant(range.GetValue(gValue)); //取得单元格数据

rValue.ChangeType(VT_BSTR); //数据转换格式

quzhi=rValue.bstrVal; //将单元格数据赋在字符串变量中

十、绘单元格边框

Range rg ; //定义变量

1、绘横线

CString strCell1strCell2，strCell3;

for (i=5;i<21;i++)

{

strCell1.Format(“I%d”,i*2);

strCell2.Format(“K%d”,i*2);

rg.AttachDispatch(sheet.GetRange(COleVariant(strCell1),

COleVariant(strCell2)),true);

rg.BorderAround(_variant_t((long)1),_variant_t((long)2),

_variant_t((long)1),vtMissing);

//线型 // 线宽 //颜色

}

2、绘竖线

for(i=0;i<20;i++)

{

strCell1.Format(“%c12”,(char)(‘I’+i));

strCell2.Format(“%c40″,(char)(‘I’+i));

rg.AttachDispatch(sheet.GetRange(COleVariant(strCell1)

,COleVariant(strCell2)),true);

rg.BorderAround(_variant_t((long)1),_variant_t((long)2),

_variant_t((long)3),vtMissing);

}

3、绘外框

strCell3=(CString)CHAR(i+74)+”40”;

rg.AttachDispatch(sheet.GetRange(COleVariant(“I10”)，

COleVariant(strCell3)),TRUE);

rg.BorderAround(_variant_t((long)1),_variant_t((long)3),

_variant_t((long)5),vtMissing);

 

十一、保存结果

book.SaveAs(COleVariant(strPath),covOptional,

covOptional,covOptional,

covOptional,covOptional, (long)0,covOptional,covOptional,covOptional,

covOptional,covOptional);

十二、关闭表及释放变量

app.Quit();

sheet.ReleaseDispatch();

sheets.ReleaseDispatch();

book.ReleaseDispatch();

books.ReleaseDispatch();

app.ReleaseDispatch();