Demo report of numbers with various decimal places

[gsharpe2000]

I've looked at ZDEMO_EXCEL30 for hints on how to use the decimal places but it's not working for me when data is coming from SAP sources.

For example, when I have a value of .90, the spreadsheet shows .9 and the customer wants to see .90

I've used the following and it's not working.
lo_worksheet->set_cell( ip_column = lv_col ip_row = lv_row ip_value = l_charact-qcres-mvalue
ip_abap_type = lc_typekind_packed ).

l_charact-qcres-mvalue is type CHAR132 coming from SAP database.

I've also tried lc_typekind_num without success.

Has any one else had this issue and if so, how did they fix it?

Regards,

Greg Sharpe

[gsharpe2000]

Sorry, I also tried changing to another field with NNNN.NN and still the spreadsheet only shows .9

Greg

[PekkaPee]

Hi,

I struggle with the same requirement. Anyone have a solution to offer?

Br,
Pekka

[rmcknight001]

i replaced some of the numeric logic to get fixed decimal places working: here are the main points for you to code if thats what you require: perform add_style using p_lo_excel: lo_style_decimals_0b '#0_-;#0-; ;', lo_style_decimals_1b '#0.0_-;#0.0-; ;', lo_style_decimals_2b '#0.00_-;#0.00-; ;', lo_style_decimals_3b '#0.000_-;#0.000-; ;', lo_style_decimals_4b '#0.0000_-;#0.0000-; ;', lo_style_decimals_5b '#0.00000_-;#0.00000-; ;', lo_style_decimals_6b '#0.000000_-;#0.000000-; ;'. perform add_style using p_lo_excel: lo_style_decimals_0z '#0_-;#0-;#0_;', lo_style_decimals_1z '#0.0_-;#0.0-;#0.0_;', lo_style_decimals_2z '#0.00_-;#0.00-;#0.00_;', lo_style_decimals_3z '#0.000_-;#0.000-;#0.000_;', lo_style_decimals_4z '#0.0000_-;#0.0000-;#0.0000_;', lo_style_decimals_5z '#0.00000_-;#0.00000-;#0.00000_;', lo_style_decimals_6z '#0.000000_-;#0.000000-;#0.000000_;'. *&---------------------------------------------------------------------* *& Form ADD_STYLE *&---------------------------------------------------------------------* form add_style using p_lo_excel type ref to zcl_excel p_style type ref to zcl_excel_style p_mask type clike. p_style = p_lo_excel->add_new_style( ). p_style->number_format->format_code = p_mask. endform. " ADD_STYLE Then i get the decimals from here: using the dd_roll ( data element ), this is then passed and used into bind_table Hope this helps someone. it handles from 0 to 6 decimal places. data: l_dd04l type dd04l. clear: l_dd04l. select single * from dd04l into l_dd04l where rollname = ls_alv_fcat- dd_roll and as4local = 'A' and as4vers = ' '. if l_dd04l-datatype = 'CURR' and l_decimals = 2. ls_xls_fcat-style = lo_style_currency->get_guid( ). ls_xls_fcat-style_total = lo_style_currency->get_guid( ). elseif ls_alv_fcat-datatype = 'DEC' or ls_alv_fcat-datatype = 'INT4'. if l_decimals = 0. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_0b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_0b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_0z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_0z->get_guid( ). endif. elseif l_decimals = 1. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_1b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_1b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_1z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_1z->get_guid( ). endif. elseif l_decimals = 2. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_2b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_2b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_2z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_2z->get_guid( ). endif. elseif l_decimals = 3. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_3b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_3b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_3z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_3z->get_guid( ). endif. elseif l_decimals = 4. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_4b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_4b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_4z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_4z->get_guid( ). endif. elseif l_decimals = 5. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_5b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_5b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_5z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_5z->get_guid( ). endif. elseif l_decimals = 6. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_6b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_6b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_6z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_6z->get_guid( ). endif. endif. endif.

…

On Mon, Jan 30, 2017 at 5:51 PM, PekkaPee ***@***.***> wrote: Hi, I struggle with the same requirement. Anyone have a solution to offer? Br, Pekka — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <https://github.com/ivanfemia/abap2xlsx/issues/406#issuecomment-275991007>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AVmWgztnyjxnohuQW6YT0jMAvt-bOqjRks5rXYiNgaJpZM4GhIza> .

[PekkaPee]

Those masks work perfectly. Thanks, now I can skip workarouds :)

Br,
Pekka

[gregorwolf]

@rmcknight001 perhaps you can contribute that as a demo report?

[PekkaPee]

Hello,

Report is way too big to put out, but I just did as rmcknight001 suggested. Declared all masks as global constants and created styles according to them. I did not have to use dd04l, since I knew that all values have 2 decimals.

*Row, type number
go_style_row_nro = go_excel->add_new_style( ).
go_style_row_nro->font->size = 11.
go_style_row_nro->number_format->format_code = gc_style_decimals_2z.
gv_style_row_nro_guid = go_style_row_nro->get_guid( ).

And

*Colored row type number
go_style_row_nro_color = go_excel->add_new_style( ).
go_style_row_nro_color->font->size = 11.
go_style_row_nro_color->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
go_style_row_nro_color->fill->fgcolor-rgb = 'FFCCFFFF'.
go_style_row_nro_color->number_format->format_code = gc_style_decimals_2z.
gv_style_row_nro_color_guid = go_style_row_nro_color->get_guid( ).

