I have code to create Microsoft Excel using XML in Excel.
Used in EBS application to generate Microsoft Excel output format.
CREATE OR REPLACE PROCEDURE XXC_TEST(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
gldate_from in date,
gldate_to in date)
IS
Crlf Char (2) Default Chr (13) || Chr (10);
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0"?>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?mso-application progid="Excel.Sheet"?>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' xmlns:o="urn:schemas-microsoft-com:office:office"'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' xmlns:x="urn:schemas-microsoft-com:office:excel"'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' xmlns:html="http://www.w3.org/TR/REC-html40">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </DocumentProperties>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <ProtectStructure>False</ProtectStructure>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <ProtectWindows>False</ProtectWindows>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </ExcelWorkbook>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Styles>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Style ss:ID="s22">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Font ss:Size="8"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <NumberFormat ss:Format="#,##0.00_);[Red]\(#,##0.00\)"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </Style>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Style ss:ID="s24">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Font ss:Size="8"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <NumberFormat ss:Format="#,##0;[Red]\(#,##0\)"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </Style>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Style ss:ID="s25">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Font ss:Size="8"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </Style>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Style ss:ID="s26">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Font ss:Size="8"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </Style>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Style ss:ID="date">'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Font ss:Size="8"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NumberFormat ss:Format="[ENG][$-409]d\-mmm\-yy;@"/>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</Style>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </Styles>'||crlf);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
'<Row><Cell ss:StyleID="s26"><Data ss:Type="String">Period</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Effective Date</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Journal Line No</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Debit</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Credit</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">GL Account</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Check Amount</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Debit2</Data></Cell>'|| ---> this one
'<Cell ss:StyleID="s26"><Data ss:Type="String">Bank Account</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Check Number</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Check Date</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Vendor Name</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Cleared Date</Data></Cell>'||
'<Cell ss:StyleID="s26"><Data ss:Type="String">Void Date</Data></Cell></Row>');
for c in (
Select '<Row><Cell ss:StyleID="s25"><Data ss:Type="String">'||period_name||'</Data></Cell>'||
'<Cell ss:StyleID="date"><Data ss:Type="DateTime">'||TO_CHAR(EFFECTIVE_DATE,'yyyy-mm-dd')||'</Data></Cell>'||
'<Cell ss:StyleID="s22"><Data ss:Type="Number">'||JE_LINE_NUM||'</Data></Cell>'||
'<Cell ss:StyleID="s22"><Data ss:Type="Number">'||entered_dr||'</Data></Cell>'||
'<Cell ss:StyleID="s22"><Data ss:Type="Number">'||entered_cr||'</Data></Cell>'||
'<Cell ss:StyleID="s25"><Data ss:Type="String">'||acct||'</Data></Cell>'||
'<Cell ss:StyleID="s22"><Data ss:Type="Number">'||amount||'</Data></Cell>'||
'<Cell ss:StyleID="s22"><Data ss:Type="Number">'||case when nvl(entered_dr,0)>0 then amount else 0 end||'</Data></Cell>'|| ---> This one
'<Cell ss:StyleID="s25"><Data ss:Type="String">'||bank_account_name||'</Data></Cell>'||
'<Cell ss:StyleID="s24"><Data ss:Type="Number">'||check_number||'</Data></Cell>'||
'<Cell ss:StyleID="date"><Data ss:Type="DateTime">'||to_char(check_date,'yyyy-mm-dd')||'</Data></Cell>'||
'<Cell ss:StyleID="s25"><Data ss:Type="String">'||xmlreplace(vendor_name)||'</Data></Cell>'||
decode(cleared_date,null,null,'<Cell ss:StyleID="date"><Data ss:Type="DateTime">'||to_char(cleared_date,'yyyy-mm-dd')||'</Data></Cell>')||
decode(void_date,null,'</Row>','<Cell ss:StyleID="date" ss:Index="12"><Data ss:Type="DateTime">'||to_char(void_date,'yyyy-mm-dd')||'</Data></Cell></Row>') line
from (SELECT y.je_category,
y.je_source,
y.period_name,
y.name,
y.description,
A.USER_NAME,
x.effective_date,
x.entered_dr,
x.entered_cr,
x.description line_desc,
x.je_header_id,
x.je_line_num,
x.attribute1,
z.segment1
||'-'
||z.segment2
||'-'
||z.segment3
||'-'
||z.segment4
||'-'
||z.segment5
||'-'
||Z.SEGMENT6 ACCT,
XLT.SOURCE_ID_INT_1
FROM apps.gl_je_lines x,
apps.gl_je_headers y,
APPS.GL_CODE_COMBINATIONS Z,
APPS.FND_USER A,
APPS.GL_IMPORT_REFERENCES GLI,
XLA.XLA_TRANSACTION_ENTITIES xlt
WHERE x.code_combination_id=z.code_combination_id
AND y.je_header_id =x.je_header_id
AND segment5 ='102005'
AND x.effective_date BETWEEN gldate_from and gldate_to
AND Y.CREATED_BY =A.USER_ID
AND JE_SOURCE ='Payables'
AND X.JE_HEADER_ID =GLI.JE_HEADER_ID
AND X.JE_LINE_NUM =GLI.JE_LINE_NUM
AND GLI.REFERENCE_5 =XLT.ENTITY_ID
),
APPS.ap_checks_all aca
WHERE SOURCE_ID_INT_1=CHECK_ID
ORDER BY EFFECTIVE_DATE, ACA.CHECK_NUMBER,je_line_num
Adding Debit2 to the existing query and not working properly...i mean while the Microsoft Excel file it was giving error.