This project has moved. For the latest updates, please go here.

Using Tables

We start with the following basic table:

BasicTable.jpg

And we end up with the following 3 Excel tables:

UsingTables.jpg

            var wb = new XLWorkbook("BasicTable.xlsx");
            var ws = wb.Worksheet(1);
            var firstCell = ws.FirstCellUsed();
            var lastCell = ws.LastCellUsed();
            var range = ws.Range(firstCell.Address, lastCell.Address);
            range.Row(1).Delete(); // Deleting the "Contacts" header (we don't need it for our purposes)

                // We want to use a theme for table, not the hard coded format of the BasicTable
                range.Clear(XLClearOptions.Formats); 
                // Put back the date and number formats
                range.Column(4).Style.NumberFormat.NumberFormatId = 15;
                range.Column(5).Style.NumberFormat.Format = "$ #,##0";

            var table = range.CreateTable();    // You can also use range.AsTable() if you want to
                                                              // manipulate the range as a table but don't want 
                                                              // to create the table in the worksheet.

            // Let's activate the Totals row and add the sum of Income
            table.ShowTotalsRow = true;
            table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum;
            // Just for fun let's add the text "Sum Of Income" to the totals row
            table.Field(0).TotalsRowLabel = "Sum Of Income";

            // Copy all the headers
            Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2;
            Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber;
            ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers";
            foreach (var cell in table.HeadersRow().Cells())
            {
                currentRow++;
                ws.Cell(currentRow, columnWithHeaders).Value = cell.Value;
            }

            // Format the headers as a table with a different style and no autofilters
            var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders);
            var htLastCell = ws.Cell(currentRow, columnWithHeaders);
            var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers");
            headersTable.Theme = XLTableTheme.TableStyleLight10;
            headersTable.ShowAutoFilter = false;

            // Add a custom formula to the headersTable
            headersTable.ShowTotalsRow = true;
            headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))";


            // Copy the names
            Int32 columnWithNames = columnWithHeaders + 2;
            currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow
            ws.Cell(currentRow, columnWithNames).Value = "Names";
            foreach (var row in table.DataRange.Rows())
            {
                currentRow++;
                var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name
                var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name
                var name = String.Format("{0} {1}", fName, lName);
                ws.Cell(currentRow, columnWithNames).Value = name;
            }

            // Format the names as a table with a different style and no autofilters
            var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames);
            var ntLastCell = ws.Cell(currentRow, columnWithNames);
            var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable();
            namesTable.Theme = XLTableTheme.TableStyleLight12;
            namesTable.ShowAutoFilter = false;

            ws.Columns().AdjustToContents();
            ws.Columns("A,G,I").Width = 3;

            wb.SaveAs("UsingTables.xlsx");

Last edited Feb 4, 2012 at 9:28 PM by MDeLeon, version 8