Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
An *Area* object represents a rectangular set of cells. To create an Area object, call one of the following methods: * {code}[
Wiki Markup
Excerpt

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:

...

...

...

...

  •  - This method takes the 0-based

...

  • indexes

...

  • of

...

  • the

...

  • area's

...

  • first

...

  • row

...

  • and

...

  • column,

...

  • and

...

  • the

...

  • number

...

  • of

...

  • rows

...

  • and

...

  • columns

...

  • to

...

  • include

...

  • in

...

  • the

...

  • area.

...

...

  • This method takes a formula representing the area, for example, "=A1:G10".

...

  • The

...

  • formula

...

  • is

...

  • relative

...

  • to

...

  • the

...

  • current

...

  • worksheet.

...

 

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:

...

  • defines

...

  • a

...

  • range

...

  • containing

...

  • one

...

  • area

...

  • and

...

  • "=Sheet1\!B$12:$H$21

...

  • Sheet1\!$F$18:$K$29

...

  • Sheet1\!D$16:$M$21"

...

  • defines

...

  • a

...

  • range

...

  • containing

...

  • three

...

  • areas.

...

  • The

...

  • formula

...

  • must

...

  • be

...

  • three-dimensional

...

  • (i.e.,

...

  • it

...

  • must

...

  • specify

...

  • the

...

  • sheet

...

  • or

...

  • sheets).

...

...

...

  • This method takes a formula representing the range, for example "=Sheet1\!A1:G10"

...

  • defines

...

  • a

...

  • range

...

  • containing

...

  • one

...

  • area

...

  • and

...

  • "=Sheet1\!B$12:$H$21

...

  • Sheet1\!$F$18:$K$29

...

  • Sheet1\!D$16:$M$21"

...

  • defines

...

  • a

...

  • range

...

  • containing

...

  • three

...

  • areas.

...

  • The

...

  • formula

...

  • must

...

  • be

...

  • three-dimensional

...

  • (i.e.,

...

  • it

...

  • must

...

  • specify

...

  • the

...

  • sheet

...

  • or

...

  • sheets).

...

 

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.

...

Image Added

 

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

...

...

  • - This method takes a formula representing the range, for example "=Sheet1\!A1:G10",

...

  • and

...

  • a

...

  • name

...

  • for

...

  • the

...

  • range.

...

  • The

...

  • formula

...

  • must

...

  • be

...

  • three-dimensional

...

  • (i.e.,

...

  • it

...

  • must

...

  • specify

...

  • the

...

  • sheet

...

  • or

...

  • sheets).

...

...

...

  • - This method takes an array of [Area]

...

  • objects

...

  • representing

...

  • the

...

  • range

...

  • and

...

  • a

...

  • name

...

  • for

...

  • the

...

  • range.

...

...

...

...

...

...

...

...

  • - This method takes a formula representing the range, for example "=Sheet1\!A1:G10",

...

  • and

...

  • a

...

  • name

...

  • for

...

  • the

...

  • range.

...

  • The

...

  • formula

...

  • must

...

  • be

...

  • three-dimensional

...

  • (i.e.,

...

  • it

...

  • must

...

  • specify

...

  • the

...

  • sheet

...

  • or

...

  • sheets).

...

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

...

  1. an

...

  1. area

...

  1. in

...

  1. a

...

  1. worksheet.

...

  1. Code Block
    ExcelApplication xla = new ExcelApplication();
    Workbook wb = xla.Create();
    Worksheet ws = wb.Worksheets[0];
    Area targetArea = ws.CreateArea(4, 4, 15, 6);

...

  1. Get a rectangular array,

...

  1. DataView,

...

  1. DataReader,

...

  1. or

...

  1. -

...

  1. as

...

  1. in

...

  1. the

...

  1. following

...

  1. example

...

  1. -

...

  1. DataTable

...

  1. to

...

  1. use

...

  1. as

...

  1. the

...

  1. data

...

  1. source.

    Code Block
    // ADO.NET code to get a DataTable from a query
    

...

  1. DataTable employeeDt = new DataTable();
    using(SqlConnection conn = new SqlConnection(connString))
    {
    	string employeeSQL = "SELECT FirstName + ' ' + LastName As Name " +
    	

...

  1.     "FROM Employee";
    	SqlCommand cmdEmployee = new SqlCommand(employeeSQL, conn);
    	SqlDataAdapter adptEmployee = new SqlDataAdapter(cmdEmployee);
    	adptEmployee.Fill(employeeDt);
    }

...

  1. Call ImportData to import the data. The method returns a new area containing the imported values.

    Code Block
    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

...

  1. a

...

  1. style.

...

  1. Code Block
    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;

...

  1. #

...

  1. Define

...

  1. an

...

  1. area

...

  1. or

...

  1. range,

...

  1. for

...

  1. example:

...

  1. Code Block
    Area areaTotalRow = ws.CreateArea(24, 0, 1, 3);

...

  1. Set or apply the style.

    Code Block
    areaTotalRow.Style = styleTotalRow;

...

For

...

more

...

information

...

on

...

setting

...

and

...

applying

...

styles,

...

see

...

Styles

...

.

...