在查阅了大量资料后我成功实现了数据的导出,方法如下。
首先Ctrl+W打开类向导,新建一个类,并选择从Type Library添加。这里,由于我用的是Office ,因此添加的是Office安装路径下的Excel.exe(在Office 2000环境下添加的应该是Excel9.OLB) 。在弹出的Confirm Classes里选择_Application,Workbooks,_Workbook,Worksheets ,_Worksheet,Range ,Font 这几个类,并确定新生成的CPP和H文件的名称,这里我定为Excel.cpp,Excel.h。然后确定,你会发现类视图里已经有了刚才添加的这些新类。
由于我需要将datagrid里已经显示出来的Sql数据导出到Excel中,因此建立一个按钮,并设单击响应函数Output。我的datagrid控件变量名为m_datagrid,代码如下:
首先头文件里#include "Excel.h"
接下来Output函数:
void CEx::OnOutput() //导出按钮
{
// TODO: Add your control notification handler code here
_Application app; //程序对象
Workbooks books; //工作簿集合
_Workbook book; //工作簿
Worksheets sheets; //工作表集合
_Worksheet sheet; //工作表
Range range; //单元格范围
Font font; //字体
Range cols;
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
if( !app.CreateDispatch("Excel.Application") ){
this->MessageBox("无法创建Excel应用!");
return;}
books=app.GetWorkbooks();
book=books.Add(covOptional); //新建工作簿
sheets=book.GetSheets();
sheet=sheets.GetItem(COleVariant((short)1)); //以下是我的具体表
if(m_table=="Ylypro") //原料油性质导出
{
range=sheet.GetRange(COleVariant("A1"),COleVariant("a1")); //字段名设置
range.SetValue2(COleVariant("厂名"));cols=range.GetEntireColumn();cols.SetColumnWidth(_variant_t((long)8)); //设置Excel列宽
range=sheet.GetRange(COleVariant("b1"),COleVariant("b1"));
range.SetValue2(COleVariant("工艺"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("c1"),COleVariant("c1"));
range.SetValue2(COleVariant("日期"));cols=range.GetEntireColumn();cols.SetColumnWidth(_variant_t((long)12));
range=sheet.GetRange(COleVariant("d1"),COleVariant("d1"));
range.SetValue2(COleVariant("时间段"));cols=range.GetEntireColumn();cols.SetColumnWidth(_variant_t((long)12));
range=sheet.GetRange(COleVariant("e1"),COleVariant("e1"));
range.SetValue2(COleVariant("密度(20℃)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("f1"),COleVariant("f1"));
range.SetValue2(COleVariant("残碳 (%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("g1"),COleVariant("g1"));
range.SetValue2(COleVariant("硫 (%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("h1"),COleVariant("h1"));
range.SetValue2(COleVariant("氮 (ppm)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("i1"),COleVariant("i1"));
range.SetValue2(COleVariant("重金属Ni (ppm)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("j1"),COleVariant("j1"));
range.SetValue2(COleVariant("重金属V (ppm)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("k1"),COleVariant("k1"));
range.SetValue2(COleVariant("重金属Fe (ppm)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("l1"),COleVariant("l1"));
range.SetValue2(COleVariant("重金属Cu (ppm)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("m1"),COleVariant("m1"));
range.SetValue2(COleVariant("重金属Na (ppm)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("n1"),COleVariant("n1"));
range.SetValue2(COleVariant("馏程HK (℃)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("o1"),COleVariant("o1"));
range.SetValue2(COleVariant("馏程10% (℃)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("p1"),COleVariant("p1"));
range.SetValue2(COleVariant("馏程50% (℃)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("q1"),COleVariant("q1"));
range.SetValue2(COleVariant("馏程90% (℃)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("r1"),COleVariant("r1"));
range.SetValue2(COleVariant("馏程KK (℃)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("s1"),COleVariant("s1"));
range.SetValue2(COleVariant("350℃(%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("t1"),COleVariant("t1"));
range.SetValue2(COleVariant("500℃(%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("u1"),COleVariant("u1"));
range.SetValue2(COleVariant("C(%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("v1"),COleVariant("v1"));
range.SetValue2(COleVariant("H(%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("w1"),COleVariant("w1"));
range.SetValue2(COleVariant("饱和烃(vol.%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("x1"),COleVariant("x1"));
range.SetValue2(COleVariant("芳烃(vol.%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("y1"),COleVariant("y1"));
range.SetValue2(COleVariant("胶质(vol.%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("z1"),COleVariant("z1"));
range.SetValue2(COleVariant("沥青质(vol.%)"));cols=range.GetEntireColumn();cols.AutoFit();
range=sheet.GetRange(COleVariant("a1"),COleVariant("a1"));//Range复位
}
int ss,nn; //双循环将m_datagrid中的数据全部导出到Excel
double dtotal;
dtotal=0;
CString cc;
nn=m_adodc.GetRecordset().GetRecordCount();
CColumns cls;
cls=m_datagrid.GetColumns();
ss=cls.GetCount();
for(int i=0;i<nn;i++)
{
m_datagrid.SetRow(i);
for(int j=0;j<ss;j++)
{
m_datagrid.SetCol(j);
cc=m_datagrid.GetText();
range.SetItem(_variant_t((long)(i+2)),_variant_t((long)(j+1)),_variant_t(cc));
}
}
app.SetVisible(TRUE); //设置Excel表可见
app.SetUserControl(TRUE); //设置Excel表可被操作
}