Babar Raza December 27, 20160 Comments

Create & Manipulate Pivot Table, Apply Conditional Formatting, Add Hyperlinks and Much More with Aspose.Cells for C++ 16.12.0

Create & Manipulate Pivot Table, Apply Conditional Formatting, Add Hyperlinks and Much More with Aspose.Cells for C++ 16.12.0

December 27, 2016
Share on FacebookTweet about this on TwitterShare on LinkedIn
Aspose.Cells for C++Aspose Team is pleased to announce the second major release of Aspose.Cells for C++ API. Aspose.Cells for C++ 16.12.0 has included many new features and enhancements that further improve the overall feature set of the API. Please check the detailed release notes in order to get an idea about what is new and what has been enhanced with this revision of Aspose.Cells for C++. If you are planning to upgrade the API from any previous version, we strongly suggest you to check the Public API Changes section to know what has been changed since your current revision of the API. While you are downloading the API build to give it a try, here is a list of added features along with a few code snippets for quick testing.

Create & Manipulate Pivot Tables

The second release of Aspose.Cells for C++ supports creation as well as the manipulation of the Pivot Tables. Aspose.Cells for C++ provides the IPivotTable class which represents a Pivot Table object whereas IPivotTableCollection represents a collection of Pivot Tables. The IPivotTableCollection can be accessed via the IWorksheet object and a new Pivot Table can be added to the collection while using the IPivotTableCollection.Add method. The following code snippet demonstrates how simple is to use Aspose.Cells for C++ API to create Pivot Tables from scratch.
//Load the sample excel file
intrusive_ptr wb = Factory::CreateIWorkbook();

//Access first worksheet
intrusive_ptr ws = wb->GetIWorksheets()->GetObjectByIndex(0);

//Add source data for pivot table
intrusive_ptr str = new String("Fruit");
ws->GetICells()->GetObjectByIndex(new String("A1"))->PutValue(str);
str = new String("Quantity");
ws->GetICells()->GetObjectByIndex(new String("B1"))->PutValue(str);
str = new String("Price");
ws->GetICells()->GetObjectByIndex(new String("C1"))->PutValue(str);
str = new String("Apple");
ws->GetICells()->GetObjectByIndex(new String("A2"))->PutValue(str);
str = new String("Orange");
ws->GetICells()->GetObjectByIndex(new String("A3"))->PutValue(str);
ws->GetICells()->GetObjectByIndex(new String("B2"))->PutValue(3);
ws->GetICells()->GetObjectByIndex(new String("B3"))->PutValue(4);
ws->GetICells()->GetObjectByIndex(new String("C2"))->PutValue(2);
ws->GetICells()->GetObjectByIndex(new String("C3"))->PutValue(1);

//Add pivot table
int idx = ws->GetIPivotTables()->Add(new String("A1:C3"), new String("E5"), new String("MyPivotTable"));

//Access created pivot table
intrusive_ptr pt = ws->GetIPivotTables()->GetObjectByIndex(idx);

//Manipulate pivot table rows, columns and data fields
pt->AddFieldToArea(PivotFieldType_Row, pt->GetIBaseFields()->GetObjectByIndex(0));
pt->AddFieldToArea(PivotFieldType_Data, pt->GetIBaseFields()->GetObjectByIndex(1));
pt->AddFieldToArea(PivotFieldType_Data, pt->GetIBaseFields()->GetObjectByIndex(2));
pt->AddFieldToArea(PivotFieldType_Column, pt->GetIDataField());

//Set pivot table style
pt->SetPivotTableStyleType(PivotTableStyleType_PivotTableStyleMedium9);

//Save the output excel file
wb->Save(outputPath);
Besides creating new Pivot Tables, Aspose.Cells for C++ APIs also support to manipulate existing Pivot Tables. The API currently supports to change the data at the source range of the Pivot Table and then refresh it. Once the Pivot Table has been manipulated as desired, it is best to use the IPivotTable.RefreshData and IPivotTable.CalculateData methods to refresh the Pivot Table against the updated data source. The following code snippet uses the Aspose.Cells for C++ API to manipulate an existing Pivot Table.
//Load the sample excel file
intrusive_ptr wb = Factory::CreateIWorkbook(samplePath);

//Access first worksheet
intrusive_ptr ws = wb->GetIWorksheets()->GetObjectByIndex(0);

//Change value of cell B3 which is inside the source data of pivot table
intrusive_ptr str = new String("Cup");
ws->GetICells()->GetObjectByIndex(new String("B3"))->PutValue(str);