Then used these normally with set cell
go_worksheet->set_cell( ip_column = gv_col
ip_row = lv_row
ip_value = lv_dec2
ip_style = gv_style_row_nro_color_guid ).

Format codes which came with the abap2xlsx-package did not work as desired.

Br,
Pekka

[rmcknight001]

Adding simple styles is quite easy - once you know how to do it. As you can all see some ( about 2 dozen) styles come pre defined and are part of one of the class attributes. But you can use the code previously provided to add any other formats you wish. Then you can assign those formats inside bind table as well if you wish. or in your ABAP program. My code is generic in nature and integrated into our existing ALV framework. Via a button on the grid. They can see any of our hundreds of ALV Grid reports in grid format and now in XLS format, even separate tabs for the selection options and run details. Because the calls are coming from any ALV - for me, i have to assign the style based on code in bind table ( ie i need to lookup how many decimals each field has and assign the best style) If I didnt do this we lost the alignment and saw things like: 1 1.1 1.11 in the same column. with this (on a 2 decimal field) we now see: 1.00 1.10 1.11 I have also got all ALV edit masks etc working. ie a period field is now displayed correctly ie) 01.2017 instead of 201701 These changes also improved the time taken to output (write) each value - as others have stated very large spreadsheets were a bit bogged down as the went through a lot to figure out how to format and write each value. If anyone really needs these changes I can look at uploading both the ALV hook and how i write the fields. But I am NO git hub expert though, i write ABAP - LOL On Tue, Jan 31, 2017 at 8:37 AM, Ralph McKnight <mcknight.r@gmail.com> wrote:

…

i replaced some of the numeric logic to get fixed decimal places working: here are the main points for you to code if thats what you require: perform add_style using p_lo_excel: lo_style_decimals_0b '#0_-;#0-; ;', lo_style_decimals_1b '#0.0_-;#0.0-; ;', lo_style_decimals_2b '#0.00_-;#0.00-; ;', lo_style_decimals_3b '#0.000_-;#0.000-; ;', lo_style_decimals_4b '#0.0000_-;#0.0000-; ;', lo_style_decimals_5b '#0.00000_-;#0.00000-; ;', lo_style_decimals_6b '#0.000000_-;#0.000000-; ;'. perform add_style using p_lo_excel: lo_style_decimals_0z '#0_-;#0-;#0_;', lo_style_decimals_1z '#0.0_-;#0.0-;#0.0_;', lo_style_decimals_2z '#0.00_-;#0.00-;#0.00_;', lo_style_decimals_3z '#0.000_-;#0.000-;#0.000_;', lo_style_decimals_4z '#0.0000_-;#0.0000-;#0.0000_;', lo_style_decimals_5z '#0.00000_-;#0.00000-;#0.00000_;', lo_style_decimals_6z '#0.000000_-;#0.000000-;#0.000000_;'. *&---------------------------------------------------------------------* *& Form ADD_STYLE *&---------------------------------------------------------------------* form add_style using p_lo_excel type ref to zcl_excel p_style type ref to zcl_excel_style p_mask type clike. p_style = p_lo_excel->add_new_style( ). p_style->number_format->format_code = p_mask. endform. " ADD_STYLE Then i get the decimals from here: using the dd_roll ( data element ), this is then passed and used into bind_table Hope this helps someone. it handles from 0 to 6 decimal places. data: l_dd04l type dd04l. clear: l_dd04l. select single * from dd04l into l_dd04l where rollname = ls_alv_fcat- dd_roll and as4local = 'A' and as4vers = ' '. if l_dd04l-datatype = 'CURR' and l_decimals = 2. ls_xls_fcat-style = lo_style_currency->get_guid( ). ls_xls_fcat-style_total = lo_style_currency->get_guid( ). elseif ls_alv_fcat-datatype = 'DEC' or ls_alv_fcat-datatype = 'INT4'. if l_decimals = 0. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_0b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_0b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_0z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_0z->get_guid( ). endif. elseif l_decimals = 1. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_1b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_1b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_1z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_1z->get_guid( ). endif. elseif l_decimals = 2. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_2b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_2b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_2z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_2z->get_guid( ). endif. elseif l_decimals = 3. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_3b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_3b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_3z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_3z->get_guid( ). endif. elseif l_decimals = 4. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_4b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_4b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_4z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_4z->get_guid( ). endif. elseif l_decimals = 5. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_5b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_5b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_5z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_5z->get_guid( ). endif. elseif l_decimals = 6. if ls_alv_fcat-no_zero = 'X'. ls_xls_fcat-style = lo_style_decimals_6b->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_6b->get_guid( ). else. ls_xls_fcat-style = lo_style_decimals_6z->get_guid( ). ls_xls_fcat-style_total = lo_style_decimals_6z->get_guid( ). endif. endif. endif. On Mon, Jan 30, 2017 at 5:51 PM, PekkaPee ***@***.***> wrote: > Hi, > > I struggle with the same requirement. Anyone have a solution to offer? > > Br, > Pekka > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <https://github.com/ivanfemia/abap2xlsx/issues/406#issuecomment-275991007>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AVmWgztnyjxnohuQW6YT0jMAvt-bOqjRks5rXYiNgaJpZM4GhIza> > . >