如何在ODBC中连接到Excel 2010? [英] How can connect to excel 2010 in ODBC ?

查看：113 发布时间：2019/6/21 5:09:35 C++ MFC

本文介绍了如何在ODBC中连接到Excel 2010?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好！

我正在使用Excel在List控件中使用ODBC程序.
运行程序时，我打开了excel文件.
向我显示了一个消息框.
它说无法登录ODBC Excel驱动程序".
我认为我在文件名(.xls)或(.xlsx)中犯了一个错误.
我在代码中写下了这个表格.我的代码有什么问题?

请给我任何建议和提示.

Hi All!

I''m making Excel ODBC program using in List control.
When I run the program, I opened the excel file.
One message box showed to me.
It said that "failed to log in ODBC Excel Driver".
I think I make a mistake in file name (.xls), or (.xlsx).
I write down this form in my code. What;s wrong with my code?

Please, give any advices and tips to me.

<pre><pre><br />
<br />
void CFormSecond::OnChange()<br />
{<br />
	CFormThird file;<br />
	CString strSheet = DEFAULT_SHEET;<br />
	CDatabase database;<br />
	CString sSql;<br />
	CString sItem1, sItem2;<br />
	CString sDriver; <br />
	CString sDsn;<br />
	CString sFile = "*.xlsx";	 //is it right?<br />
	<br />
	// Clear the contents of the listbox<br />
	m_List.DeleteAllItems();<br />
	<br />
	sDriver = GetExcelDriver();<br />
	if( sDriver.IsEmpty() )<br />
	{<br />
		// Blast! We didn큧 find that driver!<br />
		AfxMessageBox("No Excel ODBC driver found");<br />
		return;<br />
	}<br />
	<br />
	sDsn.Format("ODBC;DRIVER={%s};DSN='''';DBQ=%s",sDriver,sFile);<br />
<br />
	TRY<br />
	{<br />
		CRecordset recset( &database );<br />
<br />
		sSql.Format(_T("SELECT * FROM [%s$A1:IV65535]"), strSheet);<br />
		<br />
		int nFieldCount = recset.GetODBCFieldCount();<br />
		int nRowCount =0;<br />
		int nIndex = 0;<br />
		CDBVariant m_DBVariant;<br />
<br />
		// Browse the result<br />
		while( !recset.IsEOF() )<br />
		{<br />
			CString strFieldSum; <br />
			CString Add, Field,Value;<br />
			<br />
			recset.GetFieldValue((short)0, Add);<br />
			recset.GetFieldValue((short)1, Field);<br />
			recset.GetFieldValue((short)2, Value);<br />
<br />
			<br />
			if(Value.Right(2) == _T(".0"))<br />
<br />
			Value = Value.Left(Value.GetLength() - 2);<br />
			InsertRow(Add, Field,Value);<br />
			recset.MoveNext();<br />
		}<br />
<br />
		// Close the database<br />
		<br />
							 <br />
	}<br />
	CATCH(CDBException, e)<br />
	{<br />
		database.Close();<br />
		// A database exception occured. Pop out the details...<br />
		AfxMessageBox("Database error: "+e->m_strError);<br />
	}<br />
	END_CATCH;<br />
<br />
}</pre></pre>

CString CFormSecond::GetExcelDriver()<br />
{<br />
	TCHAR szBuf[2048]={0,};<br />
	WORD cbBufMax = 2047;<br />
	WORD cbBufOut;<br />
	LPCTSTR pszBuf = szBuf;<br />
	CString strExcelDriver;<br />
<br />
		if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))<br />
		return _T("");<br />
<br />
		do<br />
	{<br />
		if( _tcsstr(pszBuf, _T("Excel") ) != 0 )<br />
		{<br />
			//찾음<br />
			strExcelDriver = CString( pszBuf );<br />
			break;<br />
		}<br />
		pszBuf = _tcschr( pszBuf, _T(''\0'') ) + 1;<br />
	}<br />
	while( pszBuf[1] != _T(''\0'') );<br />
<br />
	return strExcelDriver;<br />
}

这是我打开并保存的代码

This is my open and save code

