How to read / write data in excel 2007 in c ++?

Question

How to read / write data in excel 2007 in c ++?

Answer 1

Excel provides a COM interface that you can use from your C ++ application.

I have experience with Excel 2003, but I think it will work for excel 2007 as well.

This can be done, for example, with C # import or the method described in this article: http://support.microsoft.com/kb/216686

Answer 2

There is a python solution here (using COM send): http://snippets.dzone.com/posts/show/2036

This is not C ++, but the COM interface should be the same no matter which language you use, right?

You will not need to transfer everything. Just __init__ , set_range , get_value , set_value , save (or save_as ), close and quit . You may also need garbage disposal (since python has an automatic gc).

Or you can simply port (and modify) the following code (which I have not tested yet, since I no longer have excel), you should probably test it by downloading python and pythonwin):

 from win32com.client import Dispatch app = Dispatch("Excel.Application") app.Visible = True # spooky - watch the app run on your desktop! app.Workbooks.Open("C:\\book.xls") range = app.ActiveWorkbook.Sheets(1).Range("a1") print 'The range was:' print range.Value range.Value = 42 print 'The value is now 42' app.ActiveWorkbook.Save() app.ActiveWorkbook.SaveAs("C:\\excel2.xls") app.ActiveWorkbook.Close() app.Quit() # any gc to do? 

Answer 3

Excel 2007 files are simply zip files. Try renaming .xlsx to .zip: you can extract files and folders. With a text editor, you can see that they are all XML files.

So the solution is:

• use generic class to unpack xlsx
• use an XML parser to capture data.
• if you changed somethig re-write everything

No COM object required. Depending on your C ++ compiler, you can easily find the required sources.

Answer 4

You need to perform three basic operations.

1) Make sure the preinstall files are installed and localized.

It is dazzlingly obvious that I know, but make sure you have the right version of Excel installed so that you can find the necessary Microsoft libraries (and their locations). namely MSO.DLL, VBE6EXT.OLB and EXCEL.EXE

2) Configure Microsoft libraries.

In your C ++ code, let's say you start with a simple console application, be sure to include import libraries in any C ++ application that interacts with Excel. In my example, I use:

 #import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE11\\MSO.DLL" \ rename( "RGB", "MSORGB" ) using namespace Office; #import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB" using namespace VBIDE; #import "C:\\Program Files (x86)\\Microsoft Office\\OFFICE11\\EXCEL.EXE" \ rename( "DialogBox", "ExcelDialogBox" ) \ rename( "RGB", "ExcelRGB" ) \ rename( "CopyFile", "ExcelCopyFile" ) \ rename( "ReplaceText", "ExcelReplaceText" ) \ exclude( "IFont", "IPicture" ) no_dual_interfaces 

3) Use the Excel object model in C ++ code

For example, to declare a pointer to an Excel application object for reading / writing an Excel workbook:

 Excel::_ApplicationPtr pXL; pXL->Workbooks->Open( L"C:\\dump\\book.xls" ); 

And to access and manage the Excel worksheet and its cells:

 Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet; Excel::RangePtr pRange = pWksheet->Cells; double value = pRange->Item[1][1]; pRange->Item[1][1] = 5.4321; 

And so on. I have a more detailed discussion on my next blog post .

Answer 5

The low-tech way I've used on several projects is to create a simple script / macro / whatever that runs an external program. Write this external program in C ++. Get this external program to read its input and write your output to a CSV file (a simple comma delimited text file). Excel can easily read and write CSV files, so this setting gives you everything you need to create a viable solution.

Answer 6

All this can be done through the IDispatch interface. It can become very bloody, complicated quickly (Microsoft Cheers), but at least once you get your head over Excel, you can also easily integrate with any other MS application :)

Fortunately, there is someone from codeguru who has made this process enjoyable and easy. After I read this code, I started looking at what excel was doing, and besides, I found it really REALLY easy to extend it to do other things that I wanted. Just remember that you send commands to Excel through the IDispatch interface. This means that you need to think about how you will do something in order to understand how to do this programmatically.

Edit: The code guru example is for Excel 2003, but it's pretty easy to extend it to 2007 :)

Answer 7

Start here with OpenXml Sdk . Download the SDK from here . The SDK uses .NET, so you may need to use C ++. NET

Answer 8

A lot of time passed, but I used Jet OLEDB to get Excel files. You can start your search in this direction.

Answer 9

In the past, I used a third-party component for this: OLE XlsFile from SM Software (not free, but inexpensive). The advantage of this component over Microsoft COM components is that you can use it to write XLS files, even if Excel is not installed.

It also allows you to create tables or books with built-in formulas and formatting, which is not possible if you use CSV files as an exchange format.

Answer 10

If you need better performance, writing Excel binaries is the way to go, and writing them is not as difficult as reading them. The binary file format is relatively well documented by the OpenOffice.org project:

http://sc.openoffice.org/excelfileformat.pdf

and Microsoft also released documentation:

http://download.microsoft.com/download/0/B/E/0BE8BDD7-E5E8-422A-ABFD-4342ED7AD886/Excel97-2007BinaryFileFormat(xls)Specification.xps

This solution will work best if you have to write a lot of Excel files, mainly with data and a little formatting, and if you do not want to open files at the same time. If you then write one Excel file to open it, you can probably use the C ++ COM requisition interface, as other posters have explained.

Using the COM interface will require expensive out-of-process calls unless you write an Excel COM Addin. If you write Addin that imports your data into the current Excel worksheet, filling out the worksheet will still be much slower than dumping the file, but for one file at a time, this may be acceptable depending on your user scenario. If you decide to use the COM interface, reduce the number of calls in Excel. Use methods that let you insert an array of values, if they exist, set style properties by row and column, not by cell.

Answer 11

You can use ODBC to read and write data from an excel file without Excel. Link: Here

Link on how to write data with odbc: Here

Answer 12

I used the C ++ class set available from CodeProject before writing XLS files.

It is very easy to use and free! You can find it here .

No time to tune.