# Working with Excel Tables

14 Mar 20239 minutes to read

Excel tables helps to organize and analyze data, and Flutter XlsIO supports creating and manipulating these tables.

## Creating a Table

The following code snippet explains how to create a simple table from scratch.

• DART
• ``````//Create a new Excel Document.
final Workbook workbook = Workbook(1);

//Access the sheet via index.
final Worksheet sheet = workbook.worksheets[0];

//Insert data into the sheet.
sheet.getRangeByName('A1').setText('Fruits');
sheet.getRangeByName('A2').setText('banana');
sheet.getRangeByName('A3').setText('Cherry');
sheet.getRangeByName('A4').setText('Banana');

sheet.getRangeByName('B1').setText('CostA');
sheet.getRangeByName('B2').setNumber(744.6);
sheet.getRangeByName('B3').setNumber(5079.6);
sheet.getRangeByName('B4').setNumber(1267.5);

sheet.getRangeByName('C1').setText('CostB');
sheet.getRangeByName('C2').setNumber(162.56);
sheet.getRangeByName('C3').setNumber(1249.2);
sheet.getRangeByName('C4').setNumber(1062.5);

//Create a table with the data in given range.
sheet.tableCollection.create('Table1',  sheet.getRangeByName('A1:C4'));

final List<int> bytes = workbook.saveSync();
File('Table.xlsx').writeAsBytes(bytes);
workbook.dispose();``````

## Built-In Table Styles

You can apply the built in table styles available in Microsoft Excel, through ExcelTableBuiltInStyle enum of Flutter XlsIO. The following code snippet illustrates this.

• DART
• ``````//Create a new Excel Document.
final Workbook workbook = Workbook(1);

//Access the sheet via index.
final Worksheet sheet = workbook.worksheets[0];

//Insert data into the sheet.
sheet.getRangeByName('A1').setText('Fruits');
sheet.getRangeByName('A2').setText('banana');
sheet.getRangeByName('A3').setText('Cherry');
sheet.getRangeByName('A4').setText('Banana');

sheet.getRangeByName('B1').setText('CostA');
sheet.getRangeByName('B2').setNumber(744.6);
sheet.getRangeByName('B3').setNumber(5079.6);
sheet.getRangeByName('B4').setNumber(1267.5);

sheet.getRangeByName('C1').setText('CostB');
sheet.getRangeByName('C2').setNumber(162.56);
sheet.getRangeByName('C3').setNumber(1249.2);
sheet.getRangeByName('C4').setNumber(1062.5);

//Create a table with the data in given range.
final ExcelTable table = sheet.tableCollection.create('Table1',  sheet.getRangeByName('A1:C4'));

//Format the table with a built-in style.
table.builtInTableStyle = ExcelTableBuiltInStyle.tableStyleDark10;

final List<int> bytes = workbook.saveSync();
File('BuiltInStyle.xlsx').writeAsBytes(bytes);
workbook.dispose();``````

## Table Style Options

You can customize a table with other table style options such as first column, last column, header row, total row, banded row, and banded column. The following code snippet illustrates the usage.

### Show First Column

Enabling the ShowFirstColumn property applies default format to first column in the table. Its default value is FALSE.

• DART
• ``````//Gets or sets a Boolean value indicating whether first column format is present.
table.showFirstColumn = true;``````

### Show Last Column

Enabling the ShowLastColumn property applies default format to last column in the table. Its default value is FALSE.

• DART
• ``````//Gets or sets a Boolean value indicating whether last column format is present.
table.showLastColumn = true;``````

### Show Header Row

Disabling the ShowHeaderRow property hides the header row of the table. Its default value is TRUE.

• DART
• ``````//Gets or sets a Boolean value indicating whether to hide/display header row.

### Show Total Row

Enabling the ShowTotalRow property shows the total row of the table. Its default value is FALSE.

• DART
• ``````//Gets or sets a Boolean value indicating whether to hide/display total row.
table.showTotalRow = true;``````

### Show Banded Rows

Disabling the ShowBandedRows property removes the row stripes for table rows. Its default value is TRUE.

• DART
• ``````//Gets or sets a Boolean value indicating whether row stripes should be present.
table.showBandedRows = false;``````

### Show Banded Columns

Enabling the ShowBandedColumns property adds the column stripes for table columns. Its default value is FALSE.

• DART
• ``````//Gets or sets a Boolean value indicating whether column stripes should be present.
table.showBandedColumns = true;``````

## Removing a Table

You can remove the table from Excel document in two ways. They are

• using table object
• using specific index

The following code snippet illustrates how to remove a table from an Excel document using the table object.

• DART
• ``````//Create a new Excel Document.
final Workbook workbook = Workbook(1);

//Access the sheet via index.
final Worksheet sheet = workbook.worksheets[0];

