VC++中EXCEL导出->自动保存

迷南。 2022-06-03 07:50 208阅读 0赞

直接上代码

//查询数据库，导出EXCEL
    	ADOConn v_cAdoConn;												   
    	_RecordsetPtr v_pRecordset;
    	v_cAdoConn.OnInitADOConn(theApp.a_iDatabase_Type,theApp.a_sDatabase_Ip,theApp.a_sDatabase_Name,theApp.a_sDatabase_User,theApp.a_sDatabase_Password);
    	int v_iIndex = 2;
    	CString v_sBSC, v_sLevel, v_sInfo, v_sStart,v_sDel,v_sDetail;
    	//CString v_sSql="Select zBSC,zLevel,zInfo,zStart,zDel,zDetail from 历史告警表 where zStart between '"+v_sStartTime+"' and '"+v_sDelTime+"' order by zStart desc";
    	CString v_sSql="Select zBSC,zLevel,zInfo,zStart,zDel,zDetail from 历史告警表 order by zStart desc";
    	//用m_ExlApp对象创建Excel2003进程 
    	/************************************************************************/
    	::OleInitialize(NULL);
    
    	COleVariant vOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    
    	Range m_ExlRge; 
    	_Worksheet m_ExlSheet; 
    	Worksheets m_ExlSheets; 
    	_Workbook m_ExlBook; 
    	Workbooks m_ExlBooks; 
    	_Application m_ExlApp; 
    
    	if(!m_ExlApp.CreateDispatch("Excel.Application",NULL)) 
    	{ 
    		AfxMessageBox("创建Excel服务失败!"); 
    		return; 
    	} 
    
    	m_ExlBooks.AttachDispatch(m_ExlApp.GetWorkbooks(),TRUE); 
    
    	m_ExlBook.AttachDispatch(m_ExlBooks.Add((_variant_t)""),TRUE);			//加载EXCEL模板 如为空加载一个新的excel
    
    	m_ExlSheets.AttachDispatch(m_ExlBook.GetSheets(),TRUE);						//加载Sheet页面 
    
    	m_ExlSheets.AttachDispatch(m_ExlBook.GetWorksheets(),true);				//得到sheet1	   
    
    	m_ExlSheet.AttachDispatch(m_ExlSheets.GetItem(_variant_t("sheet1")),true); 
    
    	//合并第一行单元格A1至G1// 
    	//加载要合并的单元格   
    	m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(_variant_t("A1"),_variant_t("F1")),TRUE);   
    	m_ExlRge.Merge(_variant_t((long)0)); 
    
    	/************************************************************************/
    	设置表格内容 
    	m_ExlRge.AttachDispatch(m_ExlSheet.GetCells(),TRUE);//加载所有单元格 
    	m_ExlRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("告警信息定时导出，导出时间："+v_tTime.Format("%Y-%m-%d %H:%M:%S"))); 
    
    	m_ExlRge.SetItem(_variant_t((long)2),_variant_t((long)1),_variant_t("网元")); 
    	m_ExlRge.SetItem(_variant_t((long)2),_variant_t((long)2),_variant_t("告警级别")); 
    	m_ExlRge.SetItem(_variant_t((long)2),_variant_t((long)3),_variant_t("告警信息"));
    	m_ExlRge.SetItem(_variant_t((long)2),_variant_t((long)4),_variant_t("发生时间"));
    	m_ExlRge.SetItem(_variant_t((long)2),_variant_t((long)5),_variant_t("恢复时间")); 
    	m_ExlRge.SetItem(_variant_t((long)2),_variant_t((long)6),_variant_t("详细信息"));
    
    	v_pRecordset =  v_cAdoConn.GetRecordSet((_bstr_t)v_sSql);
    	while (!v_pRecordset->adoEOF && this->m_bStopShow)
    	{     
    
    		if(v_pRecordset->Fields->GetItem("zBSC")->ActualSize>0) 
    			v_sBSC = v_pRecordset->GetCollect("zBSC").bstrVal;          
    		else
    			v_sBSC = "";
    
    		if(v_pRecordset->Fields->GetItem("zLevel")->ActualSize>0) 
    			v_sLevel = v_pRecordset->GetCollect("zLevel").bstrVal;          
    		else
    			v_sLevel = "";
    
    		if(v_pRecordset->Fields->GetItem("zInfo")->ActualSize>0) 
    			v_sInfo = v_pRecordset->GetCollect("zInfo").bstrVal;           
    		else
    			v_sInfo = "";
    
    		if(v_pRecordset->Fields->GetItem("zStart")->ActualSize>0) 
    			v_sStart = v_pRecordset->GetCollect("zStart").bstrVal;          
    		else
    			v_sStart = "";
    
    		if(v_pRecordset->Fields->GetItem("zDel")->ActualSize>0) 
    			v_sDel = v_pRecordset->GetCollect("zDel").bstrVal;          
    		else
    			v_sDel = "";
    
    		if(v_pRecordset->Fields->GetItem("zDetail")->ActualSize>0) 
    			v_sDetail = v_pRecordset->GetCollect("zDetail").bstrVal;          
    		else
    			v_sDetail = "";
    
    		//写入EXCEL
    		v_iIndex++;
    		m_ExlRge.SetItem(_variant_t((long)v_iIndex),_variant_t((long)1),_variant_t(v_sBSC)); 
    		m_ExlRge.SetItem(_variant_t((long)v_iIndex),_variant_t((long)2),_variant_t(v_sLevel)); 
    		m_ExlRge.SetItem(_variant_t((long)v_iIndex),_variant_t((long)3),_variant_t(v_sInfo));
    		m_ExlRge.SetItem(_variant_t((long)v_iIndex),_variant_t((long)4),_variant_t(v_sStart));
    		m_ExlRge.SetItem(_variant_t((long)v_iIndex),_variant_t((long)5),_variant_t(v_sDel));
    		m_ExlRge.SetItem(_variant_t((long)v_iIndex),_variant_t((long)6),_variant_t(v_sDetail));
    		v_pRecordset->MoveNext();
    		//导出成功，则置为TRUE
    		this->m_bExcel=true;
    	}
    	
    	/************************************************************************/
    	//生成EXCEL名称
    	/*
    	CString v_sSavePath;
    	CString v_sCurrentDate=v_tTime.Format("%Y_%m_%d");
    	CString v_sDirectoty=v_sPath+"\\EXCEL_EXPORT";
    	if( !CreateDirectory((LPCTSTR)v_sDirectoty, NULL) )
    		v_sSavePath=v_sDirectoty+"\\"+v_sCurrentDate+".xlsx";
    
    	m_ExlBook.SaveCopyAs(COleVariant((LPCTSTR)v_sSavePath));
    	*/
    
    	m_ExlSheet.SaveAs("c:\\x.xlsx", vOpt, vOpt,vOpt,vOpt,vOpt,vOpt,vOpt, vOpt );
    
    
    	/************************************************************************/
    	//释放对象（相当重要！） 
    	m_ExlRge.ReleaseDispatch(); 
    	m_ExlSheet.ReleaseDispatch(); 
    	m_ExlSheets.ReleaseDispatch(); 
    	m_ExlBook.ReleaseDispatch(); 
    	m_ExlBooks.ReleaseDispatch(); 
    	//m_ExlApp一定要释放，否则程序结束后还会有一个Excel进程驻留在内存中，而且程序重复运行的时候会出错
    	m_ExlApp.Quit();
    	m_ExlApp.ReleaseDispatch(); 
    	::OleUninitialize();
    	//关闭数据库
    	v_cAdoConn.ExitConnect();}