Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.
Create Dynamic Charts with C++
Contents
[
Hide
]
Dynamic (or interactive) charts have the ability to change when you change the scope of data. In other words, dynamic charts can automatically reflect changes when the data source is changed. In order to trigger the change in the data source, one can use the filtering option of Excel Tables or use a control such as a ComboBox or a dropdown list.
This article demonstrates the usage of Aspose.Cells for C++ APIs to create dynamic charts using both of the aforementioned approaches.
Using Excel Tables
Excel tables are referred to as ListObjects from Aspose.Cells’s perspective; therefore, we will use the term “ListObject” instead of “Table” for clarity. Please read in detail about how to create ListObjects with the Aspose.Cells for C++ API.
ListObjects provide the in‑built functionality to sort & filter the data upon user interaction. Both sorting & filtering options are provided through the drop‑down lists which are automatically added to the header row of the ListObject. Due to these features (sorting & filtering), the ListObject seems to be the perfect candidate to serve as the data source to a dynamic chart because when sorting or filtering is changed, the representation of data in the chart will be changed to reflect the current state of the ListObject.
In order to keep the demonstration simple to understand, we will create the Workbook from scratch and move forward step by step as outlined below.
- Create an empty Workbook.
- Access the Cells of the first Worksheet in the Workbook.
- Insert some data to the cells.
- Create a ListObject based on the inserted data.
- Create a Chart based on the data range of the ListObject.
- Save the result on the disk.
#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
int main()
{
Aspose::Cells::Startup();
// Source directory path
U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
// Output directory path
U16String outDir(u"..\\Data\\02_OutputDirectory\\");
// Create an instance of Workbook
Workbook book;
// Access first worksheet from the collection
Worksheet sheet = book.GetWorksheets().Get(0);
// Access cells collection of the first worksheet
Cells cells = sheet.GetCells();
// Insert data column wise
cells.Get(u"A1").PutValue(u"Category");
cells.Get(u"A2").PutValue(u"Fruit");
cells.Get(u"A3").PutValue(u"Fruit");
cells.Get(u"A4").PutValue(u"Fruit");
cells.Get(u"A5").PutValue(u"Fruit");
cells.Get(u"A6").PutValue(u"Vegetables");
cells.Get(u"A7").PutValue(u"Vegetables");
cells.Get(u"A8").PutValue(u"Vegetables");
cells.Get(u"A9").PutValue(u"Vegetables");
cells.Get(u"A10").PutValue(u"Beverages");
cells.Get(u"A11").PutValue(u"Beverages");
cells.Get(u"A12").PutValue(u"Beverages");
cells.Get(u"B1").PutValue(u"Food");
cells.Get(u"B2").PutValue(u"Apple");
cells.Get(u"B3").PutValue(u"Banana");
cells.Get(u"B4").PutValue(u"Apricot");
cells.Get(u"B5").PutValue(u"Grapes");
cells.Get(u"B6").PutValue(u"Carrot");
cells.Get(u"B7").PutValue(u"Onion");
cells.Get(u"B8").PutValue(u"Cabbage");
cells.Get(u"B9").PutValue(u"Potato");
cells.Get(u"B10").PutValue(u"Coke");
cells.Get(u"B11").PutValue(u"Colada");
cells.Get(u"B12").PutValue(u"Fizz");
cells.Get(u"C1").PutValue(u"Cost");
cells.Get(u"C2").PutValue(2.2);
cells.Get(u"C3").PutValue(3.1);
cells.Get(u"C4").PutValue(4.1);
cells.Get(u"C5").PutValue(5.1);
cells.Get(u"C6").PutValue(4.4);
cells.Get(u"C7").PutValue(5.4);
cells.Get(u"C8").PutValue(6.5);
cells.Get(u"C9").PutValue(5.3);
cells.Get(u"C10").PutValue(3.2);
cells.Get(u"C11").PutValue(3.6);
cells.Get(u"C12").PutValue(5.2);
cells.Get(u"D1").PutValue(u"Profit");
cells.Get(u"D2").PutValue(0.1);
cells.Get(u"D3").PutValue(0.4);
cells.Get(u"D4").PutValue(0.5);
cells.Get(u"D5").PutValue(0.6);
cells.Get(u"D6").PutValue(0.7);
cells.Get(u"D7").PutValue(1.3);
cells.Get(u"D8").PutValue(0.8);
cells.Get(u"D9").PutValue(1.3);
cells.Get(u"D10").PutValue(2.2);
cells.Get(u"D11").PutValue(2.4);
cells.Get(u"D12").PutValue(3.3);
// Create ListObject, get the ListObjects collection in the first worksheet
ListObjectCollection listObjects = sheet.GetListObjects();
// Add a List based on the data source range with headers on
int32_t index = listObjects.Add(0, 0, 11, 3, true);
sheet.AutoFitColumns();
// Create chart based on ListObject
index = sheet.GetCharts().Add(ChartType::Column, 21, 1, 35, 18);
Chart chart = sheet.GetCharts().Get(index);
chart.SetChartDataRange(u"A1:D12", true);
chart.GetNSeries().SetCategoryData(u"A2:B12");
// Save spreadsheet
book.Save(outDir + u"output_out.xlsx");
std::cout << "Spreadsheet created successfully!" << std::endl;
Aspose::Cells::Cleanup();
}
Using Dynamic Formulas
In case you do not wish to use the ListObject as a data source for the dynamic chart, the other option is to use Excel functions (or formulas) to create a dynamic range of data, and a control (such as a ComboBox) to trigger the change in data. In this scenario, we will use the VLOOKUP function to fetch the appropriate values based on the selection of the ComboBox. When the selection is changed, the VLOOKUP function will refresh the cell value. If a range of cells is using the VLOOKUP function, the whole range can be refreshed upon user interaction; therefore, it can be used as a source for the dynamic chart.
In order to keep the demonstration simple to understand, we will create the Workbook from scratch and move forward step by step as outlined below.
- Create an empty Workbook.
- Access the Cells of the first Worksheet in the Workbook.
- Insert some data to the cells by creating a Named Range. This data will serve as a series for the dynamic chart.
- Create a ComboBox based on the Named Range created in the previous step.
- Insert some more data to the cells that will serve as a source for the VLOOKUP function.
- Insert VLOOKUP functions (with appropriate parameters) to a range of cells. This range will serve as a source for the dynamic chart.
- Create a Chart based on the range created in the previous step.
- Save the result on the disk.
#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
using namespace Aspose::Cells::Drawing;
int main()
{
Aspose::Cells::Startup();
// Source directory path
U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
// Output directory path
U16String outDir(u"..\\Data\\02_OutputDirectory\\");
// Create a workbook object
Workbook workbook;
// Get the first worksheet
Worksheet worksheet = workbook.GetWorksheets().Get(0);
// Create a range in the second worksheet
Range range = worksheet.GetCells().CreateRange(u"C21", u"C24");
// Name the range
range.SetName(u"MyRange");
// Fill different cells with data in the range
range.Get(0, 0).PutValue(u"North");
range.Get(1, 0).PutValue(u"South");
range.Get(2, 0).PutValue(u"East");
range.Get(3, 0).PutValue(u"West");
// Add a combo box to the worksheet
ComboBox comboBox = worksheet.GetShapes().AddComboBox(15, 0, 2, 0, 17, 64);
comboBox.SetInputRange(u"=MyRange");
comboBox.SetLinkedCell(u"=B16");
comboBox.SetSelectedIndex(0);
// Get the cell and set its style
Cell cell = worksheet.GetCells().Get(u"B16");
Style style = cell.GetStyle();
style.GetFont().SetColor(Color::White());
cell.SetStyle(style);
// Set formula for cell C16
worksheet.GetCells().Get(u"C16").SetFormula(u"=INDEX(Sheet1!$C$21:$C$24,$B$16,1)");
// Put some data for chart source
// Data Headers
worksheet.GetCells().Get(u"D15").PutValue(u"Jan");
worksheet.GetCells().Get(u"D20").PutValue(u"Jan");
worksheet.GetCells().Get(u"E15").PutValue(u"Feb");
worksheet.GetCells().Get(u"E20").PutValue(u"Feb");
worksheet.GetCells().Get(u"F15").PutValue(u"Mar");
worksheet.GetCells().Get(u"F20").PutValue(u"Mar");
worksheet.GetCells().Get(u"G15").PutValue(u"Apr");
worksheet.GetCells().Get(u"G20").PutValue(u"Apr");
worksheet.GetCells().Get(u"H15").PutValue(u"May");
worksheet.GetCells().Get(u"H20").PutValue(u"May");
worksheet.GetCells().Get(u"I15").PutValue(u"Jun");
worksheet.GetCells().Get(u"I20").PutValue(u"Jun");
// Data
worksheet.GetCells().Get(u"D21").PutValue(304);
worksheet.GetCells().Get(u"D22").PutValue(402);
worksheet.GetCells().Get(u"D23").PutValue(321);
worksheet.GetCells().Get(u"D24").PutValue(123);
worksheet.GetCells().Get(u"E21").PutValue(300);
worksheet.GetCells().Get(u"E22").PutValue(500);
worksheet.GetCells().Get(u"E23").PutValue(219);
worksheet.GetCells().Get(u"E24").PutValue(422);
worksheet.GetCells().Get(u"F21").PutValue(222);
worksheet.GetCells().Get(u"F22").PutValue(331);
worksheet.GetCells().Get(u"F23").PutValue(112);
worksheet.GetCells().Get(u"F24").PutValue(350);
worksheet.GetCells().Get(u"G21").PutValue(100);
worksheet.GetCells().Get(u"G22").PutValue(200);
worksheet.GetCells().Get(u"G23").PutValue(300);
worksheet.GetCells().Get(u"G24").PutValue(400);
worksheet.GetCells().Get(u"H21").PutValue(200);
worksheet.GetCells().Get(u"H22").PutValue(300);
worksheet.GetCells().Get(u"H23").PutValue(400);
worksheet.GetCells().Get(u"H24").PutValue(500);
worksheet.GetCells().Get(u"I21").PutValue(400);
worksheet.GetCells().Get(u"I22").PutValue(200);
worksheet.GetCells().Get(u"I23").PutValue(200);
worksheet.GetCells().Get(u"I24").PutValue(100);
// Dynamically load data on selection of dropdown value
worksheet.GetCells().Get(u"D16").SetFormula(u"=IFERROR(VLOOKUP($C$16,$C$21:$I$24,2,FALSE),0)");
worksheet.GetCells().Get(u"E16").SetFormula(u"=IFERROR(VLOOKUP($C$16,$C$21:$I$24,3,FALSE),0)");
worksheet.GetCells().Get(u"F16").SetFormula(u"=IFERROR(VLOOKUP($C$16,$C$21:$I$24,4,FALSE),0)");
worksheet.GetCells().Get(u"G16").SetFormula(u"=IFERROR(VLOOKUP($C$16,$C$21:$I$24,5,FALSE),0)");
worksheet.GetCells().Get(u"H16").SetFormula(u"=IFERROR(VLOOKUP($C$16,$C$21:$I$24,6,FALSE),0)");
worksheet.GetCells().Get(u"I16").SetFormula(u"=IFERROR(VLOOKUP($C$16,$C$21:$I$24,7,FALSE),0)");
// Create Chart
int index = worksheet.GetCharts().Add(ChartType::Column, 0, 3, 12, 9);
Chart chart = worksheet.GetCharts().Get(index);
chart.GetNSeries().Add(u"='Sheet1'!$D$16:$I$16", false);
chart.GetNSeries().Get(0).SetName(u"=C16");
chart.GetNSeries().SetCategoryData(u"=$D$15:$I$15");
// Save result on disk
workbook.Save(outDir + u"output_out.xlsx");
Aspose::Cells::Cleanup();
}