Solved

Importing Excel Spreadsheets Into Visual C++ Application

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
What is C++ STL?: STL stands for Standard Template Library and is a part of standard C++ libraries. It contains many useful data structures (containers) and algorithms, which can spare you a lot of the time. Today we will look at the STL Vector. …
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

760 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

19 Experts available now in Live!

Get 1:1 Help Now