//Get the value of cell H8 before refreshing pivot table
intrusive_ptr val = ws->GetICells()->GetObjectByIndex(new String("H8"))->GetStringValue();
printf("Before refreshing Pivot Table value of cell H8: %s\r\n%", val->charValue());

//Access pivot table, refresh and calculate it
intrusive_ptr pt = ws->GetIPivotTables()->GetObjectByIndex(0);
pt->RefreshData();
pt->CalculateData();

//Get the value of cell H8 after refreshing pivot table
val = ws->GetICells()->GetObjectByIndex(new String("H8"))->GetStringValue();
printf("After refreshing Pivot Table value of cell H8: %s\r\n%", val->charValue());

//Save the output excel file
wb->Save(outputPath);

Add Conditional Formatting Rules

Aspose.Cells for C++ now provides the ability to add conditional formatting rules to the worksheet by exposing the IFormatCondition class. The aforementioned class further provides the following  methods to apply the conditional formatting as per application requirements.
  • IFormatCondition.GetIAboveAverage
  • IFormatCondition.GetIColorScale
  • IFormatCondition.GetIDataBar
  • IFormatCondition.GetIIconSet
  • IFormatCondition.GetITop10

The following sample code shows how to add a conditional formatting rule of type Cell Value on cells A1 and B2.

//Create an empty workbook
intrusive_ptr wb = Factory::CreateIWorkbook();

//Access first worksheet
intrusive_ptr ws = wb->GetIWorksheets()->GetObjectByIndex(0);

//Adds an empty conditional formatting
int idx = ws->GetIConditionalFormattings()->Add();
intrusive_ptr fcs = ws->GetIConditionalFormattings()->GetObjectByIndex(idx);

//Set the conditional format range
intrusive_ptr ca = ICellArea::CreateICellArea(new String("A1"), new String("A1"));
fcs->AddArea(ca);
ca = ICellArea::CreateICellArea(new String("B2"), new String("B2"));
fcs->AddArea(ca);

//Add condition and set the background color
idx = fcs->AddCondition(FormatConditionType_CellValue, OperatorType_Between, new String("=A2"), new String("100"));
intrusive_ptr fc = fcs->GetObjectByIndex(idx);
fc->GetIStyle()->SetBackgroundColor(Color::GetRed());

//User friendly message to test the output excel file.
StringPtr msgStr = new String("Red color in cells A1 and B2 is because of Conditional Formatting.");
ws->GetICells()->GetObjectByIndex(new String("A10"))->PutValue(msgStr);

//Save the output excel file
wb->Save(outputPath);

Add Hyperlinks

Aspose.Cells for C++ now supports adding hyperlinks to the worksheet cells. In order to provide this feature, the Aspose.Cells for C++ 16.12.0 has exposed the IHyperlinkCollection class which is accessible via the IWorksheet object whereas a hyperlink can be added to the collection while using the IHyperlinkCollection.Add method as demonstrated below.
//Create a new workbook
intrusive_ptr wb = Factory::CreateIWorkbook();

//Get the first worksheet
intrusive_ptr wsc = wb->GetIWorksheets();
intrusive_ptr ws = wsc->GetObjectByIndex(0);

//Add hyperlink in cell C7 and set its various properties
intrusive_ptr hypLnks = ws->GetIHyperlinks();
int idx = hypLnks->Add(new String("C7"), 1, 1, new String("http://www.aspose.com/"));
intrusive_ptr lnk = hypLnks->GetObjectByIndex(idx);
lnk->SetTextToDisplay(new String("Aspose"));
lnk->SetScreenTip(new String("Link to Aspose Website"));

//Save the workbook in xlsx format
wb->Save(dirPath->Append(new String("output.xlsx")), SaveFormat_Xlsx);

Manage Document Properties

Excel application supports 2 types of document properties as listed below.

  • System defined (built-in) properties: Built-in properties contain general information about the document like document title, author name, document statistics and so on.
  • User-defined (custom) properties: Custom properties defined by the end user in the form of name value pair.

Aspose.Cells for C++ supports managing both types of document properties, built-in and custom. Aspose.Cells’ IWorkbook class represents an Excel file. In order to access the built-in document properties, use IWorkbook.GetBuiltInDocumentProperties whereas the custom document properties can be accessed while using the IWorkbook.GetCustomDocumentProperties.

The following sample code loads an existing sample spreadsheet and reads the built-in document properties such as Title, Subject and custom property by the name MyCustom1.

