Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Dropdown in ABAP2XLSX

bharath_padmanabhan
Participant

Experts,

I am using ABAP2XLSX in my project. This amazing tool has been doing wonders for me.

I have a requirement to populate a particular cell with dropdown values. I can see program ZDEMO_EXCEL45 for the same.

As seen above, if the dropdown list is less than 254 characters long, I can use the logic in this demo program. In my case, the dropdown is QUITE long. I have to show around 25 records in view V_TE453 which contains the Calorific value and its description as a dropdown.

I am not able to find any other demo program for dropdown. Also searching the abap2xlsx GitHub didn't show much results related to the dropdown.

In case you have implemented abap2xlsx in your project, can you please let me know how you achieved dropdown functionality in a cell?

Thanks

Bharath

1 ACCEPTED SOLUTION

Sandra_Rossi
Active Contributor

That's a limit of Excel 365 for this field, but this field can have several meanings (same as in Excel).

ZDEMO_EXCEL45 shows a very limited feature.

Usually, in Excel, we refer to a range which contains the possible values, and there's no length restriction.

Replace the corresponding part in ZDEMO_EXCEL45 with the code below. The literals contain more than 255 characters, it works well:

  lo_worksheet->set_cell( ip_columnrow = 'A6' ip_value = '123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+' ).
  lo_worksheet->set_cell( ip_columnrow = 'A7' ip_value = '123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+' ).
  lo_worksheet->set_cell( ip_columnrow = 'A8' ip_value = '123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+' ).
  lo_data_validation->type           = zcl_excel_data_validation=>c_type_list.
  lo_data_validation->formula1       = '$A$6:$A$8'.
(c5e08e0478aa4727abc4482f5be390b2

fyi)

15 REPLIES 15

abo
Active Contributor
0 Kudos

Now, this is a good question! I suspect it is an internal limitation but I'll have to check whether it comes from Excel or the library

Sandra_Rossi
Active Contributor

That's a limit of Excel 365 for this field, but this field can have several meanings (same as in Excel).

ZDEMO_EXCEL45 shows a very limited feature.

Usually, in Excel, we refer to a range which contains the possible values, and there's no length restriction.

Replace the corresponding part in ZDEMO_EXCEL45 with the code below. The literals contain more than 255 characters, it works well:

  lo_worksheet->set_cell( ip_columnrow = 'A6' ip_value = '123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+' ).
  lo_worksheet->set_cell( ip_columnrow = 'A7' ip_value = '123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+' ).
  lo_worksheet->set_cell( ip_columnrow = 'A8' ip_value = '123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+'
    && '123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+123456789+' ).
  lo_data_validation->type           = zcl_excel_data_validation=>c_type_list.
  lo_data_validation->formula1       = '$A$6:$A$8'.
(c5e08e0478aa4727abc4482f5be390b2

fyi)

abo
Active Contributor

Ah, thanks, I still have somewhat unstable wifi at home and must yet restore my minisap, good to know 🙂

0 Kudos

Hi Sandra,

Thanks a bunch for your valuable reply.
I am not sure how I can continue typing AFTER pasting my code in the thread. So I have given the code in the end.

I am facing a few challenges:

1) I have attached dropdown to cell A2 in my excel sheet. Cells D2 to D4 have dropdown values. When I choose the first value in the dropdown(CVAKTA CV District for Alapuzha), it's displayed in the A2 cell correctly. So far so good. But if I choose 2nd or 3rd value from the dropdown, it's not populating in A2 and simply gives a blank value. I tried passing allowblank field in zcl_excel_data_validation as abap_false and it didn't help. I want the user to be able to select any value from the dropdown and NOT just the 1st value.

3) I want to show only the key value in the dropdown. For example, 1st value in the dropdown says 'CVAKTA CV District for Alapuzha'. If I choose this, I want to show CVAKTA in cell A2. Currently, it's showing the entire row CVAKTA CV District for Alapuzha. Is there a way to fill the key field in cell A2 only AFTER the user selects the value from the dropdown?

Given below is the screenshot of my excel sheet for your reference:


