Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / C++
Article

BasicExcel - A Class to Read and Write to Microsoft Excel

Rate me:
Please Sign up or sign in to vote.
4.94/5 (199 votes)
5 Aug 200610 min read 5.5M   65.5K   520   598
A Class to Read and Write to Microsoft Excel

Introduction

More than 4 years ago, in December 2001, I posted CSpreadSheet, which is able to read and write to Microsoft Excel. That class was relatively popular. However it suffers from a few deficiencies like the necessity of a header row, inability to read cells as numbers, etc. All these are a result of the ODBC driver which it relies on. The dependence on the ODBC driver also meant that the class is not usable in a Unix/Linux environment.

Thus around 3 years ago, I wanted to replace CSpreadSheet with a class which could read and write in the native Excel format. This would allow the class to be used in environments other than Windows. It would also enable the user to read and write numbers and strings to the spreadsheet. However, this is not an easy task as an Excel file is saved as a compound file. Thus I had to write a class to read and write compound files first, which I have completed. (That will be published later when I have the time to properly document the class and write an article for it. In the meantime, the class is actually included in this package.) After I had written the class to read and write compound files, I started to write the class to read and write Excel files using information on Excel file format obtained from the OpenOffice project. Alas, work commitment prevented me from completing the project.

Last week, my work commitments eased and I suddenly remembered about the Excel class that I was writing 3 years ago. I wanted to complete the project but I thought after 3 years, surely someone has done a similar project already. So I did a quick search on SourgeForge. To my dismay, there are no such projects! So I resolved to continue the project. I quickly looked at my unfinished code to familiarize myself and I started writing. Now it is finally completed and I present to you BasicExcel.

Before we look deeper into BasicExcel, let's look at its limitations. It is called BasicExcel for a reason.

  1. It does not support formatting
  2. It does not support formulas
  3. It does not support charts
  4. It does not support Unicode UTF-32
  5. It does not support ...

Actually it does not support a lot of fanciful features of Excel. It is meant to be a basic class to read and write simple things like numbers and strings to a spreadsheet. So maybe it is better to list the things that it does support.

  1. Read and write number (integers, real numbers) and strings (ANSI, UTF16)
  2. Add worksheets
  3. Rename worksheets
  4. Delete worksheets
  5. Get the name of a worksheet

That's about it. As you can see, BasicExcel is really basic but even in its present form, it is far more capable than CSpreadSheet. However, although its features are basic, programming it isn't basic. A lot of code is needed to read and write to compound files, and to read and write in the native Excel format. Thus the total number of lines in BasicExcel exceeds 6000.

Using the code

I should give a list of the functions in the three classes which you should know to use BasicExcel. This will be followed by a sample code.

class BasicExcel

