Oracle PLSQL生成Excel文件(2)
来源：互联网 发布：郑州市软件开发公司 编辑：程序博客网 时间：2024/05/18 00:19
CREATE OR REPLACE PACKAGE xml_spreadsheet
/**
||@copyright (c) public domain
||@Name xml_spreadsheet
||@Description create Excel files in XML format
|| can be read by Open Office and MS Office XP/2002
|| to import into older MS Office versions
|| see http://support.microsoft.com/kb/923505
|| For further information on the XML format see
|| http://msdn2.microsoft.com/en-us/library/aa140066.aspx
||@history <pre>
|| +Table Änderungshistorie
|| +-----------+-------------------+-------+----------------------------------------
|| Date Who Version What's new
|| +-----------+-------------------+-------+----------------------------------------
|| 25.10.2007 Matzberger Marcus 0.1 New
|| 31.10.2007 Matzberger Marcus 0.2 Comments for Column title
|| 05.01.2008 Matzberger Marcus 0.6 close and open worksheet as separate procedures
|| 26.01.2008 Matzberger Marcus 0.7 allow more than one queries in abfrage
|| allow custom cell formats and cell protection
|| 09.02.2008 Matzberger Marcus 1.0 provide all features of this XML format
|| except named ranges
|| 19.02.2008 Matzberger Marcus 1.01 Changed format for comments for automatic documentation
|| 09.05.2008 Matzberger Marcus 1.1.3 changed color definition because OO ignored named colors
|| 23.06.2008 Matzberger Marcus 1.1.4 Increase Size for caption comments,
|| change definition for c_nf2decimalGroupSep
|| allow writeData without format
|| 25.07.2008 Matzberger Marcus 1.1.5 newWorksheet and newDataRow with new parameter
|| getRelativeCellReference
|| 13.09.2008 Matzberger Marcus 1.1.6 build_cursor public
|| CHR(38) instead of ampersand to avoid SET DEFINE OFF on installation
|| 20.09.2008 Matzberger Marcus 2.0 Write into CLOB
|| 12.11.2008 Matzberger Marcus 2.1 Wordwrap as default for headers
|| createNewFile: new parameter for default font
|| closeWorksheet: print setup (margins and text)
|| 30.03.2009 Matzberger Marcus 2.2 createNewFile: Character Encoding as parameter
|| repeat titles when printing as parameter in t_rec_caption
|| 19.08.2010 Matzberger Marcus 2.3 New predefined format: percent with 2 decimals
|| Doubled length of header text
|| +-----------+-------------------+-------+----------------------------------------
</pre>
*/
IS
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number default (2 decimals)
c_numberDefault CONSTANT VARCHAR2(3) := 's22';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number integer
c_integer CONSTANT VARCHAR2(3) := 's23';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number 2 decimals
c_number2decimal CONSTANT VARCHAR2(3) := 's24';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number percent
c_prozent CONSTANT VARCHAR2(3) := 's25';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number percent
c_percent2decimal CONSTANT VARCHAR2(4) := 's25a';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number currency
c_currency CONSTANT VARCHAR2(3) := 's26';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number accounting
c_accounting CONSTANT VARCHAR2(3) := 's27';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number unformatted
c_unformatted CONSTANT VARCHAR2(3) := 's28';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- number unformatted
c_unformattedSum CONSTANT VARCHAR2(3) := 's29';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- Date
c_date CONSTANT VARCHAR2(3) := 's30';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- Date with time
c_dateTime CONSTANT VARCHAR2(3) := 's31';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- Text default
c_textDefault CONSTANT VARCHAR2(3) := 's40';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- Text Bold
c_textBold CONSTANT VARCHAR2(3) := 's41';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- Text underlined
c_textUnderline CONSTANT VARCHAR2(3) := 's42';
-- Predefined format for p_format in writeData, newDataRow or t_tab_columns.format
-- Text bold and underlined
c_textBoldUnderline CONSTANT VARCHAR2(3) := 's43';
-- Predefined Color
-- can be used for text and background color
c_red CONSTANT VARCHAR2(10) := '#FF0000';
-- Predefined Color
c_yellow CONSTANT VARCHAR2(10) := '#FFFF00';
-- Predefined Color
c_blue CONSTANT VARCHAR2(10) := '#0000FF';
-- Predefined Color
c_green CONSTANT VARCHAR2(10) := '#008000';
-- Predefined Color
c_brightGreen CONSTANT VARCHAR2(10) := '#00FF00';
-- Predefined Color
c_brightBlue CONSTANT VARCHAR2(10) := '#80FFFF';
-- Predefined Color
c_lilac CONSTANT VARCHAR2(10) := '#FF00FF';
-- Predefined Color
c_brown CONSTANT VARCHAR2(10) := '#800000';
-- Predefined Color
c_white CONSTANT VARCHAR2(10) := '#FFFFFF';
-- Predefined Color
c_silver CONSTANT VARCHAR2(10) := '#C0C0C0';
-- Predefined Color
c_lavender CONSTANT VARCHAR2(10) := '#CC99FF';
-- Predefined Color
c_brightYellow CONSTANT VARCHAR2(10) := '#FFFF99';
-- Predefined Color
c_brightOrange CONSTANT VARCHAR2(10) := '#FFCC99';
-- Predefined Color
c_brightPink CONSTANT VARCHAR2(10) := '#FF99CC';
-- predefined format for conditional formatting.
-- Bold
c_emphasisBold CONSTANT VARCHAR2(20) := 'font-weight:700;';
-- predefined format for conditional formatting.
-- Italic
c_emphasisItalic CONSTANT VARCHAR2(20) := 'font-style:italic;';
-- predefined format for conditional formatting.
-- Bold an Italic
c_emphasisBoldItalic CONSTANT VARCHAR2(40) := 'font-style:italic;font-weight:700;';
-- predefined format for conditional formatting.
-- Underlined
c_markupUnderlined CONSTANT VARCHAR2(30) := 'text-underline-style:single;';
-- predefined format for conditional formatting.
-- Double underlined
c_markupUnderlinedD CONSTANT VARCHAR2(30) := 'text-underline-style:double;';
-- Background Pattern Solid
c_bgpSolid CONSTANT VARCHAR2(30) := 'Solid';
-- Background Pattern 75% Gray
c_bgpGray75 CONSTANT VARCHAR2(30) := 'Gray75';
-- Background Pattern 50% Gray
c_bgpGray50 CONSTANT VARCHAR2(30) := 'Gray50';
-- Background Patterns 25% Gray
c_bgpGray25 CONSTANT VARCHAR2(30) := 'Gray25';
-- Background Patterns 12.5% Gray
c_bgpGray125 CONSTANT VARCHAR2(30) := 'Gray125';
-- Background Patterns 6.5% Gray
c_bgpGray625 CONSTANT VARCHAR2(30) := 'Gray0625';
-- Background Patterns Thin Horizontal Stripes
c_bgpThinHorzStripe CONSTANT VARCHAR2(30) := 'ThinHorzStripe';
-- Background Patterns Horizontal Stripes
c_bgpHorzStripe CONSTANT VARCHAR2(30) := 'HorzStripe';
-- Background Patterns Thin Vertical Stripes
c_bgpThinVertStripe CONSTANT VARCHAR2(30) := 'ThinVertStripe';
-- Background Patterns Vertical Stripes
c_bgpVertStripe CONSTANT VARCHAR2(30) := 'VertStripe';
-- Background Patterns Thin Diagonal Stripes
c_bgpThinDiagStripe CONSTANT VARCHAR2(30) := 'ThinDiagStripe';
-- Background Patterns Thin ReverseDiagonal Stripes
c_bgpThinRevDiagStripe CONSTANT VARCHAR2(30) := 'ThinReverseDiagStripe';
-- Background Patterns Thin Diagonal Cross
c_bgpThinDiagCross CONSTANT VARCHAR2(30) := 'ThinDiagCross';
-- Background Patterns Diagonal Cross
c_bgpDiagCross CONSTANT VARCHAR2(30) := 'DiagCross';
-- Background Patterns ThickDiagonal Cross
c_bgpThickDiagCross CONSTANT VARCHAR2(30) := 'ThickDiagCross';
-- Background Patterns Thin Horizontal Cross
c_bgpThinHorzCross CONSTANT VARCHAR2(30) := 'ThinHorzCross';
-- Predefined number format for custom cell formats
-- Unformatted
c_nfUnformatted CONSTANT VARCHAR2(30) := '';
-- Predefined number format for custom cell formats
-- Integer
c_nfInteger CONSTANT VARCHAR2(30) := '0';
-- Predefined number format for custom cell formats
-- 2 decimals places
c_nf2decimal CONSTANT VARCHAR2(30) := 'Fixed';
-- Predefined number format for custom cell formats
-- 3 decimals places
c_nf3decimal CONSTANT VARCHAR2(30) := '0.000';
-- Predefined number format for custom cell formats
-- 2 decimals places and group separator
c_nf2decimalGroupSep CONSTANT VARCHAR2(30) := '#,##0.00';
-- Predefined number format for custom cell formats
-- 3 decimals places and group separator
c_nf3decimalGroupSep CONSTANT VARCHAR2(30) := '#,##0.000';
-- Predefined number format for custom cell formats
-- Currency with red negative numbers and local currency symbol
c_nfCurrency CONSTANT VARCHAR2(30) := 'Currency';
-- Predefined number format for custom cell formats
-- Currency with black negative numbers and Euro currency symbol
c_nfCurrencyEur CONSTANT VARCHAR2(30 CHAR) := '#,##0.00\ '||CHR(38)||'quot;€'||CHR(38)||'quot;';
-- Predefined number format for custom cell formats
-- Currency with red negative numbers and Euro currency symbol
c_nfCurrencyEurR CONSTANT VARCHAR2(55 CHAR) := '#,##0.00\ '||CHR(38)||'quot;€'||CHR(38)||'quot;;[Red]#,##0.00\ '||CHR(38)||'quot;€'||CHR(38)||'quot;';
-- Predefined number format for custom cell formats
-- accounting, aligned decimals, minus sign aligned left
c_nfAccounting CONSTANT VARCHAR2(70 CHAR) := '_-* #,##0.00\ _€_-;\-* #,##0.00\ _€_-;_-* '||CHR(38)||'quot;-'||CHR(38)||'quot;??\ _€_-;_-@_-';
-- Predefined number format for custom cell formats
-- accounting with Euro sign, aligned decimals, minus sign aligned left
c_nfAccountingEur CONSTANT VARCHAR2(110 CHAR) := '_-* #,##0.00\ '||CHR(38)||'quot;€'||CHR(38)||'quot;_-;\-* #,##0.00\ '||CHR(38)||'quot;€'||CHR(38)||'quot;_-;_-* '||CHR(38)||'quot;-'||CHR(38)||'quot;??\ '||CHR(38)||'quot;€'||CHR(38)||'quot;_-;_-@_-';
-- Predefined number format for custom cell formats
-- accounting with Euro ISO, aligned decimals, minus sign aligned left
c_nfAccountingEurIso CONSTANT VARCHAR2(90 CHAR) := '_-* #,##0.00\ [$EUR]_-;\-* #,##0.00\ [$EUR]_-;_-* '||CHR(38)||'quot;-'||CHR(38)||'quot;??\ [$EUR]_-;_-@_-';
-- Predefined number format for custom cell formats
-- accounting with USD sign at the left side of the number, aligned decimals, negative numbers red
c_nfAccountingUsd CONSTANT VARCHAR2(50 CHAR) := '[$$-409]#,##0.00_ ;[Red]\-[$$-409]#,##0.00\ ';
-- Predefined number format for custom cell formats
-- scientific 2 decimal places
c_nfScientific2decimals CONSTANT VARCHAR2(30) := '0.00E+00';
-- Predefined number format for custom cell formats
-- scientific 3 decimal places
c_nfScientific3decimals CONSTANT VARCHAR2(30) := '0.000E+00';
-- Predefined number format for custom cell formats
-- Percent no decimal places
c_nfPercent CONSTANT VARCHAR2(30) := '0%';
-- Predefined number format for custom cell formats
-- Percent 1 decimal place
c_nfPercent1 CONSTANT VARCHAR2(30) := '0.0%';
-- Predefined number format for custom cell formats
-- Percent 2 decimal places
c_nfPercent2 CONSTANT VARCHAR2(30) := 'Percent';
-- Predefined number format for custom cell formats
-- Percent 3 decimal places
c_nfPercent3 CONSTANT VARCHAR2(30) := '0.000%';
-- Predefined date format for custom cell formats
-- dd.mm.yyyy
c_dfDDMMYYYY CONSTANT VARCHAR2(30) := 'Short Date';
-- Predefined date format for custom cell formats
-- dd.mm.yyyy hh:mi
c_dfDDMMYYYYHHMI CONSTANT VARCHAR2(30) := 'General Date';
-- Predefined date format for custom cell formats
-- dd.mon yy
c_dfDDMONYY CONSTANT VARCHAR2(30) := 'Medium Date';
-- Predefined date format for custom cell formats
-- dd.Month yyyy
c_dfDDMONTHYYYY CONSTANT VARCHAR2(30) := '[$-407]d/\ mmmm\ yyyy;@';
-- Predefined date format for custom cell formats
--Weekday, dd Month yyyy
c_dfDayDDMONTHYYYY CONSTANT VARCHAR2(40) := '[$-F800]dddd\,\ mmmm\ dd\,\ yyyy';
-- Predefined date format for custom cell formats
-- dd.mm.yyyy hh:mm:ss
c_dfDDMMYYYYHHMISS CONSTANT VARCHAR2(30) := 'dd/mm/yyyy\ hh:mm:ss';
-- Predefined date format for custom cell formats
-- hh:mi
c_dfHHMI CONSTANT VARCHAR2(30) := 'Short Time';
-- Predefined date format for custom cell formats
-- hh:mi:ss AM/PM
c_dfHHMISS CONSTANT VARCHAR2(30) := 'Long Time';
-- Predefined date format for custom cell formats
-- mi:ss,s
c_dfMISSS CONSTANT VARCHAR2(30) := 'mm:ss.0;@';
-- Predefined date format for custom cell formats
-- d.m.yy h:mi
c_dfDMYYHMI CONSTANT VARCHAR2(30) := 'd/m/yy\ h:mm;@';
-- Predefined text to include page number in header or footer text
c_mtPage CONSTANT VARCHAR2(10) := '\[page]';
-- Predefined text to include total page number in header or footer text
c_mtPageTotal CONSTANT VARCHAR2(10) := '\[pageT]';
-- Predefined text to include time in header or footer text
c_mtTime CONSTANT VARCHAR2(10) := '\[time]';
-- Predefined text to include date in header or footer text
c_mtDate CONSTANT VARCHAR2(10) := '\[date]';
-- Predefined text to include file path in header or footer text
c_mtPath CONSTANT VARCHAR2(10) := '\[path]';
-- Predefined text to include file name in header or footer text
c_mtName CONSTANT VARCHAR2(10) := '\[name]';
-- Predefined text to include tab name in header or footer text
c_mtTab CONSTANT VARCHAR2(10) := '\[tab]';
-- Predefined text set font size 8 in header or footer text
c_mtFontSize8 CONSTANT VARCHAR2(10) := '\[size8]';
-- Predefined text set font size 10 in header or footer text
c_mtFontSize10 CONSTANT VARCHAR2(10) := '\[size10]';
-- Predefined text set font size 12 in header or footer text
c_mtFontSize12 CONSTANT VARCHAR2(10) := '\[size12]';
-- Predefined text set font size 14 in header or footer text
c_mtFontSize14 CONSTANT VARCHAR2(10) := '\[size14]';
-- Predefined text set font Arial bold in header or footer text
c_mtFontBold CONSTANT VARCHAR2(10) := '\[bold]';
/**
||@Name t_rec_columns
||@Description Record type for column definitions
||@Param cWidth PLS_INTEGER column width. Between 0 and 600 or NULL.
||@Param cFormat VARCHAR2(50) ID of a user defined cell format. Set as default for cells in this column
||@Param cAutowidth BOOLEAN set the width of a column automatically. If a width is defined then this value will be the minimal width of the column. TRUE = Yes, FALSE = No, NULL = Default. Works only for date and number values.
||@param cHidden BOOLEAN Hide column. TRUE = Yes, FALSE = No, NULL = Default
||@param cIndex PLS_INTEGER Specifies the position of this column within the table. If this tag is not specified, the first instance has an assumed Index="1". Each additional Column element has an assumed Index that is one higher.
|| Indices must appear in strictly increasing order. Failure to do so will result in an XML Spreadsheet document that is invalid. Indices do not need to be sequential, however. Omitted indices are formatted with the default style's format.
*/
TYPE t_rec_columns IS RECORD(
cWidth PLS_INTEGER
,cFormat VARCHAR2(50)
,cAutowidth BOOLEAN
,cHidden BOOLEAN
,cIndex PLS_INTEGER
);
TYPE t_tab_columns IS TABLE OF t_rec_columns
INDEX BY BINARY_INTEGER;
/**
||@Name t_rec_caption
||@Description Record type for caption definitions
|| -- When there is at least one top caption then every caption without a top caption needs an blank (' ') as topTitle
|| -- Indices of t_tab_caption need to be sequential from 1 to n
||@Param title VARCHAR2(100) -- column title
||@Param topTitle VARCHAR2(100) -- first row caption (topCaption) when 2 caption rows. Default ''
||@Param span PLS_INTEGER -- topCaption spans x columns. Default 1
||@param comment VARCHAR2(1000) -- comment on title. Default ''
||@param repeatTitle BOOLEAN -- Repeat titles on every page when printed. TRUE = Yes. Needs to be set for first title only.
*/
TYPE t_rec_caption IS RECORD(
title VARCHAR2(100)
,topTitle VARCHAR2(100)
,span PLS_INTEGER
,comment VARCHAR2(1000)
,repeatTitle BOOLEAN
);
TYPE t_tab_caption IS TABLE OF t_rec_caption
INDEX BY BINARY_INTEGER;
/**
||@Name t_tab_abfrage
||@Description TABLE OF VARCHAR2(2000)
|| List of strings for column headings and definition sum columns in Function / Procedure abfrage
*/
TYPE t_tab_abfrage IS TABLE OF VARCHAR2(2000);
/**
||@Name t_rec_border
||@Description Record type for border definitions
||@Param bPosition VARCHAR2(60) -- Top, Bottom, Left, Right, DiagonalRight, DiagonalLeft. Can be combined in a comma separated list, i.e 'Top,Bottom'
||@Param bLineStyle VARCHAR2(50) -- Dot, DashDot, DashDotDot, Dash, SlantDashDot (weight 2), Double (weight 3). Default Continuous
||@Param bWeight PLS_INTEGER -- Width of the Border 1,2 or 3. Default 0: 0¿Hairline 1¿Thin 2¿Medium 3¿Thick
||@param bColor VARCHAR2(50) -- Border color like the predefined (c_red...) or RGB value '#FF99CC'
*/
TYPE t_rec_border IS RECORD(
bPosition VARCHAR2(60)
,bLineStyle VARCHAR2(50)
,bWeight PLS_INTEGER
,bColor VARCHAR2(50)
);
TYPE t_tab_border IS TABLE OF t_rec_border
INDEX BY BINARY_INTEGER;
/**
||@Name t_rec_font
||@Description Record type for font definitions
||@Param fName VARCHAR2(50) -- Font Name "Times New Roman","Courier New","Wingdings",... Default "Arial"
||@Param fFamily VARCHAR2(50) -- Font family - Automatic, Decorative, Modern, Roman, Script, Swiss
||@Param fSize PLS_INTEGER -- Font size in pt. Default 10, Min 5
||@param fColor VARCHAR2(50) -- Font color like the predefined (c_red...) or RGB value '#FF99CC'. Default black
||@param fBold BOOLEAN -- bold text TRUE = Yes, FALSE = No, NULL = Default (inherit from parent)
||@param fItalic BOOLEAN -- italic text TRUE = Yes, FALSE = No, NULL = Default (inherit from parent)
||@param fStrikeThrough BOOLEAN -- strike through text TRUE = Yes, FALSE = No, NULL = Default (inherit from parent)
||@param fUnderline VARCHAR2(50) -- underline text s = single, d = double, as = accounting single, ad = accounting double
||@param fPosition VARCHAR2(50) -- Extra Markup 'Subscript' or 'Superscript'
*/
TYPE t_rec_font IS RECORD(
fName VARCHAR2(50)
,fFamily VARCHAR2(50)
,fSize PLS_INTEGER
,fColor VARCHAR2(50)
,fBold BOOLEAN
,fItalic BOOLEAN
,fStrikeThrough BOOLEAN
,fUnderline VARCHAR2(50)
,fPosition VARCHAR2(50)
);
/**
||@Name t_rec_background
||@Description Record type for background definitions
||@Param bColor VARCHAR2(50) -- background color like the predefined (c_red...) or RGB value '#FF99CC'
||@Param bPattern VARCHAR2(50) -- background pattern: see predefined Background Patterns. Default Solid
||@Param bPatternColor VARCHAR2(50) -- background pattern color like the predefined (c_red...) or RGB value '#FF99CC'
*/
TYPE t_rec_background IS RECORD(
bColor VARCHAR2(50)
,bPattern VARCHAR2(50)
,bPatternColor VARCHAR2(50)
);
/**
||@Name t_rec_alignment
||@Description Record type for text behavior definitions
||@Param vertical VARCHAR2(50) -- vertical alignment: Top, Bottom, Center. DEFAULT Top
||@Param horizontal VARCHAR2(50) -- horizontal alignment: Left, Center, Right, Justify, Fill
||@Param textRotate PLS_INTEGER -- Degrees to rotate text (-90 to 90)
||@param readingOrder VARCHAR2(30) -- Specifies the default right-to-left text entry mode for a cell. 'RightToLeft', 'LeftToRight' and 'Context'
||@Param shrinkToFit BOOLEAN -- True means that the text size should be shrunk so that all of the text fits within the cell.
||@Param verticalText BOOLEAN -- Specifies whether the text is drawn "downwards", whereby each letter is drawn horizontally, one above the other. TRUE = Yes, FALSE = No, NULL = Default (inherit from parent)
||@param wrapText BOOLEAN -- Specifies whether the text in this cell should wrap at the cell boundary. TRUE = Yes, FALSE = No, NULL = Default (inherit from parent)
*/
TYPE t_rec_alignment IS RECORD(
vertical VARCHAR2(50)
,horizontal VARCHAR2(50)
,textRotate PLS_INTEGER
,readingOrder VARCHAR2(30)
,shrinkToFit BOOLEAN
,verticalText BOOLEAN
,wrapText BOOLEAN
);
/**
||@Name t_rec_customStyles
||@Description Record type for custom style definitions
||@Param id VARCHAR2(50) -- ID if the custom format. To be used for p_format when calling writeData. Has to be unique within the spreadsheet. s22 to s43 are predefined by the package
||@Param type VARCHAR2(50) -- Data type Text, Date, Number, Percent
||@Param format VARCHAR2(200) -- Number or date format. One of the predefined formats (c_nfX for numbers or c_dfX for dates) or self defined as in Excel
||@param font t_rec_font
||@Param background t_rec_background
||@Param border t_tab_border -- Borders, defined in a record
||@Param alignment t_rec_alignment
||@param protection BOOLEAN -- Protect cell from changes by user text TRUE = Yes, FALSE = No, NULL = Default (inherit from parent)
*/
TYPE t_rec_customStyles IS RECORD(
id VARCHAR2(50)
,type VARCHAR2(50)
,format VARCHAR2(200)
,font t_rec_font
,background t_rec_background
,border t_tab_border
,alignment t_rec_alignment
,protection BOOLEAN
);
TYPE t_tab_customStyles IS TABLE OF t_rec_customStyles
INDEX BY BINARY_INTEGER;
-- Multiple contitions can be defined for the same range with a maximum of 3.
-- They are applied in order of apperance
/**
||@Name t_rec_conditionalFormats
||@Description Record type for conditional format definitions
|| Multiple contitions can be defined for the same range with a maximum of 3.
|| They are applied in order of apperance
||@Param range VARCHAR2(50) -- In R3C2 notation e.g.R4C4, R4C4:R5C4 or C4 for entire column,
||@Param qualifier VARCHAR2(50) -- Qualifier for comparison (Less, Greater, Equal, NotEqual, LessOrEqual, GreaterOrEqual, Between, NotBetween
||@Param value1 VARCHAR2(50) -- first value to be compared with
||@param value2 VARCHAR2(50) -- second value to be compared with (for Between, NotBetween)
||@Param formatColor VARCHAR2(50) -- format for text color
||@Param formatEmphasis VARCHAR2(50) -- format for text bold/italic
||@Param formatMarkup VARCHAR2(50) -- format for text underlined singe/double
||@param formatBgColor VARCHAR2(50) -- format for background color
*/
TYPE t_rec_conditionalFormats IS RECORD(
range VARCHAR2(50)
,qualifier VARCHAR2(50)
,value1 VARCHAR2(50)
,value2 VARCHAR2(50)
,formatColor VARCHAR2(50)
,formatEmphasis VARCHAR2(50)
,formatMarkup VARCHAR2(50)
,formatBgColor VARCHAR2(50)
);
TYPE t_tab_conditionalFormats IS TABLE OF t_rec_conditionalFormats
INDEX BY BINARY_INTEGER;
/**
||@Name t_rec_printSetup
||@Description Record type for print setup
|| Margins expect values in centimeters
||@Param landscape BOOLEAN -- TRUE = landscape FALSE or NULL = portrait
||@Param headerLeft VARCHAR2(500) -- left aligned header text
||@param headerCenter VARCHAR2(500) -- centered header text
||@Param headerRight VARCHAR2(500) -- right aligned header text
||@Param headerMargin NUMBER --
||@Param footerLeft VARCHAR2(500) --
||@param footerCenter VARCHAR2(500) --
||@Param footerRight VARCHAR2(500) --
||@Param footerMargin NUMBER --
||@param pageMarginTop NUMBER --
||@Param pageMarginBottom NUMBER --
||@Param pageMarginLeft NUMBER --
||@Param pageMarginRight NUMBER --
*/
TYPE t_rec_printSetup IS RECORD(
landscape BOOLEAN
,headerLeft VARCHAR2(500)
,headerCenter VARCHAR2(500)
,headerRight VARCHAR2(500)
,headerMargin NUMBER
,footerLeft VARCHAR2(500)
,footerCenter VARCHAR2(500)
,footerRight VARCHAR2(500)
,footerMargin NUMBER
,pageMarginTop NUMBER
,pageMarginBottom NUMBER
,pageMarginLeft NUMBER
,pageMarginRight NUMBER
);
g_tab_columns t_tab_columns;
g_tab_caption t_tab_caption;
g_tab_conditionalFormats t_tab_conditionalFormats;
g_rec_customStyles t_rec_customStyles;
g_tab_customStyles t_tab_customStyles;
g_rec_printSetup t_rec_printSetup;
/**
|| @Name getExcelColFromColNumber
|| @Description gets the Excel column identifier from the current column number
|| e.g. 1 -> A, 10 -> J
|| @param p_number - current column number with 1 <= p_number <= 256
*/
FUNCTION getExcelColFromColNumber
(
p_number IN PLS_INTEGER
)
RETURN VARCHAR2;
/**
|| @Name createNewFile
|| @Description create a new Excel file
|| @param p_path - valid DIRECTORY_NAME from database
|| @param p_filename - file name
|| @param p_tab_customStyles - user defined cell formats
|| @param p_globalCustomStyle - Set a global default cell format
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
FUNCTION createNewFile
(
p_path IN VARCHAR2
,p_filename IN VARCHAR2
,p_tab_customStyles IN t_tab_customStyles := g_tab_customStyles
,p_globalCustomStyle IN t_rec_customStyles := g_rec_customStyles
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
)
RETURN utl_file.FILE_TYPE;
/**
|| @Name createNewFile
|| @Description create a new Excel file
|| @param p_file - CLOB
|| @param p_tab_customStyles - user defined cell formats
|| @param p_globalCustomStyle - Set a global default cell format
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
PROCEDURE createNewFile
(
p_file IN OUT NOCOPY CLOB
,p_tab_customStyles IN t_tab_customStyles := g_tab_customStyles
,p_globalCustomStyle IN t_rec_customStyles := g_rec_customStyles
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
);
/**
|| @Name newDataRow
|| @Description close previous row and open a new one
|| @param p_fileHandle - file handle from 'createNewFile'
|| @param p_format - Formatting of the cells according to constants
|| in package header or user defines formats.
|| @param p_index - Row index: Specifies the position of this row within
|| the table. If this tag is not specified, the first instance
|| has an assumed Index="1". Each additional Row element has an
|| assumed Index that is one higher. Indices must appear in
|| strictly increasing order. Failure to do so will result in
|| an XML Spreadsheet document that is invalid. Indices do not
|| need to be sequential, however. Omitted indices are formatted
|| with the default style's format.
|| @param p_closeRow - Close previous data row before opening the new one.
|| Set to FALSE for the first row when the worksheet is opend
|| with p_beginNewRow = FALSE
*/
PROCEDURE newDataRow
(
p_fileHandle IN utl_file.FILE_TYPE
,p_format IN VARCHAR2 := NULL
,p_height IN PLS_INTEGER := NULL
,p_index IN PLS_INTEGER := NULL
,p_closeRow IN BOOLEAN := TRUE
);
/**
|| @Name newDataRow
|| @Description close previous row and open a new one
|| @param p_file - CLOB
|| @param p_format - Formatting of the cells according to constants
|| in package header or user defines formats.
|| @param p_index - Row index: Specifies the position of this row within
|| the table. If this tag is not specified, the first instance
|| has an assumed Index="1". Each additional Row element has an
|| assumed Index that is one higher. Indices must appear in
|| strictly increasing order. Failure to do so will result in
|| an XML Spreadsheet document that is invalid. Indices do not
|| need to be sequential, however. Omitted indices are formatted
|| with the default style's format.
|| @param p_closeRow - Close previous data row before opening the new one.
|| Set to FALSE for the first row when the worksheet is opend
|| with p_beginNewRow = FALSE
*/
PROCEDURE newDataRow
(
p_file IN OUT NOCOPY CLOB
,p_format IN VARCHAR2 := NULL
,p_height IN PLS_INTEGER := NULL
,p_index IN PLS_INTEGER := NULL
,p_closeRow IN BOOLEAN := TRUE
);
/**
|| @Name writeData
|| @Description write text
|| @param p_fileHandle - file handle from 'createNewFile'
|| @param p_text - string to be written
|| @param p_format - Formatting of the text according to constants
|| in package header.
|| @param p_comment - comment.
|| @param p_formula - The cells are referenced relative to the formula cell
|| The syntax is e.g.
|| =SUM(R[-2]C,R[-1]C)
|| =IF(B6>0;"T";"U")
|| =AVERAGE(R[-2]C:R[-1]C)
|| =COUNT(R[-2]C:R[-1]C)
|| @param p_colspan - Merge cells horizontally.
|| @param p_href - URL to which to link this cell.
|| @param p_index - column index within containing row.
|| Indices must appear in strictly increasing order
|| Indices must not overlap
|| @param p_protected - indicates whether or not this cell is protected.
|| When the worksheet is unprotected, cell-level protection has
|| no effect. When a cell is protected, it will not allow the
|| user to enter information into it.
*/
PROCEDURE writeData
(
p_fileHandle IN utl_file.FILE_TYPE
,p_text IN VARCHAR2
,p_format IN VARCHAR2 := NULL
,p_comment IN VARCHAR2 := NULL
,p_formula IN VARCHAR2 := NULL
,p_colspan IN PLS_INTEGER := NULL
,p_href IN VARCHAR2 := NULL
,p_index IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
);
/**
|| @Name writeData
|| @Description write text
|| @param p_file - CLOB
|| @param p_text - string to be written
|| @param p_format - Formatting of the text according to constants
|| in package header.
|| @param p_comment - comment.
|| @param p_formula - The cells are referenced relative to the formula cell
|| The syntax is e.g.
|| =SUM(R[-2]C,R[-1]C)
|| =IF(B6>0;"T";"U")
|| =AVERAGE(R[-2]C:R[-1]C)
|| =COUNT(R[-2]C:R[-1]C)
|| @param p_colspan - Merge cells horizontally.
|| @param p_href - URL to which to link this cell.
|| @param p_index - column index within containing row.
|| Indices must appear in strictly increasing order
|| Indices must not overlap
|| @param p_protected - indicates whether or not this cell is protected.
|| When the worksheet is unprotected, cell-level protection has
|| no effect. When a cell is protected, it will not allow the
|| user to enter information into it.
*/
PROCEDURE writeData
(
p_file IN OUT NOCOPY CLOB
,p_text IN VARCHAR2
,p_format IN VARCHAR2 := NULL
,p_comment IN VARCHAR2 := NULL
,p_formula IN VARCHAR2 := NULL
,p_colspan IN PLS_INTEGER := NULL
,p_href IN VARCHAR2 := NULL
,p_index IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
);
/**
|| @Name writeData
|| @Description write number
|| @param p_fileHandle - file handle from 'createNewFile'
|| @param p_number - number to be written
|| @param p_format - Formatting of the number Values according to constants
|| in package header.
|| @param p_comment - comment.
|| @param p_formula - The cells are referenced relative to the formula cell
|| The syntax is e.g.
|| =SUM(R[-2]C,R[-1]C)
|| =IF(B6>0;"T";"U")
|| =AVERAGE(R[-2]C:R[-1]C)
|| =COUNT(R[-2]C:R[-1]C)
|| @param p_colspan - Merge cells horizontally.
|| @param p_href - URL to which to link this cell.
|| @param p_index - column index within containing row.
|| Indices must appear in strictly increasing order
|| Indices must not overlap
|| @param p_protected - indicates whether or not this cell is protected.
|| When the worksheet is unprotected, cell-level protection has
|| no effect. When a cell is protected, it will not allow the
|| user to enter information into it.
*/
PROCEDURE writeData
(
p_fileHandle IN utl_file.FILE_TYPE
,p_number IN NUMBER
,p_format IN VARCHAR2 := NULL
,p_comment IN VARCHAR2 := NULL
,p_formula IN VARCHAR2 := NULL
,p_colspan IN PLS_INTEGER := NULL
,p_href IN VARCHAR2 := NULL
,p_index IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
);
/**
|| @Name writeData
|| @Description write number
|| @param p_file - CLOB
|| @param p_number - number to be written
|| @param p_format - Formatting of the number Values according to constants
|| in package header.
|| @param p_comment - comment.
|| @param p_formula - The cells are referenced relative to the formula cell
|| The syntax is e.g.
|| =SUM(R[-2]C,R[-1]C)
|| =IF(B6>0;"T";"U")
|| =AVERAGE(R[-2]C:R[-1]C)
|| =COUNT(R[-2]C:R[-1]C)
|| @param p_colspan - Merge cells horizontally.
|| @param p_href - URL to which to link this cell.
|| @param p_index - column index within containing row.
|| Indices must appear in strictly increasing order
|| Indices must not overlap
|| @param p_protected - indicates whether or not this cell is protected.
|| When the worksheet is unprotected, cell-level protection has
|| no effect. When a cell is protected, it will not allow the
|| user to enter information into it.
*/
PROCEDURE writeData
(
p_file IN OUT NOCOPY CLOB
,p_number IN NUMBER
,p_format IN VARCHAR2 := NULL
,p_comment IN VARCHAR2 := NULL
,p_formula IN VARCHAR2 := NULL
,p_colspan IN PLS_INTEGER := NULL
,p_href IN VARCHAR2 := NULL
,p_index IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
);
/**
|| @Name writeData
|| @Description write date
|| @param p_fileHandle - file handle from 'createNewFile'
|| @param p_date - date to be written
|| @param p_format - Formatting of the date values according to constants
|| in package header.
|| @param p_comment - comment.
|| @param p_formula - The cells are referenced relative to the formula cell
|| The syntax is e.g.
|| =SUM(R[-2]C,R[-1]C)
|| =IF(B6>0;"T";"U")
|| =AVERAGE(R[-2]C:R[-1]C)
|| =COUNT(R[-2]C:R[-1]C)
|| @param p_colspan - Merge cells horizontally.
|| @param p_href - URL to which to link this cell.
|| @param p_index - column index within containing row.
|| Indices must appear in strictly increasing order
|| Indices must not overlap
|| @param p_protected - indicates whether or not this cell is protected.
|| When the worksheet is unprotected, cell-level protection has
|| no effect. When a cell is protected, it will not allow the
|| user to enter information into it.
*/
PROCEDURE writeData
(
p_fileHandle IN utl_file.FILE_TYPE
,p_date IN DATE
,p_format IN VARCHAR2 := NULL
,p_comment IN VARCHAR2 := NULL
,p_formula IN VARCHAR2 := NULL
,p_colspan IN PLS_INTEGER := NULL
,p_href IN VARCHAR2 := NULL
,p_index IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
);
/**
|| @Name writeData
|| @Description write date
|| @param p_file - CLOB
|| @param p_date - date to be written
|| @param p_format - Formatting of the date values according to constants
|| in package header.
|| @param p_comment - comment.
|| @param p_formula - The cells are referenced relative to the formula cell
|| The syntax is e.g.
|| =SUM(R[-2]C,R[-1]C)
|| =IF(B6>0;"T";"U")
|| =AVERAGE(R[-2]C:R[-1]C)
|| =COUNT(R[-2]C:R[-1]C)
|| @param p_colspan - Merge cells horizontally.
|| @param p_href - URL to which to link this cell.
|| @param p_index - column index within containing row.
|| Indices must appear in strictly increasing order
|| Indices must not overlap
|| @param p_protected - indicates whether or not this cell is protected.
|| When the worksheet is unprotected, cell-level protection has
|| no effect. When a cell is protected, it will not allow the
|| user to enter information into it.
*/
PROCEDURE writeData
(
p_file IN OUT NOCOPY CLOB
,p_date IN DATE
,p_format IN VARCHAR2 := NULL
,p_comment IN VARCHAR2 := NULL
,p_formula IN VARCHAR2 := NULL
,p_colspan IN PLS_INTEGER := NULL
,p_href IN VARCHAR2 := NULL
,p_index IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
);
/**
|| @Name closeWorksheet
|| @Description close last data row and close worksheet
|| @param p_fileHandle - file handle from 'createNewFile'
|| @param p_lineFixed - is the n-th line to be fixed when scrolling
|| within the last worksheet
|| 0 = not fixed
|| @param p_colFixed - is the n-th column to be fixed when scrolling
|| within the last worksheet
|| 0 = not fixed
|| @param p_tab_caption - collection with caption information
|| @param p_autofilter - Set autofilter TRUE = Yes
|| @param p_tab_conditionalFormats - collection with formatting information
|| @param p_rec_printSetup - format for printing
*/
PROCEDURE closeWorksheet
(
p_fileHandle IN utl_file.FILE_TYPE
,p_lineFixed IN PLS_INTEGER := 1
,p_colFixed IN PLS_INTEGER := 0
,p_tab_caption IN t_tab_caption := g_tab_caption
,p_autofilter IN BOOLEAN := TRUE
,p_tab_conditionalFormats IN t_tab_conditionalFormats := g_tab_conditionalFormats
,p_rec_printSetup IN t_rec_printSetup := g_rec_printSetup
);
/**
|| @Name closeWorksheet
|| @Description close last data row and close worksheet
|| @param p_file - CLOB
|| @param p_lineFixed - is the n-th line to be fixed when scrolling
|| within the last worksheet
|| 0 = not fixed
|| @param p_colFixed - is the n-th column to be fixed when scrolling
|| within the last worksheet
|| 0 = not fixed
|| @param p_tab_caption - collection with caption information
|| @param p_autofilter - Set autofilter TRUE = Yes
|| @param p_tab_conditionalFormats - collection with formatting information
|| @param p_rec_printSetup - format for printing
*/
PROCEDURE closeWorksheet
(
p_file IN OUT NOCOPY CLOB
,p_lineFixed IN PLS_INTEGER := 1
,p_colFixed IN PLS_INTEGER := 0
,p_tab_caption IN t_tab_caption := g_tab_caption
,p_autofilter IN BOOLEAN := TRUE
,p_tab_conditionalFormats IN t_tab_conditionalFormats := g_tab_conditionalFormats
,p_rec_printSetup IN t_rec_printSetup := g_rec_printSetup
);
/**
|| @Name newWorksheet
|| @Description open a new worksheet and open first data row
|| @param p_fileHandle - file handle from 'createNewFile'
|| @param p_sheetname - name of the first worksheet.
|| This name has to be unique within the file
|| @param p_tab_caption - collection with caption information
|| @param p_tab_columns - collection with column settings
|| @param p_format - Formatting of the cells in new row according to constants
|| in package header or user defines formats.
|| @param p_height - Row height in pt
|| @param p_protected - indicates whether or not cell protection is in effect.
|| When the worksheet is unprotected, cell-level protection has no effect
|| @param p_beginNewRow - Begin new data row after opening the worksheet
*/
PROCEDURE newWorksheet
(
p_fileHandle IN utl_file.FILE_TYPE
,p_sheetname IN VARCHAR2
,p_tab_caption IN t_tab_caption := g_tab_caption
,p_tab_columns IN t_tab_columns := g_tab_columns
,p_format IN VARCHAR2 := NULL
,p_height IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
,p_beginNewRow IN BOOLEAN := TRUE
);
/**
|| @Name newWorksheet
|| @Description open a new worksheet and open first data row
|| @param p_file - CLOB
|| @param p_sheetname - name of the first worksheet.
|| This name has to be unique within the file
|| @param p_tab_caption - collection with caption information
|| @param p_tab_columns - collection with column settings
|| @param p_format - Formatting of the cells in new row according to constants
|| in package header or user defines formats.
|| @param p_height - Row height in pt
|| @param p_protected - indicates whether or not cell protection is in effect.
|| When the worksheet is unprotected, cell-level protection has no effect
|| @param p_beginNewRow - Begin new data row after opening the worksheet
*/
PROCEDURE newWorksheet
(
p_file IN OUT NOCOPY CLOB
,p_sheetname IN VARCHAR2
,p_tab_caption IN t_tab_caption := g_tab_caption
,p_tab_columns IN t_tab_columns := g_tab_columns
,p_format IN VARCHAR2 := NULL
,p_height IN PLS_INTEGER := NULL
,p_protected IN BOOLEAN := NULL
,p_beginNewRow IN BOOLEAN := TRUE
);
/**
|| @Name closeFile
|| @Description close last data row and close file
|| @param p_fileHandle - file handle from 'createNewFile'
*/
PROCEDURE closeFile
(
p_fileHandle IN utl_file.FILE_TYPE
);
/**
|| @Name closeFile
|| @Description close last data row and close file
|| @param p_file - CLOB
*/
PROCEDURE closeFile
(
p_file IN OUT NOCOPY CLOB
);
/**
|| @Name build_cursor
|| @Description build a cursor from a SELECT
|| @param p_query - SELECT string
|| @param p_parm_names - list of parameter names
|| @param p_parm_values - list of parameter values
|| @return cursor number as INTEGER
*/
FUNCTION build_cursor
(
p_query IN VARCHAR2
,p_parm_names IN t_tab_abfrage
,p_parm_values IN t_tab_abfrage
)
RETURN INTEGER;
/**
|| @Name abfrage
|| @Description write the output of the cursor into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| @param p_cursor - cursor
|| @param p_path - directory path - DIRECTORY_NAME from DB
|| @param p_filename - file name
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
PROCEDURE abfrage
(
p_cursor IN INTEGER
,p_path IN VARCHAR2
,p_filename IN VARCHAR2 DEFAULT NULL
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
);
/**
|| @Name abfrage
|| @Description write the output of the query into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| @param p_query - SELECT
|| @param p_path - DIRECTORY_NAME from DB
|| @param p_filename - file name
|| @param p_parm_names - list of parameter names
|| @param p_parm_values - list of parameter values
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
PROCEDURE abfrage
(
p_query IN VARCHAR2
,p_path IN VARCHAR2
,p_filename IN VARCHAR2 DEFAULT NULL
,p_parm_names IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_parm_values IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
);
/**
|| @Name abfrage
|| @Description write the output of the cursor into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| next query into a file opend with FUNCTION abfrage
|| @param p_cursor - cursor
|| @param p_fileHandle - file handle from FUNCTION 'abfrage'
|| @param p_sheetname - name of the current worksheet.
|| This name has to be unique within the file
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_close - should the file be closed after the query
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
FUNCTION abfrage
(
p_cursor IN INTEGER
,p_path IN VARCHAR2
,p_filename IN VARCHAR2 DEFAULT NULL
,p_sheetname IN VARCHAR2
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_close IN BOOLEAN DEFAULT TRUE
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
)
RETURN utl_file.FILE_TYPE;
/**
|| @Name abfrage
|| @Description write the output of the query into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| @param p_query - SELECT
|| @param p_path - DIRECTORY_NAME from DB
|| @param p_filename - file name
|| @param p_sheetname - name of the first worksheet.
|| This name has to be unique within the file
|| @param p_parm_names - list of parameter names
|| @param p_parm_values - list of parameter values
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_close - should the file be closed after the query
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
FUNCTION abfrage
(
p_query IN VARCHAR2
,p_path IN VARCHAR2
,p_filename IN VARCHAR2 DEFAULT NULL
,p_sheetname IN VARCHAR2
,p_parm_names IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_parm_values IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_close IN BOOLEAN DEFAULT TRUE
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
)
RETURN utl_file.FILE_TYPE;
/**
|| @Name abfrage
|| @Description write the output of the cursor into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| next query into a file opend with FUNCTION abfrage
|| @param p_cursor - cursor
|| @param p_fileHandle - file handle from FUNCTION 'abfrage'
|| @param p_sheetname - name of the current worksheet.
|| This name has to be unique within the file
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_close - should the file be closed after the query
*/
PROCEDURE abfrage
(
p_cursor IN INTEGER
,p_fileHandle IN utl_file.FILE_TYPE
,p_sheetname IN VARCHAR2
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_close IN BOOLEAN DEFAULT TRUE
);
/**
|| @Name abfrage
|| @Description write the output of the query into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| next query into a file opend with FUNCTION abfrage
|| @param p_query - SELECT
|| @param p_fileHandle - file handle from FUNCTION 'abfrage'
|| @param p_sheetname - name of the current worksheet.
|| This name has to be unique within the file
|| @param p_parm_names - list of parameter names
|| @param p_parm_values - list of parameter values
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_close - should the file be closed after the query
*/
PROCEDURE abfrage
(
p_query IN VARCHAR2
,p_fileHandle IN utl_file.FILE_TYPE
,p_sheetname IN VARCHAR2
,p_parm_names IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_parm_values IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_close IN BOOLEAN DEFAULT TRUE
);
/**
|| @Name abfrage
|| @Description write the output of the cursor into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| @param p_cursor - cursor
|| @param p_file - CLOB
|| @param p_sheetname - name of the current worksheet.
|| This name has to be unique within the file
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_close - should the file be closed after the query
|| @param p_first - Is this the first query for this file.
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
PROCEDURE abfrage
(
p_cursor IN INTEGER
,p_file IN OUT NOCOPY CLOB
,p_sheetname IN VARCHAR2
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_close IN BOOLEAN DEFAULT TRUE
,p_first IN BOOLEAN DEFAULT TRUE
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
);
/**
|| @Name abfrage
|| @Description write the output of the query into an excelformat file
|| idea taken from package owa_silk from Tom Kyte
|| @param p_query - SELECT
|| @param p_file - CLOB
|| @param p_sheetname - name of the current worksheet.
|| This name has to be unique within the file
|| @param p_parm_names - list of parameter names
|| @param p_parm_values - list of parameter values
|| @param p_sumColumns - list of columns to be summed
|| @param p_maxRows - maximum rows to be written on one worksheet Default 64000
|| @param p_showNullAs - how to disply NULLS
|| @param p_titles - list of column captions
|| @param p_close - should the file be closed after the query
|| @param p_first - Is this the first query for this file.
|| @param p_encoding - Character encoding like UTF-8, UTF-16. DEFAULT windows-1252
*/
PROCEDURE abfrage
(
p_query IN VARCHAR2
,p_file IN OUT NOCOPY CLOB
,p_sheetname IN VARCHAR2
,p_parm_names IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_parm_values IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_sumColumns IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_maxRows IN NUMBER DEFAULT 64000
,p_showNullAs IN VARCHAR2 DEFAULT NULL
,p_titles IN t_tab_abfrage DEFAULT t_tab_abfrage()
,p_close IN BOOLEAN DEFAULT TRUE
,p_first IN BOOLEAN DEFAULT TRUE
,p_encoding IN VARCHAR2 DEFAULT 'windows-1252'
);
/**
|| @Name getRelativeCellReference
|| @Description The reference in formulas regarding other cells has to be
|| given relative to the current cell. This function gives
|| the reference in the correct format R[x]C[y]
|| @param p_rowFrom - Row of current cell
|| @param p_colFrom - Column of current cell
|| @param p_rowTo - Row of target cell
|| @param p_colTo - Column of target cell
|| @return Reference to target cell, format R[x]C[y]
*/
FUNCTION getRelativeCellReference
(
p_rowFrom IN PLS_INTEGER
,p_colFrom IN PLS_INTEGER
,p_rowTo IN PLS_INTEGER
,p_colTo IN PLS_INTEGER
)
RETURN VARCHAR2;
/* Example
SET DEFINE OFF
DECLARE
v_fileHandle utl_file.FILE_TYPE;
tab_columns xml_spreadsheet.t_tab_columns;
tab_caption xml_spreadsheet.t_tab_caption;
tab_conditionalFormats xml_spreadsheet.t_tab_conditionalFormats;
r_border xml_spreadsheet.t_rec_border;
tab_border xml_spreadsheet.t_tab_border;
r_font xml_spreadsheet.t_rec_font;
r_background xml_spreadsheet.t_rec_background;
r_alignment xml_spreadsheet.t_rec_alignment;
r_customStyles xml_spreadsheet.t_rec_customStyles;
tab_customStyles xml_spreadsheet.t_tab_customStyles;
BEGIN
r_customStyles.id := 's50';
r_customStyles.type := 'Text';
r_alignment.vertical := 'Top';
r_alignment.horizontal := 'Right';
r_alignment.textRotate := 10;
r_customStyles.alignment := r_alignment;
r_border.bPosition := 'Left, Right';
r_border.bLineStyle := 'Double';
r_border.bWeight := 2;
r_border.bColor := xml_spreadsheet.c_blue;
tab_border(1) := r_border;
r_border.bPosition := 'Top, Bottom';
r_border.bLineStyle := 'Continuous';
r_border.bWeight := 2;
r_border.bColor := xml_spreadsheet.c_red;
tab_border(2) := r_border;
r_customStyles.border := tab_border;
r_font.fName := 'Times New Roman';
r_font.fSize := 14;
r_font.fColor := xml_spreadsheet.c_blue;
r_font.fBold := TRUE;
r_font.fUnderline := 's';
r_font.fPosition := 'Subscript';
r_customStyles.font := r_font;
r_background.bColor := xml_spreadsheet.c_brightYellow;
r_background.bPattern := xml_spreadsheet.c_bgpThinHorzStripe;
r_background.bPatternColor := xml_spreadsheet.c_white;
r_customStyles.background := r_background;
tab_customStyles(1) := r_customStyles;
tab_customStyles(2).id := 's51';
tab_customStyles(2).protection := TRUE;
tab_columns(1).cWidth := 91;
tab_columns(2).cWidth := 182;
tab_columns(3).cAutowidth := TRUE;
tab_columns(4).cAutowidth := TRUE;
-- Fill caption
tab_caption(1).title := 'caption left';
-- The first two columns get a combined caption
tab_caption(1).topTitle := 'topCaption left';
tab_caption(1).span := 2;
tab_caption(1).comment := 'My Comment with '||CHR(10)||'linebreak';
tab_caption(2).title := 'caption center';
-- the third column gets its own topCaption
tab_caption(3).title := 'caption right';
tab_caption(3).topTitle := 'topCaption right';
-- the forth column gets no topCaption
tab_caption(4).title := 'caption without top caption';
tab_caption(4).topTitle := '';
-- New File
v_fileHandle := xml_spreadsheet.createNewFile('DOC','Exceltest.xml',tab_customStyles);
-- open first worksheet
xml_spreadsheet.newWorksheet(
v_fileHandle
,'test_1'
,tab_caption
,tab_columns
);
-- Write a data row
-- Date with time
xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_dateTime);
-- simple text
xml_spreadsheet.writeData(v_fileHandle,'Sample Text');
-- Number with two digits
xml_spreadsheet.writeData(v_fileHandle,100/39,xml_spreadsheet.c_number2decimal);
-- New data row
xml_spreadsheet.newDatarow(v_fileHandle);
-- Date without time
xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_date);
-- Text with special characters
xml_spreadsheet.writeData(
v_fileHandle
,'another text < kjnh " ">'||CHR(38)
);
-- number as percent
xml_spreadsheet.writeData(v_fileHandle,39/100,xml_spreadsheet.c_prozent);
-- number as integer
xml_spreadsheet.writeData(v_fileHandle,100/39,xml_spreadsheet.c_integer);
-- Text with comment
xml_spreadsheet.writeData(v_fileHandle,'Comment',xml_spreadsheet.c_textDefault,'My Comment with '||CHR(10)||'linebreak');
-- New data row
xml_spreadsheet.newDatarow(v_fileHandle);
-- Formula
xml_spreadsheet.writeData(v_fileHandle,'Summand 1 bold underlined',xml_spreadsheet.c_textBoldUnderline);
xml_spreadsheet.writeData(v_fileHandle,-100/39,xml_spreadsheet.c_currency);
xml_spreadsheet.writeData(v_fileHandle,'Summand 2');
xml_spreadsheet.writeData(v_fileHandle,39/19,xml_spreadsheet.c_accounting);
xml_spreadsheet.newDatarow(v_fileHandle);
xml_spreadsheet.writeData(v_fileHandle,'Sum');
xml_spreadsheet.writeData(
v_fileHandle
,TO_NUMBER(NULL)
,xml_spreadsheet.c_number2decimal
,NULL
,'=SUM(R[-1]C,R[-1]C[2])'
);
--Conditional format for value in D4
tab_conditionalFormats(1).range := 'R4C4';
tab_conditionalFormats(1).qualifier := 'Greater';
tab_conditionalFormats(1).value1 := '0';
tab_conditionalFormats(1).value2 := NULL;
tab_conditionalFormats(1).formatColor := xml_spreadsheet.c_red;
tab_conditionalFormats(1).formatEmphasis := xml_spreadsheet.c_emphasisBoldItalic;
tab_conditionalFormats(1).formatMarkup := xml_spreadsheet.c_markupUnderlinedD;
tab_conditionalFormats(1).formatBgColor := xml_spreadsheet.c_silver;
-- close worksheet
xml_spreadsheet.closeWorksheet(
v_fileHandle
,2 -- fix first 2 rows in first worksheet when scrolling
,0 -- fix no columns in first worksheet when scrolling
,tab_caption
,TRUE
,tab_conditionalFormats
);
-- open second worksheet
xml_spreadsheet.newWorksheet(
v_fileHandle
,'new sheet'
,xml_spreadsheet.g_tab_caption
,xml_spreadsheet.g_tab_columns
,NULL
,NULL
,TRUE -- protect cells
);
xml_spreadsheet.writeData(v_fileHandle,'Data on second sheet');
xml_spreadsheet.writeData(v_fileHandle,'My format','s50');
xml_spreadsheet.writeData(v_fileHandle,'Protected','s51');
-- close worksheet
xml_spreadsheet.closeWorksheet(
v_fileHandle
,0 -- fix no rows in second worksheet when scrolling
,0 -- fix no columns in second worksheet when scrolling
);
-- Close file
xml_spreadsheet.closeFile(v_fileHandle);
END;
DECLARE
l_cursor number := dbms_sql.open_cursor;
BEGIN
dbms_sql.parse( l_cursor,
'SELECT username, user_id, created ' ||
'FROM all_users ' ||
'WHERE UPPER(username) = ''RVS_FE'' ',
dbms_sql.native );
xml_spreadsheet.abfrage(
p_cursor => l_cursor
,p_path => 'PFAD_REPORTS'
,p_sumColumns => xml_spreadsheet.t_tab_abfrage( 'N', 'Y', 'N')
,p_titles => xml_spreadsheet.t_tab_abfrage( 'Username', 'ID', 'Created')
);
END;
DECLARE
v_fileHandle utl_file.FILE_TYPE;
BEGIN
v_fileHandle := xml_spreadsheet.abfrage(
p_query => 'SELECT username, user_id, created ' ||
'FROM all_users ' ||
'WHERE UPPER(username) = ''RVS_TE'''
,p_path => 'PFAD_REPORTS'
,p_sheetname => 'sheet1'
,p_sumColumns => xml_spreadsheet.t_tab_abfrage( 'N', 'Y', 'N')
,p_titles => xml_spreadsheet.t_tab_abfrage( 'Username', 'ID', 'Created')
,p_close => FALSE
);
xml_spreadsheet.abfrage(
p_query => 'SELECT SYSDATE FROM dual'
,p_fileHandle => v_fileHandle
,p_sheetname => 'sheet2'
);
END;
*/
END xml_spreadsheet;
/
SHOW ERRORS
- Oracle PLSQL生成Excel文件(2)
- 【玩转Excel】Oracle PLSQL处理生成XLSX文件
- plsql 导入excel文件和txt文件
- 关于ORACLE PLSQL读文件
- plsql导入excel数据oracle表中
- plsql导入excel数据oracle表中
- Excel数据通过plsql 导入到Oracle
- Excel数据通过plsql导入到Oracle
- 用PlSql导出数据到Excel文件
- Oracle PLSQL 读文件与写文件
- JAVA生成EXCEL文件
- JAVA生成EXCEL文件
- JSP生成EXCEL文件
- php生成EXCEL文件
- Java生成EXCEL文件
- c#生成excel文件
- JAVA生成EXCEL文件
- jxls生成Excel文件
- jQuery源码分析-10事件处理-Event-事件绑定与删除-bind/unbind+live/die+delegat/unde
- Cacti显示端口名称不全的问题
- Linux下常用解压命令
- 3.3.4.6. NULL值操作
- DoDataExchange使用
- Oracle PLSQL生成Excel文件(2)
- 关于“Initializing the Oracle ASMLib driver: [FAILED]”的解决办法
- coredump产生的原因
- Micro2440 U-boot 编译实践 增加U-boot命令
- 高级系统架构师
- 在ios中保存图片到照片库
- 3.3.4.7. 模式匹配
- 对free和malloc自己的猜想和理解
- 几个可以让你暴跳的命令