Given below is the code that I have added based on your solution:

      lo_worksheet->set_cell( ip_columnrow = 'D2' ip_value = 'CVAKTA CV District for Alapuzha' ).
      lo_worksheet->set_cell( ip_columnrow = 'D3' ip_value = 'CVAKTK CV District for Kollam' ).
      lo_worksheet->set_cell( ip_columnrow = 'D4' ip_value = 'CVAKTT CV District for Thiruvananthapuram' ).
* Create validation object
      lo_data_validation = lo_worksheet->add_new_data_validation( ).
* Create new validation
      lo_data_validation->type           = zcl_excel_data_validation=>c_type_list.
      lo_data_validation->formula1       = '$DA$2:$D$4'.
      lo_data_validation->cell_row       = 2.
      lo_data_validation->cell_row_to    = 2.
      lo_data_validation->cell_column    = 'A'.
      lo_data_validation->cell_column_to = 'A'.
      lo_data_validation->allowblank     = abap_true.
      lo_data_validation->showdropdown   = abap_true.

Typo in formula1:

'$DA$2:$D$4'

It should be:

'$D$2:$D$4'

Thank you so much, Sandra. I changed the formula now and it's populating values from the dropdown.

Out of 2 challenges, point 1 has been addressed now. Thanks to you.

For point 2 where I want to just populate the key value, I am not sure how this can be achieved.

As a workaround, I added 2 columns in that excel sheet and I am simply showing the values from the table V_TE453 in these 2 columns - Column 1 which shows the key value and column 2 which shows the description. I managed to convince the users to use this as their reference to choose the correct value that they want. So instead of a dropdown showing all the key values and description, these 2 new columns in excel are showing the same values.

I got lucky with this client. In the future, I would be interested to see a feature in ABAP2XLSX which allows us to just populate the key field alone in the cell when a value is selected from the dropdown.

Say my dropdown has 2 values as below,

KEY Description

Apple Morning fruit

Orange Evening fruit

So the dropdown has 2 fruit names along with the description. Say user selects the 1st row in the dropdown which is Apple Morning fruit, we should simply populate the KEY value Apple in the cell instead of Apple Morning fruit.

Thank you so much for your valuable help, Sandra. Its because of experts like you who patiently answer our questions, we are able to deliver better solutions.

BR,

Bharath

0 Kudos

I don't know how to do it in Excel except with VBA and forms. In abap2xlsx you can create VBA macros, not sure concerning forms.

You may open an issue in https://github.com/abap2xlsx/abap2xlsx with an Excel sample that you want abap2xlsx to be able to create and someone can have a look at it.

0 Kudos

Thanks a bunch, Sandra. Sorry for the late reply. I am not getting notifications to my email ID whenever there is an update in my threads. Not sure what I am missing.

I have created a new issue in Github

BR,

Bharath

bharath.padmanabhan It's the wrong place to post abap2xlsx issues.

Right place to create issues: https://github.com/abap2xlsx/abap2xlsx

0 Kudos

bharath.padmanabhan Concerning SAP notifications, see Step 5 Q&A here: Tour the SAP Community 2022 | Tutorials for SAP Developers

I have now moved my abap2xlsx issue thread to the right place based on your guidance. Thank you so much, Sandra.

This forum is hands down a better place because of experts like you who generously give their valuable time and energy to patiently help young developers like me.

Thanks

Bharath

Sandra,

I checked step 5 of SAP community tour link. I see that my communication settings are in place to notify me via email. My email ID looks correct. I also ensured that SCN email ID is NOT blocked in my personal email.
Not sure what else is missing. I really want this feature back as I was getting these notifications years back without any issues. I can't even recall since when they stopped working.

For now, I bookmark my SCN threads and I check every now and then to see if there is a reply. This is a tentative workaround. But sometimes, I end up replying several days after someone like you has invested their time and energy to reply back to me. This makes me feel very bad as I know how lucky I am to get support from experts like you.

Thanks

Bharath

bharath.padmanabhan

(I paste your hyperlinked name in case it works better for notifications in your case)

1) I answered in GitHub Web site.

2) Concerning the email, sorry for you. You might ask the community why it doesn't work. Use the primary tag SAP Community.

Sandra,

I never used the primary tag SAP Community so far. I guess that might be the reason why I am not getting notified. Let me try this for my future threads. Its so nice of you to answer basic questions like this.

Good day.

Thanks

Bharath

0 Kudos

It just depends on your SAP Community profile settings.