test.cpp

c语言访问Excel数据库

#include <afxwin.h>
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \
no_namespace rename("EOF", "EndOfFile")
#include <iostream>
using namespace std;

void GetDataFromExcel(int **data);
int GetIndex(CString str);
void GetX1Data(int **data,CString str);
int X1Data[17][17];//县局X1到各个支局的距离
/*
以X1为0，Z1-Z16为坐标1-16
*/
void GetX1Data(int (&data)[17][17],CString str1,CString str2,int distance){
	char *cstr1 = (char*)(LPCSTR)str1;
	char *cstr2 = (char*)(LPCSTR)str2;
	if(cstr1[0] == 'Z' && cstr2[0] == 'Z' ){
		if(atoi(cstr1+1) <= 16 && atoi(cstr2+1) <= 16)
			data[atoi(cstr1+1)][atoi(cstr2+1)] = distance;
	}
	else if(cstr1[0] == 'X' && cstr2[0] == 'Z'){
		if(atoi(cstr2+1) <= 16)
			data[0][atoi(cstr2+1)] = distance;
	}
}
/*
建立二维数组，规定：
以D的坐标为0，Z1-Z73的坐标为1-73，1-X5的坐标为74-78
*/
int GetIndex(CString str){
	char *name = (char *)(LPCSTR)str;
	if(name[0] == 'Z'){
		//str.Format("%s",name+1);
		//AfxMessageBox(str);
		return atoi(name+1);
	}
	else if(name[0] == 'X'){
		return atoi(name+1)+73;
	}
	else if(name[0] == 'D'){
		return 0;
	}

	return -1;
}

//access post[][] to get data from excel
void GetDataFromExcel(int (&post)[79][79]){
	::CoInitialize(NULL);
	
	_RecordsetPtr m_pRecordset("ADODB.Recordset");
	_ConnectionPtr m_pConnection("ADODB.Connection");
	
	_bstr_t bstrSQL("select * from [Sheet1$]");

	int i,j;
	for(i=0;i<79;i++)
		for(j=0;j<79;j++)
			post[i][j] = 1000;
	for(i=0;i<17;i++){
		for(j=0;j<17;j++){
			X1Data[i][j] = 1000;
		}
	}

	try
	{
		//create Connection 
		m_pConnection.CreateInstance("ADODB.Connection");
		//set connect string，must be BSTR or _bstr_t Type
		_bstr_t strConnect= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=2007年D题附件     邮局间直达公路里程.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';" ; 
		m_pConnection->Open(strConnect,"","",adModeUnknown);
		if(m_pConnection==NULL)
			cerr<<"Link data ERROR!\n";

		//create recordset
		m_pRecordset.CreateInstance(__uuidof(Recordset));
		//get the record
		m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),
			adOpenDynamic,adLockOptimistic,adCmdText);
		
		_variant_t postOffice1,postOffice2,distance;

		while (!m_pRecordset->EndOfFile)
		{
			postOffice1 = m_pRecordset->GetCollect("邮局i");
			postOffice2 = m_pRecordset->GetCollect("邮局j");
			distance = m_pRecordset->GetCollect("直达公路里程（km）");
			
			//str = (TCHAR*)(_bstr_t)postOffice1;
			//str = postOffice1.bstrVal;//we can also use this statement to transform the data type
			
			/*
			int index = GetIndex(str);
			CString strIndex;
			strIndex.Format("%d",index);
			AfxMessageBox(strIndex);
			*/
			i = GetIndex((TCHAR*)(_bstr_t)postOffice1);
			j = GetIndex((TCHAR*)(_bstr_t)postOffice2);
			post[i][j] = atoi((char *)(_bstr_t)distance); 

			GetX1Data(X1Data,(TCHAR*)(_bstr_t)postOffice1,(TCHAR*)(_bstr_t)postOffice2,atoi((char *)(_bstr_t)distance));
			
			//print the excel data
			//cout<<(char*)(_bstr_t)postOffice1<<"\t"<<(char*)(_bstr_t)postOffice2<<"\t"<<(char*)(_bstr_t)distance<<endl;
			m_pRecordset->MoveNext(); 
		}
		
		m_pRecordset->Close(); 
	}
	catch(_com_error e)  
	{ 
		cerr << "\nERROR:" << (char*)e.Description();
	}
	if(m_pConnection->State)
		m_pConnection->Close();
	
	::CoUninitialize();
}

int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
	int a[79][79] ;
	int i,j;

	FILE *fp;
	fp = fopen("data.txt","w");
	if(!fp)
	{
		AfxMessageBox("Open Error!");
		return 0;
	}

	GetDataFromExcel(a);
	for(i=0;i<79;i++){
		for(j=0;j<79;j++){
			//cout<<a[i][j]<<"\t";
			fprintf(fp,"%d\t",a[i][j]);
		}
		//cout<<endl;
		fprintf(fp,"\n");
	}

	fclose(fp);

	char string[100];
	for(i=0;i<17;i++){
		for(j=0;j<17;j++){
			cout<<(X1Data[i][j]<1000?itoa(X1Data[i][j],string,10):"∞")<<"\t";
			//fprintf(fp,"%d\t",a[i][j]);
		}
		cout<<endl;
		//fprintf(fp,"\n");
	}
	return 0;
}