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.2.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