void New(int sheets=3)Create a new Excel workbook with a given number of spreadsheets (Minimum 1).
bool Load(const char* filename)Load an Excel workbook from a file.
bool Save()Save current Excel workbook to opened file.
bool SaveAs(const char* filename)Save current Excel workbook to a file.
size_t GetTotalWorkSheets()Total number of Excel worksheets in current Excel workbook.
BasicExcelWorksheet* GetWorksheet(size_t sheetIndex)Get a pointer to an Excel worksheet at the given index. Index starts from 0. Returns 0 if index is invalid.
BasicExcelWorksheet* GetWorksheet(const char* name)Get a pointer to an Excel worksheet that has given ANSI name. Returns 0 if there is no Excel worksheet with the given name.
BasicExcelWorksheet* GetWorksheet(const wchar_t* name)Get a pointer to an Excel worksheet that has given Unicode name. Returns 0 if there is no Excel worksheet with the given name.
BasicExcelWorksheet* AddWorksheet(int sheetIndex=-1)Add a new Excel worksheet to the given index. Name given to worksheet is SheetX, where X is a number which starts from 1. Index starts from 0. Worksheet is added to the last position if sheetIndex == -1. Returns a pointer to the worksheet if successful, 0 if otherwise.
BasicExcelWorksheet* AddWorksheet(const char* name, int sheetIndex=-1)Add a new Excel worksheet with given ANSI name to the given index. Index starts from 0. Worksheet is added to the last position if sheetIndex == -1. Returns a pointer to the worksheet if successful, 0 if otherwise.
BasicExcelWorksheet* AddWorksheet(const wchar_t* name, int sheetIndex=-1)Add a new Excel worksheet with given Unicode name to the given index. Index starts from 0. Worksheet is added to the last position if sheetIndex == -1. Returns a pointer to the worksheet if successful, 0 if otherwise.
bool DeleteWorksheet(size_t sheetIndex)Delete an Excel worksheet at the given index. Index starts from 0. Returns true if successful, false if otherwise.
bool DeleteWorksheet(const char* name)Delete an Excel worksheet that has given ANSI name. Returns true if successful, false if otherwise.
bool DeleteWorksheet(const wchar_t* name)Delete an Excel worksheet that has given Unicode name. Returns true if successful, false if otherwise.
char* GetAnsiSheetName(size_t sheetIndex)Get the worksheet name at the given index. Index starts from 0. Returns 0 if name is in Unicode format.
wchar_t* GetUnicodeSheetName(size_t sheetIndex)Get the worksheet name at the given index. Index starts from 0. Returns 0 if name is in ANSI format.
bool GetSheetName(size_t sheetIndex, char* name)Get the worksheet name at the given index. Index starts from 0. Returns false if name is in Unicode format.
bool GetSheetName(size_t sheetIndex, wchar_t* name)Get the worksheet name at the given index. Index starts from 0. Returns false if name is in ANSI format.
bool RenameWorksheet(size_t sheetIndex, const char* to)Rename an Excel worksheet at the given index to the given ANSI name. Index starts from 0. Returns true if successful, false if otherwise.
bool RenameWorksheet(size_t sheetIndex, const wchar_t* to)Rename an Excel worksheet at the given index to the given Unicode name. Index starts from 0. Returns true if successful, false if otherwise.
bool RenameWorksheet(const char* from, const char* to)Rename an Excel worksheet that has given ANSI name to another ANSI name. Returns true if successful, false if otherwise.
bool RenameWorksheet(const wchar_t* from, const wchar_t* to)Rename an Excel worksheet that has given Unicode name to another Unicode name. Returns true if successful, false if otherwise.

class BasicExcelWorksheet

char* GetAnsiSheetName()Get the current worksheet name. Returns 0 if name is in Unicode format.
wchar_t* GetUnicodeSheetName()Get the current worksheet name. Returns 0 if name is in ANSI format.
bool GetSheetName(char* name)Get the current worksheet name. Returns false if name is in Unicode format.
bool GetSheetName(wchar_t* name)Get the current worksheet name. Returns false if name is in ANSI format.
bool Rename(const char* to)Rename current Excel worksheet to another ANSI name. Returns true if successful, false if otherwise.
bool Rename(const wchar_t* to)Rename current Excel worksheet to another Unicode name. Returns true if successful, false if otherwise.
void Print(ostream& os, char delimiter=',', char textQualifier='\0')Print entire worksheet to an output stream, separating each column with the defined delimiter and enclosing text using the defined textQualifier. Leave out the textQualifier argument if do not wish to have any text qualifiers.
size_t GetTotalRows()Total number of rows in current Excel worksheet.
size_t GetTotalCols()Total number of columns in current Excel worksheet.
BasicExcelCell* Cell(size_t row, size_t col)Return a pointer to an Excel cell. row and col starts from 0. Returns 0 if row exceeds 65535 or col exceeds 255.
bool EraseCell(size_t row, size_t col)Erase content of a cell. row and col starts from 0. Returns true if successful, false if row or col exceeds range.

class BasicExcelCell

