MFC学习之 将 list 表里的信息写入.txt文本 和 .xls EXCEL 文件中

1、将 listcontrol 表中的信息 用 Cfile 函数写入 .txt 中 ，用ODBC来实现excel的写操作。

afx_msg void OnBnClickedButtonExport();
int32_t ExportToTXT(CString& strFilePath);
int32_t ExportToXLS(CString& strFilePath);

在XXXDlg.cpp中

void CLogPageDlg::OnBnClickedButtonExport()
{
	//弹出保存文件对话框，输入保存的日志文件名，并选择保存类型 txt | xls
	SYSTEMTIME st;
	GetLocalTime(&st);

	CString str;
	str.Format(_T("%4d%02d%02d%02d%02d%02d.txt"), st.wYear, st.wMonth, st.wDay, st.wHour, st.wMinute, st.wSecond);

	CFileDialog dlg(FALSE, _T("txt"), str, OFN_EXPLORER | OFN_PATHMUSTEXIST, _T("Text Files (*.txt)|*.txt|WorkSheet Files (*.xls)|*.xls||"));
	if (IDOK != dlg.DoModal())
	{
		return;
	}

	//开始导出
	CString strPathName = dlg.GetPathName();
	CString strExt = dlg.GetFileExt();
	strExt.MakeLower();

	int32_t ret = 0;

	if (_T("txt") == strExt)//保存txt
	{
		ret = ExportToTXT(strPathName);
	}
	else if (_T("xls") == strExt)//保存xls
	{
		ret = ExportToXLS(strPathName);
	}

	return;
}

int32_t CLogPageDlg::ExportToTXT(CString& strFilePath)
{
	USES_CONVERSION;

	CFile mytxtFile;
	CString strCaption, strMsg;

	if(!mytxtFile.Open(strFilePath, CFile::modeCreate | CFile::modeReadWrite))
	{
		strCaption.LoadString(IDS_MESSAGEBOX_CAPTION);
		strMsg.LoadString(IDS_OPENFILE_FAILED);

		strMsg += strFilePath;

		MessageBoxDLg(strMsg, strCaption, MB_OK | MB_ICONERROR, this);

		return fail;
	}

	//计算列的宽度
	int32_t width[5] = {0};
	int32_t i = 0, j = 0, nLen = 0;
	int32_t nCount = m_logList.GetItemCount();

	//构造格式化字符串
	char format[512] = {0};
	char buf[1024] = {0};

	CString str0, str1, str2, str3, str4;

	str0.LoadString(IDS_COME);
	width[0] = strlen(T2A(str0));

	str1.LoadString(IDS_TIME);
	width[1] = strlen(T2A(str1));

	str2.LoadString(IDS_USERS);
	width[2] = strlen(T2A(str2));

	str3.LoadString(IDS_CONTENT);
	width[3] = strlen(T2A(str3));

	str4.LoadString(IDS_REMAKE);
	width[4] = strlen(T2A(str4));

	for (i = 0; i < 5; i++)
	{
		for (j = 0; j < nCount; j++)
		{
			nLen = strlen(T2A(m_logList.GetItemText(j, i)));
			if (nLen > width[i]) width[i] = nLen;
		}

		//多点间隔
		width[i] += 10;
	}

	//format
	sprintf_s(format, "%%-%ds %%-%ds %%-%ds %%-%ds %%-%ds \r\n", width[0], width[1], width[2], width[3], width[4]);

	//打印列名
	sprintf_s(buf, format, T2A(str0), T2A(str1), T2A(str2), T2A(str3), T2A(str4));
	mytxtFile.Write(buf, strlen(buf));

	for (i = 0; i < nCount; i++)
	{
		str0 = m_logList.GetItemText(i, 0);
		str1 = m_logList.GetItemText(i, 1);
		str2 = m_logList.GetItemText(i, 2);
		str3 = m_logList.GetItemText(i, 3);
		str4 = m_logList.GetItemText(i, 4);

		sprintf_s(buf, format, T2A(str0), T2A(str1), T2A(str2), T2A(str3), T2A(str4));
		mytxtFile.Write(buf, strlen(buf));
	}

	mytxtFile.Close();

	return success;
}

int32_t CLogPageDlg::ExportToXLS(CString& strFilePath)
{
	CDatabase database;
	CString sDriver = _T("MICROSOFT EXCEL DRIVER (*.XLS)"); // Excel安装驱动
	CString sSql;
	CString strCaption, strMsg;

	try
	{
		//检测是否安装了excel驱动程序
		if (!CheckExcelDriver())
		{
			strCaption.LoadString(IDS_MESSAGEBOX_CAPTION);
			strMsg.LoadString(IDS_EXCEL_DRIVER_FAILED);

			MessageBoxDLg(strMsg, strCaption, MB_OK | MB_ICONERROR, this);
			return fail;
		}

		//创建excel连接字符串
		sSql.Format(_T("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s"),
			sDriver, strFilePath, strFilePath);

		//打开xls文件失败，成功则创建xls数据库文件
		if(!database.OpenEx(sSql, CDatabase::noOdbcDialog))
		{
			strCaption.LoadString(IDS_MESSAGEBOX_CAPTION);
			strMsg.LoadString(IDS_OPEN_EXCEL_FAILED);

			MessageBoxDLg(strMsg, strCaption, MB_OK | MB_ICONERROR, this);
			return fail;
		}

		//创建表
		CString str0, str1, str2, str3, str4;

		str0.LoadString(IDS_COME);
		str1.LoadString(IDS_TIME);
		str2.LoadString(IDS_USERS);
		str3.LoadString(IDS_CONTENT);
		str4.LoadString(IDS_REMAKE);

		sSql.Format(_T("CREATE TABLE log (%s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)"), 
			str0, str1, str2, str3, str4);
		database.ExecuteSQL(sSql);

		//循环导入数据
		int32_t i = 0, j = 0;
		CString str5, str6, str7, str8, str9;
		int32_t nCount = m_logList.GetItemCount();
		for (i = 0; i < nCount; i++)
		{
			str5 = m_logList.GetItemText(i, 0);
			str6 = m_logList.GetItemText(i, 1);
			str7 = m_logList.GetItemText(i, 2);
			str8 = m_logList.GetItemText(i, 3);
			str9 = m_logList.GetItemText(i, 4);
			
			sSql.Format(_T("INSERT INTO log (%s, %s, %s, %s, %s) values('%s', '%s', '%s', '%s', '%s')"), 
				str0, str1, str2, str3, str4, str5, str6, str7, str8, str9);
			database.ExecuteSQL(sSql);
		}

		//关闭xls数据库
		database.Close();
	}
	catch(...)
	{
		//提示excel操作出现异常
		strCaption.LoadString(IDS_MESSAGEBOX_CAPTION);
		strMsg.LoadString(IDS_EXCEL_OPENFILE_FAILED);

		MessageBoxDLg(strMsg, strCaption, MB_OK | MB_ICONERROR, this);
		return fail;
	}

	return success;
}

 

BOOL CLogPageDlg::CheckExcelDriver()
{
	TCHAR szBuf[2001] = {0};
	WORD cbBufMax = 2000;
	WORD cbBufOut = 0;
	TCHAR *pszBuf = szBuf;

	//获取已安装驱动的名称
	if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
	{
		return FALSE;
	}

	//查找excel
	while (_tcslen(pszBuf) > 0)
	{
		if (NULL != _tcsstr(pszBuf, _T("Excel")))
		{
			return TRUE;
		}

		pszBuf += _tcslen(pszBuf) + 1;
	}

	return FALSE;
}