C++ Read and write Excel files

Language

Number Duck is a commercial C++ library that I've written to simplify reading and writing Excel files. You can download Number Duck here, and check out this page for Windows and Linux instructions on installing, building and linking the library. Below is an overview of the main components and some quick examples that show how to start accessing Excel files with Number Duck.

Components of a spreadsheet

A Workbook is the general representation of the Excel spreadsheet, the initial object created before reading or writing. It contains all the Styles, Worksheets and any overall functionality such as saving and loading. Using Styles you can change the appearance of the cells by applying different fonts and colors. Styles can be created once and then applied to many cells across many worksheets. Worksheets manage all cells and embedded Pictures. Cell contain values or formulas, and can have previously created styles applied to them.

Writing to an Excel file

You can refer to individual cells by address just like you would in Excel, eg: "A1", or by its X and Y coordinates, eg: (0,0). Note that the coordinates start at 0 while addresses start at "1". The cells can then be assigned directly with the SetString, SetFloat, SetBoolean or SetFormula functions.
  1. #include "Workbook.h"
  2. #include "Worksheet.h"
  3. #include "Cell.h"
  4.  
  5. using namespace FileScribe;
  6.  
  7. int main(int argc, char **argv)
  8. {
  9. Workbook workbook("");
  10. Worksheet* pWorksheet = workbook.GetWorksheetByIndex(0);
  11.  
  12. for (uint16_t y = 0; y < 10; y++)
  13. {
  14. Cell* pCell = pWorksheet->GetCell(0,y);
  15. pCell->SetFloat(y*3.1417f);
  16. }
  17.  
  18. pWorksheet->GetCellByAddress("B1")->SetString("http://numberduck.com");
  19. pWorksheet->GetCellByAddress("B2")->SetBoolean(true);
  20. pWorksheet->GetCellByAddress("B3")->SetFormula("=SUM(A1:A10)");
  21.  
  22. workbook.Save("Spreadsheet.xls");
  23.  
  24. return 0;
  25. }

Reading an Excel file

Reading an Excel file is as easy as writing, the file is loaded and the cells referenced in the same way. When reading, you should be careful to use the correct type for the cell. EG: If a cell contains a float, you must use GetFloat. Using an incorrect function will return the default value for that type, EG : GetString returns "" if the cell does not contain a string. GetType function allows two that you check the type.
  1. Workbook* pWorkbookIn = new Workbook("");
  2. if (pWorkbookIn->Load("Spreadsheet.xls"))
  3. {
  4. Worksheet* pWorksheetIn = pWorkbookIn->GetWorksheetByIndex(0);
  5. Cell* pCellIn = pWorksheetIn->GetCell(0,0);
  6. if (pCellIn->GetType() == Cell::TYPE_FLOAT)
  7. printf("Cell Contents: %f\n", pCellIn->GetFloat());
  8. }

Formatting

Existing Styles can be accessed or new styles created through the workbook object. Note that the default style can also be accessed through the Workbook, which is used by all Cells unless overridden.
  1. Workbook workbook("");
  2. Worksheet* pWorksheet = workbook.GetWorksheetByIndex(0);
  3.  
  4. Style* pDefaultStyle = workbook.GetDefaultStyle();
  5. pDefaultStyle->SetColor(Color(128, 128, 128));
  6.  
  7. Style* pStyle = workbook.CreateStyle();
  8. pStyle->SetFont("Comic Sans MS");
  9. pStyle->SetColor(Color(255, 0, 255));
  10. pStyle->SetBackgroundColor(Color(255, 255, 0));
  11. pStyle->SetItalic(true);
  12. pStyle->SetBold(true);
  13.  
  14. Cell* pCell = pWorksheet->GetCellByAddress("A1");
  15. pCell->SetString("Default style");
  16.  
  17. pCell = pWorksheet->GetCellByAddress("A2");
  18. pCell->SetStyle(pStyle);
  19. pCell->SetString("This looks hideous!");
  20.  
  21. workbook.Save("Formatting.xls");

Pictures

The images are created from a JPEG or PNG image through the Worksheet object. SetX and SetY are used to establish the cell coordinates of the picture while SetSubY and SetSubX position of image within the cell in pixel increments.
  1. Workbook workbook("");
  2. Worksheet* pWorksheet = workbook.GetWorksheetByIndex(0);
  3.  
  4. Picture* pPicture = pWorksheet->CreatePicture("duck.png");
  5. pPicture->SetX(1);
  6. pPicture->SetY(1);
  7. pPicture->SetSubX(10);
  8. pPicture->SetSubY(10);
  9.  
  10. workbook.Save("Picture.xls");

Conclusion

If you need more information, there is heaps more Number Duck documentation you can look at. Thanks for reading! Adam Clifton, [email protected]

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Comments

Submitted byPrasmit Pansare (not verified)on Wed, 10/05/2016 - 19:06

Hi, This is not complete solution, cant see any of the header files. thanks, Prasmit

Add new comment