write the content of list control to a excel file

[kaya17]

hi, all, now i got another problem,
i have a list control, CListCtrl
and i want to print the contents out, but i learnt that a print can only print the screenshot ( a bitmap of the interface). in that case, if the list is not able to display all the records(from database) at one time, then, it will not be possible to print everything in the list. more over, if it is a screenshot, does it mean that the buttons which are in the dialog container also, will be printed out as well?


since the print from the dialog interface is not possible, i decided to save the contents in a excel file first, and print from the excel interface.

anybody can show me how to save the list contents in a excel file?

or any alternatives to perform a print?

any suggestions will be received gratefully!

=)
kaya

 

[Cagliostro]

>any suggestions
you may use ADO, DAO, ODBC as well as OLE.

Ion Filipski

 

[B00gyeMan]

If you are looking for a simple easy way to "print" list's contents you could write all the items in a CSV file (CSV=Comma Separated Values). This .CSV format is recognized by Excel and is really easy to use (just write comma separated column headers on the first line in the file. On the next lines, you write the values)

Ex.
ITEMNAME, ITEMINDEX, OWNER, COLOR
phone, 13, me, red
table, 166, Anne, brown
<etc....>

You also can skip the column headers if you don't want them.

P.S.: Again, this is a simple way of creating a file that can be used in Excel. For something more complex, use what IonFilipski told you. You could help by providing a brief list's contents description.

 

[kaya17]

hi, can u tell me how to write the list contents to a csv file?

because i am a new learner of MFC, not quite sure of how to write to a csv file...
thanks in advance!

kaya

 

[Zyrenthian]

The easiest way is to make a csv file in excel. Use commas, quotes and other characters. Here are some functions I used to parse a csv file a while back.

You can call it as:

CString str = "Hello,how,are,you";
int start = 0;
GetCommaDelimtedWord(str,start); // returns Hello
GetCommaDelimtedWord(str,start); // returns how
GetCommaDelimtedWord(str,start); // returns are
GetCommaDelimtedWord(str,start); // returns you

You will notice in the csv file, a comma is enclosed in quotes if it is part of the text in the field. This is the reason for "GetDataBetweenQuotes"

Good Luck
Matt

#define QUOTE _T('\"')
#define TWO_QUOTES _T("\"\"")

int FindEndQuote(CString buffer, int nStart)
{
	int loc = nStart;
	int levelsDeep = 0;

	if(buffer.GetAt(loc) == QUOTE)
	{
		loc++;
		int tmp = loc;
		tmp = buffer.Find(TWO_QUOTES,tmp);
		while(tmp != -1)
		{
			tmp +=2;
			loc = tmp;
			tmp = buffer.Find(TWO_QUOTES,tmp);
		}
		loc = buffer.Find(QUOTE,loc);
	}
	return loc;
}

CString GetDataBetweenQuotes(CString buffer, int& nStart)
{
	
	CString ret = _T("");

	// remove spaces
	while(nStart< buffer.GetLength() && buffer.GetAt(nStart) == _T(' '))
	{
		nStart++;
	}

	// stop processing
	if(nStart>=buffer.GetLength())
		return _T("");

	if(buffer.GetAt(nStart) == QUOTE)
	{
		int endQuoteLoc = FindEndQuote(buffer, nStart);
		ret = buffer.Mid(nStart+1,endQuoteLoc-nStart-1);
		
		// one line while... notice semicolon at end
		while(ret.Replace(_T("\"\""), _T("\"")));		
		nStart = endQuoteLoc+1;
	}
	return ret;
}

CString GetCommaDelimitedWord(CString buffer,int& nStart)
{
	// stop processing
	if(nStart>=buffer.GetLength())
		return _T("");

	// if we have a quoted string... want to pull what is enclosed
	CString ret = GetDataBetweenQuotes(buffer,nStart);

	// if we got something, return it
	if(!ret.IsEmpty())
	{
		while(nStart<buffer.GetLength() && (buffer.GetAt(nStart) == _T(' ') || buffer.GetAt(nStart) == _T(',')))
			nStart++;

		return ret;
	}

	int loc = buffer.Find(_T(","),nStart);

	if(loc == -1)
	{
		ret = buffer.Mid(nStart);
		nStart = buffer.GetLength();
	}
	else
	{
		ret = buffer.Mid(nStart,loc-nStart);
		nStart = loc+1;

		// no spaces
		while(nStart< buffer.GetLength() && buffer.GetAt(nStart) == _T(' '))
			nStart++;
	}


	ret.TrimLeft();
	ret.TrimRight();
	return ret;
}

 

[kaya17]

Hi, i think ur code is for reading a csv file, right? how to write a csv file?

