menu_contractflowerdlg.cpp

VC++和ACCESS使用ADO连接

	for (int i = 0; i < m_nItemCount; i++)
	{
		for (int n = nRec; n < m_Menu3_CF_List.GetItemCount(); n++) 
		{
			if (m_Menu3_CF_List.GetCheck(n))
			{
				SQL.Format("update Employee_InActiveSer_Table \
							set InActiveSer_ContractBegT = '%s', InActiveSer_ContractEndT = '%s' \
							where employee_IDCard = '%s'", 
							strBT, strET, m_Menu3_CF_List.GetItemText(n, 2));
				ADOdbo.OpenRecordset(SQL);
				nRec++;
				break;
			}
			nRec++;
		}
	}
	ADOdbo.CloseRecorset();
}

/*********************************************************************
函数说明:	  生成Excel
函数参数:	  
*********************************************************************/
void Menu_ContractFlowerDlg::OnMenu3CfBtnExcel() 
{
	if (!ContractRecToExcel())	// 生成 Excel 表
	{
		return;
	}		
}

/*********************************************************************
函数说明:	  生成excel文档
函数参数:	  
*********************************************************************/
BOOL Menu_ContractFlowerDlg::ContractRecToExcel()
{
	//变量定义
	_Application_Excel m_Application;    
	Workbooks_Excel    m_Workbooks;
	_Workbook_Excel    m_Workbook;
	Worksheets_Excel   m_Worksheets;
	_Worksheet_Excel   m_Worksheet;
	Range_Excel        m_range;

	LPDISPATCH  lpDisp;    
	COleVariant vResult;
	COleVariant covTrue((short)TRUE);
    COleVariant covFalse((short)FALSE);
    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

	// 因为在 InitInstance 中已经初始化过 COM 的动态连接库
	// 	if(!AfxOleInit())  
	// 	{
	//         AfxMessageBox("无法初始化COM的动态连接库!");
	//         return FALSE;
	// 	}

	//创建Excel 2000服务器(启动Excel)
	if(!m_Application.CreateDispatch("Excel.Application")) 
	{
		AfxMessageBox("无法启动Excel服务器!");
		return FALSE;
	}
	
	m_Application.SetVisible(FALSE);          // 使Excel不可见
	m_Application.SetUserControl(FALSE);      // 不允许其它用户控制Excel
	
	CFuncOper FunOper;
	CString strFile;
	strFile = FunOper.GetProgramPath() + "\\DocsWord\\合同修改记录(空).xls";
	// COleVariant varFilePath(strFile);

	// Workbooks 打开
	m_Workbooks.AttachDispatch(m_Application.GetWorkbooks(), TRUE);
	lpDisp = m_Workbooks.Open(strFile, covOptional, covOptional, covOptional, covOptional, 
								covOptional, covOptional, covOptional, covOptional, 
								covOptional, covOptional,covOptional, covOptional);
	
	// 得到 Workbook
	m_Workbook.AttachDispatch(lpDisp, TRUE);

	// 得到 Worksheets 
	m_Worksheets.AttachDispatch(m_Workbook.GetWorksheets(), TRUE);
	
	// 得到sheet1 
	m_Worksheet.AttachDispatch(m_Worksheets.GetItem(COleVariant("sheet1")), TRUE); 
	
	// 得到全部 Cells，此时, m_range 是 cells 的集合 
	m_range.AttachDispatch(m_Worksheet.GetCells(), TRUE);

	// 设置单元格的值
	int nRec = 0;
	for (int i = 2; i < m_nItemCount + 2; i++)
	{					
		for (int n = nRec; n < m_Menu3_CF_List.GetItemCount(); n++) 
		{
			if (m_Menu3_CF_List.GetCheck(n))
			{
				m_range.SetItem(COleVariant((long)i),COleVariant((long)1), COleVariant(m_Menu3_CF_List.GetItemText(n, 0)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)2), COleVariant(m_Menu3_CF_List.GetItemText(n, 1)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)3), COleVariant(m_Menu3_CF_List.GetItemText(n, 2)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)4), COleVariant(m_Menu3_CF_List.GetItemText(n, 3)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)5), COleVariant(m_Menu3_CF_List.GetItemText(n, 4)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)6), COleVariant(m_Menu3_CF_List.GetItemText(n, 5)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)7), COleVariant(m_Menu3_CF_List.GetItemText(n, 6)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)8), COleVariant(m_Menu3_CF_List.GetItemText(n, 7)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)9), COleVariant(m_Menu3_CF_List.GetItemText(n, 8)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)10),COleVariant(m_Menu3_CF_List.GetItemText(n, 9)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)11),COleVariant(m_Menu3_CF_List.GetItemText(n, 10)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)12),COleVariant(m_Menu3_CF_List.GetItemText(n, 11)));
				m_range.SetItem(COleVariant((long)i),COleVariant((long)13),COleVariant(m_Menu3_CF_List.GetItemText(n, 12)));				
				nRec++;
				break;
			}
			nRec++;
		}
	}		
	
	SYSTEMTIME SysTime;
	GetSystemTime(&SysTime);
	CString strSavePath;
	strSavePath.Format(m_strContractPath + "\\合同修改记录(%d-%d-%d).xls", 
						SysTime.wYear, SysTime.wMonth, SysTime.wDay);

	// 保存 
	m_Workbook.SaveAs(COleVariant(strSavePath), covOptional, covOptional, covOptional, 
						covOptional, covOptional, 0, covOptional, covOptional, covOptional, covOptional);
	
	// 关闭所有的book 退出 Excel 
	m_Workbook.Close(covOptional, COleVariant(strSavePath), covOptional);
	m_Workbooks.Close();      
	m_Application.Quit();

	MessageBox("合同修改记录的Excel表生成成功！\t", "提示", MB_OK | MB_ICONEXCLAMATION);
	return TRUE;
}

