Create and Format Table
Create Table
One of the advantages of spreadsheets is that they allow you to create different types of lists, for example, phone lists, task lists, lists of transactions, assets or liabilities. Several users can work together to use, create and maintain various lists.
Aspose.Cells supports creating and managing Lists.
Advantages of a List Object
There are quite a few advantages when you convert a list of data to an actual List Object
- New rows and columns are automatically included.
- A total row at the bottom of your list can be easily added to display SUM, AVERAGE, COUNT, etc.
- Columns added to the right are automatically incorporated into the List object.
- Charts based on rows and columns will be expanded automatically.
- Named ranges assigned to rows and columns will be expanded automatically.
- The list is protected from accidental row and column deletion.
Creating a List Object using Microsoft Excel
| Selecting data range for creating List object |
|---|
 |
| This displays the Create List dialog. |
| Create List dialog |
|---|
 |
| Implementing the List object for the data and specifying total row (Select Data, then List, followed by Total Row). |
| Creating a list object |
|---|
 |
Using Aspose.Cells API
Aspose.Cells provides a class Workbook that represents a Microsoft Excel file. The Workbook class contains an Worksheets collection that allows access to each worksheet in an Excel file.
A worksheet is represented by the Worksheet class. The Worksheet class provides a wide range of methods for managing a worksheet. To create an ListObject in a worksheet, use the GetListObjects collection method of the Worksheet class. Each [ListObject] is in fact, an object of the ListObjectCollection class, which further provides the Add method for adding an [ListObject] object and specifying a range of cells for the list.
According to the specified range of cells, the [ListObject] object is created by Aspose.Cells. Use attributes (for example SetShowTotals and GetListColumns etc.) of the [ListObject] class to control the list.
In the example given below, we have created the same [ListObject] using Aspose.Cells API as we created using Microsoft Excel in the above section.
| //For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C | |
| Aspose::Cells::Startup(); | |
| //Path of input | |
| U16String dirPath(u""); | |
| //Path of output | |
| U16String outPath(u""); | |
| // Instantiate a Workbook object and open an Excel file | |
| Workbook workbook(dirPath + u"book1.xls"); | |
| // Accessing the first worksheet in the Excel file | |
| Worksheet worksheet = workbook.GetWorksheets().Get(0); | |
| // Get the List objects collection in the first worksheet. | |
| ListObjectCollection listObjects = worksheet.GetListObjects(); | |
| // Add a List based on the data source range with headers on. | |
| listObjects.Add(1, 1, 7, 5, true); | |
| // Show the total row for the List. | |
| listObjects.Get(0).SetShowTotals(true); | |
| // Saving the Excel file | |
| workbook.Save(outPath + u"CreatingListObjects_out.xls"); | |
| Aspose::Cells::Cleanup(); |
Format a Table
To manage and analyze a group of related data, it is possible to turn a range of cells into a list object (also known as an Excel table). A table is a series of rows and columns that contain related data managed independently from the data in other rows and columns. By default, every column in the table has filtering enabled in the header row so that you can filter or sort your list object data quickly. You can add a total row (a special row in a list that provides a selection of aggregate functions useful for working with numerical data) to the list object that provides a drop-down list of aggregate functions for each totals row cell. Aspose.Cells provides options for creating and managing lists (or tables).
Formatting a List Object
Aspose.Cells provides a class Workbook that represents a Microsoft Excel file. The Workbook class contains an Worksheets collection that allows access to each worksheet in an Excel file.
A worksheet is represented by the Worksheet class. The Worksheet class provides a wide range of methods for managing worksheets. To create a ListObject in a worksheet, use ListObjectCollection. Each [ListObject] is in fact, an object of the ListObjectCollection class, which further provides the Add method for adding an [ListObject] object and specify the range of cells it should encompass. According to the specified range of cells, a ListObject is created in the worksheet by Aspose.Cells. Use attributes (for example, SetTableStyleType) of the [ListObject] class to format the table for your requirements.
The example below adds sample data to a worksheet, adds an [ListObject] and applies default styles to it. [ListObject] styles are supported by Microsoft Excel 2007/2010.
| //For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C | |
| Aspose::Cells::Startup(); | |
| //Path of input | |
| U16String dirPath(u""); | |
| //Path of output | |
| U16String outPath(u""); | |
| // Instantiate a Workbook object | |
| Workbook workbook; | |
| // Obtaining the reference of the default(first) worksheet | |
| Worksheet worksheet = workbook.GetWorksheets().Get(0); | |
| // Obtaining Worksheet's cells collection | |
| Cells cells = worksheet.GetCells(); | |
| // Setting the value to the cells | |
| cells.Get(u"A1").PutValue(u"Employee"); | |
| cells.Get(u"B1").PutValue(u"Quarter"); | |
| cells.Get(u"C1").PutValue(u"Product"); | |
| cells.Get(u"D1").PutValue(u"Continent"); | |
| cells.Get(u"E1").PutValue(u"Country"); | |
| cells.Get(u"F1").PutValue(u"Sale"); | |
| cells.Get(u"A2").PutValue(u"David"); | |
| cells.Get(u"A3").PutValue(u"David"); | |
| cells.Get(u"A4").PutValue(u"David"); | |
| cells.Get(u"A5").PutValue(u"David"); | |
| cells.Get(u"A6").PutValue(u"James"); | |
| cells.Get(u"A7").PutValue(u"James"); | |
| cells.Get(u"A8").PutValue(u"James"); | |
| cells.Get(u"A9").PutValue(u"James"); | |
| cells.Get(u"A10").PutValue(u"James"); | |
| cells.Get(u"A11").PutValue(u"Miya"); | |
| cells.Get(u"A12").PutValue(u"Miya"); | |
| cells.Get(u"A13").PutValue(u"Miya"); | |
| cells.Get(u"A14").PutValue(u"Miya"); | |
| cells.Get(u"A15").PutValue(u"Miya"); | |
| cells.Get(u"B2").PutValue(1); | |
| cells.Get(u"B3").PutValue(2); | |
| cells.Get(u"B4").PutValue(3); | |
| cells.Get(u"B5").PutValue(4); | |
| cells.Get(u"B6").PutValue(1); | |
| cells.Get(u"B7").PutValue(2); | |
| cells.Get(u"B8").PutValue(3); | |
| cells.Get(u"B9").PutValue(4); | |
| cells.Get(u"B10").PutValue(4); | |
| cells.Get(u"B11").PutValue(1); | |
| cells.Get(u"B12").PutValue(1); | |
| cells.Get(u"B13").PutValue(2); | |
| cells.Get(u"B14").PutValue(2); | |
| cells.Get(u"B15").PutValue(2); | |
| cells.Get(u"C2").PutValue(u"Maxilaku"); | |
| cells.Get(u"C3").PutValue(u"Maxilaku"); | |
| cells.Get(u"C4").PutValue(u"Chai"); | |
| cells.Get(u"C5").PutValue(u"Maxilaku"); | |
| cells.Get(u"C6").PutValue(u"Chang"); | |
| cells.Get(u"C7").PutValue(u"Chang"); | |
| cells.Get(u"C8").PutValue(u"Chang"); | |
| cells.Get(u"C9").PutValue(u"Chang"); | |
| cells.Get(u"C10").PutValue(u"Chang"); | |
| cells.Get(u"C11").PutValue(u"Geitost"); | |
| cells.Get(u"C12").PutValue(u"Chai"); | |
| cells.Get(u"C13").PutValue(u"Geitost"); | |
| cells.Get(u"C14").PutValue(u"Geitost"); | |
| cells.Get(u"C15").PutValue(u"Geitost"); | |
| cells.Get(u"D2").PutValue(u"Asia"); | |
| cells.Get(u"D3").PutValue(u"Asia"); | |
| cells.Get(u"D4").PutValue(u"Asia"); | |
| cells.Get(u"D5").PutValue(u"Asia"); | |
| cells.Get(u"D6").PutValue(u"Europe"); | |
| cells.Get(u"D7").PutValue(u"Europe"); | |
| cells.Get(u"D8").PutValue(u"Europe"); | |
| cells.Get(u"D9").PutValue(u"Europe"); | |
| cells.Get(u"D10").PutValue(u"Europe"); | |
| cells.Get(u"D11").PutValue(u"America"); | |
| cells.Get(u"D12").PutValue(u"America"); | |
| cells.Get(u"D13").PutValue(u"America"); | |
| cells.Get(u"D14").PutValue(u"America"); | |
| cells.Get(u"D15").PutValue(u"America"); | |
| cells.Get(u"E2").PutValue(u"China"); | |
| cells.Get(u"E3").PutValue(u"India"); | |
| cells.Get(u"E4").PutValue(u"Korea"); | |
| cells.Get(u"E5").PutValue(u"India"); | |
| cells.Get(u"E6").PutValue(u"France"); | |
| cells.Get(u"E7").PutValue(u"France"); | |
| cells.Get(u"E8").PutValue(u"Germany"); | |
| cells.Get(u"E9").PutValue(u"Italy"); | |
| cells.Get(u"E10").PutValue(u"France"); | |
| cells.Get(u"E11").PutValue(u"U.S."); | |
| cells.Get(u"E12").PutValue(u"U.S."); | |
| cells.Get(u"E13").PutValue(u"Brazil"); | |
| cells.Get(u"E14").PutValue(u"U.S."); | |
| cells.Get(u"E15").PutValue(u"U.S."); | |
| cells.Get(u"F2").PutValue(2000); | |
| cells.Get(u"F3").PutValue(500); | |
| cells.Get(u"F4").PutValue(1200); | |
| cells.Get(u"F5").PutValue(1500); | |
| cells.Get(u"F6").PutValue(500); | |
| cells.Get(u"F7").PutValue(1500); | |
| cells.Get(u"F8").PutValue(800); | |
| cells.Get(u"F9").PutValue(900); | |
| cells.Get(u"F10").PutValue(500); | |
| cells.Get(u"F11").PutValue(1600); | |
| cells.Get(u"F12").PutValue(600); | |
| cells.Get(u"F13").PutValue(2000); | |
| cells.Get(u"F14").PutValue(500); | |
| cells.Get(u"F15").PutValue(900); | |
| // Adding a new List Object to the worksheet | |
| worksheet.GetListObjects().Add(u"A1", u"F15", true); | |
| ListObject listObject = worksheet.GetListObjects().Get(0); | |
| // Adding Default Style to the table | |
| listObject.SetTableStyleType(TableStyleType::TableStyleMedium10); | |
| // Show Total | |
| listObject.SetShowTotals(true); | |
| // Saving the Excel file | |
| workbook.Save(outPath + u"FormatTable_out.xlsx"); | |
| Aspose::Cells::Cleanup(); |