How do I display an excel spreadsheet in a Microsft DataGrid Control?

[Guest_imported]

How do I display an excel spreadsheet in a Microsft DataGrid Control in a MFC application? I have put a DataGrid and a Microsft ADO Data Control on my dialog. The code below is called when a button on my dialog is clicked. The variable m_adoSource is the ADO Data Control, m_dataGrid is the DataGrid. The connection is made to the excel spreadsheet but I cannot get it to display in the DataGrid. How do I do this?

void CCleaningToolDlg::OnButtonOpenFile() 
{
	// TODO: Add your control notification handler code here
	
	//
	// get file name from user using standard file dialog
	//

	//
	// Connect to Excel spreadsheet
	//

	CString path = "E:\\CleaningTool\\TEST FILE.xls";
								
	CString sDriver = GetExcelDriver();

	CString connectionString ("Provider=MSDASQL; Driver={" + sDriver + "};" + path);
	m_adoSource.SetConnectionString(connectionString);

	//m_adoSource.SetRecordSource("SELECT *");

	// DEBUG stuff
	CString temp = m_adoSource.GetConnectionString();
	MessageBox(temp);

	//
	// Populate grid control with contents of spreadsheet
	//
	C_Recordset recordSet = m_adoSource.GetRecordset();
	
	m_adoSource.SetRecordSource("SELECT * ");

	m_adoSource.Refresh();
	m_dataGrid.UpdateData();
	m_dataGrid.Refresh();
}

// Get the name of the Excel-ODBC driver
// Contibuted by Christopher W. Backen - Thanx Christoper
CString CCleaningToolDlg::GetExcelDriver()
{
    char szBuf[2001];
    WORD cbBufMax = 2000;
    WORD cbBufOut;
    char *pszBuf = szBuf;
    CString sDriver;

    // Get the names of the installed drivers
    // (<odbcinst.h> and <afxdb.h> has to be included )
    if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
        return &quot;&quot;;
   
    // Search for the driver...
    do
    {
        if (strstr(pszBuf, &quot;Excel&quot;) != 0)
        {
            // Found !
            sDriver = CString(pszBuf);
            break;
        }
        pszBuf = strchr(pszBuf, '\0') + 1;
    }
    while (pszBuf[1] != '\0');

    return sDriver;
}

 

[williamu]

I don't see an Execute() in your code I'm not familiar with the ADO Control but I would suspect that you need to Execute() the SQL Statement.

HTH

William
Software Engineer

 

[Guest_imported]

Thanks William,

