How to read/include excel file (*.xls ) to VC++ - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

How to read/include excel file (*.xls ) to VC++

s07452s07452 Posts: 4Member
Excuse me:
I'd like to know How can I read/include Excel file (.xls) into VC++ ?

I mean, is there a API or class to access it ?

thankx
«1

Comments

  • dwccgcdwccgc Posts: 576Member
    : Excuse me:
    : I'd like to know How can I read/include Excel file (.xls) into VC++ ?
    :
    : I mean, is there a API or class to access it ?
    :
    : thankx
    :
    Maybe this site can help you.
    http://www.codeproject.com/database/excel_odbc.asp
  • TribalTechTribalTech Posts: 74Member
    : Excuse me:
    : I'd like to know How can I read/include Excel file (.xls) into VC++ ?
    :
    : I mean, is there a API or class to access it ?
    :
    : thankx
    :
    Yes, I have done this before. It involves connecting to Excel thru ActiveX. You can manipulate Excel spread sheets via your C++ application. Want to know more ?
  • s07452s07452 Posts: 4Member
    yeap ^_^
    thank again!
  • TribalTechTribalTech Posts: 74Member
    : yeap ^_^
    : thank again!
    :
    I have the project at work. I'll zip the project. I'll need an e-mail address to send it to. The project will be a bare bones ActiveX connection exercise. There is enough functionality in it to understand the fundamentals of doing what you need to do. It is not the simplest thing in the world, but do-able. I have used this project as a starting point for a few applications and then expanded it into my new application. This should get you up the learning curve pretty fast.
    ->TribalTech<-
  • s07452s07452 Posts: 4Member
    I am deeply appreciated your help ^_^

    [email protected]
  • dshandshan Posts: 6Member
    : : yeap ^_^
    : : thank again!
    : :
    : I have the project at work. I'll zip the project. I'll need an e-mail address to send it to. The project will be a bare bones ActiveX connection exercise. There is enough functionality in it to understand the fundamentals of doing what you need to do. It is not the simplest thing in the world, but do-able. I have used this project as a starting point for a few applications and then expanded it into my new application. This should get you up the learning curve pretty fast.
    : ->TribalTech<-
    :

    Hi, TribalTech

    I have the same problem and like to learn the ActiveX way to read Excel file and insert data into SQL. Basically I need read excel file to VC++.

    Thank you a lot. Can you also zip your project and send to me? I am not sure how big is your project? I worry the email system can not handle this big size. You can also cc to this following account: [email protected]

    dshan
  • getcodegetcode Posts: 1Member
    Can u send me ur project to [email protected] .

    does it consume a lot of time to get the connection (consider i am a novice). Does it allow to read nd write on to the excel file.

    thnx u



    : I have the project at work. I'll zip the project. I'll need an e-mail address to send it to. The project will be a bare bones ActiveX connection exercise. There is enough functionality in it to understand the fundamentals of doing what you need to do. It is not the simplest thing in the world, but do-able. I have used this project as a starting point for a few applications and then expanded it into my new application. This should get you up the learning curve pretty fast.
    : ->TribalTech<-
    :

  • TribalTechTribalTech Posts: 74Member
    : Thank you a lot. Can you also zip your project and send to me? I am not sure how big is your project? I worry the email system can not handle this big size. You can also cc to this following account: [email protected]
    :
    : dshan
    :
    Yes, I can zip the project to you. Should I use the [email protected] account or something else. The project is 660k.

    TribalTech
  • TribalTechTribalTech Posts: 74Member
    : Can u send me ur project to [email protected] .
    :
    : does it consume a lot of time to get the connection (consider i am a novice). Does it allow to read nd write on to the excel file.
    :
    : thnx u
    Once your application loads Excel and an .xls file, accessing spreadsheets is relatively quick. You can read and write to spreadsheets. In fact you can do almost anything you would do as if you were sitting in front of Excel working it directly.

    TribalTech
  • s07452s07452 Posts: 4Member
    //there is just a example...enjoy it..
    // The system calls this to obtain the cursor to display while the user drags
    // the minimized window.
    HCURSOR CAutoProjectDlg::OnQueryDragIcon()
    {
    return (HCURSOR) m_hIcon;
    }

    void CAutoProjectDlg::OnRun()
    {
    // TODO: Add your control notification handler code here

    TRACE("test 111");
    _tprintf(_T("Oops - hit an error!
    "));

    //OLE variant for optional
    COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

    _Application objApp;
    _Workbook objBook; //COleDispatchDriver
    Workbooks objBooks;
    Worksheets objSheets;
    _Worksheet objSheet;
    Range objRange;
    VARIANT ret; //struct

    //instantiate excel and open an existing workbook
    objApp.CreateDispatch("Excel.Application");
    objBooks= objApp.GetWorkbooks();
    objBook= objBooks.Open("C:\test.xls",
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional);
    objSheets= objBook.GetWorksheets();
    objSheet= objSheets.GetItem(COleVariant((short)1));

    //get the range object for which you wish to retrieve the data
    // and then retrieve the data (as a variant array , ret)
    objRange= objSheet.GetRange(COleVariant("A1"), COleVariant("C8"));
    ret= objRange.GetValue();

    //create the SAFEARRAY from the Variant ret
    COleSafeArray sa(ret);

    //determine the array's dimension
    long lNumRows;
    long lNumCols;

    //sa.GetUBound(0, &lz); //無效的索引
    sa.GetUBound(1, &lNumRows); //retrieve the upper bound for any dimension of a safe array
    sa.GetUBound(2, &lNumCols); //範圍由上面的_Worksheet.GetRange()決定

    //display the elements in the SAFEARRAY
    long index[2];
    VARIANT val;
    int r, c;

    s_test1 testf[9];
    double buf[1];
    char sbuf[20];
    LPCTSTR sstr; //pointer
    CString str1(""); //class
    char* pszFileName = "c:\11.dat";
    CFile myFile;
    CFileException fileException;
    CArchive ar( &myFile, CArchive::store);

    if ( !myFile.Open( pszFileName, CFile::modeCreate |
    CFile::modeReadWrite ), &fileException )
    {
    TRACE( "Can't open file %s, error = %u
    ",
    pszFileName, fileException.m_cause );
    }


    TRACE("Contents of SafeArray
    ");
    TRACE("=====================
    ");
    for (c=1; c<=lNumCols; c++)
    {
    TRACE(" Col %d", c);
    }
    TRACE("
    ");
    for (r=1; r<=lNumRows; r++)
    {
    TRACE("Row %d", r);
    for (c=1; c<=lNumCols; c++)
    {
    index[0]= r;
    index[1]= c;
    sa.GetElement(index, &val); //Retrieves a single element of the safe array
    switch(val.vt)
    {
    case VT_R8:
    {
    TRACE(" %1.2f", val.dblVal);
    //testf[r].m_int1= val.dblVal;
    //buf[0]= val.dblVal;
    //myFile.Write(buf, sizeof(buf));
    if( ar.IsStoring() )
    ar<< val.dblVal;
    break;
    }
    case VT_BSTR:
    {
    TRACE(" %s", (CString)val.bstrVal);
    //testf[r].m_str1= (CString)val.bstrVal;
    //TRACE(" %s", testf[r].m_str1);
    //sbuf= (CString)val.bstrVal;
    //sstr= (CString)val.bstrVal;
    //myFile.Write(sstr, 20);

    ZeroMemory(sbuf, 20);
    int ii= ((CString)val.bstrVal).GetLength();
    for (int li=0; li< ii; li++)
    sbuf[li]= ((CString)val.bstrVal).GetAt(li);
    //str1= (CString)val.bstrVal;
    //ZeroMemory(sbuf, 20);
    //int i=0;
    /*while(str1[i]!= NULL)
    {
    sbuf[i]= str1[i];
    i++;
    } */
    if( ar.IsStoring() )
    //ar.WriteString(sstr);
    //ar.Write((CString)val.bstrVal, 20);
    ar.Write(sbuf, 20);
    break;
    }
    case VT_EMPTY:
    {
    TRACE(" <empty>");
    break;
    }
    }//end switch
    }//end for
    TRACE("
    ");
    }//end for

    ar.Close();
    myFile.Close();

    //close the workbook without saving changes
    //and quit microsoft excel
    objBook.Close(COleVariant((short)FALSE), VOptional, VOptional);
    objApp.Quit();




    /*
    //Ole variant for optional
    COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

    _Application objApp;

    _Workbook objBook;
    Workbooks objBooks;

    Worksheets objSheets;
    _Worksheet objSheet;

    Range range;

    if (!UpdateData(TRUE))
    {
    return;
    }

    //instantiate excel and start a new workbook
    objApp.CreateDispatch("Excel.Application");
    objBooks= objApp.GetWorkbooks();
    objBook= objBooks.Add(VOptional);
    objSheets= objBook.GetWorksheets();
    objSheet= objSheets.GetItem(COleVariant((short)1));

    //get the range where the starting cell has the address
    //m_sStartingCell and it's dimensions are m_iNumRows*m_iNumCols
    range= objSheet.GetRange(COleVariant(m_sStartingCell), COleVariant(m_sStartingCell));
    range= range.GetResize(COleVariant(m_iNumRows), COleVariant(m_iNumCols));

    //fill the range with an array of value
    //create the SAFEARRAY
    COleSafeArray saRet;
    DWORD numElements[2];

    numElements[0]= m_iNumRows; //number of rows in the range
    numElements[1]= m_iNumCols; //number of columns in the range

    if (m_bFillWithStrings)
    {
    saRet.Create(VT_BSTR, 2, numElements);
    }
    else
    {
    saRet.Create(VT_R8, 2, numElements);
    }

    //fill the SAFEARRAY
    long index[2];
    long iRow;
    long iCol;

    for (iRow=0; iRow<=m_iNumRows-1; iRow++)
    {
    for (iCol=0; iCol<=m_iNumCols-1; iCol++)
    {
    index[0]= iRow;
    index[1]= iCol;
    if (m_bFillWithStrings) //fill with strings
    {
    VARIANT v;
    CString s;

    VariantInit(&v);
    v.vt= VT_BSTR;
    s.Format("r%dc%d", iRow, iCol);
    v.bstrVal= s.AllocSysString();
    saRet.PutElement(index, v.bstrVal);
    SysFreeString(v.bstrVal);
    VariantClear(&v);
    }
    else
    {
    double d;

    d= (iRow* 1000)+ iCol;
    saRet.PutElement(index, &d);
    }
    }
    }

    //set the range value to SAFEARRAY
    range.SetValue(COleVariant(saRet));
    saRet.Detach();

    //return control of excel to the user
    objApp.SetVisible(TRUE);
    objApp.SetUserControl(TRUE);
    */

    /*
    _Application app; //excel _Application object

    //statr excel and get application object
    if (!app.CreateDispatch("Excel.Application"))
    {
    AfxMessageBox("Couldn't statr excel");
    }
    else
    {
    //Make exvel visible and display a message
    app.SetVisible(TRUE);
    AfxMessageBox("Excel is running");
    }
    */
    /*
    //commonly used OLE variants
    COleVariant covTrue((short)TRUE),
    covFalse((short)FALSE),
    covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

    _Application app;
    Workbooks books;
    _Workbook book;

    Worksheets sheets;
    _Worksheet sheet;
    Range range;
    Font font;
    Range cols;

    //start excel and get application object

    if (!app.CreateDispatch("Excel.Application"))
    {
    AfxMessageBox("Couldn't start excel and get applocation object");
    return;
    }

    //get a new work book
    books= app.GetWorkbooks();
    book= books.Add(covOptional);

    //get the first sheet
    sheets= book.GetSheets();
    sheet= sheets.GetItem(COleVariant((short)1));

    //fill cells A1, B1, C1, and D1 one cell at a time with "headers"
    range= sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
    range.SetValue(COleVariant("Fist name"));
    range= sheet.GetRange(COleVariant("B1"), COleVariant("B1"));
    range.SetValue(COleVariant("Last name"));
    range= sheet.GetRange(COleVariant("C1"), COleVariant("C1"));
    range.SetValue(COleVariant("Full name"));
    range= sheet.GetRange(COleVariant("D1"), COleVariant("D1"));
    range.SetValue(COleVariant("Salary"));

    //format A1:D1 as bold, vertical alignment= center
    range= sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
    font= range.GetFont();
    font.SetBold(covTrue);
    range.SetVerticalAlignment(COleVariant((short)-4108)); //xlVAlignCenter=-4108

    //fill A2:B6 with an array of values (first & last name)
    {
    COleSafeArray saRet;
    DWORD numElements[]= {5, 2}; //5*2 element array

    saRet.Create(VT_BSTR, 2, numElements);

    //fill the 5*2 safearray with the following data
    //John Smith
    //Tom Brown
    //Sue Thomas
    //Jane Jones
    //Adam Johnson

    FillSafeArray(L"John", 0, 0, &saRet);
    FillSafeArray(L"Smith", 0, 1, &saRet);
    FillSafeArray(L"Tom", 1, 0, &saRet);
    FillSafeArray(L"Brown", 1, 1, &saRet);
    FillSafeArray(L"Sue", 2, 0, &saRet);
    FillSafeArray(L"Thomas", 2, 1, &saRet);
    FillSafeArray(L"Jane", 3, 0, &saRet);
    FillSafeArray(L"Jones", 3, 1, &saRet);
    FillSafeArray(L"Adam", 4, 0, &saRet);
    FillSafeArray(L"Johnson", 4, 1, &saRet);

    range= sheet.GetRange(COleVariant("A2"), COleVariant("B6"));
    range.SetValue(COleVariant(saRet));

    saRet.Detach();
    }

    //fill C2:C6 with a relative formula (=A2 & " " & B2)
    range= sheet.GetRange(COleVariant("C2"), COleVariant("C6"));
    range.SetFormula(COleVariant("=A2 & "" & B2"));

    //fill D2:D6 with a formula (=RAND()*100000) and apply a number format
    range= sheet.GetRange(COleVariant("D2"), COleVariant("D6"));
    range.SetFormula(COleVariant("=RAND()*100000"));
    range.SetNumberFormat(COleVariant("$0.00"));

    //autofit columns A:D
    range= sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
    cols= range.GetEntireColumn();
    cols.AutoFit();

    //manipulate a varialbe number of columns for quarterly sales data
    {
    short NumQtrs;
    CString msg;
    Range resizedrange;
    Interior interior;
    Borders borders;

    //determine how many quarters to display data for
    for (NumQtrs=1; NumQtrs<=3; NumQtrs++)
    {
    msg.Format("Enter sales data for %d quarter(s)?", NumQtrs);
    if (AfxMessageBox(msg, MB_YESNO)== IDYES)
    {
    break;
    }
    }
    msg.Format("Display data for %d quarters.", NumQtrs);
    AfxMessageBox(msg);

    //starting at E1, fill headers for the number of columns selected
    range= sheet.GetRange(COleVariant("E1"), COleVariant("E1"));
    resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
    resizedrange.SetFormula(COleVariant("="Q" & COLUMN()-1 & CHAR(10) & "Sales""));

    //change the orientation and wraptext properties for the headers
    resizedrange.SetOrientation(COleVariant((short)38));
    resizedrange.SetWrapText(covTrue);

    //fill the interior color of the headers
    interior= resizedrange.GetInterior();
    interior.SetColorIndex(COleVariant((short)36));

    //fill the columns with a formula and apply a number format
    range= sheet.GetRange(COleVariant("E2"), COleVariant("E6"));
    resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
    resizedrange.SetFormula(COleVariant("=RAND()*100"));
    resizedrange.SetNumberFormat(COleVariant("$0.00"));

    //apply borders to the sales data headers
    range= sheet.GetRange(COleVariant("E1"), COleVariant("E6"));
    resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
    borders= resizedrange.GetBorders();
    borders.SetWeight(COleVariant((short)2)); //xlThin= 2

    //add a totals formula for the quarterly sales data and apply a border
    range= sheet.GetRange(COleVariant("E8"), COleVariant("E8"));
    resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
    resizedrange.SetFormula(COleVariant("=SUM(E2:E6)"));
    borders= resizedrange.GetBorders();
    {
    Border bottomborder;

    bottomborder= borders.GetItem((long)9);
    bottomborder.SetLineStyle(COleVariant((short)-4119)); //xlDouble= -4119
    bottomborder.SetWeight(COleVariant((short)4)); //xlThick= 4
    }
    }

    //make the application visible and give the user control of Microsoft Excel
    app.SetVisible(TRUE);
    app.SetUserControl(TRUE);
    */

    }
«1
Sign In or Register to comment.