无效的CFormThird :: OnXlSave()
{
//BOOL bRead;

int mrowNum，mcolNum;

int rowNum = 1;
int colNum = 1;

char temp [10];

CXLEzAutomation XL(FALSE);

//m_SFileName ="MODBUS DATA FILE";

//칼럼데이터

XL.SetCellValue(colNum ++，1，地址");
XL.SetCellValue(colNum ++，1，"Field");
XL.SetCellValue(colNum ++，1，"Value");

rowNum = m_cList.GetItemCount();
//colNum = m_cList.GetItemCount();
colNum = 3;

//XL.SetCellValue(mcolNum + 1，mrowNum + 2，m_cList.GetItemText(mcolNum，mrowNum));
对于(mrowNum = 0; mrowNum< rowNum; mrowNum ++)
{
for(mcolNum = 0; mcolNum< colNum; mcolNum ++)
{
CString mText;
mText = m_cList.GetItemText(mrowNum，mcolNum);
XL.SetCellValue(mcolNum + 1，mrowNum + 2，mText);//m_cList.GetItemText(mcolNum，mrowNum));
}
}

char szFilter [] ="Excel 2003文件(* .xls)| * .xls | Excel 2010文件(* .xlsx)| * .xlsx |所有文件(*.*)| *.* ||";

//cel File(*.xls)| * .xls | (* .xlsx)| * .xlsx | 모든파일(*.*)| *.* | ;

CFileDialog DataWrite(FALSE，"Excel File(*.xlsx)"，"* .xlsx"，OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT，szFilter，NULL);
//창을Modal()로띄움

if(DataWrite.DoModal()== IDOK)
{
//XL.SaveFileAs(DataWrite.GetPathName());

m_SFileName = DataWrite.GetPathName();
//使用Excel SaveFile函数保存工作表
XL.SaveFileAs(m_SFileName);
XL.ReleaseExcel();

}

}

无效的CFormThird :: OnXlOpen()
{

char chThisPath [256];

CString strThisPath;

char szFilter [] ="Excel 2003文件(* .xls)| * .xls | Excel 2010文件(* .xlsx)| * .xlsx |所有文件(*.*)| *.* ||";

CFileDialog DataRead(TRUE，"Excel File(*.xlsx)"，m_SFileName，OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT，szFilter，NULL);

UpdateData(TRUE);

GetCurrentDirectory(256，chThisPath);
strThisPath.Format(％s \\％s.xlsx"，chThisPath，m_SFileName);
GetModuleFileName(NULL，chThisPath，256);

int nFileRead = DataRead.DoModal();

if(IDOK == nFileRead)
{

CXLEzAutomation XL;
CString sFile = DataRead.GetPathName();

CString地址，字段，值；

for(int i = 1;; i ++)
{
地址= XL.GetCellValue(1，i + 1);
field = XL.GetCellValue(2，i + 1);
值= XL.GetCellValue(3，i + 1);

如果(地址==")
休息；

m_cList.InsertItem(i-1，address);
m_cList.SetItemText(i-1,1，field);
m_cList.SetItemText(i-1,2，value);

}

if(！XL.OpenExcelFile(sFile))
{

//XL.SaveFileAs(strThisPath);
//m_cList.DeleteAllItems();
XL.ReleaseExcel();
返回；
}

}
}

void CFormThird::OnXlSave()
{
//BOOL bRead;

int mrowNum, mcolNum;

int rowNum = 1;
int colNum = 1;

char temp [10];

CXLEzAutomation XL(FALSE);

//m_SFileName ="MODBUS DATA FILE";

//칼럼 데이터

XL.SetCellValue(colNum++, 1, "Address");
XL.SetCellValue(colNum++, 1, "Field");
XL.SetCellValue(colNum++, 1, "Value");

rowNum= m_cList.GetItemCount();
// colNum = m_cList.GetItemCount();
colNum = 3;

// XL.SetCellValue(mcolNum+1,mrowNum+2,m_cList.GetItemText(mcolNum,mrowNum));
for (mrowNum=0; mrowNum<rowNum ; mrowNum++)
{
for(mcolNum=0; mcolNum<colNum; mcolNum++)
{
CString mText;
mText = m_cList.GetItemText(mrowNum,mcolNum);
XL.SetCellValue(mcolNum+1,mrowNum+2,mText);//m_cList.GetItemText(mcolNum,mrowNum));
}
}

char szFilter[] = "Excel 2003 File (*.xls)|*.xls| Excel 2010 File (*.xlsx)|*.xlsx| All Files (*.*)|*.*||";

//cel File(*.xls) |*.xls| (*.xlsx) |*.xlsx | 모든파일(*.*) |*.*| ";

CFileDialog DataWrite(FALSE, "Excel File(*.xlsx)", "*.xlsx", OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter, NULL);
// 저장 창을 Modal()로 띄움

if(DataWrite.DoModal() == IDOK)
{
//XL.SaveFileAs(DataWrite.GetPathName());

m_SFileName = DataWrite.GetPathName();
//Use Excel SaveFile function to save worksheet
XL.SaveFileAs(m_SFileName);
XL.ReleaseExcel();

}

}

void CFormThird::OnXlOpen()
{

char chThisPath[256];

CString strThisPath ;

char szFilter[] = "Excel 2003 File (*.xls)|*.xls| Excel 2010 File (*.xlsx)|*.xlsx| All Files (*.*)|*.*||";

CFileDialog DataRead(TRUE, "Excel File(*.xlsx)",m_SFileName, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter, NULL);

UpdateData(TRUE);

GetCurrentDirectory(256, chThisPath);
strThisPath.Format("%s\\%s.xlsx",chThisPath,m_SFileName);
GetModuleFileName( NULL, chThisPath, 256);

int nFileRead = DataRead.DoModal();

if(IDOK == nFileRead)
{

CXLEzAutomation XL;
CString sFile = DataRead.GetPathName();

CString address, field, value;

for(int i=1; ; i++)
{
address=XL.GetCellValue(1,i+1);
field=XL.GetCellValue(2,i+1);
value=XL.GetCellValue(3,i+1);

if ( address == "")
break;

m_cList.InsertItem(i-1,address);
m_cList.SetItemText(i-1,1,field);
m_cList.SetItemText(i-1,2,value);

}

if(!XL.OpenExcelFile(sFile))
{

// XL.SaveFileAs(strThisPath);
// m_cList.DeleteAllItems();
XL.ReleaseExcel();
return;
}

}
}

推荐答案

A1:IV65535])，strSheet); int nFieldCount = recset.GetODBCFieldCount(); int nRowCount = 0; int nIndex = 0; CDBVariant m_DBVariant; //浏览结果 while(！recset.IsEOF()) { CString strFieldSum; CString Add，Field，Value; recset.GetFieldValue((short)0，Add); recset.GetFieldValue((short)1，Field); recset.GetFieldValue((short)2，Value); if(Value.Right(2)== _T(.0")) 值= Value.Left(Value.GetLength()-2); InsertRow(Add，Field，Value); recset.MoveNext(); } //关闭数据库 } CATCH(CDBException，e) { database.Close(); //发生数据库异常.弹出细节... AfxMessageBox(数据库错误:" + e-> m_strError); } END_CATCH; }</pre></pre>

