Skip to content

xlsx-write-access-8dddce9.md

Latest commit

 

History

History
517 lines (371 loc) · 17.1 KB

xlsx-write-access-8dddce9.md

File metadata and controls

517 lines (371 loc) · 17.1 KB

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=>document->empty( )->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->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( ).

Caution:

Write access can also be used to update existing XLSX documents. The original documents must meet one of the following prerequisites:

  • The XLSX file was originally created using the XLSX Write APIs

    or

  • The XLSX file was created manually or with other tools, but its content and functional scope are fully compatible with the features supported by the XCO XLSX Write APIs (this means that the file could also be created using these APIs)

If these conditions are not fulfilled, the behavior of the update operation is undefined and correct processing can't be guaranteed. In such cases, errors may occur during processing, and the resulting XLSX documents may show unexpected behavior. Certain features, formatting, or functionalities may be altered, impaired, or lost.

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) via the method SELECT on IF_XCO_XLSX_WA_WORKSHEET. Accessing the cells contained in the selection is done following a stream-based 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=>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 elementSPRAS 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 ) ) ).

Cell Border Customization

You can customize the borders of individual cells, with each border side (top, bottom, left, right) having its own style and color. You can also apply an outline to a range of cells. You can customize borders for a single cell like this:

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 )
).

" Configure border properties
DATA(lo_border) = xco_cp_xlsx=>style->border( ).

" You can set individual border side.
lo_border->set_top(
  io_style = xco_cp_xlsx=>border_style->dashed
  io_color = xco_cp_xlsx=>color->standard->blue
).

lo_border->set_bottom(
  io_style = xco_cp_xlsx=>border_style->thick
  io_color = xco_cp_xlsx=>color->standard->blue
).

lo_border->set_left(
  io_style = xco_cp_xlsx=>border_style->double
  io_color = xco_cp_xlsx=>color->standard->blue
).

lo_border->set_right(
  io_style = xco_cp_xlsx=>border_style->medium
  io_color = xco_cp_xlsx=>color->standard->blue
).

" You can also set the cell outline.
lo_border->set_outline(
  io_style = xco_cp_xlsx=>border_style->medium
  io_color = xco_cp_xlsx=>color->standard->blue
).

" Apply alignment to target cell
lo_cursor->get_cell( )->apply_styles( VALUE #( ( lo_border ) ) ).

You can apply an outline to a range of cells like this:

Sample Code:

" Get cells pattern reference
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->set_outline(
  io_pattern = lo_pattern
  io_style   = xco_cp_xlsx=>border_style->thick
  io_color   = xco_cp_xlsx=>color->standard->purple
).

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 ).
  
" Get cells pattern reference
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( ).
  
" Merge and unmerge cells
lo_worksheet->merge_cells( lo_pattern ).
lo_worksheet->unmerge_cells( lo_pattern ).
 
" Set the outline of cells range
lo_worksheet->set_outline(
  io_style = xco_cp_xlsx=>border_style->medium
  io_color = xco_cp_xlsx=>color->standard->blue
).
  
" Protect
lo_worksheet->protect( ).
 
" Show or remove Gridline
lo_worksheet->show_gridlines( abap_true ).
lo_worksheet->show_gridlines( abap_false ).