Writing data to Excel

When using Excel data for data processing, note the following when writing data to Excel.

(1) Sheets to be written

When writing data to a book file, you can specify the target sheets by using the following methods:

  • Use a fixed value

  • Use a field value

By specifying both a method for selecting target sheets and names of individual target sheets, you can write data in units of single sheets or in units of multiple sheets according to your needs.

a) The method to write data to only one sheet

To write data to a book file in units of individual sheets, use the following settings:

Select Use a fixed value, and then specify the name of one sheet to which you want to write data.

This method, where the setting specified directly corresponds to the name of a sheet to which data is output, is useful if you already know the name of a sheet to which you want to write data.

Note that, if the specified sheet does not exist in a file to which the data is to be output, a new sheet will be created with the specified name. The characters that can be specified for a sheet name comply with the specifications of Microsoft.

If Use a fixed value is selected, you cannot specify multiple sheet names. Sheet names cannot be mapped either.

b) The method to write data to multiple sheets

Note

The sheets to which data is to be written must be in the same format.

To write to multiple sheets of a book file, use the following settings:

Select Use a field value, map the field names to the Sheet that are used in the input settings.

This method is useful when you want to output data items sorted by field (such as department name, ID, type, or date) so that the data is categorized into different sheets according to the subject.

The number of sheets to be created is the same as the number of field types that are mapped to the Sheet of the mapping destination. For example, if you are sorting the data by ID and IDs 0001 to 0010 exist, 10 sheets will be created. Note that, the input data to be used as field values does not need to be sorted in any specific order.

(2) Writing data to a sheet

Use the methods described below to write data to a sheet. You can use the method for writing data in units of records and the method for writing data in units of cells together.

Write data in units of records

Use this method to write data in a specific range of cells in a single row. The data is regarded as a single record and written vertically. This method is useful for writing data to Excel data in a table format.

Write data in units of cells

Use this method to write data by directly specifying locations of cells. This method is useful for writing data to Excel data in a ledger format where locations of data to be written is scattered throughout a sheet.

Note

You can use the method for writing data in units of records and the method for writing data in units of cells together.

a) Write data in units of records

To write data in units of records, specify a field name of your choice for the field name.

Specify a column letter for the column.

Example: "A"

 

Use the following method to specify locations to which data is to be written.

  • Specify a row from which writing starts

    Data is written starting from the specified row number. The data in a table format where a header cell contains information can also be read.

    Figure 4.96 Overview - Data writing in units of records

b) Writing in units of cells

To write in units of cells, specify a field name of your choice for the field name.

Specify a column letter and row number for the cell.

Example: "A1"

 

The following describes locations to which data is written.

  • Write to a single cell or multiple cells

    To write data in units of cells, specify as many locations as the number of cells you want to write data to.

    • Note

    When writing in units of cells, if a single cell applies to multiple output records, the last record is chosen for output. If both a cell and a record are applicable, the record is chosen for output.

     

  • Write to merged cells

    If cells are merged, data is written to that merged cells as the value of the top left cell in the merged range.

    Figure 4.97 Overview - Data writing in units of cells

(3) Writing data by using templates

By using Excel functionality that is set in advance for a template file, such as format, numeric formula, functions, and graphs, you can quickly create documents that match specific purposes.

To use the template file, specify the path of the template file at the same time as when setting write destination files, and then specify the name of one sheet in the template file. The template file is copied as an output file when data is output, and the specified template sheet also is copied to generate an output sheet. Data will be output to the generated sheet. As a result, the output file includes the template sheet.

You can use a book file with multiple sheets for a template file. This enables you to include sheets such as the following to an output file: a sheet for managing front covers or logs, a sheet that contains a description of how to view data or samples, and a sheet that contains the past data to be referenced. The following describes an overview of writing that uses template files.

Figure 4.98 Writing images using a template

If the template file is used to output data, the format specified for the template file will be applied to the output data.

Note

If data is output to an Excel file whose format is "xlsx" or "xlsm", and the format settings of a sheet is one of the following patterns, the format settings specified for that sheet will not be applied.

  • Format settings are specified for an entire sheet

  • Format settings are specified for an entire row

  • Format settings are specified for an entire column

(4) Write behavior in various output patterns

The following describes the writing behavior for each data output pattern

a) Output data to hidden cells or sheets

Even if cells or sheets are hidden, data can be output to the hidden locations. The hidden locations remain hidden when a file is opened, but the data is written.

b) Output data to a book file for which the read only recommended setting is enabled

Data can be output to a book file whose Read-only recommended check box is selected.

c) Output to a shared book file

Do not output data to a shared book file. Data can be written, but the file might get corrupted.