KAYA

 

[Zyrenthian]

Right, it is for reading it Figured it may be of some use. If you write an excel csv file and open it with a text editor you can see the "inner workings" of how it is written. As I mentioned above, write one with commas and quotes etc so you can see how excel exports the csv file. Also, see BOOgyeMan's post. That is the basic gist of the csv file. The special cases come in when you have commas in your text.

Matt

 

[kaya17]

Hi, anybody can tell me how to use ADO, DAO, ODBC as well as OLE to write the list contents to an Excel file? as the deadline is approaching, i am very desperate now :'(

as i am a new learner in MFc, hope there would be some sample codes to demonstrate...

Your help is so much appreciated!

kaya

 

[kaya17]

or, can i use CFile to write it to a csv file?

 

[Cagliostro]

I think what is time me to write a new FAQ. I'll tell you how to use OLE, ADO, DAO and ODBC.
1. OLE, I'll explain strategically, shortly by using VBScript. The same thing is in C++, but you should know OLE API. To each VB object correspond an IDispatch*. Each property set/put as well as member function call is a IDispatch::Invoke. CreateObject in VB is a pear of CLSIDFromProgID/CoCreateInstance. To get interface IDispatch of Excel.Application you should QueryInterface IID_IDispatch. So, let's begin:

'excel.vbs, you will find in directory
'MyDocuments the file hello.xls
dim xls, sh, shs
dim oldalert
set xls = CreateObject("Excel.Application")
set sh = xls.WorkBooks.Add()
oldalert = xls.AlertBeforeOverwriting
xls.AlertBeforeOverwriting = false
sh.Sheets.Item(1).Range("A1") = "hello from JScript"
sh.SaveAs("hello.xls")
xls.AlertBeforeOverwriting = oldalert
xls.Quit()

To use ADO/DAO/ODBC is quite the same thing for any databases. So I will explain for Access. The same thing you will do for excel.

//using ODBC
#include<windows.h>
#include<iostream>
#include<string>
#include<sql.h>
#include<sqlext.h>
using namespace std;
int main()
{
    HENV hEnv;
    HDBC hDbc;
    RETCODE rc;
    int iOut;
    char strOut[256];
    char szDSN[256] = "driver={Microsoft Access Driver (*.mdb)};dbq=[c:\\db1.mdb];";
    //dsn samples:
    //"driver={Microsoft Access Driver (*.mdb)};dbq=[f:\\db1.mdb];"
    //"driver={SQL Server};pwd={password there};Server={server name};Database={dbname there}"
    //driver names for different databases:
    //{SQL Server}
    //{Microsoft ODBC for Oracle}
    //{Oracle in oracle9}
    //{Microsoft Access Driver (*.mdb)}
    //{MySQL ODBC 3.51 Driver}

    char* szSql = "select * from table1";
    rc = SQLAllocEnv(&hEnv);
    rc = SQLAllocConnect(hEnv, &hDbc);

    rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, 
        SQL_NTS, (unsigned char*)strOut, 
        255, (SQLSMALLINT*)&iOut, SQL_DRIVER_NOPROMPT);
    {
        int ival;
        char chval[128];
        int ret1;
        int ret2;
        HSTMT hStmt;
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt,(unsigned char*)szSql, SQL_NTS);//1
        //rc = SQLBindCol(hStmt, tab_column, tr_type, tr_value, tr_len, len_or_ind);
        rc = SQLBindCol(hStmt, 1, SQL_C_ULONG, &ival, 4, (SQLINTEGER*)& ret1);
		rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval, 128, (SQLINTEGER*)&ret2);
        rc = SQLExecute(hStmt); //2
        //if you have queries like drop/create/many update... 
        //instead of //1 //2 and //3 you could use
        //rc=SQLExecDirectA(hStmt,(unsigned char*)szSql,SQL_NTS);
        cout<< ">table:"<< endl;
        while(1) //3
        {
            rc = SQLFetch(hStmt);
            if(rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)break;
            cout<< "{"<< ival<<"}{"<< chval<< "}"<< endl;
        }
        rc=SQLFreeStmt(hStmt, SQL_DROP);
    }
    rc = SQLDisconnect(hDbc);
    rc = SQLFreeEnv(hEnv);
    return 0;
}

// not automated ADO 2ATL
#import "G:\Program Files\Common Files\System\ADO\msado15.dll"
#include "Debug\msado15.tlh"
#include<atlbase.h>
#include<iostream>

using namespace std;

