cbsecsnip-logo                         cbse-syllabus
cbse 100%
An online library for CBSE Computer Science and Informatics Practices students

How to use Microsoft Excel from C++.Print Friendly Version of this pagePrint Get a PDF version of this webpagePDF

One very simple and common question –
Write a program in C++ for saving new record of a student, editing or deleting of an existing record of a student.
Generally we use plain text file (.txt) for performing the tasks mentioned in the question. We learn this traditional method of doing these tasks in our school or college.
But thing if someone gives you a simple text file containing 500 records of students and ask you to manage and manipulate it like sort it on Student Name, Add a new record after a given specific record. What will be your condition? It’s like a NIGHT MARE.
Well don’t worry in this tutorial, I’ll show you a How MS Excel and C++ do hand shaking. But before we start our mission you have to be ready with some tools and resources.
Precaution: If you have a 16 bit compiler like Turbo C/C++ for DOS, you may face lots of issues like linking with .dll files, compilation process as we will use 3rd party library files.
More over Turbo C++ is good for learning and practicing basic concepts of C++. In reality now a days there are many modern and advance C++ compilers are available in market. In this tutorial I am going to use MinGW C++ compiler and more advance and feature enriched IDE i.e. Code::Block.
Let’s start with all these Prerequisites –
  1. Windows OS of 32 bits/64 bits
  2. MinGW C++ compiler
  3. Code::Blocks IDE
  4. LibXL (3rd party library from XLware)
You can download all above mentioned software from our website , install and configure them on your system.
Assuming you are ready with all settings.
1. Open Code::Blocks IDE

2. Start a New project from File->Project
3. Select Console Application in New from Template window and click on Go Button.

4. Click on Next button on this window and move to next window.
5. Select C++ and click on Next, move forward.
6. Give a name to your project, here my project name is XLDemo.
7. Select the folder where you want to save your project, my folder is C:\excelandc++\
8. In next window nothing to be change just move to the next window. Before going to next window check compiler it should be “GNU GCC Compiler”.
9. Now your project is created and you are seeing a screen almost lie this

10. Expand the Sources folder by double clicking on Sources or just by a click on + sign. After expanding the sources folder you can see your main.cpp file where you have to write C++ code.
11. Open main.cpp and you can see the main.cpp file on right side of the pane

You need to do few setting before proceeding further.
12. Right click on your project name (mine is XLDemo) in Management window->Projects.
13. From the shortcut menu list select Build options…
14. Here you have to configure your Linker settings and Directories settings. Before doing all these setting select the Debug option from left pane of window
      a. Select Linker settings tab from the right side of pane
      b. Click on Add button
      c. Open the Open file window by clicking on … button
      d. Locate you libxl-3.3.1 folder which you extracted from the downloaded libxl zip file. And select libxl.lib file, you will get this file in this path C:\libxl-3.3.1\lib. You will get a message window asking a question "Keep this as a relative path ?" Select NO and then Yes

      e. Now Go on the Search Directories tab->Compiler sub tab->Click on Add, Select the header file path for LibXL you can find it here C:\libxl-3.3.1\include_cpp. Don’t keep relative path.

      f. Move on Linker sub tab->select your libxl.dll file which is under C:\libxl-3.3.1\bin. Do keep relative path.

      15. After completing all the above mentioned settings write the following C++ code –
#include <iostream>
#include <conio.h>
#include "libxl.h"

using namespace std;
using namespace libxl;
class myExcel
        void create_book();     //function to create new excel file
        void extract_data();    //function to get data from existing excel file
void myExcel::create_book()
    Book* book = xlCreateBook();
        Sheet* sheet = book->addSheet("MySheet");
		//write string value
            sheet->writeStr(2, 1, "Hello, World !");

		//write numeric value
            sheet->writeNum(3, 1, 1000);

		//write date value
            Format* dateFormat = book->addFormat();
            sheet->writeNum(4, 1, book->datePack(2008, 4, 29), dateFormat);

            sheet->setCol(1, 1, 12);

        if(book->save("example.xls")) std::cout << "\nFile example.xls has been created." << std::endl;
    std::cout << "\nPress any key to exit...";
void myExcel::extract_data()
    Book* book = xlCreateBook();
            Sheet* sheet = book->getSheet(0);
			//read string value
                const char* s = sheet->readStr(2, 1);
                if(s) std::cout << s << std::endl;

			//read numeric value
                double d = sheet->readNum(3, 1);
                std::cout << d << std::endl;

//read date value
   int year, month, day;
                 book->dateUnpack(sheet->readNum(4, 1), &year, &month, &day);
                 std::cout << year << "-" << month << "-" << day << std::endl;

int main()
    char ch;
	myExcel me;
		cout<<"\n\n\n\tMAIN MENU";
		cout<<"\n\n\t01. NEW EXCEL BOOK";
		cout<<"\n\n\t02. VIEW DATA FROM EXCEL SHEET";
		cout<<"\n\n\t03. EXIT";
		cout<<"\n\n\tSelect Your Option (1-3) ";
		case '1':
		case '2':
		 case '3':
			cout<<"\n\n\tThanks for using XLDemo";
		 default :cout<<"\a";
	return 0;
Now build your project, it can be done in various ways –
      1st option: From menu bar Build->Build
      2nd option: ctrl + F9
      3rd option: From compiler toolbar click on yellow gear wheel icon
After following any one of the above mention method you project will start compilation and building process, and view the status at the bottom of the window.

If everything goes well you will see the status as shown in figure.
Now finally time to run your project –
      1st option: From menu bar Build->Run
      2nd option: ctrl + F10
      3rd option: From compiler toolbar click on green play icon
And you will see following screen as your project output –

Select the option 1 for creating your MS Excel file –

You can see the message “File example.xls has been created.” And you can check this file is created in your project folder.
For view the saved data in example.xls file select option 2.
So at this point we are able to create and read data from MS Excel by using C++ program. Please note this point I have shown this demo in Debug mode it cannot be used for Release mode, for Release mode we have make some more modification as we are using .DLL file which are shared library file and will be required at runtime.
You can get all detail of the classes and functions from the documentation included in the zip file you downloaded.
I tried my best to make this tutorial simple and easy, if you have any query then please feel free to send us feedback from our contact us

Vishal Deb
Copyright © 2012, | | Disclaimer | Terms of Service | Privacy Policy