多sheet页报表

最新推荐文章于 2023-02-21 15:57:54 发布

粉红色的猪猪

最新推荐文章于 2023-02-21 15:57:54 发布

阅读量492

点赞数

分类专栏： Report 文章标签： p2p 蓝桥杯 linq

本文链接：https://blog.csdn.net/chaojiitxiazai/article/details/122128365

版权

Report 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

sheet逻辑伪代码

DECLARE
l_worksheet_per_number NUMBER;
l_worksheet_start_number NUMBER;
l_worksheet_end_number NUMBER;
l_print_number NUMBER;
BEGIN
l_worksheet_per_number := 12;
l_worksheet_start_number := 0;
l_worksheet_end_number := 0;
l_print_number := 0;

--sheet页表头
IF MOD(l_print_number, l_worksheet_per_number) = 0 THEN
dbms_output.put_line('sheet页表头');
l_worksheet_start_number := l_worksheet_start_number + 1;
END IF;

FOR rec IN (SELECT pap.employee_number, pap.last_name
FROM per_all_people_f pap
WHERE rownum <= 26) LOOP
--多sheet页开头
IF MOD(l_print_number, l_worksheet_per_number) = 0 THEN
IF trunc(l_print_number / l_worksheet_per_number) > 0 THEN
dbms_output.put_line('sheet页表头');
l_worksheet_start_number := l_worksheet_start_number + 1;
END IF;
END IF;
l_print_number := l_print_number + 1;
--输出sheet页内容
dbms_output.put_line(l_print_number || ' employee_number = ' ||
rec.employee_number);
--多sheet页结束
IF MOD(l_print_number, l_worksheet_per_number) = 0 THEN
IF trunc(l_print_number / l_worksheet_per_number) > 0 THEN
dbms_output.put_line('sheet页结束');
l_worksheet_end_number := l_worksheet_end_number + 1;
END IF;
END IF;
END LOOP;

l_sheet_start := REPLACE(l_sheet_start,
'#sheet_title#',
l_sheet_title || '_' || l_worksheet_start_number);

--sheet页结束
IF l_worksheet_start_number > l_worksheet_end_number THEN
dbms_output.put_line('sheet页结束');
END IF;

END;
-------------------------包头------------------------

CREATE OR REPLACE PACKAGE cux_excel_report_pkg IS

-- Author : brad.teng
-- Created : 2021/12/24 15:12:28
-- Purpose :

PROCEDURE main(errout VARCHAR2,
retcode VARCHAR2);

END cux_excel_report_pkg;

-------------------------包体------------------------

CREATE OR REPLACE PACKAGE BODY cux_excel_report_pkg IS

PROCEDURE main(errout VARCHAR2,
retcode VARCHAR2) AS
l_output_chars VARCHAR2(4000);
l_workbook_start VARCHAR2(4000);
l_workbook_end VARCHAR2(4000);
l_styles_chars VARCHAR2(4000);
l_sheet_start VARCHAR2(4000);
l_sheet_end VARCHAR2(4000);
l_sheet_title VARCHAR2(240);
l_worksheet_per_number NUMBER := 25;
l_worksheet_start_number NUMBER;
l_worksheet_end_number NUMBER;
l_print_number NUMBER;

CURSOR c IS
SELECT ph.segment1 po_number,
pv.vendor_name,
to_char(ph.approved_date,'YYYY-MM-DD HH24:MI:SS') approved_date,
pl.quantity,
pl.unit_price
FROM po_headers_all ph, po_lines_all pl, po_vendors pv
WHERE ph.po_header_id = pl.po_header_id
AND ph.org_id = pl.org_id
AND ph.vendor_id = pv.vendor_id
AND ph.type_lookup_code = 'BLANKET'
AND ph.authorization_status = 'APPROVED'
AND nvl(ph.cancel_flag, 'N') = 'N'
AND ROWNUM <= 104;

l_vendor_name VARCHAR2(512);

BEGIN
--workbook开头
l_workbook_start := '<?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<LastAuthor>brad</LastAuthor>
<Created>2013-12-24T07:41:16Z</Created>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6795</WindowHeight>
<WindowWidth>11340</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>15</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>';

fnd_file.put_line(fnd_file.output, l_workbook_start);
dbms_output.put_line(l_workbook_start);

l_styles_chars := ' <Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s16" ss:Name="千位分隔">
<NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ "/>
</Style>
<Style ss:ID="s62">
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s63">
<NumberFormat/>
</Style>
<Style ss:ID="s66">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s67">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"
ss:Bold="1"/>
<NumberFormat ss:Format="@"/>
</Style>';

fnd_file.put_line(fnd_file.output, l_styles_chars);
dbms_output.put_line(l_styles_chars);

l_styles_chars := '<Style ss:ID="s68">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"
ss:Bold="1"/>
<NumberFormat/>
</Style>
<Style ss:ID="s69">
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s71" ss:Parent="s16">
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
</Style>
</Styles>';

fnd_file.put_line(fnd_file.output, l_styles_chars);
dbms_output.put_line(l_styles_chars);

l_sheet_title := '采购订单';

