Wiki Markup |
---|
An *Area* object represents a rectangular set of cells. To create an Area object, call one of the following methods: |
...
* {code}[Worksheet.CreateArea(Int32, Int32, Int32, Int32)] |
...
{code}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. |
...
* {code}[Worksheet.CreateArea(String)] |
...
{code}This method takes a formula representing the area, for example, "=A1:G10". The formula is relative to the current worksheet. |
...
* {code}[Worksheet.CreateAreaOfColumns(Int32, Int32)] |
...
{code}This method creates a range containing one rectangular area. |
...
{obsolete |
...
}This method has been deprecated in favor of using [Worksheet.GetColumnProperties(Int32)] to get and edit column properties. |
...
{obsolete} * {code}[Worksheet.CreateAreaOfRows(Int32, Int32)] |
...
{code}This method creates a range containing one rectangular area. |
...
{obsolete |
...
}This method has been deprecated in favor of using [Worksheet.GetRowProperties(Int32)] to get and edit row properties. |
...
{obsolete} 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: |
...
* {code}[Workbook.CreateRange(String)] |
...
{code}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). |
...
* {code}[Workbook.CreateRange(Area())] |
...
{code}This method takes an array of [Area] objects representing the range. |
...
* {code}[Worksheet.CreateRange(String)] |
...
{code}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. |
...
\\\ |
...
!Ranges.png!\\To create a named range, call one of the following methods: * {code}[Workbook.CreateNamedRange(String, String)] |
...
{code}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). |
...
* {code}[Workbook.CreateNamedRange(Area(), String)] |
...
{code}This method takes an array of [Area] objects representing the range and a name for the range. |
...
* {code}[Worksheet.CreateNamedRange(Int32, Int32, Int32, Int32, String)] |
...
{code}This method returns a named range that contains one rectangular area. |
...
* {code}[Worksheet.CreateNamedRange(String, String)] |
...
{code}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). |
...
h2. Importing Data to an Area |
...
You can use an area as a set of target cells for imported data. The [Area] object's [ImportData|Area.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: |
...
# Create an area in a worksheet. |
...
{code |
...
:c# |
...
} ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Create(); Worksheet ws = wb.Worksheets[0]; Area targetArea = ws.CreateArea(4, 4, 15, 6); |
...
{code} # 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 |
...
{code |
...
:c# |
...
} 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); } |
...
{code} # Call ImportData to import the data. The method returns a new area containing the imported values. {code:c#} importedValues = targetArea.ImportData(employeeDt); |
...
{code} For more information, see [Importing Data]. |
...
h2. Applying Formatting to Areas and Ranges |
...
Defined styles can be assigned to [cells|Cell.Style], [rows|RowProperties.Style], [columns|ColumnProperties.Style], [areas|Area.SetStyle(Style)], and [ranges|Range.SetStyle(Style)]. To assign a style to a range or area: |
...
# Create a style. |
...
{code |
...
:c# |
...
} 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; |
...
{code} # Define an area or range, for example: {code:c#}Area areaTotalRow = ws.CreateArea(24, 0, 1, 3); |
...
{code} # Set or apply the style. {code:c#}areaTotalRow.Style = styleTotalRow; |
...
{code} For more information on setting and applying styles, see [Styles |
...
|Styles in ExcelWriter].
{scrollbar} |