Solved

Importing Excel Spreadsheets Into Visual C++ Application

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PDF library for Delphi 2 128
Template syntax for variable length arrays 9 75
Dynamically allocate memory 9 64
Why  my code (program) build with old compiler? 11 79
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…
Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
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 how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

828 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