/*********************************************************************
函数说明:	  消息响应 NM_CLICK
函数参数:	  
*********************************************************************/
void Menu_ContractFlowerDlg::OnClickMenu3CfList(NMHDR* pNMHDR, LRESULT* pResult) 
{
	DWORD  dwPos = GetMessagePos();
	CPoint point(LOWORD(dwPos), HIWORD(dwPos));
	
	m_Menu3_CF_List.ScreenToClient(&point);
	
	LVHITTESTINFO lvinfo;
	lvinfo.pt    = point;
	lvinfo.flags = LVHT_ABOVE;
	
	UINT nFlag;
	int nItem = m_Menu3_CF_List.HitTest(point, &nFlag);
	if (nFlag == LVHT_ONITEMSTATEICON)	// 判断是否点在 checkbox 上
	{
		if (!m_Menu3_CF_List.GetCheck(nItem))
		{
			m_Menu3_CF_List.SetItemState(nItem, LVIS_SELECTED, LVIS_SELECTED);		// 设置成选中
			TRACE("Item %d is %d \n", nItem + 1, m_Menu3_CF_List.GetCheck(nItem));
		}
		else
		{
			m_Menu3_CF_List.SetItemState(nItem, ~LVIS_SELECTED, LVIS_SELECTED);		// 设置成取消选中
			TRACE("Item %d is %d \n", nItem + 1, m_Menu3_CF_List.GetCheck(nItem));
		}
	} 
	
	*pResult = 0;
}

/*********************************************************************
函数说明:	  设置路径
函数参数:	  
*********************************************************************/
void Menu_ContractFlowerDlg::OnMenu3CfBtnPath() 
{
	CFuncOper FunOper;
	CString strNewPath;
	strNewPath = FunOper.SettingSavePath(this, "请选择【合同修改记录表】的保存路径");
	if (strNewPath.IsEmpty())
	{
		strNewPath = m_strContractPath;
	}
	m_strContractPath = strNewPath;

	CString strPathMsg;
	strPathMsg.Format("（当前保存路径为：%s）", strNewPath);
	SetDlgItemText(IDC_MENU3_CF_STATIC_PATH, strPathMsg);

	CADOOperation ADOdbo;
	CString SQL;
	SQL.Format("update AccessInfo_Table set Infos_Path = '%s' where Index_Path = '合同路径'", strNewPath);
	ADOdbo.OpenRecordset(SQL);
	ADOdbo.CloseRecorset();
}

/*********************************************************************
函数说明:	  初始化显示设置路径
函数参数:	  
*********************************************************************/
void Menu_ContractFlowerDlg::InitShowContractPath()
{
	CADOOperation ADOdbo;
	CString SQL;
	SQL.Format("select Infos_Path from AccessInfo_Table where Index_Path = '合同路径'");
	ADOdbo.OpenRecordset(SQL);

	CString strField[] = {"Infos_Path"};
	CString strRetPath[1];
	ADOdbo.GetFieldsValue(strField, 1, strRetPath, FALSE);
	ADOdbo.CloseRecorset();

	m_strContractPath = strRetPath[0];
	CString strPathMsg;
	strPathMsg.Format("（当前保存路径为：%s）", strRetPath[0]);
	SetDlgItemText(IDC_MENU3_CF_STATIC_PATH, strPathMsg);
}