Selecting Ranges of Cells

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > Microsoft OOXML Excel 2007+ >

Selecting Ranges of Cells

You can define what range of cells must be read by MapForce (when reading from a workbook) or written to (when writing to a workbook) from the "Select Range of Cells" dialog box. To open this dialog box, click the AddRemove_Selections button next to a cell range on the component.

excel_select_range_of_cells

Select Range of Cells dialog box

The available settings are as follows.

 

Load Range from Excel Input File

If you are reading from a workbook

Use this option to select a particular worksheet range, named range or table.

xlsx_select_range

If the Show Worksheets by name option is disabled (see Adding and Removing Worksheets), data from all worksheets is visible in the list.

 

The Refresh button updates the cell ranges from the input Excel file.

 

Note that only rectangular ranges are currently supported.

 

If you are writing to a workbook

This option is not available.

Starting Row

The Row option lets you define the first row of data for the specific range. For example, if you enter "5" as starting row, MapForce will read (or write) beginning with the fifth row of the workbook.

 

The Previous range with offset option is meaningful if there is another range in the same worksheet. It instructs MapForce to move the current range N rows down from the previously defined range. The minimum offset value is 1.

Row Count

If you are reading from a workbook

Count defines the exact number of rows from which you want to read data, starting from the position defined in the Starting Row (see previous option). This value is automatically populated if you selected an Excel named range or table.

The option Dynamic instructs MapForce to read all rows found in the source data beginning with Starting Row. Use this option only if your range is the last defined range of the worksheet, otherwise any subsequent range will not select data from the source Excel file.

 

If you are writing to a workbook

Count defines the exact number of rows to which data should be written, starting from the position defined in the Starting Row (see previous option). Note that if your input instance contains more rows than allowed by Count, MapForce writes only the number of rows defined by Count, and ignores the rest of data without any warning.

 

The option Dynamic instructs MapForce to write all rows found in the input instance, beginning with Starting Row.

 

If you defined a header row using the First row is header with column names option, Count does not take the header row into account (see the Excel_Company_to_XML.mfd sample).

xlsx_first_row_is_header

Show a single Cells item for all columns

Collapses all cell items into a single mappable Cells item as shown below.

xlsx_single_cells_item

If you are reading from a workbook

Use this option if you want to read all the cells of a particular row. For examples, see the ExcelColumnsToRecords.mfd and ExcelWith2Dimensions.mfd samples available in the MapForceExamples project, OOXML Excel 2007+ folder.

 

If you are writing to a workbook

Use this option to write data to one or multiple cells in the same row. For an example, see the Altova_Hierarchical_Excel.mfd sample.

Show separate items for columns

This option enables you to access individual columns of the given row range.

 

If you selected a worksheet range, named range or table, the column names are automatically populated. Otherwise, you can select specific column names by typing their corresponding alphabetic letter in the from and to text boxes.

 

If the ranges in the input Excel file have changed, click Reload to update the component with the changes.

 

To instruct MapForce to consider the first row of a range as the column header for that range, select the First row is header with column names option. When you activate or deactivate this option, and Row Count has been set, MapForce prompts you to optionally adjust the Row Count value. This prevents the Row Count from being one row too large, or too small.

xlsx_row_count_warning

Note that the Row Count setting does not take the header row into account.


© 2019 Altova GmbH