I've posted this on 4 different forums and searched the internet, documentation etc... and no-one seems to know how to do this. It's fairly easy to do it with an Access database or in Visual Basic (tons of visual basic documentation but I don't want to do it in Visual Basic).

I can't really afford to spend any more time on this so I'm giving Borland C++ Builder a go. I really dislike the debugger and IDE but it provides various Excel objects, and OleContainers that can just be dropped onto the form.

Thanks for all your help I really appreciate it.

Martin Patterson

 

[cnitadesigner]

I pretty much a novice to this but have you tried calling the file in a different way. Is it necessary to have double \\ after each word. For instance you have: "E:\\CleaningTool\\TEST FILE.xls" have you tried just using one \ as opposed to two. Can you try "E:\CleaningTool\TEST FILE.xls".

 

[williamu]

In this instance double \\ are required.

William
Software Engineer

 

[cnitadesigner]

Oh yeah ok because it's a reserved char. Thanks!! I forgot about that.

 

[Guest_imported]

Thanks for your help. I recieved an e-mail that describes the process of going about doing this. I was going in the wrong (more difficult) direction with the ADO stuff instead OLE automation should be used.

Unfortuantely I'm already part way through implementing this in Borland C++ Builder so I haven't had a chance to try it Visual C++. I've copied and pasted the information in the e-mail below if anyone is interested:

One way of manipulating spreadsheets from your application is to use
what
was OLE automation but now comes under COM. This is the way that
Microsoft
would reccomend. I have used it to create Word documents and it works
great.
There is an example of how to do this on the MSDN, try searching for
+automation +excel. Here is a snippet from MSDN which creates a
worksheet
and transfers data to it...

MFC example
Start a new dialog-based MFC AppWizard EXE project named "ExcelArrays."

Using ClassWizard, add the wrapper classes for the Excel type library.

Add a button to the dialog resource IDD_EXCELARRAYS_DIALOG and add the
following code to the button's handler in ExcelDataDlg.cpp:
#define NUMROWS 20

void CExcelArraysDlg::OnRun()
{

// For optional arguments
COleVariant vOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

// Instantiate Excel
_Application oApp;
oApp.CreateDispatch("Excel.Application"
if (!oApp)
{
AfxMessageBox("Cannot start Excel."
return;
}

//Get the Workbooks collection so that you can add a new
//workbook
Workbooks oBooks = oApp.GetWorkbooks();
_Workbook oBook = oBooks.Add(vOpt);

//Get the Worksheets collection of the new Workbook so that
//you can get the IDispatch for the first WorkSheet
Worksheets oSheets = oBook.GetWorksheets();
_Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));

//*** ADD DATA TO THE WORKSHEET

//Add Headers to Row 1 of the worksheet
Range oRange;
oRange = oSheet.GetRange(COleVariant("A1&quot, vOpt);
oRange.SetValue(COleVariant("Date&quot);
oRange = oSheet.GetRange(COleVariant("B1&quot, vOpt);
oRange.SetValue(COleVariant("Order #&quot);
oRange = oSheet.GetRange(COleVariant("C1&quot, vOpt);
oRange.SetValue(COleVariant("Amount&quot);
oRange = oSheet.GetRange(COleVariant("D1&quot, vOpt);
oRange.SetValue(COleVariant("Tax&quot);

//Create a safe array that is NUMROWS x 3 --
//column 1 will contain dates column 2 will contain strings
//and column 2 will contain numbers
COleSafeArray sa;
DWORD dwElements[2];
dwElements[0]= NUMROWS; //Number of rows
dwElements[1]= 3; //Number of columns
sa.Create(VT_VARIANT, 2, dwElements);

//Populate the safe array with the data
long index[2];
long lRow;
COleVariant vTemp;
COleDateTime vDateTime;
CString s;

for(lRow=0;lRow<=NUMROWS-1;lRow++)
{
index[0] = lRow;

//Fill the first column with dates
index[1] = 0;
vDateTime.SetDate(1999, rand()%12, rand()%28);
sa.PutElement(index, (COleVariant)vDateTime);

//Fill the second column with strings
index[1] = 1;
s.Format(&quot;ORDR%d&quot;, lRow+1000);
vTemp = s;
sa.PutElement(index, vTemp);

//Fill the third column with numbers
index[1] = 2;
vTemp = (long)rand();
sa.PutElement(index, vTemp);
}

//Fill a range, starting at A2 with the data in
//the safe array
oRange = oSheet.GetRange(COleVariant(&quot;A2&quot, vOpt);
oRange = oRange.GetResize(COleVariant((short)NUMROWS),
COleVariant((short)3));
oRange.SetValue(sa);
sa.Detach();

//*** ADD FORMULAS TO THE WORKSHEET

//Fill the fourth column with a formula to compute the
//sales tax. Note that the formula uses a &quot;relative&quot;
//cell reference so that it fills properly.
oRange = oSheet.GetRange(COleVariant(&quot;D2&quot, vOpt);
oRange = oRange.GetResize(COleVariant((long)NUMROWS),
COleVariant((long)1));
oRange.SetFormula(COleVariant(&quot;=C2*0.07&quot);

//*** FORMAT THE WORKSHEET
oRange = oSheet.GetRange(COleVariant(&quot;A1&quot, COleVariant(&quot;D1&quot);
Font oFont = oRange.GetFont();
oFont.SetBold(COleVariant((short)TRUE));//Apply Bold to Headers
oRange = oRange.GetEntireColumn();
oRange.AutoFit(); //AutoFit the columns 1:4

//Make Excel visible and give the user control
oApp.SetVisible(TRUE);
oApp.SetUserControl(TRUE);

}

Add the following includes to ExcelArraysDlg.cpp:
#include &quot;Excel8.h&quot; // or &quot;Excel9.h&quot; for Excel 2000
Modify CExcelArraysApp::InitInstance() in ExcelArrays.cpp to start COM
services:
if(!AfxOleInit())
{
AfxMessageBox(&quot;Cannot initialize COM services.&quot;
return FALSE;
}

Build and run the application. Click the button you added to the dialog
box.
When the Automation code finishes running, you see a new workbook in
Microsoft Excel containing data similar to that of Figure 15.