2022 Oct 06 5:53 PM
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
2022 Oct 06 9:07 PM
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'.
(c5e08e0478aa4727abc4482f5be390b2fyi)
2022 Oct 06 6:25 PM
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
2022 Oct 06 9:07 PM
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'.
(c5e08e0478aa4727abc4482f5be390b2fyi)
2022 Oct 06 9:11 PM
Ah, thanks, I still have somewhat unstable wifi at home and must yet restore my minisap, good to know 🙂
2022 Oct 07 9:23 AM
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.
2022 Oct 07 2:17 PM
2022 Oct 10 12:47 AM
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
2022 Oct 10 11:02 AM
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.
2022 Oct 19 10:21 AM
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
2022 Oct 19 12:11 PM
bharath.padmanabhan It's the wrong place to post abap2xlsx issues.
Right place to create issues: https://github.com/abap2xlsx/abap2xlsx
2022 Oct 19 12:13 PM
bharath.padmanabhan Concerning SAP notifications, see Step 5 Q&A here: Tour the SAP Community 2022 | Tutorials for SAP Developers
2022 Oct 23 6:55 PM
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
2022 Oct 23 7:15 PM
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
2022 Oct 23 9:55 PM
(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.
2022 Dec 05 3:53 AM
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
2022 Dec 05 7:13 AM