1、添加头文件
#include "stdafx.h"
#include "testExcel.h"
#include "testExcelDlg.h"
#include "afxdialogex.h"
#include "CApplication.h"
#include "CRanges.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
#include "CRange.h"
2、初始化com组件
//初始化COM组件
if (!AfxOleInit())
{
AfxMessageBox(_T("无法初始化COM的动态链接库"));
return FALSE;
}
3、关键部分
void CtestExcelDlg::OnBnClickedButtonWrite()
{
// TODO: Add your control notification handler code here
CApplication app;
//创建Excel服务器(启动Excel)
if (!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("无法启动Excel服务器");
return;
}
//设置Excel的状态
//app.put_Visible(TRUE); //使Excel可见
app.put_UserControl(TRUE); //允许其他用户控制Excel
//vc对Excel的操作
//定义变量
CWorkbooks books;
CWorkbook book;
CWorksheets sheets;
CWorksheet sheet;
CRange range;
LPDISPATCH lpDisp;
COleVariant vResult;
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
/*________________________
打开已有的Excel文件
books.AttachDispatch(app.get_Workbooks());
//books=app.get_Workbooks();
lpDisp=books.Open("D://1.xls",covOptional,covOptional,covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional);
*/
//新建一个.xls文件,并写入数据
books.AttachDispatch(app.get_Workbooks());//得到Workbooks
book=books.Add(covOptional);//得到Workbook
//分别向每个单元格添加数据
//Excel2000用函数SetValue()即可,Excel2003用函数SetValue2()
sheets=book.get_Worksheets();
sheet=sheets.get_Item(COleVariant((short)1));
range=sheet.get_Range(COleVariant(_T("A1")),covOptional); //单个
range.put_Value2(COleVariant(_T("hanhan")));
m_excelTxt=range.get_Value2();
UpdateData(FALSE); //显示在文本框中
range=sheet.get_Range(COleVariant(_T("B5")),COleVariant(_T("D17"))); //从B5到D17即从B第五行到D第17行矩形区域写东西。
range.put_Value2(COleVariant(_T("Order"))); //添加数据
//range=sheet.get_Range(COleVariant("A1"),covOptional);;//获取A1 range
//range.put_Value2(COleVariant(Date)); //添加数据
//range=sheet.get_Range(COleVariant("B1"),covOptional); //获取B1 Range
//range.put_Value2(COleVariant("Order")); //添加数据
//range=sheet.get_Range(COleVariant("C1"),covOptional);
//range.put_Value2(COleVariant("Amount")); //添加数据
//range=sheet.get_Range(COleVariant("D1"),covOptional);//获取D1 Range
//range.put_Value2(COleVariant("Tax")); //添加数据
// range.AutoFit(); //自动适合尺寸
CString strFile=_T("d:\\8.xlsx");
//将已建的xls文件另存为
// COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
book.SaveAs(COleVariant(strFile),covOptional,covOptional,covOptional,covOptional,
covOptional,0,covOptional,covOptional,covOptional,covOptional,covOptional);
//关闭Excel服务
// COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
book.put_Saved(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();
app.Quit();//退出application
app.ReleaseDispatch();//释放application
}