## 4.2.15.16.4.11.2  XLSX Write Access
The starting point for programmatically writing the content of an XLSX document is to get a write access for the document. Find out how this is done.
## Context
You can create a new, empty XLSX document and get write access for it like this:
##  Sample Code
DATA(lo\_write\_access) = xco\_cp\_xlsx=&gt;document-&gt;empty( )-&gt;write\_access( ).
An empty XLSX document consists of one worksheet named Sheet1 which you can access via
##  Sample Code
DATA(lo\_worksheet) = lo\_write\_access-&gt;get\_workbook(
```
)->worksheet->at_position( 1 ).
```
You can populate it with data using the means described below. Once the worksheet has been filled as desired, you can get the corresponding file content of the document as an XSTRING via
```
 Sample Code DATA(lv_file_content) = lo_write_access->get_file_content( ).
```
Adding a new worksheet works like this:
```
 Sample Code DATA(lo_worksheet) = lo_write_access->get_workbook( )->add_new_sheet( ).
```
You can provide a name for the sheet by filling the optional parameter iv\_name .
To modify an existing XSLX document, get a write access like this:
```
 Sample Code DATA(lo_write_access) = xco_cp_xlsx=>document->for_file_content( lv_file_content )->write_access( ).
```
## Writing Data via a Stream
The first way how you can write data into a worksheet is by selecting a collection of cells based on a selection pattern (see also the section Selection patterns here: XLSX [page 1708]) via the method SELECT on IF\_XCO\_XLSX\_WA\_WORKSHEET . Accessing the cells contained in the selection is done following a streambased approach, which means that a dedicated stream is obtained for the selection that will provide sequential access to the individual blocks of the selection. One kind of stream is currently offered:
- Row stream: A row stream provides access to the selection one row at a time, traversing the selection from top to bottom
## Row streams
Row streams are best used when the structure of the data that should be written is statically known. The primary use case is to write the rows of an internal table into a corresponding portion of the worksheet (as identified by a selection). The internal table used to hold the data stream must only contain elementary components. The following operations are offered for row streams:
- Write From: The write from operation takes a reference to an internal table as the input whose rows will be written to the selected rows in the worksheet (upon running the operation)
Consider the following example of how a write from operation can be obtained and run:
```
 Sample Code " A selection pattern that was obtained via XCO_CP_XLSX_SELECTION=>PATTERN_BUILDER. DATA lo_selection_pattern TYPE REF TO if_xco_xlsx_slc_pattern. " The write access to the worksheet. DATA lo_worksheet TYPE REF TO if_xco_xlsx_wa_worksheet. " The type definition for the internal table. TYPES: BEGIN OF ts_row, first_name   TYPE string, last_name    TYPE string, day_of_birth TYPE d, END OF ts_row, tt_row TYPE STANDARD TABLE OF ts_row WITH DEFAULT KEY. DATA lt_rows TYPE tt_row. lo_worksheet->select( lo_selection_pattern )->row_stream( )->operation->write_from( REF #( lt_rows ) )->execute( ). " At this point, the rows of internal table LT_ROWS will have been written into the " worksheet selection.
```
## Writing Data via a Cursor
An alternative to writing data into a worksheet via selections and streams is to get a cursor for a worksheet write access using the method CURSOR on IF\_XCO\_XLSX\_WA\_WORKSHEET . Just as with desktop office suites, you can position a cursor on any given cell (identified by coordinate values for both the column and row of the cell). Afterwards, you can move it around the worksheet freely via the methods on IF\_XCO\_XLSX\_WA\_CURSOR :
- You can use the methods MOVE\_UP , MOVE\_RIGHT , MOVE\_DOWN and MOVE\_LEFT to move the cursor relative to its current position by the given number of steps
- You can use the methods SET\_COLUMN and SET\_ROW to set the new column or row for the cursor
- Method GET\_CELL provides access to the cell at the current position of the cursor
The following example illustrates how the current date and time (stored in ABAP variables LV\_DATE and LV\_TIME ) can be written into a worksheet:
```
 Sample Code " The write access to the worksheet. DATA lo_worksheet TYPE REF TO if_xco_xlsx_wa_worksheet. DATA(lo_cursor) = lo_worksheet->cursor( io_column = xco_cp_xlsx=>coordinate->for_alphabetic_value( 'B' ) io_row    = xco_cp_xlsx=>coordinate->for_numeric_value( 2 ) ). " Write the current date. lo_cursor->get_cell( )->value->write_from( 'Date:' ).
```
```
DATA(lv_date) = CONV d( xco_cp=>sy->date( )->as( xco_cp_time=>format->abap )>value ). lo_cursor->move_right( )->get_cell( )->value->write_from( lv_date ). " Write the current time. lo_cursor->move_down( )->move_left( )->get_cell( )->value->write_from( 'Time:' ). DATA(lv_time) = CONV t( xco_cp=>sy->time( )->as( xco_cp_time=>format->abap )>value ). lo_cursor->move_right( )->get_cell( )->value->write_from( lv_time ).
```
For an otherwise empty worksheet, this will produce the following content:
|    | A   | B     | C         |
|----|-----|-------|-----------|
|  1 |     |       |           |
|  2 |     | Date: | $LV_DATE$ |
|  3 |     | Time: | $LV_TIME$ |
where $LV\_DATE$ is the current date and $LV\_TIME$ is the current time.
## Value Transformations
When writing the value of an individual cell (via IF\_XCO\_XLSX\_WA\_CELL\_VALUE ) or of a complete row (as part of a row stream operation) it's possible to apply transformations to the value, which will affect what XLSX value is written to the cell in the worksheet. Technically, a value transformation that you can get using the method XCO\_CP\_XLSX\_WRITE\_ACCESS=&gt;VALUE\_TRANSFORMATION contains a transformation routine that can be applied to
- Values of individual cells (in case the value transformation implements the interface IF\_XCO\_XLSX\_WA\_VT\_CELL\_VALUE )
- Values of rows (in case the value transformation implements the interface IF\_XCO\_XLSX\_WA\_VT\_ROW\_VALUE )
The following value transformations are currently offered:
- Best effort
The default value transformation is the best effort value transformation. Value transformations can be set explicitly via the following methods:
- Method SET\_VALUE\_TRANSFORMATION of IF\_XCO\_XLSX\_WA\_CELL\_VALUE when the value of an individual cell is written
- Method SET\_VALUE\_TRANSFORMATION of IF\_XCO\_XLSX\_WA\_RS\_OP\_WRITE\_FRM when row values are written as part of the write from row stream operation
## "Best effort" value transformation
The best effort value transformation is based on an inspection (based on ABAP runtime type services) of the ABAP field that should be written to a given cell. Based on the type determined for the ABAP field, a
transformation is applied to the value before it's written to the cell of the worksheet. The best effort value transformation provides support for the following types of ABAP fields:
- Type ABAP\_BOOL : When an ABAP field of type ABAP\_BOOL is written to a cell, a boolean value will be written to the worksheet
- D: When an ABAP field of type D is written to a cell, a date value will be written to the worksheet
- T: When an ABAP field of type T is written to a cell, a time value will be written to the worksheet
- Data element MSEHI : When an ABAP field typed against data element MSEHI is written to a cell, the external value of the corresponding unit of measurement as determined by conversion routine CUNIT will be written to the worksheet
- Data element SPRAS : When an ABAP field typed against data element SPRAS is written to a cell, the external value of the corresponding language as determined by conversion routine ISOLA will be written to the worksheet
- C, N and STRING : When an ABAP field of type C, N or STRING is written to a cell, a string value will be written to the worksheet
- I, INT8 and P: When an ABAP field of type I, INT8 or P is written to a cell, a numeric value will be written to the worksheet
If an attempt is made to write an ABAP field of any other type to a cell of a worksheet using the best effort value transformation, you can expect a runtime error.
## Cell Styling
XCO XLSX offers various ways to style a cell in an XSLX document.
## Background Color
To change the background color of a cell, do the following:
```
 Sample Code " Get cell reference DATA(lo_cursor) = lo_worksheet->cursor( io_column = xco_cp_xlsx=>coordinate->for_alphabetic_value( 'B' ) io_row    = xco_cp_xlsx=>coordinate->for_numeric_value( 2 ) ). " Create fill style and apply to target cell DATA(lo_fill_color) = xco_cp_xlsx=>style->fill( )->set_background_color( xco_cp_xlsx=>color->standard->yellow ). lo_cursor->get_cell( )->apply_styles( VALUE #( ( lo_fill_color ) ) ).
```
## Protection Configuration
You can also protect cells from modifications by locking the entire worksheet. Single cells can then be unlocked to allow selective modifications. See the following example:
## 
```
Sample Code " Protect a worksheet lo_worksheet->protect( ).
```
```
" Get cell reference DATA(lo_cursor) = lo_worksheet->cursor( io_column = xco_cp_xlsx=>coordinate->for_alphabetic_value( 'B' ) io_row    = xco_cp_xlsx=>coordinate->for_numeric_value( 2 ) ). " Create and apply unlock style to target cell DATA(lo_protection_unlock_cell) = xco_cp_xlsx=>style->protection( )->set_locked( abap_false ). lo_cursor->get_cell( )->apply_styles( VALUE #( ( lo_protection_unlock_cell ) ) ).
```
## Data Validation
To show a drop-down list of values for a cell, set the data validation. Add the values that should be shown on the list by calling add\_source with a string that contains a single value or a comma-separated list of values. See the following example:
```
 Sample Code " Get cell reference DATA(lo_cursor) = lo_worksheet->cursor( io_column = xco_cp_xlsx=>coordinate->for_alphabetic_value( 'B' ) io_row    = xco_cp_xlsx=>coordinate->for_numeric_value( 2 ) ). " Define validation list with multiple value sources DATA(lo_data_validation) = xco_cp_xlsx=>data_validation_type->if_xco_xlsx_dat_val_type_f~list( )->add_source( '1' )->add_source( '2' )->add_source( '3,4,5' ). " Apply validation to target cell lo_cursor->get_cell( )->data_validation->set_type( lo_data_validation ).
```
## Font Customization
You can change the font in various ways as depicted in the following example:
```
" Get cell reference DATA(lo_cursor) = lo_worksheet->cursor( io_column = xco_cp_xlsx=>coordinate->for_alphabetic_value( 'B' ) io_row    = xco_cp_xlsx=>coordinate->for_numeric_value( 2 ) ). " Configure multiple font attributes DATA(lo_font) = xco_cp_xlsx=>style->font( ). lo_font->set_color( xco_cp_xlsx=>color->standard->orange )->set_type( xco_cp_xlsx=>font_type->arial )->set_size( 16 )->set_bold( )->set_italic( )->set_strikethrough( )->set_superscript( )->set_underline_type( xco_cp_xlsx=>underline_type->single ). " Apply font style to target cell lo_cursor->get_cell( )->apply_styles( VALUE #( ( lo_font ) ) ).
```
## Text Alignment Customization
You can customize the text alignment, as shown in the following example:
```
" Get cell reference DATA(lo_cursor) = lo_worksheet->cursor( io_column = xco_cp_xlsx=>coordinate->for_alphabetic_value( 'B' ) io_row    = xco_cp_xlsx=>coordinate->for_numeric_value( 2 ) ). " Configure alignment properties DATA(lo_alignment) = xco_cp_xlsx=>style->alignment( ). lo_alignment->set_horizontal_alignment( xco_cp_xlsx=>horizontal_alignment->right ). lo_alignment->set_indent( 2 ). lo_alignment->set_vertical_alignment( xco_cp_xlsx=>vertical_alignment->center ). lo_alignment->set_wrap_text( ). " Apply alignment to target cell lo_cursor->get_cell( )->apply_styles( VALUE #( ( lo_alignment ) ) ).
```
## Worksheet Customizing
You can customize your worksheet with tab coloring, cell merging operations, and much more. The method merge\_cells applies cell merging to the worksheet using a specified rectangular pattern that must represent a valid worksheet range (such as A1:C5 ). Only contiguous rectangular areas are allowed. The method unmerge\_cells splits previously merged cells back into individual cells within the given pattern, which only affects merged ranges that are fully contained within the given pattern. An example:
```
" Set tab color lo_worksheet->set_tab_color( xco_cp_xlsx=>color->standard->yellow ). " Merge and unmerge cells DATA(lo_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to( )->from_column( xco_cp_xlsx=>coordinate->for_alphabetic_value( 'A' ) )->from_row( xco_cp_xlsx=>coordinate->for_numeric_value( 1 ) )->to_column( xco_cp_xlsx=>coordinate->for_alphabetic_value( 'C' ) )->to_row( xco_cp_xlsx=>coordinate->for_numeric_value( 2 ) )->get_pattern( ). lo_worksheet->merge_cells( lo_pattern ). lo_worksheet->unmerge_cells( lo_pattern ). " Protect lo_worksheet->protect( ).
```