Using ole2 objects to create an excel file
Tired of download always the same excel sheet ? Without colours, borders, validations, etc…
Let’s see how to create a lovely excel sheet like this using ole2 objects: 😀
First you need to know the different parts in MS excel. Each part will represent an ole2 object in our program:
Starting
All the examples below use the following template report. You only need to copy the code on the example and paste it in the space reserved for this purpose.
In this report you can see how to create a new document, how to save it and how to close it.
Template Report |
---|
REPORT zric_ole2. TYPE-POOLS: soi,ole2. DATA: lo_application TYPE ole2_object, lo_workbook TYPE ole2_object, lo_workbooks TYPE ole2_object, lo_range TYPE ole2_object, lo_worksheet TYPE ole2_object, lo_worksheets TYPE ole2_object, lo_column TYPE ole2_object, lo_row TYPE ole2_object, lo_cell TYPE ole2_object, lo_font TYPE ole2_object. DATA: lo_cellstart TYPE ole2_object, lo_cellend TYPE ole2_object, lo_selection TYPE ole2_object, lo_validation TYPE ole2_object. DATA: lv_selected_folder TYPE string, lv_complete_path TYPE char256, lv_titulo TYPE string. CALL METHOD cl_gui_frontend_services=>directory_browse EXPORTING window_title = lv_titulo initial_folder = ‘C:\’ CHANGING selected_folder = lv_selected_folder EXCEPTIONS cntl_error = 1 error_no_gui = 2 OTHERS = 3. CHECK NOT lv_selected_folder IS INITIAL. CREATE OBJECT lo_application ‘Excel.Application’. CALL METHOD OF lo_application ‘Workbooks’ = lo_workbooks. CALL METHOD OF lo_workbooks ‘Add’ = lo_workbook. SET PROPERTY OF lo_application ‘Visible’ = 0. GET PROPERTY OF lo_application ‘ACTIVESHEET’ = lo_worksheet. * ———- * —- PASTE HERE THE CODE * ———-
CALL METHOD OF lo_workbook ‘SaveAs’ CALL METHOD OF lo_application ‘QUIT’. |
Basic movements
Select a cell |
---|
CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell |
Select a Range of cells |
---|
* 1. Select starting cell CALL METHOD OF lo_worksheet ‘Cells’ = lo_cellstart EXPORTING #1 = 1 #2 = 1. * 2. Select ending cell * Select the Range: |
Select a Column |
---|
CALL METHOD OF lo_worksheet ‘Columns’ = lo_column EXPORTING #1 = 1. |
Select a Row |
---|
CALL METHOD OF lo_worksheet ‘Rows’ = lo_row EXPORTING #1 = 1. |
Get the selection reference |
---|
* Select a Row CALL METHOD OF lo_worksheet ‘Rows’ = lo_row EXPORTING #1 = 1. * Active the selection |
Change the active Worksheet |
---|
CALL METHOD OF lo_application ‘Worksheets’ = lo_worksheet EXPORTING #1 = 2. CALL METHOD OF lo_worksheet ‘Activate’. |
Change the name of the Worksheet |
---|
SET PROPERTY OF lo_worksheet ‘Name’ = ‘Hello!’. |
Add a new Worksheet |
---|
CALL METHOD OF lo_worksheet ‘add’. |
Modifying the content
I think the best way to understand how it works is creating a macro in excel and seeing the Visual Basic code in order to “translate” it to abap.
For create a macro you need first to activate the developer tab, the following link explains how to do it:
Create a macro is easy, you can follow the following link:
http://office.microsoft.com/en-us/excel-help/create-or-delete-a-macro-HP010014111.aspx
I also recommend to download the VB language reference as guide.
http://msdn.microsoft.com/en-us/library/aa220733(v=office.11).aspx
Compare the VB code with the Abap code. And you will understand how it works. You don’t need to transform the entire VB code in Abap in all the cases, only the parts you need.
1 – Select a cell and set a Value:
Abap |
---|
CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = 1 “Row #2 = 2. “Column SET PROPERTY OF lo_cell ‘Value’ = ‘Hello World’. |
Result:
2- Change Font and Size
Abap |
---|
CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = 1 “Row #2 = 2. “Column SET PROPERTY OF lo_cell ‘Value’ = ‘Hello World’. CALL METHOD OF lo_cell ‘FONT’ = lo_font. SET PROPERTY OF lo_font ‘Name’ = ‘Arial’. SET PROPERTY OF lo_font ‘Size’ = 15. |
Result:
3- Change Colour, Bold, Underline and Italics:
Abap |
---|
CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = 1 “Row #2 = 2. “Column SET PROPERTY OF lo_cell ‘Value’ = ‘Hello World’. CALL METHOD OF lo_cell ‘FONT’ = lo_font. SET PROPERTY OF lo_font ‘Color’ = –16776961. SET PROPERTY OF lo_font ‘TintAndShade’ = 0. SET PROPERTY OF lo_font ‘Bold’ = 1. SET PROPERTY OF lo_font ‘Italic’ = 1. SET PROPERTY OF lo_font ‘Underline’ = 2. “xlUnderlineStyleSingle DATA: lo_interior TYPE ole2_object. CALL METHOD OF lo_cell ‘Interior’ = lo_interior. SET PROPERTY OF lo_interior ‘Color’ = 15773696. |
Result:
TIP: In this example you can see we are using the VB constant xlUnderlineStyleSingle with the value 2. To know the values of those constants in excel you can download the VBA language reference from the link http://msdn.microsoft.com/en-us/library/aa220733(v=office.11).aspx and follow the following path for see all the constants. Or also you can use this link to look up the constants values online http://msdn.microsoft.com/en-us/library/aa221100(v=office.11).aspx. But I prefer to execute the macro recorded step by step for debug the code and you can see the value of the constants you want leaving the mouse cursor over that constant. |
– 4 Add Borders
Abap |
---|
DATA: lo_borders TYPE ole2_object.
CALL METHOD OF lo_cell ‘Borders’ = lo_borders EXPORTING #1 = ‘7’. “xlEdgeLeft
CALL METHOD OF lo_cell ‘Borders’ = lo_borders EXPORTING #1 = ‘8’. “xlEdgeTop
CALL METHOD OF lo_cell ‘Borders’ = lo_borders EXPORTING #1 = ‘9’. “xlEdgeBottom
* Increase the weight of the border if you want, in this case only for EdgeRight:
|
Result:
– 5 Change cell format
Abap |
---|
CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = 1 “Row #2 = 1. “Column SET PROPERTY OF lo_cell ‘Value’ = ‘1.23’. SET PROPERTY OF lo_cell ‘NumberFormat’ = ‘0.00’. CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = 3 “Row #2 = 1. “Column SET PROPERTY OF lo_cell ‘Value’ = ’02/01/2012′. SET PROPERTY OF lo_cell ‘NumberFormat’ = ‘m/d/yyyy’. CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = 5 “Row #2 = 1. “Column SET PROPERTY OF lo_cell ‘NumberFormat’ = ‘0.00’. SET PROPERTY OF lo_cell ‘Value’ = ‘1/2’. SET PROPERTY OF lo_cell ‘NumberFormat’ = ‘# ?/?’. |
Result:
– 6 Add validation
For example allow only dates between Jan-2000 and Jan-2010 and show an error if not.
Abap |
---|
CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = 1 “Row #2 = 1. “Column CALL METHOD OF lo_cell ‘select’. CALL METHOD OF lo_application ‘selection’ = lo_selection. CALL METHOD OF lo_selection ‘Validation’ = lo_validation. CALL METHOD OF lo_validation ‘Add’ EXPORTING #1 = 4 “Type = xlValidateDate #2 = 1 “AlertStype = xlValidAlertStop #3 = 1 “Operator = xlBetween #4 = ‘1/1/2000’ “Formula1 #5 = ‘1/1/2010’.“Formula2 SET PROPERTY OF lo_validation ‘ErrorMessage’ = ‘Enter a valid date’. |
Result:
– 7 Create a drop down list with value list in other worksheet:
Here you have an example of creation in excel:
http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx
Abap |
---|
DATA: lv_range_name TYPE char24 VALUE ‘Values’. * Go to sheet 2 * Fill the cells with the values; * Select the range and set a name; * Return to sheet 1 * Select the cell A1 CALL METHOD OF lo_cell ‘select’. |
Result:
Improving the performance
If you want to download a large amount of data it can take a lot of time. For improve the performance we are going to copy the data from abap to clipboard and paste it to excel.
Compares the execution time of these two examples:
Cell by Cell |
---|
DATA: lt_ekpo TYPE ekpo OCCURS 0 WITH HEADER LINE, lv_cont TYPE i, lv_row TYPE i. FIELD-SYMBOLS: <field> TYPE ANY. * Select some data; SELECT * INTO TABLE lt_ekpo FROM ekpo UP TO 50ROWS. * Print the data cell by cell: LOOP AT lt_ekpo. lv_cont = 1. lv_row = sy-tabix. * Write for example 15 columns per row. DO 15 TIMES. CALL METHOD OF lo_worksheet ‘Cells’ = lo_cell EXPORTING #1 = lv_row #2 = lv_cont. ASSIGN COMPONENT lv_cont OF STRUCTURE lt_ekpoTO <field>. SET PROPERTY OF lo_cell ‘Value’ = <field>. ADD 1 TO lv_cont. ENDDO. ENDLOOP. |
Printing cell by cell takes about 145 seconds:
Copy-Paste |
---|
TYPES: ty_data(1500) TYPE c.
* Select some data; * Prepare the data before copy to clipboard; LOOP AT lt_ekpo. ADD 1 TO lv_cont. * Copy to clipboard into ABAP * Select the cell A1 * Paste clipboard from cell A1 |
Printing doing copy-paste takes less than 4 seconds !!!
Useful subrutines
I’ve created an include that you can include in your programs with several useful subrutines. The code of the include is at the end of this document. And here you have a test report in wich you can see how it works:
Test report: Example of use the include ZRIC_OLE2_UTILS |
---|
REPORT zric_ole2. INCLUDE: zric_ole2_utils. DATA: BEGIN OF lt_spfli OCCURS 0, DATA: lv_selected_folder TYPE string, START-OF-SELECTION. CALL METHOD cl_gui_frontend_services=>directory_browse * Create the document; * ——————————————————–* * Fill a header with some data of the passenger: * Fill the positions: * First a Header with the column’s names * Print the rest of the data: * Copy-paste the data from cell A1 * Bold the header: * Change the colour of the item’s header. * Add borders * Adjust the width of the cells to content * Align centered the two first columns of the item table * Set the width to the second column * ——————————————————–* * Select cities: * Go to worksheet 2; * Print the cities: * Set a name to this values: * Change the name of the worksheet: * Return to the worksheet 1 and create the drop down list: * If you have an internal table with a lot of fields DATA: lt_spfli_2 TYPE STANDARD TABLE OF spfli. * Go to worksheet 3; * Fill the field catalog: * Print the data: * Change the name of the worksheet: * Return to the worksheet 1 * File name * Save the document * Close the document and free memory |
After the execution you can download an excel sheet like this:
Code of include ZRIC_OLE2_UTILS |
---|
*&———————————————————————* TYPE-POOLS: soi,ole2. DATA: go_application TYPE ole2_object, DATA: gv_lines TYPE i. “Lines printed by the moment * Data to be printed. * Data to be printed. * Fields to be printed * Some colours you can use: * Theme Colours: * Align: *&———————————————————————* CREATE OBJECT go_application ‘Excel.Application’. ENDFORM. ” CREATE_DOCUMENT *&———————————————————————* CALL METHOD OF go_application ‘QUIT’. ENDFORM. ” CLOSE_DOCUMENT *&———————————————————————* DATA: lo_font TYPE ole2_object, FIELD-SYMBOLS: <field> TYPE ANY. DO. * Select the cell; * Exit the loop? ENDFORM. “print_line FIELD-SYMBOLS: <field> TYPE ANY. DATA: lo_abap_typedescr TYPE REF TO cl_abap_typedescr. CLEAR gs_data. * Convert data depend on the kind type. * Exit the loop? * Quit the first horizontal_tab: APPEND gs_data TO gt_data. CLEAR gs_data. ENDFORM. “add_line2print CLEAR gs_data. * Quit the first horizontal_tab: APPEND gs_data TO gt_data. CLEAR gs_data. ENDFORM. “add_line2print_from_table DATA: lo_cell TYPE ole2_object. * Copy to clipboard into ABAP * Select the cell A1 * Paste clipboard from cell A1 ENDFORM. ” PASTE_CLIPBOARD DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: * Format: ENDFORM. “change_format DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: * Format: * Colour: * BackGround Colour: ENDFORM. “set_soft_colour DATA: lo_cellstart TYPE ole2_object, * Select the Column CALL METHOD OF lo_column ‘select’. SET PROPERTY OF lo_column ‘ColumnWidth’ = p_width. ENDFORM. “Column_width DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: SET PROPERTY OF lo_range ‘WrapText’ = 1. ENDFORM. “WrapText DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: CALL METHOD OF lo_range ‘Select’ . ENDFORM. “merge_cells DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: CALL METHOD OF lo_range ‘select’. ENDFORM. “align_cells DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: CALL METHOD OF lo_range ‘select’. CALL METHOD OF go_worksheet ‘Protect’ ENDFORM. “Lock_cells DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: CALL METHOD OF lo_range ‘Borders’ = lo_borders EXPORTING #1 = ‘7’. “xlEdgeLeft CALL METHOD OF lo_range ‘Borders’ = lo_borders EXPORTING #1 = ‘8’. “xlEdgeTop CALL METHOD OF lo_range ‘Borders’ = lo_borders EXPORTING #1 = ‘9’. “xlEdgeBottom CALL METHOD OF lo_range ‘Borders’ = lo_borders EXPORTING #1 = ’10’. “xlEdgeRight CALL METHOD OF lo_range ‘Borders’ = lo_borders EXPORTING #1 = ’11’. “xlInsideVertical CALL METHOD OF lo_range ‘Borders’ = lo_borders EXPORTING #1 = ’12’. “xlInsideHorizontal ENDFORM. “Add Border DATA: lo_cellstart TYPE ole2_object, * Select the Range of Cells: * Set a name to this Range ENDFORM. “set_range_name DATA: lo_cellstart TYPE ole2_object, DATA: lv_range_name TYPE char24. * Select the Range of Cells: CALL METHOD OF lo_range ‘select’. ENDFORM. “drop_down_list FIELD-SYMBOLS: <field> TYPE ANY, DATA: lo_abap_typedescr TYPE REF TO cl_abap_typedescr. CLEAR: gs_data, gt_data[]. * Print the header: * Print the data: APPEND gs_data TO gt_data. CLEAR gs_data. * Print the data: DATA: lo_columns TYPE ole2_object.
CALL METHOD OF lo_column ‘select’. ENDFORM. “print_data_fieldcat |
Related content:
http://help.sap.com/printdocu/core/print46c/en/data/pdf/bcfesde6/bcfesde6.pdf
Hi Ricardo,
Good work.
Regards,
Madhu. 🙂
Hi Ricardo,
try to consider abap2xlsx project, there are a series of blogs that explain the features and a collection of demo reports.
Have a look to the blog abap2xlsx - Generate your professional Excel spreadsheet from ABAP
OLE2 technology depends on front-end, does not allow batch generation and, even more, you cannot read back the file.
Regards,
Ivan
Hi Ivan,
thanks for the info !
Sounds really interesting. When I have some free time I'll try to learn how it works.
Hi Ricardo, great work.
I'm having issues to convert this VBA code to ABAP, I can't convert the instruction of With Selection.Interior.Gradient.ColorStops.Add(0) to ABAP. Do you know how to do this?
I'm trying to mix in cells several colours, for example yellow and red in this code:
Sub Macro1()
'
' Macro1 Macro
'
'
With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 0
.Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(0)
.Color = 65535
.TintAndShade = 0
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
.Color = 255
.TintAndShade = 0
End With
End Sub
Let me know, thanks. 🙂
Hi Mario,
Try with this code:
Regards
Hi Ricardo,
I am impressed, thank you for your work.
Do you know the way how to save the EXCEL to PDF ('SaveAs' PDF).
In advance, Thank you.
Best Regards,
Serge
Hi Ricardo,
I could solve it with the following statement:
CALL METHOD OF lo_worksheets 'ExportAsFixedFormat'
EXPORTING
#1 = '0'
#2 = lv_complete_path.
Thanks and best regards,
Serge
Hi serge Thuet,
Above method worked and its great.
But can you please help to convert Entire workbook into single pdf because here i have around 15 sheets in workbook and they all needed to be in one pdf file.
Thanks in advance.
Regards
Pavan
Hi Pavan,
I will send you the solution begining of next week.
Thank you for your understanding.
Best Regards,
Serge
Hi serge Thuet,
I will be waiting for your reply......i am currently developing for the same requirement i asked you. I am unable to crack VBA to abap for this particular one. Try to reply as soon as possible.
Thank you in advance and wish you a very wonderful new year ahead.
Regards,
Pavan
Hi Ricardo,
Nice Document 🙂 .
Regard's
Smruti
Hi Ricardo,
love you man , really good work
it will slove half of my problems
Regards,
Murthy Sannidhi
Nicely Documented... Good To Learn... Thanks for sharing
Wonderful! Thank you
Nicely documented, Can any one help me by providing the code to embed a image file(Insert Object) to EXCEL. I did it with insert picture with the pc file.. but the image cannot be carried with the file when sent as email.
Thanks in advance.
Ravi
Hi Ravichandran,
Try this code to insert the picture in your file:
Regards,
Ricardo.
Hello Ricardo,
Thanks a Lot, Great document.
I have a requirement for creation of excel with 100 sheets in it each sheet has employee data.
everything works fine but sometimes i an not getting 100 sheets.
report is behaving weird sometimes i get 100 sheets some times bellow 10 don't know why excel is not getting created with number of sheets provided.
i using below code to create sheets.
CREATE OBJECT gv_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF gv_excel 'VISIBLE' = 0.
CALL METHOD OF
gv_excel
'WORKBOOKS' = gv_workbook.
CALL METHOD OF
gv_workbook
'ADD'.
*Create 100 sheets
SET PROPERTY OF gv_excel 'SheetsInNewWorkbook' = lv_sheets.
Request you help on this.
Thanks in advance for your help.
Regards,
Phanindra
Hi, Phanindra
I don't know what can be the problem, I've never used this method.... mmm
You can always use something like this:
GET PROPERTY OF gv_excel 'ACTIVESHEET' = lo_worksheet.
DO 100 TIMES.
CALL METHOD OF lo_worksheet 'add'.
ENDDO.
It's less elegant, but it works fine 😉
thanks for the response...:)
can we insert a macro in excel which can auto calculate hours enterd and display in the total column.
thanks,
Phanindra
Hi Ricardo,
Thanks for sharing your knowledge!
Can you please confirm if it is possible to add the image stored in SE78 or Application Server to our excel?
If yes, please let me know.
Cheers,
Raj
Hi Ricardo,
Very useful.Well illustration.
Thanks for sharing.
Regards,
Sucheta
That’s really helpful who wants to learn OLE.
By the way, could anybody help me to read the value of radio button from Excel to abap using OLE.
Regards,
Sakthi Sri.
Hi Ricardo,
I have a scenario where, I need to populate data into pre formatted excel template in background mode. Do you know if OLE2 supports excell sheet generation in background mode?
Also the excel template has marco's build into it and has an file extension of .XLSM.
Thanks in Advance,
Ramesh
OLE requires a connection from ABAP server to a server with OLE application installed (note that all Microsoft Office applications (Excel, Word...) are OLE compatible). In dialog, all OLE applications from the frontend computer can be accessed. In background, the frontend computers cannot be accessed, or it's difficult/rather unusual to do it. So we always prefer using abap2xlsx to generate directly the Excel file, and it's much faster too.
Hi Ricardo thanks for sharing your knowledge, in question to the subject I would like to see if they could help me to convert a code from a Macro to Abap.
I’m new to this is the basics but I’m having a few things.
Here is the code I am converting:
finrgo = Range(“B65536”).End(xlUp).Row
Range(“B9”).Select / call method of lo_worksheet ‘Cells’ = w_cell exporting #1 = 9 #2 = 2
While ActiveCell.Row <= finrgo
If ActiveCell.Value = “” Then / SET PROPERTY OF w_cell ‘Value’ = ”.
ActiveCell.EntireRow.Delete
finrgo = finrgo – 1
Else
ActiveCell.Offset(1, 0).Select
End If
Wend
THANK YOU!!
Excellent work, Ricardo!
It helped me a lot to export easlily several flows of data in Excel.
I'm struggling (so far with no result) trying to convert in ABAP the macro below.
The purpose is to put a Red/Yellow/Green trafficlight according with the values (8,9,10) stored in the cells of the range A1:A3000
Range("A1:A3000").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(8)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 9
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 10
.Operator = 7
End With
Any help would be appreciated
Thanks in advance.
M.
Hello, Mauro.
Try with this code, use the template report in this blog.
I’m geting this result:
* ——————————————————–*
gs_data = ‘8’. APPEND gs_data TO gt_data.
gs_data = ‘9’. APPEND gs_data TO gt_data.
gs_data = ’10’. APPEND gs_data TO gt_data.
PERFORM paste_clipboard USING 1 1.
*——————————————————————–*
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_format TYPE ole2_object,
lo_icon TYPE ole2_object,
lo_count TYPE ole2_object.
CALL METHOD OF go_worksheet ‘Cells’ = lo_cellstart
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF go_worksheet ‘Cells’ = lo_cellend
EXPORTING
#1 = 3000
#2 = 1.
CALL METHOD OF go_worksheet ‘Range’ = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range ‘select’.
CALL METHOD OF go_application ‘Selection’ = lo_selection.
CALL METHOD OF lo_selection ‘FormatConditions’ = lo_format.
CALL METHOD OF lo_format ‘AddIconSetCondition’.
CALL METHOD OF lo_format ‘Count’ = lo_count.
CALL METHOD OF lo_selection ‘FormatConditions’ = lo_format
EXPORTING #1 = lo_count.
CALL METHOD OF lo_format ‘SetFirstPriority’.
CALL METHOD OF lo_selection ‘FormatConditions’ = lo_format
EXPORTING #1 = ‘1’.
SET PROPERTY OF lo_format ‘ReverseOrder’ = ‘False’.
SET PROPERTY OF lo_format ‘ShowIconOnly’ = ‘False’.
SET PROPERTY OF lo_format ‘IconSet’ = ‘ActiveWorkbook.IconSets(8)’.
CALL METHOD OF lo_format ‘IconCriteria’ = lo_icon EXPORTING #1 = ‘2’.
SET PROPERTY OF lo_icon ‘Type’ = ‘0’. “xlConditionValueNumber
SET PROPERTY OF lo_icon ‘Value’ = ‘9’.
SET PROPERTY OF lo_icon ‘Operator’ = ‘7’.
CALL METHOD OF lo_format ‘IconCriteria’ = lo_icon EXPORTING #1 = ‘3’.
SET PROPERTY OF lo_icon ‘Type’ = ‘0’. “xlConditionValueNumber
SET PROPERTY OF lo_icon ‘Value’ = ’10’.
SET PROPERTY OF lo_icon ‘Operator’ = ‘7’.
Hi Ricardo!
Sorry for my late reply.
Thanks mate, it helped me a lot!
Hi Ricardo,
Very nicely explained.
I tried this and it is working perfectly fine but it is creating some formatting issues with a field which has value like - ''08-2019" and its a character field in my internal table but at the time of saving in excel it is showing the output as - " Aug 19".
I don't want excel to convert its formatting.
Can you please help me regarding the same.
Thanks in advance.
Hello Ricardo!
Hope you're doing fine, I did implement your code (with the include) and is working, I mean file is being downloaded, however there's no format... is actually like your first image. Do you know what could happen?
Hello Ricardo!
What A Tutorial!!
Very Simple to read and easy to implement but with milions possibilities of creation.
Thank You.
Eugenio.
Hello Ricardo,
My requirement is for adding a dropdown to one of the columns. I have tried to execute your code but I am getting it into any format. its plain excel getting downloaded and there is no dropdown for Departure city.
Regards,
Priti