Create and Manipulate Excel Table

Possible Usage Scenarios

Aspose.Cells allows you to create and manipulate new or existing list objects or tables. You can make use of various methods of the list object or table e.g. header row style, column stripes, style type, show subtotal, etc. and also work with individual columns of the table and set their name and totals calculation function which could be Min, Max, Count, Average, Sum etc.

Create and Manipulate Excel Table

The following sample code loads the sample excel file and then creates a list object or table in a range A1:H10, then it makes use of its various methods and sets show subtotal. Then it sets the total functions of 3rd, 4th and 5th columns to Min, Max and Count respectively and writes the output excel file. The following screenshot shows the effect of the sample code on the sample excel file after execution.

Sample Code

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

Aspose::Cells::Startup();

//Source directory path
U16String dirPath(u"..\\Data\\TechnicalArticles\\");

//Output directory path
U16String outPath(u"..\\Data\\Output\\");

//Path of input excel file
U16String sampleCreateAndManipulateExcelTable = dirPath + u"sampleCreateAndManipulateExcelTable.xlsx";

//Path of output excel file
U16String outputCreateAndManipulateExcelTable = outPath + u"outputCreateAndManipulateExcelTable.xlsx";

//Load the sample excel file
Workbook wb(sampleCreateAndManipulateExcelTable);

//Access first worksheet
Worksheet ws = wb.GetWorksheets().Get(0);

//Add table i.e. list object
int idx = ws.GetListObjects().Add(u"A1", u"H10", true);

//Access the newly added list object
ListObject lo = ws.GetListObjects().Get(idx);

//Use its display methods
lo.SetShowHeaderRow(true);
lo.SetShowTableStyleColumnStripes(true);
lo.SetShowTotals(true);

//Set its style
lo.SetTableStyleType(TableStyleType::TableStyleLight12);

//Set total functions of 3rd, 4th and 5th columns
lo.GetListColumns().Get(2).SetTotalsCalculation(TotalsCalculation::Min);
lo.GetListColumns().Get(3).SetTotalsCalculation(TotalsCalculation::Max);
lo.GetListColumns().Get(4).SetTotalsCalculation(TotalsCalculation::Count);

//Save the output excel file
wb.Save(outputCreateAndManipulateExcelTable);

Aspose::Cells::Cleanup();