1) создаем батник select.bat с текстом :
SQLPLUS.EXE -s user/
pass@namebase @select.sql > result.xls
2)создаем в текущем каталоге файл select.sql :
set heading off
set pagesize 0
set sqlnumber off
set feedback off
set verify off
set serveroutput on size 1000000
set linesize 250
set timing off
--
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;
--
dbms_output.put_line('
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font x:CharSet="204"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="List01">
');
Dbms_output.put_line(‘<Table>’);
Dbms_output.put_line(‘ <Row>’);
For C1 in (
Select distinct id_urok
From test
Order by id_urok
)
Loop
Dbms_output.put_line(‘ <Cell ss:MergeAcross="1"><Data ss:Type="Number">’||id_student\id_urok ||’</Data></Cell>’);
Dbms_output.put_line(‘ <Cell ss:Index="3"><Data ss:Type="Number">’||c1.id_urok||’</Data></Cell>’);
End loop;
Dbms_output.put_line(‘ </Row>’);
Dbms_output.put_line(‘</Table>’);
Dbms_output.put_line(‘<Table>’);
For X in (
Select distinct id_student
From test
Order id_student
)
Loop
Dbms_output.put_line(‘ <Row>’);
Dbms_output.put_line(‘ <Cell><Data ss:Type="string">’||x.id_urok||’</Data></Cell>’);
For Y in (
Select id_student, id_urok, ball
From test
Order id_student, id_urok
)
Loop
if x.id_student==Y.id_student then begin
Dbms_output.put_line(‘ <Cell ss:Index="’||to_number(Y.id_urok+2)||’"><Data ss:Type="Number">’||Y.ball||’</Data></Cell>’);
end;
End loop;
Dbms_output.put_line(‘ </Row>’);
End loop;
Dbms_output.put_line(‘</Table>’);
dbms_output.put_line('
</Worksheet>
</Workbook>
');
3)запускаем батник
4) возможные проблемки с выполнением скрипта решаются перепрописанием одинарных кавычек [всех :)) ]
sanyurik@mail.ru
