在文件中包含头文件
#includeodbcinst.h
#includeafxdb.h
VC导出CListCtrl数据到Excel两种方法转
#includecomdef.h
voidExportListToExcel(CListCtrl*pList,CStringsExcelFileCStringsSheetName)
{
CStringwarningStr;
冒险-VC导出CListCtrl数据到Excel两种方法转
if(pList-GetItemCount()0){
CDatabasedatabase;
CStringsDriver;
CStringsSql;
//检索是否安装有Excel驱动"MicrosoftExcelDriver(*.xls)"
sDriver=GetExcelDriver();
if(sDriver.IsEmpty())
{
//没有发现Excel驱动
MessageBox(NULL,"没有安装Excel!/n请先安装Excel软件才能使用导出功能!","提示",MB_OK|MB_ICONSTOP|MB_TOPMOST);
return;
}
//创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=/"%s/";DBQ=%s",sDriver,sExcelFile,sExcelFile);
//创建数据库(既Excel表格文件)
if(database.OpenEx(sSql,CDatabase::noOdbcDialog))
{
//创建表结构
inti;
LVCOLUMNcolumnData;
CStringcolumnName;
intcolumnNum=0;
CStringstrH;
CStringstrV;
sSql="";
strH="";
columnData.mask=LVCF_TEXT;
columnData.cchTextMax=100;
columnData.pszText=columnName.GetBuffer(100);
for(i=0;pList-GetColumn(i,&columnData);i++)
{
if(i!=0)
{
sSql=sSql+",";
strH=strH+",";
}
sSql=sSql+columnData.pszText+"TEXT";
strH=strH+columnData.pszText+"";
}
columnName.ReleaseBuffer();
columnNum=i;
sSql="CREATETABLE"+sSheetName+"("+sSql+")";
database.ExecuteSQL(sSql);
//*数据项
intnItemIndex;
for(nItemIndex=0;nItemIndexpList-GetItemCount();nItemIndex++)
{
strV="";
for(i=0;icolumnNum;i++)
{
if(i!=0)
{
strV=strV+",";
}
strV=strV+"'"+pList-GetItemText(nItemIndex,i)+"'";
}
sSql="INSERTINTO"+sSheetName
+"("+strH+")"
+"VALUES("+strV+")";
database.ExecuteSQL(sSql);
}
}
//关闭数据库
database.Close();
warningStr.Format("导出文件保存于%s!",sExcelFile);
MessageBox(NULL,warningStr,"提示",MB_OK|MB_ICONINFORMATION|MB_TOPMOST);
}
else
{
//没有数据
MessageBox(NULL,"没有数据,不能导出!","提示",MB_OK|MB_ICONWARNING|MB_TOPMOST);
}
}
//获取ODBC中Excel驱动函数
CStringGetExcelDriver()
{
charszBuf[2001];
WORDcbBufMax=2000;
WORDcbBufOut;
char*pszBuf=szBuf;
CStringsDriver="";
//获取已安装驱动的名称(涵数在odbcinst.h里)
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,&cbBufOut))
return"";
//检索已安装的驱动是否有Excel...
do
{
if(strstr(pszBuf,"Excel")!=0)
{
//发现!
sDriver=CString(pszBuf);
break;
}
pszBuf=strchr(pszBuf,'/0')+1;
}
while(pszBuf[1]!='/0');
returnsDriver;
}
方法2:使用Excel类型库接口的方式实现
voidExportListToExcel(CListCtrl*pList,CStringsExcelFile,CStringsSheetName)
{
_Worksheet m_wsExcelSingle;
Worksheet sm_wsExcels;
_Workbook m_wbExcelSingle;
Workbooks m_wbExcels;
_Application m_appExcel;
Rangem_rangeExcel;
COleVariantcovOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
if(!m_appExcel.CreateDispatch("Excel.Application",NULL))
{
MessageBox("创建Excel服务失败!","提示",MB_OK|MB_ICONWARNING);
return;
}
m_appExcel.SetVisible(false);
m_wbExcels.AttachDispatch(m_appExcel.GetWorkbooks(),true);
m_wbExcelSingle.AttachDispatch(m_wbExcels.Add(covOptional));
//得到Worksheets
m_wsExcels.AttachDispatch(m_wbExcelSingle.GetWorksheets(),true);
//删除多余的表
m_wsExcelSingle.AttachDispatch(m_wsExcels.GetItem(COleVariant((short)3)));
m_wsExcelSingle.Delete();
m_wsExcelSingle.AttachDispatch(m_wsExcels.GetItem(COleVariant((short)2)));
m_wsExcelSingle.Delete();
//表改名
m_wsExcelSingle.AttachDispatch(m_wsExcels.GetItem(COleVariant((short)1)));
m_wsExcelSingle.SetName(sSheetName);
m_rangeExcel.AttachDispatch(m_wsExcelSingle.GetUsedRange(),TRUE);
if(pList-GetItemCount()0)
{
intnItemCount,nColumnCount;
nColumnCount=pList-GetHeaderCtrl()-GetItemCount();
nItemCount=pList-GetItemCount();
inti;
LVCOLUMNcolumnData;
CStringcolumnName;
intcolumnNum=0;
CStringstrH;
CStringstrV;
columnData.mask=LVCF_TEXT;
columnData.cchTextMax=100;
columnData.pszText=columnName.GetBuffer(100);
//写列表头
for(i=0;pList-GetColumn(i,&columnData);i++)
{
m_rangeExcel.SetItem(_variant_t((long)(1)),_variant_t((long)(i+1)),COleVariant(columnData.pszText));
}
columnName.ReleaseBuffer();
//向Excel中写数据
for(i=0;inItemCount;i++)
{
for(intj=0;jnColumnCount;j++)
{
m_rangeExcel.SetItem(_variant_t((long)(i+2)),
_variant_t((long)(j+1)),
COleVariant(pList-GetItemText(i,j)));
}
}
//此方法对于不同的Excel版本参数个数可能不能,具体参看MSDN
m_wbExcelSingle.SaveAs(COleVariant(sExcelFile),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
0,
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing));
}
else
{
//没有数据
MessageBox("没有数据,不能导出!","提示",MB_OK|MB_ICONWARNING|MB_TOPMOST);
}
m_wbExcelSingle.Close(covOptional,COleVariant(sExcelFile),covOptional);
m_wbExcels.Close();
m_rangeExcel.ReleaseDispatch();
m_wsExcelSingle.ReleaseDispatch();
m_wsExcels.ReleaseDispatch();
m_wbExcelSingle.ReleaseDispatch();
m_wbExcels.ReleaseDispatch();
m_appExcel.ReleaseDispatch();
m_appExcel.Quit();
}