int Type() constGet type of value stored in current Excel cell. Returns one of the following enums: UNDEFINED, INT, DOUBLE, STRING, WSTRING.
bool Get(int& val) constGet an integer value. Returns false if cell does not contain an integer.
bool Get(double& val) constGet a double value. Returns false if cell does not contain a double.
bool Get(char* str) constGet an ANSI string. Returns false if cell does not contain an ANSI string.
bool Get(wchar_t* str) constGet an Unicode string. Returns false if cell does not contain an Unicode string.
size_t GetStringLength()Return length of ANSI or Unicode string (excluding null character).
int GetInteger() constGet an integer value. Returns 0 if cell does not contain an integer.
double GetDouble() constGet a double value. Returns 0.<code>0 if cell does not contain a double.
const char* GetString() constGet an ANSI string. Returns 0 if cell does not contain an ANSI string.
const wchar_t* GetWString() constGet an Unicode string. Returns 0 if cell does not contain an Unicode string.
ostream& operator<<(ostream& os, const BasicExcelCell& cell)Print cell to output stream. Print a null character if cell is undefined.
void Set(int val)Set content of current Excel cell to an integer.
void Set(double val)Set content of current Excel cell to a double.
void Set(const char* str)Set content of current Excel cell to an ANSI string.
void Set(const wchar_t* str)Set content of current Excel cell to an Unicode string.
void SetInteger(int val)Set content of current Excel cell to an integer.
void SetDouble(double val)Set content of current Excel cell to a double.
void SetString(const char* str)Set content of current Excel cell to an ANSI string.
void SetWString(const wchar_t* str)Set content of current Excel cell to an Unicode string.
void EraseContents()Erase the content of current Excel cell. Set type to UNDEFINED.

C++
#include "BasicExcel.hpp"
using namespace YExcel;

int main(int argc, char* argv[])
{
  BasicExcel e;

  // Load a workbook with one sheet, display its contents and
  // save into another file.
  e.Load("example1.xls");
  BasicExcelWorksheet* sheet1 = e.GetWorksheet("Sheet1");
  if (sheet1)
  {
    size_t maxRows = sheet1->GetTotalRows();
    size_t maxCols = sheet1->GetTotalCols();
    cout << "Dimension of " << sheet1->GetAnsiSheetName() <<
        " (" << maxRows << ", " << maxCols << ")" << endl;

    printf(" ");
    for (size_t c=0; c<maxCols; ++c) printf("%10d", c+1);
    cout << endl;

    for (size_t r=0; r<maxRows; ++r)
    {
      printf("%10d", r+1);
      for (size_t c=0; c<maxCols; ++c)
      {
        BasicExcelCell* cell = sheet1->Cell(r,c);
        switch (cell->Type())
        {
          case BasicExcelCell::UNDEFINED:
            printf(" ");
            break;

          case BasicExcelCell::INT:
            printf("%10d", cell->GetInteger());
            break;

          case BasicExcelCell::DOUBLE:
            printf("%10.6lf", cell->GetDouble());
            break;

          case BasicExcelCell::STRING:
            printf("%10s", cell->GetString());
            break;

          case BasicExcelCell::WSTRING:
            wprintf(L"%10s", cell->GetWString());
            break;
        }
      }
      cout << endl;
    }
  }
  cout << endl;
  e.SaveAs("example2.xls");

  // Create a new workbook with 2 worksheets and write some contents.
  e.New(2);
  e.RenameWorksheet("Sheet1", "Test1");
  BasicExcelWorksheet* sheet = e.GetWorksheet("Test1");
  BasicExcelCell* cell;
  if (sheet)
  {
    for (size_t c=0; c<4; ++c)
    {
      cell = sheet->Cell(0,c);
      cell->Set((int)c);
    }

    cell = sheet->Cell(1,3);
    cell->SetDouble(3.141592654);

    sheet->Cell(1,4)->SetString("Test str1");
    sheet->Cell(2,0)->SetString("Test str2");
    sheet->Cell(2,5)->SetString("Test str1");

    sheet->Cell(4,0)->SetDouble(1.1);
    sheet->Cell(4,1)->SetDouble(2.2);
    sheet->Cell(4,2)->SetDouble(3.3);
    sheet->Cell(4,3)->SetDouble(4.4);
    sheet->Cell(4,4)->SetDouble(5.5);

    sheet->Cell(4,4)->EraseContents();
  }

  sheet = e.AddWorksheet("Test2", 1);
  sheet = e.GetWorksheet(1);
  if (sheet)
  {
    sheet->Cell(1,1)->SetDouble(1.1);
    sheet->Cell(2,2)->SetDouble(2.2);
    sheet->Cell(3,3)->SetDouble(3.3);
    sheet->Cell(4,4)->SetDouble(4.4);
    sheet->Cell(70,2)->SetDouble(5.5);
  }
  e.SaveAs("example3.xls");

  // Load the newly created sheet and display its contents
  e.Load("example3.xls");

  size_t maxSheets = e.GetTotalWorkSheets();
  cout << "Total number of worksheets: " << e.GetTotalWorkSheets() << endl;
  for (size_t i=0; i<maxSheets; ++i)
  {
    BasicExcelWorksheet* sheet = e.GetWorksheet(i);
    if (sheet)
    {
      size_t maxRows = sheet->GetTotalRows();
      size_t maxCols = sheet->GetTotalCols();
      cout << "Dimension of " << sheet->GetAnsiSheetName() <<
         " (" << maxRows << ", " << maxCols << ")" << endl;

      if (maxRows>0)
      {
        printf(" ");
        for (size_t c=0; c<maxCols; ++c) printf("%10d", c+1);
        cout << endl;
      }

      for (size_t r=0; r<maxRows; ++r)
      {
        printf("%10d", r+1);
        for (size_t c=0; c<maxCols; ++c)
        {
          cout << setw(10) << *(sheet->Cell(r,c));
        // Another way of printing a cell content.
        }
        cout << endl;
      }
      if (i==0)
      {
        ofstream f("example4.csv");
        sheet->Print(f, ',', '\"'); // Save the first sheet as a CSV file.
        f.close();
      }
    }
    cout << endl;
  }
  return 0;
}