//Insert data into the sheet.
sheet.getRangeByName('A1').setText('Fruits');
sheet.getRangeByName('A2').setText('banana');
sheet.getRangeByName('A3').setText('Cherry');
sheet.getRangeByName('A4').setText('Banana');

sheet.getRangeByName('B1').setText('CostA');
sheet.getRangeByName('B2').setNumber(744.6);
sheet.getRangeByName('B3').setNumber(5079.6);
sheet.getRangeByName('B4').setNumber(1267.5);

sheet.getRangeByName('C1').setText('CostB');
sheet.getRangeByName('C2').setNumber(162.56);
sheet.getRangeByName('C3').setNumber(1249.2);
sheet.getRangeByName('C4').setNumber(1062.5);

sheet.getRangeByName('F1').setText('Vegetables');
sheet.getRangeByName('F2').setText('Egg Plant');
sheet.getRangeByName('F3').setText(Lettuce);
sheet.getRangeByName('F4').setText('Tomato');

sheet.getRangeByName('G1').setText('CostA1');
sheet.getRangeByName('G2').setNumber(744.6);
sheet.getRangeByName('G3').setNumber(5079.6);
sheet.getRangeByName('G4').setNumber(1267.5);

sheet.getRangeByName('H1').setText('CostB1');
sheet.getRangeByName('H2').setNumber(162.56);
sheet.getRangeByName('H3').setNumber(1249.2);
sheet.getRangeByName('H4').setNumber(1062.5);

//Create tables with the data in given range.
final ExcelTable table1 = sheet.tableCollection.create('Table1',  sheet.getRangeByName('A1:C4'));
final ExcelTable table2 = sheet.tableCollection.create('Table2',  sheet.getRangeByName('F1:H4'));

//Remove a table from the worksheet.
sheet.tableCollection.remove(table1);

final List<int> bytes = workbook.saveSync();
File('RemoveTable.xlsx').writeAsBytes(bytes);
workbook.dispose();``````

The following code snippet illustrates how to remove a table from an Excel document using a specific index.

• DART
• ``````//Create a new Excel Document.
final Workbook workbook = Workbook(1);

//Access the sheet via index.
final Worksheet sheet = workbook.worksheets[0];

//Insert data into the sheet.
sheet.getRangeByName('A1').setText('Fruits');
sheet.getRangeByName('A2').setText('banana');
sheet.getRangeByName('A3').setText('Cherry');
sheet.getRangeByName('A4').setText('Banana');

sheet.getRangeByName('B1').setText('CostA');
sheet.getRangeByName('B2').setNumber(744.6);
sheet.getRangeByName('B3').setNumber(5079.6);
sheet.getRangeByName('B4').setNumber(1267.5);

sheet.getRangeByName('C1').setText('CostB');
sheet.getRangeByName('C2').setNumber(162.56);
sheet.getRangeByName('C3').setNumber(1249.2);
sheet.getRangeByName('C4').setNumber(1062.5);

sheet.getRangeByName('F1').setText('Vegetables');
sheet.getRangeByName('F2').setText('Egg Plant');
sheet.getRangeByName('F3').setText(Lettuce);
sheet.getRangeByName('F4').setText('Tomato');

sheet.getRangeByName('G1').setText('CostA1');
sheet.getRangeByName('G2').setNumber(744.6);
sheet.getRangeByName('G3').setNumber(5079.6);
sheet.getRangeByName('G4').setNumber(1267.5);

sheet.getRangeByName('H1').setText('CostB1');
sheet.getRangeByName('H2').setNumber(162.56);
sheet.getRangeByName('H3').setNumber(1249.2);
sheet.getRangeByName('H4').setNumber(1062.5);

sheet.getRangeByName('D6').setText('Product A');
sheet.getRangeByName('D7').setText('shirt');
sheet.getRangeByName('D8').setText('bags');
sheet.getRangeByName('D9').setText('Trousers');

sheet.getRangeByName('E6').setText('Cost1');
sheet.getRangeByName('E7').setNumber(654);
sheet.getRangeByName('E8').setNumber(745);
sheet.getRangeByName('E9').setNumber(187);
sheet.getRangeByName('F6').setText('Cost2');
sheet.getRangeByName('F7').setNumber(967);
sheet.getRangeByName('F8').setNumber(543);
sheet.getRangeByName('F9').setNumber(864);

//Create tables with the data in given range.
final ExcelTable table1 = sheet.tableCollection.create('Table1',  sheet.getRangeByName('A1:C4'));
final ExcelTable table2 = sheet.tableCollection.create('Table2',  sheet.getRangeByName('F1:H4'));

//Remove a table from the worksheet at the specified index.
sheet.tableCollection.removeAt(1);

final List<int> bytes = workbook.saveSync();
File('RemoveTable.xlsx').writeAsBytes(bytes);
workbook.dispose();``````