-
Notifications
You must be signed in to change notification settings - Fork 11
/
Copy pathxlsx_builder_pkg.pks
407 lines (368 loc) · 19.1 KB
/
xlsx_builder_pkg.pks
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
CREATE OR REPLACE
PACKAGE xlsx_builder_pkg
AUTHID CURRENT_USER
IS
/**********************************************
**
** Author: Anton Scheffer
** Date: 19-02-2011
** Website: http://technology.amis.nl/blog
** See also: http://technology.amis.nl/blog/?p=10995
**
** Changelog:
** Date: 21-02-2011
** Added Aligment, horizontal, vertical, wrapText
** Date: 06-03-2011
** Added Comments, MergeCells, fixed bug for dependency on NLS-settings
** Date: 16-03-2011
** Added bold and italic fonts
** Date: 22-03-2011
** Fixed issue with timezone's set to a region(name) instead of a offset
** Date: 08-04-2011
** Fixed issue with XML-escaping from text
** Date: 27-05-2011
** Added MIT-license
** Date: 11-08-2011
** Fixed NLS-issue with column width
** Date: 29-09-2011
** Added font color
** Date: 16-10-2011
** fixed bug in add_string
** Date: 26-04-2012
** Fixed set_autofilter (only one autofilter per sheet, added _xlnm._FilterDatabase)
** Added list_validation = drop-down
** Date: 27-08-2013
** Added freeze_pane
** Date: 01-03-2014 (MK)
** Changed new_sheet to function returning sheet id
** Date: 22-03-2014 (MK)
** Added function to convert Oracle Number Format to Excel Format
** Date: 07-04-2014 (MK)
** Removed references to UTL_FILE
** query2sheet is now function returning BLOB
** changed date handling to be based on 01-01-1900
** Date: 08-04-2014 (MK)
** internal function for date to excel serial conversion added
** Date: 01-12-2014 (AMEI)
** Some Naming-conventions (and renaming of elements accordingly), new FUNCTION get_sheet_id
** Triggered by: @SEE AMEI, 20141129 Bugfix:
** For concatenation operations (in particular where record fields are involved) added a lot of TO_CHAR (...)
** to make sure correct explicit conversion (mayby not all caught where necessary)
** To make this easier to recognize, inducted some naming conventions and renamed some elements.
** Date: 26-04-2017 (MP)
** Added new function "query2sheet2" which is faster.
** For dates used following logic:
** - if trunc([column])=[column], then outputed cell value is formatted to format YYYYMMDD;
** - otherwise, outputted cell value is formatted to format YYYYMMDDTHH24MISS;
** Date: 24-09-2019 (PH)
** Added optional parameter "p_hidden" to function "new_sheet" to create a hidden sheet
** Added optional parameter "p_sheet_datasource" to procedure "list_validation" to allow dropdowns with data from a different sheet
******************************************************************************
******************************************************************************
Copyright (C) 2011, 2012 by Anton Scheffer
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
******************************************************************************
******************************************************************************
* @headcom
*/
/**
* Record with data about column alignment.
* @param vertical Vertical alignment.
* @param horizontal Horizontal alignment.
* @param wrapText Switch to allow or disallow word wrap.
*/
TYPE t_alignment_rec IS RECORD
(
vc_vertical VARCHAR2 (11),
vc_horizontal VARCHAR2 (16),
bo_wraptext BOOLEAN
);
type t_bind_tab is table of varchar2(32767) index by varchar2(32767);
type t_header_tab is table of varchar2(32767) index by pls_integer;
/**
* Clears the whole workbook to start fresh.
*/
PROCEDURE clear_workbook;
/**
* Create a new sheet in the workbook.
* @param p_sheetname Name Excel should display for the new worksheet.
* @return ID of newly created worksheet.
*/
FUNCTION new_sheet (p_sheetname VARCHAR2 := NULL, p_hidden BOOLEAN := FALSE)
RETURN PLS_INTEGER;
/**
* Converts an Oracle date format to the corresponding Excel date format.
* @param p_format The Oracle date format to convert.
* @return Corresponding Excel date format.
*/
FUNCTION orafmt2excel (p_format VARCHAR2 := NULL)
RETURN VARCHAR2;
/**
* Converts an Oracle number format to the corresponding Excel number format.
* @param The Oracle number format to convert.
* @return Corresponding Excel number format.
*/
FUNCTION oranumfmt2excel (p_format VARCHAR2)
RETURN VARCHAR2;
/**
* Get ID for given number format.
* @param p_format Wanted number formatting using Excle number format.
* Use OraNumFmt2Excel to convert from Oracle to Excel.
* @return ID for given number format.
*/
FUNCTION get_numfmt (p_format VARCHAR2 := NULL)
RETURN PLS_INTEGER;
/**
* Get ID for given font settings.
* @param p_name
* @param p_family
* @param p_fontsize
* @param p_theme
* @param p_underline
* @param p_italic
* @param p_bold
* @param p_rgb
* @return ID for given font definition
*/
FUNCTION get_font (p_name VARCHAR2,
p_family PLS_INTEGER := 2,
p_fontsize NUMBER := 8,
p_theme PLS_INTEGER := 1,
p_underline BOOLEAN := FALSE,
p_italic BOOLEAN := FALSE,
p_bold BOOLEAN := FALSE,
p_rgb VARCHAR2 := NULL -- this is a hex ALPHA Red Green Blue value, but RGB works also
)
RETURN PLS_INTEGER;
/**
* Get ID for given cell fill
* @param p_patternType Pattern for the fill.
* @param p_fgRGB Color using an ARGB or RGB hex value
* @return ID for given cell fill.
*/
FUNCTION get_fill (p_patterntype VARCHAR2, p_fgrgb VARCHAR2 := NULL)
RETURN PLS_INTEGER;
/**
* Get ID for given border definition.
* Possible values for all parameters:
* none, thin, medium, dashed, dotted, thick, double, hair, mediumDashed,
* dashDot, mediumDashDot, dashDotDot, mediumDashDotDot, slantDashDot
* @param p_top Style for top border
* @param p_bottom Style for bottom border
* @param p_left Style for left border
* @param p_right Style for right border
* @return ID for given border definition
*/
FUNCTION get_border (p_top VARCHAR2 := 'thin',
p_bottom VARCHAR2 := 'thin',
p_left VARCHAR2 := 'thin',
p_right VARCHAR2 := 'thin')
RETURN PLS_INTEGER;
/**
* Function to get a record holding alignment data.
* @param p_vertical Vertical alignment.
* (bottom, center, distributed, justify, top)
* @param p_horizontal Horizontal alignment.
* (center, centerContinuous, distributed, fill, general, justify, left, right)
* @param p_wraptext Switch to allow or disallow text wrapping.
* @return Record with alignment data.
*/
FUNCTION get_alignment (p_vertical VARCHAR2 := NULL, p_horizontal VARCHAR2 := NULL, p_wraptext BOOLEAN := NULL)
RETURN t_alignment_rec;
/**
* Puts a number value into a cell of the spreadsheet.
* @param p_col Column number where the cell is located
* @param p_row Row number where the cell is located
* @param p_value The value to put into the cell
* @param p_numFmtId ID of number format
* @param p_fontId ID of font defintion
* @param p_fillId ID of fill definition
* @param p_borderId ID of border definition
* @param p_alignment The wanted alignment
* @param p_sheet Worksheet the cell is located, if omitted last worksheet is used
*/
PROCEDURE cell (p_col PLS_INTEGER,
p_row PLS_INTEGER,
p_value NUMBER,
p_numfmtid PLS_INTEGER := NULL,
p_fontid PLS_INTEGER := NULL,
p_fillid PLS_INTEGER := NULL,
p_borderid PLS_INTEGER := NULL,
p_alignment t_alignment_rec := NULL,
p_sheet PLS_INTEGER := NULL);
/**
* Puts a character value into a cell of the spreadsheet.
* @param p_col Column number where the cell is located
* @param p_row Row number where the cell is located
* @param p_value The value to put into the cell
* @param p_numFmtId ID of formatting definition
* @param p_fontId ID of font defintion
* @param p_fillId ID of fill definition
* @param p_borderId ID of border definition
* @param p_alignment The wanted alignment
* @param p_sheet Worksheet the cell is located, if omitted last worksheet is used
* @param p_formula The formula you put into the cell
*/
PROCEDURE cell (p_col PLS_INTEGER,
p_row PLS_INTEGER,
p_value VARCHAR2,
p_numfmtid PLS_INTEGER := NULL,
p_fontid PLS_INTEGER := NULL,
p_fillid PLS_INTEGER := NULL,
p_borderid PLS_INTEGER := NULL,
p_alignment t_alignment_rec := NULL,
p_sheet PLS_INTEGER := NULL,
p_formula VARCHAR2 := NULL);
/**
* Puts a date value into a cell of the spreadsheet.
* @param p_col Column number where the cell is located
* @param p_row Row number where the cell is located
* @param p_value The value to put into the cell
* @param p_numFmtId ID of format definition
* @param p_fontId ID of font defintion
* @param p_fillId ID of fill definition
* @param p_borderId ID of border definition
* @param p_alignment The wanted alignment
* @param p_sheet Worksheet the cell is located, if omitted last worksheet is used
*/
PROCEDURE cell (p_col PLS_INTEGER,
p_row PLS_INTEGER,
p_value DATE,
p_numfmtid PLS_INTEGER := NULL,
p_fontid PLS_INTEGER := NULL,
p_fillid PLS_INTEGER := NULL,
p_borderid PLS_INTEGER := NULL,
p_alignment t_alignment_rec := NULL,
p_sheet PLS_INTEGER := NULL);
PROCEDURE hyperlink (p_col PLS_INTEGER,
p_row PLS_INTEGER,
p_url VARCHAR2,
p_value VARCHAR2 := NULL,
p_sheet PLS_INTEGER := NULL);
PROCEDURE comment (p_col PLS_INTEGER,
p_row PLS_INTEGER,
p_text VARCHAR2,
p_author VARCHAR2 := NULL,
p_width PLS_INTEGER := 150 -- pixels
,
p_height PLS_INTEGER := 100 -- pixels
,
p_sheet PLS_INTEGER := NULL);
PROCEDURE mergecells (p_tl_col PLS_INTEGER -- top left
,
p_tl_row PLS_INTEGER,
p_br_col PLS_INTEGER -- bottom right
,
p_br_row PLS_INTEGER,
p_sheet PLS_INTEGER := NULL);
PROCEDURE add_validation (p_type VARCHAR2,
p_sqref VARCHAR2,
p_style VARCHAR2 := 'stop' -- stop, warning, information
,
p_formula1 VARCHAR2 := NULL,
p_formula2 VARCHAR2 := NULL,
p_title VARCHAR2 := NULL,
p_prompt VARCHAR2 := NULL,
p_show_error BOOLEAN := FALSE,
p_error_title VARCHAR2 := NULL,
p_error_txt VARCHAR2 := NULL,
p_sheet PLS_INTEGER := NULL) ;
PROCEDURE list_validation (p_sqref_col PLS_INTEGER,
p_sqref_row PLS_INTEGER,
p_tl_col PLS_INTEGER -- top left
,
p_tl_row PLS_INTEGER,
p_br_col PLS_INTEGER -- bottom right
,
p_br_row PLS_INTEGER,
p_style VARCHAR2 := 'stop' -- stop, warning, information
,
p_title VARCHAR2 := NULL,
p_prompt VARCHAR2 := NULL,
p_show_error BOOLEAN := FALSE,
p_error_title VARCHAR2 := NULL,
p_error_txt VARCHAR2 := NULL,
p_sheet PLS_INTEGER := NULL,
p_sheet_datasource PLS_INTEGER := NULL);
PROCEDURE list_validation (p_sqref_col PLS_INTEGER,
p_sqref_row PLS_INTEGER,
p_defined_name VARCHAR2,
p_style VARCHAR2 := 'stop' -- stop, warning, information
,
p_title VARCHAR2 := NULL,
p_prompt VARCHAR2 := NULL,
p_show_error BOOLEAN := FALSE,
p_error_title VARCHAR2 := NULL,
p_error_txt VARCHAR2 := NULL,
p_sheet PLS_INTEGER := NULL);
PROCEDURE defined_name (p_tl_col PLS_INTEGER -- top left
,
p_tl_row PLS_INTEGER,
p_br_col PLS_INTEGER -- bottom right
,
p_br_row PLS_INTEGER,
p_name VARCHAR2,
p_sheet PLS_INTEGER := NULL,
p_localsheet PLS_INTEGER := NULL);
PROCEDURE set_column_width (p_col PLS_INTEGER, p_width NUMBER, p_sheet PLS_INTEGER := NULL);
PROCEDURE set_column (p_col PLS_INTEGER,
p_numfmtid PLS_INTEGER := NULL,
p_fontid PLS_INTEGER := NULL,
p_fillid PLS_INTEGER := NULL,
p_borderid PLS_INTEGER := NULL,
p_alignment t_alignment_rec := NULL,
p_sheet PLS_INTEGER := NULL);
PROCEDURE set_row (p_row PLS_INTEGER,
p_numfmtid PLS_INTEGER := NULL,
p_fontid PLS_INTEGER := NULL,
p_fillid PLS_INTEGER := NULL,
p_borderid PLS_INTEGER := NULL,
p_alignment t_alignment_rec := NULL,
p_sheet PLS_INTEGER := NULL);
PROCEDURE freeze_rows (p_nr_rows PLS_INTEGER := 1, p_sheet PLS_INTEGER := NULL);
PROCEDURE freeze_cols (p_nr_cols PLS_INTEGER := 1, p_sheet PLS_INTEGER := NULL);
PROCEDURE freeze_pane (p_col PLS_INTEGER, p_row PLS_INTEGER, p_sheet PLS_INTEGER := NULL);
PROCEDURE set_autofilter (p_column_start PLS_INTEGER := NULL,
p_column_end PLS_INTEGER := NULL,
p_row_start PLS_INTEGER := NULL,
p_row_end PLS_INTEGER := NULL,
p_sheet PLS_INTEGER := NULL);
FUNCTION finish
RETURN BLOB;
FUNCTION query2sheet (p_sql VARCHAR2, p_column_headers BOOLEAN := TRUE, p_sheet PLS_INTEGER := NULL)
RETURN BLOB;
FUNCTION finish2 (p_clob IN OUT NOCOPY CLOB,
p_columns PLS_INTEGER,
p_rows PLS_INTEGER,
p_XLSX_date_format VARCHAR2,
p_XLSX_datetime_format VARCHAR2)
RETURN BLOB;
FUNCTION query2sheet2(p_sql VARCHAR2,
p_XLSX_date_format VARCHAR2 := 'dd/mm/yyyy',
p_XLSX_datetime_format VARCHAR2 := 'dd/mm/yyyy hh24:mi:ss')
RETURN BLOB;
function query2sheet3
(
p_sql in varchar2
, p_binds in t_bind_tab
, p_headers in t_header_tab
, p_XLSX_date_format VARCHAR2 := 'dd/mm/yyyy'
, p_XLSX_datetime_format VARCHAR2 := 'dd/mm/yyyy hh24:mi:ss'
)
return blob;
END;
/