References

History

  • 20 April 2006: Version 1 released.
  • 22 April 2006: Version 1.1 released. Fixed bugs with compound files not being able to write files more than 65535 bytes. Fixed bugs with reading and writing to Excel files containing many strings.
  • 30 April 2006: Version 1.2 released. Added operator<< to pass BasicExcelCell to an output stream. Added Print() to BasicExcelWorksheet to print the worksheet to an output stream. Change BasicExcelCell Get functions to const functions. Rename BasicExcelWorksheet functions RenameWorkSheet() to Rename().
  • 10 May 2006: Version 1.3 released. Fixed bugs with reading from Excel files containing Asian characters.
  • 13 May 2006: Version 1.4 released. Fixed bugs with reading and writing to Excel files containing many strings.
  • 15 May 2006: Version 1.5 released. Remove code for ExtSST because it was causing problems with reading and writing to Excel files containing many strings.
  • 16 May 2006: Version 1.6 released. Optimized code for reading and writing.
  • 22 May 2006: Version 1.7 released. Fixed code to remove some warnings. Fixed bug with BasicExcelWorksheet::Cell. Fixed bug with BasicExcel::UpdateWorksheets().
  • 23 May 2006: Version 1.8 released. Fixed bug with reading Excel files containing many unicode strings. Fixed code to remove some warnings. Fixed variable code_ duplication in BoolErr. Minor changes to BasicExcelCell:Set functions.
  • 24 May 2006: Version 1.9 released. Changed name_ in Style from SmallString to LargeString. Fixed bug in BasicExcelCell::GetString and BasicExcelCell::GetWString. Minor changes to functions in BasicExcel and BasicExcelWorksheet which checks for unicode. Minor change to SmallString::Read.
  • 30 May 2006: Version 1.10 released. Fixed bug with reading Excel files containing many strings. Removed memory leaks. Added BasicExcelVC6.hpp and BasicExcelVC6.cpp for VC6 users
  • 2 June 2006: Version 1.11 released. Fixed bug with reading and writing Excel files containing many unicode and ANSI strings.
  • 6 June 2006: Version 1.12 released. Fixed bug with reading and writing Excel files containing many Unicode and ANSI strings.
  • 1 August 2006: Version 1.13 released. Changed BasicExcelCell::Get() so that it will get a stored double as an integer or vice versa if necessary. Changed BasicExcelCell::Get() so that it will not cause any errors if a string is empty. Changed BasicExcelCell::SetString() and BasicExcelCell::SetWString() so that it will not save an empty string.
  • 6 August 2006: Version 1.14 released. Fixed bug with reading Excel files that contain a null string.

