Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Importing Excel Spreadsheets Into Visual C++ Application

Posted on 1998-08-31
5
Medium Priority
?
460 Views
Last Modified: 2012-06-27
Will like to ask opinions on the following:

1. Which way to import Excel spreadsheets into Visual C++ application is better, using ODBC or DAO?

2. How do I convert numeric values in Excel to text when importing data? If I do not convert to text, it will give me overflow error. I'm using DAO.

Thanks.

0
Comment
Question by:eng071398
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:milenvk
ID: 1171669
1.It's better to use DAO since it is faster for sure. The drawback is that you have to have DAO on every client machine along with the Excel ISAM driver.

2.To convert a VARIANT (since this is the type that comes when getting data from DAO data sources) to string consider using the CCrack class. It comes with DAOVIEW example and is not part of MFC, but you can directly add it to your project and use its functionality. This class lets you format the value of a variant as a displayable string.

0
 

Author Comment

by:eng071398
ID: 1171670
I'm using the CCrack class. But, the overflow problems occur even before I can format the value of a variant to string i.e. during the import of Excel spreadsheets numeric values into COleVariant.
I need a way to convert numeric values in Excel to text before importing to overcome the overflow problem. Any clues as to how to do this?
0
 
LVL 2

Expert Comment

by:shchuka
ID: 1171671
There's a product called OpenExchange - there are several versions of it, the most complete being a full DLL that require no other components.  I've used it with VB but it can also be used with any other window programming language.

It can read/write files in tons of DB and spreadsheets formats, including excel, lotus 1-2-3, quattro pro, foxpro, oracle, etc.  You can read the whole sheet altogether, by line, or by one cell.  You can read any cell as any type, including reading numbers as strings.

I really recommend it.  The only draw back of it is its price of around $300 - but it's well worth it.
0
 
LVL 2

Expert Comment

by:shchuka
ID: 1171672
There's a product called OpenExchange - there are several versions of it, the most complete being a full DLL that require no other components.  I've used it with VB but it can also be used with any other window programming language.

It can read/write files in tons of DB and spreadsheets formats, including excel, lotus 1-2-3, quattro pro, foxpro, oracle, etc.  You can read the whole sheet altogether, by line, or by one cell.  You can read any cell as any type, including reading numbers as strings.

I really recommend it.  The only draw back of it is its price of around $300 - but it's well worth it.
0
 

Accepted Solution

by:
pstg earned 150 total points
ID: 1171673
In C++, it is easy to use Excel automation.  You have to add a new class with the Wizard from a type library.  You will find the type library in the Excel directory (with OLB extension).  Specify all classes.

You can then use Excel classes, like in this example:

      try
      {
      _Application app;     // app is an _Application object.
      _Workbook book;       // More object declarations.
      _Worksheet sheet;
      Workbooks books;
      Worksheets sheets;
      Range range;          // Used for Microsoft Excel 97 components.
      LPDISPATCH lpDisp;    // Often reused variable.

      // Common OLE variants. Easy variants to use for calling arguments.
      COleVariant
        covTrue((short)TRUE),
        covFalse((short)FALSE),
        covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);


      // Start Microsoft Excel, get _Application object,
      // and attach to app object.
      if(!app.CreateDispatch("Excel.Application"))
       {
        AfxMessageBox("Couldn't CreateDispatch() for Excel");
        return;
       }

      // Set visible.
      app.SetVisible(TRUE);

      // Get the Workbooks collection.
      lpDisp = app.GetWorkbooks();     // Get an IDispatch pointer.
      ASSERT(lpDisp);
      books.AttachDispatch(lpDisp);    // Attach the IDispatch pointer
                                       // to the books object.

      // Open a new workbook and attach that IDispatch pointer to the
      // Workbook object.
      lpDisp = books.Add( covOptional );
      ASSERT(lpDisp);
      book.AttachDispatch( lpDisp );

      // To open an existing workbook, you need to provide all
      // 13 arguments for the Open member function.
      // The code below opens a workbook and adds it to the Workbook’s
      // Collection object.
      // You need to modify the path and file name for your own
      // workbook.
      //
      // lpDisp = books.Open("C:\\Test",     // Test.xls is a workbook.
      // covOptional, covOptional, covOptional, covOptional, covOptional,
      // covOptional, covOptional, covOptional, covOptional, covOptional,
      // covOptional, covOptional );   // Return Workbook's IDispatch
      // pointer.


      // Get the Sheets collection and attach the IDispatch pointer to your
      // sheets object.
      lpDisp = book.GetSheets();
      ASSERT(lpDisp);
      sheets.AttachDispatch(lpDisp);

      // Get sheet #1 and attach the IDispatch pointer to your sheet
      // object.
      lpDisp = sheets.GetItem( COleVariant((short)(1)) );
                                        //GetItem(const VARIANT &index)
      ASSERT(lpDisp);
      sheet.AttachDispatch(lpDisp);

      lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);// Attach the IDispatch pointer
                                    // to the range object.
       COleVariant res;
       res = range.GetValue();

I hope it helps!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: SunnyDark
This article's goal is to present you with an easy to use XML wrapper for C++ and also present some interesting techniques that you might use with MS C++. The reason I built this class is to ease the pain of using XML files with C++, since there is…
Templates For Beginners Or How To Encourage The Compiler To Work For You Introduction This tutorial is targeted at the reader who is, perhaps, familiar with the basics of C++ but would prefer a little slower introduction to the more ad…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question