l_sheet_start := '<Worksheet ss:Name="#sheet_title#">
<Table ss:ExpandedColumnCount="256" ss:ExpandedRowCount="65535" x:FullColumns="1"
x:FullRows="1">
<Column ss:StyleID="s62" ss:Width="145.5"/>
<Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="72"/>
<Column ss:StyleID="s62" ss:Width="115.5"/>
<Column ss:StyleID="s63" ss:Width="94.5"/>
<Column ss:StyleID="s63" ss:Width="84.75"/>
<Column ss:StyleID="s63" ss:Width="94.5"/>
<Column ss:StyleID="s63" ss:Width="84.75"/>
<Column ss:StyleID="s62" ss:Width="28.5"/>
<Column ss:StyleID="s62" ss:Width="302.25"/>
<Column ss:StyleID="s62" ss:AutoFitWidth="0"/>
<Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="52.5"/>
<Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="54" ss:Span="1"/>
<Column ss:Index="14" ss:StyleID="s62" ss:AutoFitWidth="0"/>
<Column ss:Index="16" ss:Width="241.5"/>
<Column ss:Width="251.25"/>
<Column ss:AutoFitWidth="0" ss:Width="72"/>
<Row ss:StyleID="s66">
<Cell ss:StyleID="s67"><Data ss:Type="String">Po Number</Data></Cell>
<Cell ss:StyleID="s67"><Data ss:Type="String">Vendor Name</Data></Cell>
<Cell ss:StyleID="s67"><Data ss:Type="String">Approved Date</Data></Cell>
<Cell ss:StyleID="s67"><Data ss:Type="String">Quantity</Data></Cell>
<Cell ss:StyleID="s68"><Data ss:Type="String">Unit Price</Data></Cell>
</Row>';

l_sheet_end := '</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<PageMargins x:Left="0.8" x:Right="0.8"/>
</PageSetup>
<DisplayPageBreak/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
<NumberofCopies>0</NumberofCopies>
</Print>
<Selected/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>2</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>';

l_worksheet_start_number := 0;
l_worksheet_end_number := 0;
l_print_number := 0;

--sheet页表头
IF MOD(l_print_number, l_worksheet_per_number) = 0 THEN
l_worksheet_start_number := l_worksheet_start_number + 1;
l_output_chars := REPLACE(l_sheet_start,
'#sheet_title#',
l_sheet_title || '_' ||
l_worksheet_start_number);
fnd_file.put_line(fnd_file.output, l_output_chars);
dbms_output.put_line(l_output_chars);
END IF;

FOR r_c IN c LOOP

--多sheet页开头
IF MOD(l_print_number, l_worksheet_per_number) = 0 THEN
IF trunc(l_print_number / l_worksheet_per_number) > 0 THEN
l_worksheet_start_number := l_worksheet_start_number + 1;
l_output_chars := REPLACE(l_sheet_start,
'#sheet_title#',
l_sheet_title || '_' ||
l_worksheet_start_number);
fnd_file.put_line(fnd_file.output, l_output_chars);
dbms_output.put_line(l_output_chars);
END IF;
END IF;
l_print_number := l_print_number + 1;

--输出sheet页内容
l_vendor_name := REPLACE(r_c.vendor_name, '&', '&');
l_vendor_name := REPLACE(l_vendor_name, '<', '<');
l_vendor_name := REPLACE(l_vendor_name, '>', '>');
l_vendor_name := REPLACE(l_vendor_name, '''', ''');
l_vendor_name := REPLACE(l_vendor_name, '''''''', '"');

l_output_chars := '<Row>
<Cell ss:StyleID="s69"><Data ss:Type="String">' || r_c.po_number || '</Data></Cell>';
l_output_chars := l_output_chars || '<Cell ss:StyleID="s69"><Data ss:Type="String">' || l_vendor_name || '</Data></Cell>';
l_output_chars := l_output_chars || '<Cell ss:StyleID="s69"><ss:Data ss:Type="String"
xmlns="http://www.w3.org/TR/REC-html40"><Font html:Color="#000000">' || r_c.approved_date || '</Font></ss:Data></Cell>';
l_output_chars := l_output_chars || '<Cell ss:StyleID="s71"><Data ss:Type="Number">' || r_c.quantity || '</Data></Cell>';
l_output_chars := l_output_chars || '<Cell ss:StyleID="s71"><Data ss:Type="Number">' || r_c.unit_price || '</Data></Cell>' || '</Row>';

fnd_file.put_line(fnd_file.output, l_output_chars);
dbms_output.put_line(l_output_chars);
l_output_chars := '';

--多sheet页结束
IF MOD(l_print_number, l_worksheet_per_number) = 0 THEN
IF trunc(l_print_number / l_worksheet_per_number) > 0 THEN
fnd_file.put_line(fnd_file.output, l_sheet_end);
dbms_output.put_line(l_sheet_end);
l_worksheet_end_number := l_worksheet_end_number + 1;
END IF;
END IF;

END LOOP;
--sheet页结束
IF l_worksheet_start_number > l_worksheet_end_number THEN
fnd_file.put_line(fnd_file.output, l_sheet_end);
dbms_output.put_line(l_sheet_end);
END IF;

--workbook结束
l_workbook_end := '</Workbook>';
fnd_file.put_line(fnd_file.output, l_workbook_end);
dbms_output.put_line(l_workbook_end);

END;

END cux_excel_report_pkg;

粉红色的猪猪

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
多sheet页报表

sheet逻辑伪代码DECLARE l_worksheet_per_number NUMBER; l_worksheet_start_number NUMBER; l_worksheet_end_number NUMBER; l_print_number NUMBER;BEGIN l_worksheet_per_number := 12; l_worksheet_start_number := 0; l_worksheet_end_numbe...
复制链接

扫一扫