//Load the sample excel file
intrusive_ptr wb = Factory::CreateIWorkbook(samplePath);

//Read built-in title and subject properties
StringPtr strTitle = wb->GetIBuiltInDocumentProperties()->GetTitle();
StringPtr strSubject = wb->GetIBuiltInDocumentProperties()->GetSubject();

printf("Title: %s\r\n", strTitle->charValue());
printf("Subject: %s\r\n", strSubject->charValue());
printf("\r\n");

//Modify built-in title and subject properties
strTitle = new String("Aspose.Cells New Title");
strSubject = new String("Aspose.Cells New Subject");
wb->GetIBuiltInDocumentProperties()->SetTitle(strTitle);
wb->GetIBuiltInDocumentProperties()->SetSubject(strSubject);

//Read the custom property
StringPtr strCustomPropName = new String("MyCustom1");
StringPtr strCustomPropValue = wb->GetICustomDocumentProperties()->GetObjectByIndex(strCustomPropName)->ToString();
printf("MyCustom1: %s\r\n", strCustomPropValue->charValue());

//Add a new custom property
strCustomPropName = new String("MyCustom5");
strCustomPropValue = new String("This is my custom five.");
wb->GetICustomDocumentProperties()->AddIDocumentProperty(strCustomPropName, strCustomPropValue);

//Save the output excel file
wb->Save(outputPath);

Manage Excel Tables

An Excel table is a matrix of cells containing any number of rows and columns whereas the same table is referred to be as a List Object in Aspose.Cells for C++ APIs. The Aspose::Cells::Tables namespace contains all the necessary classes that deals with the operations related to the List Objects. Most worth mentioning classes are IListObject and IListObjectCollection which allow to create and format List Objects and so on.

Group Rows & Columns

Aspose.Cells for C++ API can be used to group rows & columns while using the ICells class which is basically the collection of all cells in a given worksheet. The ICells class offers the GroupRows and GroupColumns methods in order to group rows and columns respectively.

The following code snippet demonstrates the simple usage scenario of both aforementioned methods.

//Create an empty workbook
intrusive_ptr wb = Factory::CreateIWorkbook();

//Add worksheet for grouping rows
intrusive_ptr grpRows = wb->GetIWorksheets()->GetObjectByIndex(0);
grpRows->SetName(new String("GroupRows"));

//Add worksheet for grouping columns
int idx = wb->GetIWorksheets()->Add();
intrusive_ptr grpCols = wb->GetIWorksheets()->GetObjectByIndex(idx);
grpCols->SetName(new String("GroupColumns"));

//Add sample values in both worksheets
for (int i = 0; i<50; i++)
{
	intrusive_ptr str = new String("Text");

	grpRows->GetICells()->GetObjectByIndex(i, 0)->PutValue(str);
	grpCols->GetICells()->GetObjectByIndex(0, i)->PutValue(str);
}

//Grouping rows at first level
grpRows->GetICells()->GroupRows(0, 10);
grpRows->GetICells()->GroupRows(12, 22);
grpRows->GetICells()->GroupRows(24, 34);

//Grouping rows at second level
grpRows->GetICells()->GroupRows(2, 8);
grpRows->GetICells()->GroupRows(14, 20);
grpRows->GetICells()->GroupRows(28, 30);

//Grouping rows at third level
grpRows->GetICells()->GroupRows(5, 7);

//Grouping columns at first level
grpCols->GetICells()->GroupColumns(0, 10);
grpCols->GetICells()->GroupColumns(12, 22);
grpCols->GetICells()->GroupColumns(24, 34);

//Grouping columns at second level
grpCols->GetICells()->GroupColumns(2, 8);
grpCols->GetICells()->GroupColumns(14, 20);
grpCols->GetICells()->GroupColumns(28, 30);

//Grouping columns at third level
grpCols->GetICells()->GroupColumns(5, 7);

//Save the output excel file
wb->Save(outputPath);

Other Enhancements

There are several other enhancements with this release of Aspose.Cells for C++. Here is a list of just a few worth mentioning features.

Aspose.Cells for C++ Resources

The resources, you may need to accomplish your tasks: Keeping the Aspose tradition, you are welcome to shape the upcoming releases of Aspose.Cells for C++ API by posting your suggestions and concerns in the Aspose.Cells support forum.

Leave a comment

Posted inAspose.Cells Product FamilyTags: , , , , , ,
 

Related Articles