Skip to main content

IXlTable.GetRowReference(String, String) Method

Returns a reference to the portion of the current row bounded by the specified columns.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v24.1.Core.dll

NuGet Package: DevExpress.Printing.Core

Declaration

XlTableReference GetRowReference(
    string firstColumnName,
    string lastColumnName
)

Parameters

Name Type Description
firstColumnName String

A string that is the name of the column specifying the leftmost cell in the referenced row.

lastColumnName String

A string that is the name of the column specifying the rightmost cell in the referenced row.

Returns

Type Description
XlTableReference

An XlTableReference object that specifies the created reference.

Remarks

Use this method to create a reference to the cells in the same row as the formula, as the example below demonstrates. It creates a calculated column and uses a structured reference to specify the formula, which calculates yearly sales for each product in the table.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples

IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4", "Yearly Total" };

// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
    // Start generating the table with a header row displayed.
    table = row.BeginTable(columnNames, true);
    // Specify the total row label.
    table.Columns[0].TotalRowLabel = "Total";
    // Specify the function to calculate the total.
    table.Columns[5].TotalRowFunction = XlTotalRowFunction.Sum;
    // Specify the number format for numeric values in the table and the total cell of the "Yearly Total" column.
    XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
    table.DataFormatting = accounting;
    table.Columns[5].TotalRowFormatting = accounting;
    // Set the formula to calculate annual sales of each product
    // and display results in the "Yearly Total" column.
    table.Columns[5].SetFormula(XlFunc.Sum(table.GetRowReference("Q1", "Q4")));
}

// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Camembert Pierrot", 17000, 18500, 17500, 18000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Gnocchi di nonna Alice", 15500, 14500, 15000, 14000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Mascarpone Fabioli", 15000, 15750, 16000, 15500 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Ravioli Angelo", 12500, 11000, 13500, 12000 }, null);

// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
    row.EndTable(table, true);
See Also