Previously, I have written a post on how to implement Excel automation features and create Excel XLS/XLSX files from scratch using C#. Today, I’ll show you how to create Excel workbooks, insert data to Excel worksheets, calculate formulas, and create charts and tables in worksheets using C++. All the spreadsheet automation features will be powered by C++ Excel API – Aspose.Cells for C++.
Aspose.Cells for C++ is a native C++ library that lets you create, read, parse, and convert spreadsheet documents without requiring Microsoft Excel. It provides a complete set of Excel automation features that can be utilized to generate and manipulate XLS/XLSX spreadsheets. In this article, we’ll cover the following features of creating Excel XLS/XLSX files from scratch.
- Create Excel files (XLS/XLSX) using C++
- Add data to an Excel worksheet using C++
- Calculate workbook formulas using C++
- Create tables in an Excel worksheet using C++
- Create charts in Excel XLS/XLSX using C++
C++ Excel Spreadsheet API – Installation
You can download the complete package of library files of Aspose.Cells for C++ from the Downloads section. The package also contains a ready-to-run sample console application.
Create Excel Files (XLS/XLSX) using C++
Lets first create a simple Excel XLSX workbook from scratch. A workbook is composed of one or multiple worksheets and each worksheet contains the data in the form of rows and columns. Therefore, in order to create an Excel spreadsheet, you need to create a workbook first and then add worksheets in it. The following are the steps to create an Excel file using Aspose.Cells for C++.
- Create an object of the IWorkbook class.
- Get the first worksheet (created by default) of the workbook into an IWorksheet object from IWorksheetCollection.
- Access the cells of the worksheet into an ICells object using the IWorksheet->GetICells() method.
- Access the desired cell of the worksheet into an ICell object using the ICells->GetObjectByIndex() method by specifying the row and column index.
- Add value to the cell using ICell->PutValue() method.
- Save the workbook as .xlsx file using IWorkbook->Save() method.
The following code sample shows how to create an Excel XLSX file using C++.
Excel Workbook
The following is the screenshot of the Excel workbook we have just created.
Add Data to an Excel Worksheet using C++
In the previous example, we have seen how to create a simple Excel file and insert value to a particular cell using row and column index. However, most often the cells in the Excel worksheets are identified using the column letter and row numbers such as A1, A2, B1, and etc. So let’s look at the example of how to insert data into the worksheet using the cell names. The process only differs in adding value to cell and all other steps of creating the Excel workbook and worksheets are the same.
The following code sample shows how to create an Excel XLSX workbook and insert data into it using C++.
Calculate Workbook Formulas using C++
Setting formulas in Excel workbooks is an amazing feature to perform calculations on data. It makes it quite easier to perform complex calculations on the data effectively and efficiently. The following are the steps to set and calculate formulas in Excel worksheets.
- Create an object of the IWorkbook class.
- Get the worksheet into an IWorksheet object from IWorksheetCollection.
- Use the ICell class to access the cell to which you want to apply the formula.
- Set formula for the cell using the ICell->SetFormula() method.
- Calculate the formula using IWorkbook->CalculateFormula() method.
- Save the workbook using IWorkbook->Save() method.
The following code sample shows how to add and calculate formulas in an Excel XLSX workbook using C++.
Create Tables in an Excel Worksheet using C++
The tables in Excel worksheets are used to organize a group of data located in a range of cells. Tables also help you maintain different types of lists in the worksheets. The following are the steps to create a table in the Excel worksheet.
- Create a new Excel workbook using the IWorkbook class.
- Access the default worksheet into the IWorksheet object or add a new one.
- Insert value/data into the cells in the same way we have done in the previous example.
- Add the range of cells to Lists collection of worksheet using IWorksheet->GetIListObjects()->Add() method.
- Get the list into the IListObject object.
- Apply styling to the table using IListObject->SetTableStyleType() method.
- Save the workbook as .xlsx file.
The following code sample shows how to create a table in Excel XLSX file using C++.
Excel Workbook with Table
Create Charts in Excel Spreadsheet using C++
Charts in Excel spreadsheets are used to visualize the data using different types of graphical objects. They give us a quick insight and understanding of the data particularly when data is huge. Aspose.Cells for C++ supports a variety of charts including Sunburst, Treemap, Histogram, Pyramid, Bubble, Line, and many more. The following are the steps to create a chart in Excel workbook using Aspose.Cells for C++.
- Create a new Excel workbook.
- Access the desired worksheet into the IWorksheet object.
- Add a new chart to the worksheet using IWorksheet->GetICharts()->Add() method by passing chart’s type.
- Access the newly added chart into IChart object.
- Set the data source for the chart using IChart->GetNISeries()->Add() method.
- Save the workbook as .xlsx file.
The following code sample shows how to create a chart in Excel XLSX file using C++.
Excel Workbook with Chart
Conclusion
In this article, we have covered how to create MS Excel spreadsheets from scratch using C++. The article also contains step by step guidelines and code samples on how to create tables, charts, and calculate formulas in Excel worksheets. You can learn more about other advanced features related to Excel automation from the documentation of Aspose.Cells for C++.
