Solved

Importing Excel Spreadsheets Into Visual C++ Application

Posted on 1998-08-31
5
406 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
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 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

  Included as part of the C++ Standard Template Library (STL) is a collection of generic containers. Each of these containers serves a different purpose and has different pros and cons. It is often difficult to decide which container to use and …
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

929 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now