A1:IV65535]"), strSheet); int nFieldCount = recset.GetODBCFieldCount(); int nRowCount =0; int nIndex = 0; CDBVariant m_DBVariant; // Browse the result while( !recset.IsEOF() ) { CString strFieldSum; CString Add, Field,Value; recset.GetFieldValue((short)0, Add); recset.GetFieldValue((short)1, Field); recset.GetFieldValue((short)2, Value); if(Value.Right(2) == _T(".0")) Value = Value.Left(Value.GetLength() - 2); InsertRow(Add, Field,Value); recset.MoveNext(); } // Close the database } CATCH(CDBException, e) { database.Close(); // A database exception occured. Pop out the details... AfxMessageBox("Database error: "+e->m_strError); } END_CATCH; }</pre></pre>

CString CFormSecond::GetExcelDriver()<br />
{<br />
	TCHAR szBuf[2048]={0,};<br />
	WORD cbBufMax = 2047;<br />
	WORD cbBufOut;<br />
	LPCTSTR pszBuf = szBuf;<br />
	CString strExcelDriver;<br />
<br />
		if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))<br />
		return _T("");<br />
<br />
		do<br />
	{<br />
		if( _tcsstr(pszBuf, _T("Excel") ) != 0 )<br />
		{<br />
			//찾음<br />
			strExcelDriver = CString( pszBuf );<br />
			break;<br />
		}<br />
		pszBuf = _tcschr( pszBuf, _T(''\0'') ) + 1;<br />
	}<br />
	while( pszBuf[1] != _T(''\0'') );<br />
<br />
	return strExcelDriver;<br />
}

这是我打开并保存的代码

This is my open and save code

您可以在此处 [

You can verify your connection string from here[^] to make sure nothing is wrong with it.

您的代码问

CString sFile = "*.xlsx";    //is it right?

答案是否"，您无法打开所有XLSX文件".您必须一次提出一个文件名.

The answer is "no", you can''t have it open "All XLSX Files". You have to come up with the file names one at a time.

这篇关于如何在ODBC中连接到Excel 2010?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

如何在ODBC中连接到Excel 2010? [英] How can connect to excel 2010 in ODBC ?

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

如何在ODBC中连接到Excel 2010? [英] How can connect to excel 2010 in ODBC ?

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