ExcelApplication offers several methods that allow for the programmatic creation of areas and ranges.

 

An Area object represents a rectangular set of cells. To create an Area object, call one of the following methods:

 

A Range is a collection of areas. The areas in a range may be non-adjacent, and a range can include areas in different worksheets. To create a Range (without a name), call one of the following methods:

 

A NamedRange is stored in ExcelWriter's NamedRanges collections (Workbook.NamedRanges and Worksheet.NamedRanges) and is accessible after the workbook is saved. In Excel, named ranges are listed in the name box above the top-left corner of the worksheet.

 

To create a named range, call one of the following methods:

Importing Data to an Area

You can use an area as a set of target cells for imported data. The Area object's ImportData method allows you to import a block of data from a rectangular array or from an ADO.NET DataTable, DataView, or DataReader. To import data to an area:

  1. Create an area in a worksheet.

    ExcelApplication xla = new ExcelApplication();
    Workbook wb = xla.Create();
    Worksheet ws = wb.Worksheets[0];
    Area targetArea = ws.CreateArea(4, 4, 15, 6);
  2. Get a rectangular array, DataView, DataReader, or - as in the following example - DataTable to use as the data source.

    // ADO.NET code to get a DataTable from a query
    DataTable employeeDt = new DataTable();
    using(SqlConnection conn = new SqlConnection(connString))
    {
    	string employeeSQL = "SELECT FirstName + ' ' + LastName As Name " +
    	    "FROM Employee";
    	SqlCommand cmdEmployee = new SqlCommand(employeeSQL, conn);
    	SqlDataAdapter adptEmployee = new SqlDataAdapter(cmdEmployee);
    	adptEmployee.Fill(employeeDt);
    }
  3. Call ImportData to import the data. The method returns a new area containing the imported values.

    importedValues = targetArea.ImportData(employeeDt);

For more information, see Importing Data.

Applying Formatting to Areas and Ranges

Defined styles can be assigned to cells, rows, columns, areas, and ranges. To assign a style to a range or area:

  1. Create a style.

    ExcelApplication xla = new ExcelApplication();
    Workbook wb = xla.Create();
    Worksheet ws = wb.Worksheets[0];
    Style styleTotalRow = wb.CreateStyle();
    styleTotalRow.NumberFormat = "$#.##0";
    styleTotalRow.Font.Italic = true;
    styleTotalRow.Font.Bold = true;
  2. # Define an area or range, for example:

    Area areaTotalRow = ws.CreateArea(24, 0, 1, 3);
  3. Set or apply the style.

    areaTotalRow.Style = styleTotalRow;

For more information on setting and applying styles, see Styles.