Skip to Content
0

Excel download with listbox / Dropdown in one column for field plant

Jun 02, 2017 at 12:14 PM

240

avatar image
Former Member

Requirement is to add dropdown and set values to one field to excel file downloading with predefined format of few columns.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Evgeny Gubenko Jun 02, 2017 at 12:25 PM
0

Hi!

You can record a macro using such steps: go to Data tab, select Data Validation and set Allow = List, Source = range of cells which contain allowed values. And then call this macro while creating your output.

Hope it's helpful

Evgeny

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks for writing..

I am not getting direction to it that how to initiate as right now i am just using GUI_Download to download file and passed structure excel.

If i get how to write and what to then i will be able to manage.

0
Raymond Giuseppi
Jun 02, 2017 at 12:22 PM
0

Did you perform some search on OLE, and Excel macro recording /or/ look for abap2xlsx project.

Show 6 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks for writing..

No i have not used any OLE method and no micro for that....

I need logic to write in my abap code...

0

Start with links from abap2xlsx in the wiki

0

abap2xlsx provides the demo program ZDEMO_EXCEL9, with this result:

And with the following code:

lo_worksheet->set_cell( ip_row = 2 ip_column = 'C' ip_value = c_fruits ).
lo_worksheet->set_cell( ip_row = 4 ip_column = 'C' ip_value = 'Apple' ).
lo_worksheet->set_cell( ip_row = 5 ip_column = 'C' ip_value = 'Banana' ).
lo_worksheet->set_cell( ip_row = 6 ip_column = 'C' ip_value = 'Blueberry' ).
lo_worksheet->set_cell( ip_row = 7 ip_column = 'C' ip_value = 'Ananas' ).
lo_worksheet->set_cell( ip_row = 8 ip_column = 'C' ip_value = 'Grapes' ).

lo_range            = lo_excel->add_new_range( ).
lo_range->name      = c_fruits.
lo_range->set_value( ip_sheet_name    = lv_title
                     ip_start_column  = 'C'
                     ip_start_row     = 4
                     ip_stop_column   = 'C'
                     ip_stop_row      = 8 ).

lo_data_validation              = lo_worksheet->add_new_data_validation( ).
lo_data_validation->type        = zcl_excel_data_validation=>c_type_list.
lo_data_validation->formula1    = c_fruits.
lo_data_validation->cell_row    = row.
lo_data_validation->cell_column = 'A'.
lo_worksheet->set_cell( ip_row = row ip_column = 'A' ip_value = 'Select a value' ).
pda2e.png (17.6 kB)
0
Former Member

Thanks for showing the way to achieve.

The full code is this : Problem is with custom classes defined here? Why custom and what to add there?

REPORT  zdemo_excel6bis.

DATA: lo_excel                TYPE REF TO zcl_excel,
      lo_excel_writer         TYPE REF TO zif_excel_writer,
      lo_worksheet            TYPE REF TO zcl_excel_worksheet,
      lv_file                 TYPE xstring,
      lv_bytecount            TYPE i,
      lt_file_tab             TYPE STANDARD TABLE OF solisti1,
      lo_style                TYPE REF TO zcl_excel_style,
      lv_style_guid           TYPE zexcel_cell_style.

CREATE OBJECT lo_excel.

lo_style = lo_excel->add_new_style( ).
lo_style->number_format->format_code = zcl_excel_style_number_format=>c_format_number_comma_sep1.
lv_style_guid = lo_style->get_guid( ).

" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).

lo_worksheet->set_cell( ip_row = 4 ip_column = 'C' ip_value = 100 ip_style = lv_style_guid ).
lo_worksheet->set_cell( ip_row = 5 ip_column = 'C' ip_value = 1000  ).
lo_worksheet->set_cell( ip_row = 6 ip_column = 'C' ip_value = 150 ).
lo_worksheet->set_cell( ip_row = 7 ip_column = 'C' ip_value = -10  ).
lo_worksheet->set_cell( ip_row = 8 ip_column = 'C' ip_value = 500  ).
lo_worksheet->set_cell( ip_row = 9 ip_column = 'C' ip_formula = 'SUM(C4:C8)' ).

CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
lv_file = lo_excel_writer->write_file( lo_excel ).

"convert to table
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
  EXPORTING
    buffer        = lv_file
  IMPORTING
    output_length = lv_bytecount
  TABLES
    binary_tab    = lt_file_tab.
"Save the file
CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
    bin_filesize = lv_bytecount
    filename     = 'C:\FormulaBis.xlsx'
    filetype     = 'BIN'
  TABLES 

data_tab = lt_file_tab.

0

These custom classes are provided with abap2xlsx project material (it's free; search internet to download it).

0
Former Member

Thanks.

I have searched a lot and gone through so many linksbut didnt find any code or these customised classes.

If you can provide me then i would be greatful.

Thanks

0