Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Importing Excel Spreadsheets Into Visual C++ Application

Posted on 1998-08-31
5
Medium Priority
?
470 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 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction This article is the first in a series of articles about the C/C++ Visual Studio Express debugger.  It provides a quick start guide in using the debugger. Part 2 focuses on additional topics in breakpoints.  Lastly, Part 3 focuses on th…
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 viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

972 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