- // 变量的定义
- _Application app;
- Workbooks books;
- _Workbook book;
- Worksheets sheets;
- _Worksheet sheet;
- Range range;
- LPDISPATCH lpDisp;
- COleVariant vResult;
- CString str = "";
- COleVariant
- covTrue((short)TRUE),
- covFalse((short)FALSE),
- covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
- //创建Excel 2003服务器(启动Excel)
- if (!app.CreateDispatch("Excel.Application",NULL))
- {
- AfxMessageBox("Create Excel service failure!");
- return;
- }
- // 设置为FALSE时,后面的app.Quit();注释要打开
- // 否则EXCEL.EXE进程会一直存在,并且每操作一次就会多开一个进程
- app.SetVisible(TRUE);
- books.AttachDispatch(app.GetWorkbooks(),true);
- /*
- * 打开一个工作簿。
- * Excel 2000 只需要13个参数就行,Excel 2003需要15个参数
- */
- lpDisp = books.Open("E://test.xls",
- covOptional, covOptional, covOptional, covOptional,
- covOptional, covOptional, covOptional, covOptional,
- covOptional, covOptional, covOptional, covOptional,
- covOptional, covOptional);
- ASSERT(lpDisp);
- book.AttachDispatch(lpDisp);
- // 得到Worksheets
- sheets.AttachDispatch(book.GetWorksheets(),true);
- // 得到Worksheet
- sheet.AttachDispatch(sheets.GetItem(_variant_t((short)(1))));
- // 得到全部Cells
- range.AttachDispatch(sheet.GetCells(),true);
- // 往单元格A1里写入字符串数据,就像操作矩阵一样,第1行第1列
- range.SetItem(_variant_t((LONG)1),_variant_t((LONG)1),_variant_t("hello word!"));
- // 往单元格A2里写入时间数据
- range.AttachDispatch(sheet.GetRange(_variant_t("A2"), _variant_t("A2")),true);
- range.SetValue2(_variant_t("2011/02/15"));
- // 往单元格A3~A6里写入浮点数据
- range.AttachDispatch(sheet.GetRange(_variant_t("A3"), _variant_t("A6")),true);
- range.SetValue2(_variant_t((double)3.14));
- // 设置单元格的列宽为12
- range.AttachDispatch(sheet.GetRange(_variant_t("A1"), _variant_t("A1")),true);
- range.SetColumnWidth(_variant_t((long)12));
- // 所有单元格居中显示
- range.AttachDispatch(sheet.GetCells(),true);
- range.SetHorizontalAlignment(_variant_t((long)-4108)); // -4108:居中,-4131:靠左,-4152:靠右
- range.SetVerticalAlignment(_variant_t((long)-4108)); // -4108:居中,-4160:靠上,-4107:靠下
- // 读取单元格的数据,第4行第1列
- range.AttachDispatch(range.GetItem(_variant_t((long)(4)),_variant_t((long)(1))).pdispVal );
- vResult =range.GetValue(covOptional);
- switch (vResult.vt)
- {
- case VT_BSTR: // 字符串
- str=vResult.bstrVal;
- break;
- case VT_R8: // 8字节的数字
- str.Format("%f",vResult.dblVal);
- break;
- case VT_DATE: // 时间格式
- SYSTEMTIME st;
- VariantTimeToSystemTime(vResult.date, &st);
- break;
- case VT_EMPTY: // 单元格空的
- str="";
- break;
- }
- //MessageBox(str);
- Font ft; // 要插入excel类库里面的Font类,下面类似
- range.AttachDispatch(sheet.GetRange(_variant_t("A3"), _variant_t("A5")),true);
- ft.AttachDispatch(range.GetFont());
- ft.SetName(_variant_t("华文行楷")); // 字体
- ft.SetSize(_variant_t((long)12)); // 字号
- //ft.SetColorIndex(_variant_t((long)3)); // 字的颜色: 红色
- ft.SetColor( _variant_t((long) RGB(255, 0, 0) ) );
- ft.SetBold(_variant_t((long)1)); // 1:粗体,0:非粗体
- Interior it; // 底色设置
- range.AttachDispatch(sheet.GetRange(_variant_t("C3"), _variant_t("E6")),true);
- it.AttachDispatch(range.GetInterior());
- it.SetColorIndex(_variant_t((long)20)); // 底色设置为浅青色
- Borders borders; // 先设置区域内所有单元格的边框
- borders = range.GetBorders();
- borders.SetColorIndex(_variant_t((long)1));
- borders.SetLineStyle(_variant_t((long)1));
- borders.SetWeight(_variant_t((long)2));
- // 然后设置外边框
- // LineStyle=线型(1~13) Weight=线宽 ColorIndex=线的颜色(-4105为自动, 1为黑色)
- range.BorderAround(_variant_t((long)9),_variant_t((long)1),_variant_t((long)1),vtMissing);
- range.AttachDispatch(sheet.GetRange(_variant_t("C8"), _variant_t("D9")),true);
- // 合并单元格
- range.Merge(_variant_t((long)0));
- book.Save(); // 保存Excel的内容
- // app.SetDisplayAlerts(false); // 不弹出对话框询问是否保存
- // app.Quit(); // 退出
- //释放对象
- range.ReleaseDispatch();
- sheet.ReleaseDispatch();
- sheets.ReleaseDispatch();
- book.ReleaseDispatch();
- books.ReleaseDispatch();
- app.ReleaseDispatch();
- books.close();
- app.quit();
VC 操作Excel 2007的基本操作!
最新推荐文章于 2021-08-04 14:34:01 发布