int main()
{
    CoInitialize(0);
    {
        HRESULT hr = -1;
        CComPtr<IUnknown> cnnobj;
        CComPtr<IUnknown> rdsnobj;
        CComPtr<IUnknown> errobj;
        hr = cnnobj.CoCreateInstance(L"ADODB.Connection.2.7");
        ADODB::_Connection* cnn;
        ADODB::_Recordset* rds;
        ADODB::Error* err;
        BSTR berr;
        ADODB::Fields* fds;
        ADODB::Field* fd1;
        ADODB::Field* fd2;

        
        hr = cnnobj->QueryInterface(&cnn);
        hr = cnn->Open(L"driver={Microsoft Access Driver (*.mdb)};dbq=[c:\\db1.mdb];", L"", L"", -1);
        
        hr = rdsnobj.CoCreateInstance(L"ADODB.Recordset.2.7");
        hr = rdsnobj->QueryInterface(&rds);

        hr = errobj.CoCreateInstance(L"ADODB.Error.2.7");
        hr = errobj->QueryInterface(&err);

        hr = rds->put_ActiveConnection(_variant_t(cnn));
        hr = rds->Open(_variant_t("select * from Table1"), _variant_t(cnn),  ADODB::adOpenUnspecified, ADODB::adLockUnspecified, -1);

        VARIANT_BOOL eof;
        VARIANT_BOOL bof;
        hr = rds->MoveFirst();
        hr = rds->get_Fields(&fds);
        hr = fds->get_Item(_variant_t(L"X"), &fd1);
        hr = fds->get_Item(_variant_t(L"Y"), &fd2);
        BSTR namex;
        BSTR namey;
        hr = fd1->get_Name(&namex);
        hr = fd2->get_Name(&namey);

        cout<< "filends count: "<< fds->GetCount()<< endl;
        wcout<< L"<"<< namex<< L"><"<< namey<< L">"<< endl;
        VARIANT valx, valy;
        while(true)
        {
            hr = rds->get_EOF(&eof);
            hr = rds->get_BOF(&bof);
            if(eof)break;
            if(bof)break;
            fd1->get_Value(&valx);
            fd2->get_Value(&valy);
            wcout<< L"{"<< (wchar_t*)_bstr_t(_variant_t(valx))<< L"}{";
            wcout<< (wchar_t*)_bstr_t(_variant_t(valy))<< L"}"<< endl;

            hr = rds->MoveNext();
        }
        hr = rds->Close();
        hr = cnn->Close();

    }
	CoUninitialize();
	
	return 0;
}

//using DAO
#import "G:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
#include "Debug\dao360.tlh"
#include<atlbase.h>
#include<iostream>

using namespace std;

int main()
{
    CoInitialize(0);
    {
        HRESULT hr = -1;
        CComPtr<IUnknown> app;
        DAO::_DBEngine* eng;
        DAO::Workspace* wr;
        DAO::Database* db;
        DAO::Recordset* rs;
        VARIANT_BOOL eof;
        VARIANT_BOOL bof;
        DAO::Fields* fs;
        DAO::_Field* f1;
        DAO::_Field* f2;
        hr = app.CoCreateInstance(L"DAO.DBEngine.36");
        hr = app->QueryInterface(&eng);
        hr = eng->raw_CreateWorkspace(L"TestWorkSpace1", L"Admin", L"", _variant_t(DAO::dbUseJet), &wr);
        //open dbname, ifexclusive, ifreadonly, connect, retdb
        hr = wr->raw_OpenDatabase(_bstr_t(L"c:\\db1.mdb"), _variant_t(0), _variant_t(0), _variant_t(L""), &db);
        hr = db->raw_OpenRecordset(_bstr_t(L"select * from Table1"), 
            _variant_t(DAO::dbOpenDynaset), _variant_t(0), _variant_t(2), &rs);
        hr = rs->get_Fields(&fs);
        hr = fs->get_Item(_variant_t(0), &f1);
        hr = fs->get_Item(_variant_t(1), &f2);
        wcout<< L"<"<< (BSTR)f1->Name<< L"><" << f2->Name<< L">"<< endl;
        VARIANT valx, valy;
        while(true)
        {
            hr = rs->get_EOF(&eof);
            hr = rs->get_BOF(&bof);
            if(eof)break;
            if(bof)break;
            f1->get_Value(&valx);
            f2->get_Value(&valy);
            wcout<< L"{"<< (wchar_t*)_bstr_t(_variant_t(valx))<< L"}{";
            wcout<< (wchar_t*)_bstr_t(_variant_t(valy))<< L"}"<< endl;
            hr = rs->MoveNext();
        }

        hr = hr;
       
    }
    CoUninitialize();
	
	return 0;
}

Ion Filipski

 

[kaya17]

thanks a lot.

kaya