Final Notes

If you are using this class in any of your programs, whether commercial, shareware, freeware, open source, etc. I would appreciate it if you could just send me an email to let me know. It will make me happy to learn that my class is useful to somebody.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Researcher
Singapore Singapore
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralReading Formular Pin
Alfred Willi26-May-06 0:22
Alfred Willi26-May-06 0:22 
GeneralRe: Reading Formular [modified] Pin
Yap Chun Wei28-May-06 14:43
Yap Chun Wei28-May-06 14:43 
QuestionAnother large file problem Pin
KeanuLee23-May-06 18:04
KeanuLee23-May-06 18:04 
AnswerRe: Another large file problem Pin
KeanuLee24-May-06 1:54
KeanuLee24-May-06 1:54 
GeneralRe: Another large file problem Pin
Yap Chun Wei24-May-06 14:52
Yap Chun Wei24-May-06 14:52 
Generaltwo idears Pin
eat_union22-May-06 14:45
eat_union22-May-06 14:45 
GeneralRe: two idears Pin
Yap Chun Wei22-May-06 15:03
Yap Chun Wei22-May-06 15:03 
GeneralRe: two idears Pin
eat_union22-May-06 19:09
eat_union22-May-06 19:09 
QuestionAny up to date VC6 source? Pin
Brad Bruce22-May-06 1:16
Brad Bruce22-May-06 1:16 
AnswerRe: Any up to date VC6 source? Pin
zjeng22-May-06 22:41
zjeng22-May-06 22:41 
AnswerRe: Any up to date VC6 source? Pin
luckyluche22-May-06 23:50
luckyluche22-May-06 23:50 
AnswerRe: Any up to date VC6 source? Pin
Yap Chun Wei23-May-06 18:35
Yap Chun Wei23-May-06 18:35 
AnswerRe: Any up to date VC6 source? Pin
Yap Chun Wei29-May-06 16:48
Yap Chun Wei29-May-06 16:48 
GeneralRe: Any up to date VC6 source? Pin
crisoc30-May-06 1:36
crisoc30-May-06 1:36 
GeneralVery useful - 5 with thanks! Pin
Jerry Evans22-May-06 0:38
Jerry Evans22-May-06 0:38 
GeneralRe: Very useful - 5 with thanks! Pin
Yap Chun Wei22-May-06 14:56
Yap Chun Wei22-May-06 14:56 
GeneralThank u Pin
Chhoto Bou17-May-06 22:33
Chhoto Bou17-May-06 22:33 
GeneralCompiling Error Pin
xfocus17-May-06 16:16
xfocus17-May-06 16:16 
GeneralRe: Compiling Error Pin
eat_union17-May-06 20:51
eat_union17-May-06 20:51 
GeneralError occurs when the lines added to the table are too much(about 200 lines) Pin
eat_union17-May-06 3:00
eat_union17-May-06 3:00 
GeneralRe: Error occurs when the lines added to the table are too much(about 200 lines) Pin
Yap Chun Wei17-May-06 14:55
Yap Chun Wei17-May-06 14:55 
GeneralRe: Error occurs when the lines added to the table are too much(about 200 lines) Pin
eat_union17-May-06 18:53
eat_union17-May-06 18:53 
GeneralRe: Error occurs when the lines added to the table are too much(about 200 lines) Pin
eat_union17-May-06 20:47
eat_union17-May-06 20:47 
GeneralRe: Error occurs when the lines added to the table are too much(about 200 lines) Pin
Yap Chun Wei21-May-06 16:13
Yap Chun Wei21-May-06 16:13 
GeneralThanks for ur cooparetion Pin
Chhoto Bou17-May-06 1:06
Chhoto Bou17-May-06 1:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.