-
Notifications
You must be signed in to change notification settings - Fork 284
/
zcl_excel_worksheet.clas.abap
3781 lines (3173 loc) · 147 KB
/
zcl_excel_worksheet.clas.abap
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
*----------------------------------------------------------------------*
* CLASS ZCL_EXCEL_WORKSHEET DEFINITION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS zcl_excel_worksheet DEFINITION
PUBLIC
CREATE PUBLIC .
PUBLIC SECTION.
*"* public components of class ZCL_EXCEL_WORKSHEET
*"* do not include other source files here!!!
*"* protected components of class ZCL_EXCEL_WORKSHEET
*"* do not include other source files here!!!
*"* protected components of class ZCL_EXCEL_WORKSHEET
*"* do not include other source files here!!!
TYPE-POOLS abap .
TYPE-POOLS slis .
TYPE-POOLS soi .
INTERFACES zif_excel_sheet_printsettings .
INTERFACES zif_excel_sheet_properties .
INTERFACES zif_excel_sheet_protection .
INTERFACES zif_excel_sheet_vba_project .
TYPES:
BEGIN OF mty_s_outline_row,
row_from TYPE i,
row_to TYPE i,
collapsed TYPE abap_bool,
END OF mty_s_outline_row .
TYPES:
mty_ts_outlines_row TYPE SORTED TABLE OF mty_s_outline_row WITH UNIQUE KEY row_from row_to .
CONSTANTS c_break_column TYPE zexcel_break VALUE 2. "#EC NOTEXT
CONSTANTS c_break_none TYPE zexcel_break VALUE 0. "#EC NOTEXT
CONSTANTS c_break_row TYPE zexcel_break VALUE 1. "#EC NOTEXT
DATA excel TYPE REF TO zcl_excel READ-ONLY .
DATA print_gridlines TYPE zexcel_print_gridlines READ-ONLY VALUE abap_false. "#EC NOTEXT
DATA sheet_content TYPE zexcel_t_cell_data .
DATA sheet_setup TYPE REF TO zcl_excel_sheet_setup .
DATA show_gridlines TYPE zexcel_show_gridlines READ-ONLY VALUE abap_true. "#EC NOTEXT
DATA show_rowcolheaders TYPE zexcel_show_gridlines READ-ONLY VALUE abap_true. "#EC NOTEXT
DATA styles TYPE zexcel_t_sheet_style .
DATA tabcolor TYPE zexcel_s_tabcolor READ-ONLY .
METHODS add_comment
IMPORTING
!ip_comment TYPE REF TO zcl_excel_comment .
METHODS add_drawing
IMPORTING
!ip_drawing TYPE REF TO zcl_excel_drawing .
METHODS add_new_column
IMPORTING
!ip_column TYPE simple
RETURNING
VALUE(eo_column) TYPE REF TO zcl_excel_column .
METHODS add_new_style_cond
IMPORTING
!ip_dimension_range TYPE string DEFAULT 'A1'
RETURNING
VALUE(eo_style_cond) TYPE REF TO zcl_excel_style_cond.
METHODS add_new_data_validation
RETURNING
VALUE(eo_data_validation) TYPE REF TO zcl_excel_data_validation .
METHODS add_new_range
RETURNING
VALUE(eo_range) TYPE REF TO zcl_excel_range .
METHODS add_new_row
IMPORTING
!ip_row TYPE simple
RETURNING
VALUE(eo_row) TYPE REF TO zcl_excel_row .
METHODS bind_alv
IMPORTING
!io_alv TYPE REF TO object
!it_table TYPE STANDARD TABLE
!i_top TYPE i DEFAULT 1
!i_left TYPE i DEFAULT 1
!table_style TYPE zexcel_table_style OPTIONAL
!i_table TYPE abap_bool DEFAULT abap_true
RAISING
zcx_excel .
METHODS bind_alv_ole2
IMPORTING
!i_document_url TYPE char255 DEFAULT space
!i_xls TYPE c DEFAULT space
!i_save_path TYPE string
!io_alv TYPE REF TO cl_gui_alv_grid
!it_listheader TYPE slis_t_listheader OPTIONAL
!i_top TYPE i DEFAULT 1
!i_left TYPE i DEFAULT 1
!i_columns_header TYPE c DEFAULT 'X'
!i_columns_autofit TYPE c DEFAULT 'X'
!i_format_col_header TYPE soi_format_item OPTIONAL
!i_format_subtotal TYPE soi_format_item OPTIONAL
!i_format_total TYPE soi_format_item OPTIONAL
EXCEPTIONS
miss_guide
ex_transfer_kkblo_error
fatal_error
inv_data_range
dim_mismatch_vkey
dim_mismatch_sema
error_in_sema .
METHODS bind_table
IMPORTING
!ip_table TYPE STANDARD TABLE
!it_field_catalog TYPE zexcel_t_fieldcatalog OPTIONAL
!is_table_settings TYPE zexcel_s_table_settings OPTIONAL
VALUE(iv_default_descr) TYPE c OPTIONAL
!iv_no_line_if_empty TYPE abap_bool DEFAULT abap_false
EXPORTING
!es_table_settings TYPE zexcel_s_table_settings
RAISING
zcx_excel .
METHODS calculate_column_widths
RAISING
zcx_excel .
METHODS change_cell_style
IMPORTING
!ip_column TYPE simple
!ip_row TYPE zexcel_cell_row
!ip_complete TYPE zexcel_s_cstyle_complete OPTIONAL
!ip_xcomplete TYPE zexcel_s_cstylex_complete OPTIONAL
!ip_font TYPE zexcel_s_cstyle_font OPTIONAL
!ip_xfont TYPE zexcel_s_cstylex_font OPTIONAL
!ip_fill TYPE zexcel_s_cstyle_fill OPTIONAL
!ip_xfill TYPE zexcel_s_cstylex_fill OPTIONAL
!ip_borders TYPE zexcel_s_cstyle_borders OPTIONAL
!ip_xborders TYPE zexcel_s_cstylex_borders OPTIONAL
!ip_alignment TYPE zexcel_s_cstyle_alignment OPTIONAL
!ip_xalignment TYPE zexcel_s_cstylex_alignment OPTIONAL
!ip_number_format_format_code TYPE zexcel_number_format OPTIONAL
!ip_protection TYPE zexcel_s_cstyle_protection OPTIONAL
!ip_xprotection TYPE zexcel_s_cstylex_protection OPTIONAL
!ip_font_bold TYPE flag OPTIONAL
!ip_font_color TYPE zexcel_s_style_color OPTIONAL
!ip_font_color_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_font_color_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_font_color_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_font_color_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_font_family TYPE zexcel_style_font_family OPTIONAL
!ip_font_italic TYPE flag OPTIONAL
!ip_font_name TYPE zexcel_style_font_name OPTIONAL
!ip_font_scheme TYPE zexcel_style_font_scheme OPTIONAL
!ip_font_size TYPE zexcel_style_font_size OPTIONAL
!ip_font_strikethrough TYPE flag OPTIONAL
!ip_font_underline TYPE flag OPTIONAL
!ip_font_underline_mode TYPE zexcel_style_font_underline OPTIONAL
!ip_fill_filltype TYPE zexcel_fill_type OPTIONAL
!ip_fill_rotation TYPE zexcel_rotation OPTIONAL
!ip_fill_fgcolor TYPE zexcel_s_style_color OPTIONAL
!ip_fill_fgcolor_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_fill_fgcolor_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_fill_fgcolor_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_fill_fgcolor_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_fill_bgcolor TYPE zexcel_s_style_color OPTIONAL
!ip_fill_bgcolor_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_fill_bgcolor_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_fill_bgcolor_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_fill_bgcolor_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_borders_allborders TYPE zexcel_s_cstyle_border OPTIONAL
!ip_fill_gradtype_type TYPE zexcel_s_gradient_type-type OPTIONAL
!ip_fill_gradtype_degree TYPE zexcel_s_gradient_type-degree OPTIONAL
!ip_xborders_allborders TYPE zexcel_s_cstylex_border OPTIONAL
!ip_borders_diagonal TYPE zexcel_s_cstyle_border OPTIONAL
!ip_fill_gradtype_bottom TYPE zexcel_s_gradient_type-bottom OPTIONAL
!ip_fill_gradtype_top TYPE zexcel_s_gradient_type-top OPTIONAL
!ip_xborders_diagonal TYPE zexcel_s_cstylex_border OPTIONAL
!ip_borders_diagonal_mode TYPE zexcel_diagonal OPTIONAL
!ip_fill_gradtype_right TYPE zexcel_s_gradient_type-right OPTIONAL
!ip_borders_down TYPE zexcel_s_cstyle_border OPTIONAL
!ip_fill_gradtype_left TYPE zexcel_s_gradient_type-left OPTIONAL
!ip_fill_gradtype_position1 TYPE zexcel_s_gradient_type-position1 OPTIONAL
!ip_xborders_down TYPE zexcel_s_cstylex_border OPTIONAL
!ip_borders_left TYPE zexcel_s_cstyle_border OPTIONAL
!ip_fill_gradtype_position2 TYPE zexcel_s_gradient_type-position2 OPTIONAL
!ip_fill_gradtype_position3 TYPE zexcel_s_gradient_type-position3 OPTIONAL
!ip_xborders_left TYPE zexcel_s_cstylex_border OPTIONAL
!ip_borders_right TYPE zexcel_s_cstyle_border OPTIONAL
!ip_xborders_right TYPE zexcel_s_cstylex_border OPTIONAL
!ip_borders_top TYPE zexcel_s_cstyle_border OPTIONAL
!ip_xborders_top TYPE zexcel_s_cstylex_border OPTIONAL
!ip_alignment_horizontal TYPE zexcel_alignment OPTIONAL
!ip_alignment_vertical TYPE zexcel_alignment OPTIONAL
!ip_alignment_textrotation TYPE zexcel_text_rotation OPTIONAL
!ip_alignment_wraptext TYPE flag OPTIONAL
!ip_alignment_shrinktofit TYPE flag OPTIONAL
!ip_alignment_indent TYPE zexcel_indent OPTIONAL
!ip_protection_hidden TYPE zexcel_cell_protection OPTIONAL
!ip_protection_locked TYPE zexcel_cell_protection OPTIONAL
!ip_borders_allborders_style TYPE zexcel_border OPTIONAL
!ip_borders_allborders_color TYPE zexcel_s_style_color OPTIONAL
!ip_borders_allbo_color_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_borders_allbo_color_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_borders_allbo_color_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_borders_allbo_color_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_borders_diagonal_style TYPE zexcel_border OPTIONAL
!ip_borders_diagonal_color TYPE zexcel_s_style_color OPTIONAL
!ip_borders_diagonal_color_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_borders_diagonal_color_inde TYPE zexcel_style_color_indexed OPTIONAL
!ip_borders_diagonal_color_them TYPE zexcel_style_color_theme OPTIONAL
!ip_borders_diagonal_color_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_borders_down_style TYPE zexcel_border OPTIONAL
!ip_borders_down_color TYPE zexcel_s_style_color OPTIONAL
!ip_borders_down_color_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_borders_down_color_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_borders_down_color_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_borders_down_color_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_borders_left_style TYPE zexcel_border OPTIONAL
!ip_borders_left_color TYPE zexcel_s_style_color OPTIONAL
!ip_borders_left_color_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_borders_left_color_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_borders_left_color_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_borders_left_color_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_borders_right_style TYPE zexcel_border OPTIONAL
!ip_borders_right_color TYPE zexcel_s_style_color OPTIONAL
!ip_borders_right_color_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_borders_right_color_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_borders_right_color_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_borders_right_color_tint TYPE zexcel_style_color_tint OPTIONAL
!ip_borders_top_style TYPE zexcel_border OPTIONAL
!ip_borders_top_color TYPE zexcel_s_style_color OPTIONAL
!ip_borders_top_color_rgb TYPE zexcel_style_color_argb OPTIONAL
!ip_borders_top_color_indexed TYPE zexcel_style_color_indexed OPTIONAL
!ip_borders_top_color_theme TYPE zexcel_style_color_theme OPTIONAL
!ip_borders_top_color_tint TYPE zexcel_style_color_tint OPTIONAL
RETURNING
VALUE(ep_guid) TYPE zexcel_cell_style
RAISING
zcx_excel .
METHODS constructor
IMPORTING
!ip_excel TYPE REF TO zcl_excel
!ip_title TYPE zexcel_sheet_title OPTIONAL
RAISING
zcx_excel .
METHODS delete_merge
IMPORTING
!ip_cell_column TYPE simple OPTIONAL
!ip_cell_row TYPE zexcel_cell_row OPTIONAL
RAISING
zcx_excel .
METHODS delete_row_outline
IMPORTING
!iv_row_from TYPE i
!iv_row_to TYPE i
RAISING
zcx_excel .
METHODS freeze_panes
IMPORTING
!ip_num_columns TYPE i OPTIONAL
!ip_num_rows TYPE i OPTIONAL
RAISING
zcx_excel .
METHODS get_active_cell
RETURNING
VALUE(ep_active_cell) TYPE string
RAISING
zcx_excel .
METHODS get_cell
IMPORTING
!ip_column TYPE simple
!ip_row TYPE zexcel_cell_row
EXPORTING
!ep_value TYPE zexcel_cell_value
!ep_rc TYPE sysubrc
!ep_style TYPE REF TO zcl_excel_style
!ep_guid TYPE zexcel_cell_style
!ep_formula TYPE zexcel_cell_formula
RAISING
zcx_excel .
METHODS get_column
IMPORTING
!ip_column TYPE simple
RETURNING
VALUE(eo_column) TYPE REF TO zcl_excel_column .
METHODS get_columns
RETURNING
VALUE(eo_columns) TYPE REF TO zcl_excel_columns .
METHODS get_columns_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_style_cond_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_data_validations_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_data_validations_size
RETURNING
VALUE(ep_size) TYPE i .
METHODS get_default_column
RETURNING
VALUE(eo_column) TYPE REF TO zcl_excel_column .
METHODS get_default_excel_date_format
RETURNING
VALUE(ep_default_excel_date_format) TYPE zexcel_number_format .
METHODS get_default_excel_time_format
RETURNING
VALUE(ep_default_excel_time_format) TYPE zexcel_number_format .
METHODS get_default_row
RETURNING
VALUE(eo_row) TYPE REF TO zcl_excel_row .
METHODS get_dimension_range
RETURNING
VALUE(ep_dimension_range) TYPE string
RAISING
zcx_excel .
METHODS get_comments
RETURNING
VALUE(r_comments) TYPE REF TO zcl_excel_comments .
METHODS get_drawings
IMPORTING
!ip_type TYPE zexcel_drawing_type OPTIONAL
RETURNING
VALUE(r_drawings) TYPE REF TO zcl_excel_drawings .
METHODS get_comments_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_drawings_iterator
IMPORTING
!ip_type TYPE zexcel_drawing_type
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_freeze_cell
EXPORTING
!ep_row TYPE zexcel_cell_row
!ep_column TYPE zexcel_cell_column .
METHODS get_guid
RETURNING
VALUE(ep_guid) TYPE sysuuid_x16 .
METHODS get_highest_column
RETURNING
VALUE(r_highest_column) TYPE zexcel_cell_column
RAISING
zcx_excel .
METHODS get_highest_row
RETURNING
VALUE(r_highest_row) TYPE int4
RAISING
zcx_excel .
METHODS get_hyperlinks_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_hyperlinks_size
RETURNING
VALUE(ep_size) TYPE i .
METHODS get_merge
RETURNING
VALUE(merge_range) TYPE string_table
RAISING
zcx_excel .
METHODS get_pagebreaks
RETURNING
VALUE(ro_pagebreaks) TYPE REF TO zcl_excel_worksheet_pagebreaks
RAISING
zcx_excel .
METHODS get_ranges_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_row
IMPORTING
!ip_row TYPE int4
RETURNING
VALUE(eo_row) TYPE REF TO zcl_excel_row .
METHODS get_rows
RETURNING
VALUE(eo_rows) TYPE REF TO zcl_excel_rows .
METHODS get_rows_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_row_outlines
RETURNING
VALUE(rt_row_outlines) TYPE mty_ts_outlines_row .
METHODS get_style_cond
IMPORTING
!ip_guid TYPE zexcel_cell_style
RETURNING
VALUE(eo_style_cond) TYPE REF TO zcl_excel_style_cond .
METHODS get_tabcolor
RETURNING
VALUE(ev_tabcolor) TYPE zexcel_s_tabcolor .
METHODS get_tables_iterator
RETURNING
VALUE(eo_iterator) TYPE REF TO cl_object_collection_iterator .
METHODS get_tables_size
RETURNING
VALUE(ep_size) TYPE i .
METHODS get_title
IMPORTING
!ip_escaped TYPE flag DEFAULT ''
RETURNING
VALUE(ep_title) TYPE zexcel_sheet_title .
METHODS is_cell_merged
IMPORTING
!ip_column TYPE simple
!ip_row TYPE zexcel_cell_row
RETURNING
VALUE(rp_is_merged) TYPE abap_bool
RAISING
zcx_excel .
METHODS set_cell
IMPORTING
!ip_column TYPE simple
!ip_row TYPE zexcel_cell_row
!ip_value TYPE simple OPTIONAL
!ip_formula TYPE zexcel_cell_formula OPTIONAL
!ip_style TYPE zexcel_cell_style OPTIONAL
!ip_hyperlink TYPE REF TO zcl_excel_hyperlink OPTIONAL
!ip_data_type TYPE zexcel_cell_data_type OPTIONAL
!ip_abap_type TYPE abap_typekind OPTIONAL
RAISING
zcx_excel .
METHODS set_cell_formula
IMPORTING
!ip_column TYPE simple
!ip_row TYPE zexcel_cell_row
!ip_formula TYPE zexcel_cell_formula
RAISING
zcx_excel .
METHODS set_cell_style
IMPORTING
!ip_column TYPE simple
!ip_row TYPE zexcel_cell_row
!ip_style TYPE zexcel_cell_style
RAISING
zcx_excel .
METHODS set_column_width
IMPORTING
!ip_column TYPE simple
!ip_width_fix TYPE simple DEFAULT 0
!ip_width_autosize TYPE flag DEFAULT 'X'
RAISING
zcx_excel .
METHODS set_default_excel_date_format
IMPORTING
!ip_default_excel_date_format TYPE zexcel_number_format
RAISING
zcx_excel .
METHODS set_merge
IMPORTING
!ip_column_start TYPE simple DEFAULT zcl_excel_common=>c_excel_sheet_min_col
!ip_column_end TYPE simple DEFAULT zcl_excel_common=>c_excel_sheet_max_col
!ip_row TYPE zexcel_cell_row DEFAULT zcl_excel_common=>c_excel_sheet_min_row
!ip_row_to TYPE zexcel_cell_row DEFAULT zcl_excel_common=>c_excel_sheet_max_row
!ip_style TYPE zexcel_cell_style OPTIONAL "added parameter
!ip_value TYPE simple OPTIONAL "added parameter
!ip_formula TYPE zexcel_cell_formula OPTIONAL "added parameter
RAISING
zcx_excel .
METHODS set_print_gridlines
IMPORTING
!i_print_gridlines TYPE zexcel_print_gridlines .
METHODS set_row_height
IMPORTING
!ip_row TYPE simple
!ip_height_fix TYPE simple
RAISING
zcx_excel .
METHODS set_row_outline
IMPORTING
!iv_row_from TYPE i
!iv_row_to TYPE i
!iv_collapsed TYPE abap_bool
RAISING
zcx_excel .
METHODS set_show_gridlines
IMPORTING
!i_show_gridlines TYPE zexcel_show_gridlines .
METHODS set_show_rowcolheaders
IMPORTING
!i_show_rowcolheaders TYPE zexcel_show_rowcolheader .
METHODS set_tabcolor
IMPORTING
!iv_tabcolor TYPE zexcel_s_tabcolor .
METHODS set_table
IMPORTING
!ip_table TYPE STANDARD TABLE
!ip_hdr_style TYPE zexcel_cell_style OPTIONAL
!ip_body_style TYPE zexcel_cell_style OPTIONAL
!ip_table_title TYPE string
!ip_top_left_column TYPE zexcel_cell_column_alpha DEFAULT 'B'
!ip_top_left_row TYPE zexcel_cell_row DEFAULT 3
!ip_transpose TYPE abap_bool OPTIONAL
!ip_no_header TYPE abap_bool OPTIONAL
RAISING
zcx_excel .
METHODS set_title
IMPORTING
!ip_title TYPE zexcel_sheet_title
RAISING
zcx_excel .
METHODS get_table
IMPORTING
!iv_skipped_rows TYPE int4 DEFAULT 0
!iv_skipped_cols TYPE int4 DEFAULT 0
!iv_max_col TYPE int4 OPTIONAL
!iv_max_row TYPE int4 OPTIONAL
EXPORTING
!et_table TYPE STANDARD TABLE
RAISING
zcx_excel .
METHODS set_merge_style
IMPORTING
!ip_column_start TYPE simple OPTIONAL
!ip_column_end TYPE simple OPTIONAL
!ip_row TYPE zexcel_cell_row OPTIONAL
!ip_row_to TYPE zexcel_cell_row OPTIONAL
!ip_style TYPE zexcel_cell_style OPTIONAL .
METHODS set_area_formula
IMPORTING
!ip_column_start TYPE simple
!ip_column_end TYPE simple OPTIONAL
!ip_row TYPE zexcel_cell_row
!ip_row_to TYPE zexcel_cell_row OPTIONAL
!ip_formula TYPE zexcel_cell_formula
!ip_merge TYPE abap_bool OPTIONAL
RAISING
zcx_excel .
METHODS set_area_style
IMPORTING
!ip_column_start TYPE simple
!ip_column_end TYPE simple OPTIONAL
!ip_row TYPE zexcel_cell_row
!ip_row_to TYPE zexcel_cell_row OPTIONAL
!ip_style TYPE zexcel_cell_style
!ip_merge TYPE abap_bool OPTIONAL .
METHODS set_area
IMPORTING
!ip_column_start TYPE simple
!ip_column_end TYPE simple OPTIONAL
!ip_row TYPE zexcel_cell_row
!ip_row_to TYPE zexcel_cell_row OPTIONAL
!ip_value TYPE simple OPTIONAL
!ip_formula TYPE zexcel_cell_formula OPTIONAL
!ip_style TYPE zexcel_cell_style OPTIONAL
!ip_hyperlink TYPE REF TO zcl_excel_hyperlink OPTIONAL
!ip_data_type TYPE zexcel_cell_data_type OPTIONAL
!ip_abap_type TYPE abap_typekind OPTIONAL
!ip_merge TYPE abap_bool OPTIONAL
RAISING
zcx_excel .
METHODS get_header_footer_drawings
RETURNING
VALUE(rt_drawings) TYPE zexcel_t_drawings .
METHODS set_area_hyperlink
IMPORTING
!ip_column_start TYPE simple
!ip_column_end TYPE simple OPTIONAL
!ip_row TYPE zexcel_cell_row
!ip_row_to TYPE zexcel_cell_row OPTIONAL
!ip_url TYPE string
!ip_is_internal TYPE abap_bool
RAISING
zcx_excel .
PROTECTED SECTION.
PRIVATE SECTION.
TYPES:
BEGIN OF mty_s_font_metric,
char TYPE c LENGTH 1,
char_width TYPE tdcwidths,
END OF mty_s_font_metric .
TYPES:
mty_th_font_metrics
TYPE HASHED TABLE OF mty_s_font_metric
WITH UNIQUE KEY char .
TYPES:
BEGIN OF mty_s_font_cache,
font_name TYPE zexcel_style_font_name,
font_height TYPE tdfontsize,
flag_bold TYPE abap_bool,
flag_italic TYPE abap_bool,
th_font_metrics TYPE mty_th_font_metrics,
END OF mty_s_font_cache .
TYPES:
mty_th_font_cache
TYPE HASHED TABLE OF mty_s_font_cache
WITH UNIQUE KEY font_name font_height flag_bold flag_italic .
TYPES:
* types:
* mty_ts_row_dimension TYPE SORTED TABLE OF zexcel_s_worksheet_rowdimensio WITH UNIQUE KEY row .
BEGIN OF mty_merge,
row_from TYPE i,
row_to TYPE i,
col_from TYPE i,
col_to TYPE i,
END OF mty_merge .
TYPES:
mty_ts_merge TYPE SORTED TABLE OF mty_merge WITH UNIQUE KEY table_line .
*"* private components of class ZCL_EXCEL_WORKSHEET
*"* do not include other source files here!!!
DATA active_cell TYPE zexcel_s_cell_data .
DATA charts TYPE REF TO zcl_excel_drawings .
DATA columns TYPE REF TO zcl_excel_columns .
DATA row_default TYPE REF TO zcl_excel_row .
DATA column_default TYPE REF TO zcl_excel_column .
DATA styles_cond TYPE REF TO zcl_excel_styles_cond .
DATA data_validations TYPE REF TO zcl_excel_data_validations .
DATA default_excel_date_format TYPE zexcel_number_format .
DATA default_excel_time_format TYPE zexcel_number_format .
DATA comments TYPE REF TO zcl_excel_comments .
DATA drawings TYPE REF TO zcl_excel_drawings .
DATA freeze_pane_cell_column TYPE zexcel_cell_column .
DATA freeze_pane_cell_row TYPE zexcel_cell_row .
DATA guid TYPE sysuuid_x16 .
DATA hyperlinks TYPE REF TO cl_object_collection .
DATA lower_cell TYPE zexcel_s_cell_data .
DATA mo_pagebreaks TYPE REF TO zcl_excel_worksheet_pagebreaks .
CLASS-DATA mth_font_cache TYPE mty_th_font_cache .
DATA mt_merged_cells TYPE mty_ts_merge .
DATA mt_row_outlines TYPE mty_ts_outlines_row .
DATA print_title_col_from TYPE zexcel_cell_column_alpha .
DATA print_title_col_to TYPE zexcel_cell_column_alpha .
DATA print_title_row_from TYPE zexcel_cell_row .
DATA print_title_row_to TYPE zexcel_cell_row .
DATA ranges TYPE REF TO zcl_excel_ranges .
DATA rows TYPE REF TO zcl_excel_rows .
DATA tables TYPE REF TO cl_object_collection .
DATA title TYPE zexcel_sheet_title VALUE 'Worksheet'. "#EC NOTEXT . . . . . . . . . . . . . . . . . . . . . . . . . . . . " .
DATA upper_cell TYPE zexcel_s_cell_data .
METHODS calculate_cell_width
IMPORTING
!ip_column TYPE simple
!ip_row TYPE zexcel_cell_row
RETURNING
VALUE(ep_width) TYPE float
RAISING
zcx_excel .
METHODS generate_title
RETURNING
VALUE(ep_title) TYPE zexcel_sheet_title .
METHODS get_value_type
IMPORTING
!ip_value TYPE simple
EXPORTING
!ep_value TYPE simple
!ep_value_type TYPE abap_typekind .
METHODS print_title_set_range .
METHODS update_dimension_range
RAISING
zcx_excel .
ENDCLASS.
CLASS zcl_excel_worksheet IMPLEMENTATION.
METHOD add_comment.
comments->include( ip_comment ).
ENDMETHOD. "add_comment
METHOD add_drawing.
CASE ip_drawing->get_type( ).
WHEN zcl_excel_drawing=>type_image.
drawings->include( ip_drawing ).
WHEN zcl_excel_drawing=>type_chart.
charts->include( ip_drawing ).
ENDCASE.
ENDMETHOD. "ADD_DRAWING
METHOD add_new_column.
DATA: lv_column_alpha TYPE zexcel_cell_column_alpha.
lv_column_alpha = zcl_excel_common=>convert_column2alpha( ip_column ).
CREATE OBJECT eo_column
EXPORTING
ip_index = lv_column_alpha
ip_excel = me->excel
ip_worksheet = me.
columns->add( eo_column ).
ENDMETHOD. "ADD_NEW_COLUMN
METHOD add_new_data_validation.
CREATE OBJECT eo_data_validation.
data_validations->add( eo_data_validation ).
ENDMETHOD. "ADD_NEW_DATA_VALIDATION
METHOD add_new_range.
* Create default blank range
CREATE OBJECT eo_range.
ranges->add( eo_range ).
ENDMETHOD. "ADD_NEW_RANGE
METHOD add_new_row.
CREATE OBJECT eo_row
EXPORTING
ip_index = ip_row.
rows->add( eo_row ).
ENDMETHOD. "ADD_NEW_ROW
METHOD add_new_style_cond.
CREATE OBJECT eo_style_cond EXPORTING ip_dimension_range = ip_dimension_range.
styles_cond->add( eo_style_cond ).
ENDMETHOD. "ADD_NEW_STYLE_COND
METHOD bind_alv.
DATA: lo_converter TYPE REF TO zcl_excel_converter.
CREATE OBJECT lo_converter.
TRY.
lo_converter->convert(
EXPORTING
io_alv = io_alv
it_table = it_table
i_row_int = i_top
i_column_int = i_left
i_table = i_table
i_style_table = table_style
io_worksheet = me
CHANGING
co_excel = excel ).
CATCH zcx_excel .
ENDTRY.
ENDMETHOD. "BIND_ALV
METHOD bind_alv_ole2.
CALL METHOD ('ZCL_EXCEL_OLE')=>('BIND_ALV_OLE2')
EXPORTING
i_document_url = i_document_url
i_xls = i_xls
i_save_path = i_save_path
io_alv = io_alv
it_listheader = it_listheader
i_top = i_top
i_left = i_left
i_columns_header = i_columns_header
i_columns_autofit = i_columns_autofit
i_format_col_header = i_format_col_header
i_format_subtotal = i_format_subtotal
i_format_total = i_format_total
EXCEPTIONS
miss_guide = 1
ex_transfer_kkblo_error = 2
fatal_error = 3
inv_data_range = 4
dim_mismatch_vkey = 5
dim_mismatch_sema = 6
error_in_sema = 7
others = 8.
IF sy-subrc <> 0.
CASE sy-subrc.
WHEN 1. RAISE miss_guide.
WHEN 2. RAISE ex_transfer_kkblo_error.
WHEN 3. RAISE fatal_error.
WHEN 4. RAISE inv_data_range.
WHEN 5. RAISE dim_mismatch_vkey.
WHEN 6. RAISE dim_mismatch_sema.
WHEN 7. RAISE error_in_sema.
ENDCASE.
ENDIF.
ENDMETHOD. "BIND_ALV_OLE2
METHOD bind_table.
*--------------------------------------------------------------------*
* issue #230 - Pimp my Code
* - Stefan Schmöcker, (wi p) 2012-12-01
* - ...
* aligning code
* message made to support multilinguality
*--------------------------------------------------------------------*
* issue #237 - Check if overlapping areas exist
* - Alessandro Iannacci 2012-12-01
* changes: - Added raise if overlaps are detected
*--------------------------------------------------------------------*
CONSTANTS:
lc_top_left_column TYPE zexcel_cell_column_alpha VALUE 'A',
lc_top_left_row TYPE zexcel_cell_row VALUE 1.
DATA:
lv_row_int TYPE zexcel_cell_row,
lv_first_row TYPE zexcel_cell_row,
lv_last_row TYPE zexcel_cell_row,
lv_column_int TYPE zexcel_cell_column,
lv_column_alpha TYPE zexcel_cell_column_alpha,
lt_field_catalog TYPE zexcel_t_fieldcatalog,
lv_id TYPE i,
lv_rows TYPE i,
lv_formula TYPE string,
ls_settings TYPE zexcel_s_table_settings,
lo_table TYPE REF TO zcl_excel_table,
lt_column_name_buffer TYPE SORTED TABLE OF string WITH UNIQUE KEY table_line,
lv_value TYPE string,
lv_value_lowercase TYPE string,
lv_syindex TYPE char3,
lv_errormessage TYPE string, "ins issue #237
lv_columns TYPE i,
lt_columns TYPE zexcel_t_fieldcatalog,
lv_maxcol TYPE i,
lv_maxrow TYPE i,
lo_iterator TYPE REF TO cl_object_collection_iterator,
lo_style_cond TYPE REF TO zcl_excel_style_cond,
lo_curtable TYPE REF TO zcl_excel_table.
FIELD-SYMBOLS:
<ls_field_catalog> TYPE zexcel_s_fieldcatalog,
<ls_field_catalog_custom> TYPE zexcel_s_fieldcatalog,
<fs_table_line> TYPE any,
<fs_fldval> TYPE any.
ls_settings = is_table_settings.
IF ls_settings-top_left_column IS INITIAL.
ls_settings-top_left_column = lc_top_left_column.
ENDIF.
IF ls_settings-table_style IS INITIAL.
ls_settings-table_style = zcl_excel_table=>builtinstyle_medium2.
ENDIF.
IF ls_settings-top_left_row IS INITIAL.
ls_settings-top_left_row = lc_top_left_row.
ENDIF.
IF it_field_catalog IS NOT SUPPLIED.
lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = ip_table ).
ELSE.
lt_field_catalog = it_field_catalog.
ENDIF.
SORT lt_field_catalog BY position.
*--------------------------------------------------------------------*
* issue #237 Check if overlapping areas exist Start
*--------------------------------------------------------------------*
"Get the number of columns for the current table
lt_columns = lt_field_catalog.
DELETE lt_columns WHERE dynpfld NE abap_true.
DESCRIBE TABLE lt_columns LINES lv_columns.
"Calculate the top left row of the current table
lv_column_int = zcl_excel_common=>convert_column2int( ls_settings-top_left_column ).
lv_row_int = ls_settings-top_left_row.
"Get number of row for the current table
DESCRIBE TABLE ip_table LINES lv_rows.
"Calculate the bottom right row for the current table
lv_maxcol = lv_column_int + lv_columns - 1.
lv_maxrow = lv_row_int + lv_rows - 1.
ls_settings-bottom_right_column = zcl_excel_common=>convert_column2alpha( lv_maxcol ).
ls_settings-bottom_right_row = lv_maxrow.
lv_column_int = zcl_excel_common=>convert_column2int( ls_settings-top_left_column ).
lo_iterator = me->tables->get_iterator( ).
WHILE lo_iterator->has_next( ) EQ abap_true.
lo_curtable ?= lo_iterator->get_next( ).
IF ( ( ls_settings-top_left_row GE lo_curtable->settings-top_left_row AND ls_settings-top_left_row LE lo_curtable->settings-bottom_right_row )
OR
( ls_settings-bottom_right_row GE lo_curtable->settings-top_left_row AND ls_settings-bottom_right_row LE lo_curtable->settings-bottom_right_row )
)
AND
( ( lv_column_int GE zcl_excel_common=>convert_column2int( lo_curtable->settings-top_left_column ) AND lv_column_int LE zcl_excel_common=>convert_column2int( lo_curtable->settings-bottom_right_column ) )
OR
( lv_maxcol GE zcl_excel_common=>convert_column2int( lo_curtable->settings-top_left_column ) AND lv_maxcol LE zcl_excel_common=>convert_column2int( lo_curtable->settings-bottom_right_column ) )
).
lv_errormessage = 'Table overlaps with previously bound table and will not be added to worksheet.'(400).
zcx_excel=>raise_text( lv_errormessage ).
ENDIF.
ENDWHILE.
*--------------------------------------------------------------------*
* issue #237 Check if overlapping areas exist End
*--------------------------------------------------------------------*
CREATE OBJECT lo_table.
lo_table->settings = ls_settings.
lo_table->set_data( ir_data = ip_table ).
lv_id = me->excel->get_next_table_id( ).
lo_table->set_id( iv_id = lv_id ).
* lo_table->fieldcat = lt_field_catalog[].
me->tables->add( lo_table ).
* It is better to loop column by column (only visible column)
LOOP AT lt_field_catalog ASSIGNING <ls_field_catalog> WHERE dynpfld EQ abap_true.
lv_column_alpha = zcl_excel_common=>convert_column2alpha( lv_column_int ).
" Due restrinction of new table object we cannot have two column with the same name
" Check if a column with the same name exists, if exists add a counter
" If no medium description is provided we try to use small or long
* lv_value = <ls_field_catalog>-scrtext_m.
FIELD-SYMBOLS: <scrtxt1> TYPE any,
<scrtxt2> TYPE any,
<scrtxt3> TYPE any.
CASE iv_default_descr.
WHEN 'M'.
ASSIGN <ls_field_catalog>-scrtext_m TO <scrtxt1>.
ASSIGN <ls_field_catalog>-scrtext_s TO <scrtxt2>.
ASSIGN <ls_field_catalog>-scrtext_l TO <scrtxt3>.
WHEN 'S'.
ASSIGN <ls_field_catalog>-scrtext_s TO <scrtxt1>.
ASSIGN <ls_field_catalog>-scrtext_m TO <scrtxt2>.
ASSIGN <ls_field_catalog>-scrtext_l TO <scrtxt3>.
WHEN 'L'.
ASSIGN <ls_field_catalog>-scrtext_l TO <scrtxt1>.
ASSIGN <ls_field_catalog>-scrtext_m TO <scrtxt2>.
ASSIGN <ls_field_catalog>-scrtext_s TO <scrtxt3>.
WHEN OTHERS.
ASSIGN <ls_field_catalog>-scrtext_m TO <scrtxt1>.
ASSIGN <ls_field_catalog>-scrtext_s TO <scrtxt2>.
ASSIGN <ls_field_catalog>-scrtext_l TO <scrtxt3>.
ENDCASE.
IF <scrtxt1> IS NOT INITIAL.
lv_value = <scrtxt1>.
<ls_field_catalog>-scrtext_l = lv_value.
ELSEIF <scrtxt2> IS NOT INITIAL.
lv_value = <scrtxt2>.
<ls_field_catalog>-scrtext_l = lv_value.
ELSEIF <scrtxt3> IS NOT INITIAL.
lv_value = <scrtxt3>.
<ls_field_catalog>-scrtext_l = lv_value.
ELSE.
lv_value = 'Column'. " default value as Excel does
<ls_field_catalog>-scrtext_l = lv_value.
ENDIF.
WHILE 1 = 1.
lv_value_lowercase = lv_value.
TRANSLATE lv_value_lowercase TO LOWER CASE.
READ TABLE lt_column_name_buffer TRANSPORTING NO FIELDS WITH KEY table_line = lv_value_lowercase BINARY SEARCH.
IF sy-subrc <> 0.
<ls_field_catalog>-scrtext_l = lv_value.
INSERT lv_value_lowercase INTO TABLE lt_column_name_buffer.
EXIT.
ELSE.
lv_syindex = sy-index.
CONCATENATE <ls_field_catalog>-scrtext_l lv_syindex INTO lv_value.
ENDIF.
ENDWHILE.
" First of all write column header
IF <ls_field_catalog>-style_header IS NOT INITIAL.
me->set_cell( ip_column = lv_column_alpha
ip_row = lv_row_int
ip_value = lv_value
ip_style = <ls_field_catalog>-style_header ).
ELSE.
me->set_cell( ip_column = lv_column_alpha
ip_row = lv_row_int
ip_value = lv_value ).
ENDIF.
ADD 1 TO lv_row_int.
LOOP AT ip_table ASSIGNING <fs_table_line>.
ASSIGN COMPONENT <ls_field_catalog>-fieldname OF STRUCTURE <fs_table_line> TO <fs_fldval>.
" issue #290 Add formula support in table
IF <ls_field_catalog>-formula EQ abap_true.
IF <ls_field_catalog>-style IS NOT INITIAL.
IF <ls_field_catalog>-abap_type IS NOT INITIAL.
me->set_cell( ip_column = lv_column_alpha
ip_row = lv_row_int
ip_formula = <fs_fldval>
ip_abap_type = <ls_field_catalog>-abap_type
ip_style = <ls_field_catalog>-style ).
ELSE.
me->set_cell( ip_column = lv_column_alpha
ip_row = lv_row_int
ip_formula = <fs_fldval>
ip_style = <ls_field_catalog>-style ).
ENDIF.
ELSEIF <ls_field_catalog>-abap_type IS NOT INITIAL.
me->set_cell( ip_column = lv_column_alpha
ip_row = lv_row_int
ip_formula = <fs_fldval>
ip_abap_type = <ls_field_catalog>-abap_type ).
ELSE.
me->set_cell( ip_column = lv_column_alpha
ip_row = lv_row_int
ip_formula = <fs_fldval> ).
ENDIF.
ELSE.