EBS Multi-sheet page Excel dynamic report development process

http://zhangzhongjie.iteye.com/blog/1779891

. preface
This article describes the Multi-sheet page Excel Report development method and the way to develop html,pdf such reports is roughly the same, the only difference is that the report output is an XML file, but this XML file supports Excel to open directly.
This approach has the following two points of obvious advantages:
(1) flexibility.
If the customer on the report display style requirements very strict, that way is very convenient, such as my customer is a German project, the report of the display style is very demanding, even strict to each column color, border lines, column width, font and so on. In this way, you can write Excel's display style code in the same way as CSS development, and then apply different styles to the output data.
(2) Data processing is very convenient.
When the report on the output of the data to do a large number of calculations, subtotals and other operations, if you write code directly in the calculation, classification, etc., once the data volume will cause the report to run very slow, it affects efficiency. In this way, simply take out the most basic data from the system, and then write a variety of calculations and subtotals and other formulas, and so on after the report ran out of the function of Excel automatically calculated. This can improve the performance of the report, on the other hand can make the entire report data is dynamic, in the report changes in the basic data, those through the calculations and subtotals and other operations of the data will be linked to change.

3.EXCEL e-Form
The report output file is an XML file that is very similar to our common file, except that the normal XML is an unformatted resource file that is a formatted resource file. Just like we open any Excel file, choose Save as "XML Spreadsheet 2003 (*.xml)" format. Then open the XML file with Notepad and you'll see the Excel table.

The following is a brief introduction to the XML format file for Excel tables.

3.1 Example
(1) When the Excel file is opened in XML format, you can see the following structure diagram:



(2) The structure diagram of a single sheet page is as follows:



3.2 Grammatical structure
1. Head
The first two lines are the header, and as with XML Publisher, we can add control of the encoding to the XML tag in line one.

XML code

1. <? XML version="1.0"?>
2. <? mso-application progid="Excel.Sheet"?>

2. Workbook
Workbook tags cover the entire spreadsheet, a bit like HTML tags in the HTML language. The worksheet tag is one of the sheet pages, a bit like the body tag in the HTML language, except that there can be multiple "body" in an XML spreadsheet.

3. "Head"
We can see that there is a lot of content between the start tag of workbook and the first worksheet, including DocumentProperties, Officedocumentsettings, ExcelWorkbook and styles four labels. This content is equivalent to the head tag in the HTML language, which controls the entire spreadsheet. The first three we can not pay attention to, where the ActiveSheet tag in ExcelWorkbook determines when the XML Spreadsheet opens, the first few sheet pages are displayed by default.

4.Styles
The most important thing in "Head" is the Styles tab. As the name implies, styles are the spreadsheet style. Style is a child tag of styles and is a single style. Styles contains all the styles needed for the entire spreadsheet.
Take a look at the style below.

XML code

2. <Style ss:id="normal">
4. <Borders>
6. <Border ss:position="Bottom" ss:linestyle="continuous" ss:weight="1"/>
8. <Border ss:position="left" ss:linestyle="continuous" ss:weight="1" />
10. <Border ss:position="right" ss:linestyle="continuous" ss:weight="1"/>
12. <Border ss:position="Top" ss:linestyle="continuous" ss:weight="1"/ >
14. </Borders>
16. <Font ss:fontname= "song body" x:charset="134" ss:size="One" ss:color="#000000" />
18. </Style>

XML Spreadsheet markup languages are similar to HTML languages. Each property of a label is separated by a space, and the attribute name is concatenated with the attribute value with a "=", and the attribute value needs to be enclosed in double quotation marks. The difference is that a "SS:" is added to the front of each property name.
The style tag must be set with only one property, which is the ID, used to distinguish between different style. The style can have a lot of sub-tags, such as controlling the border of the cell borders, font control, the interior of the control cell background, and the protection that controls the cell lock.

5.Worksheet
The name of the worksheet is a sheet page that must be present and not repeatable, or Excel cannot be opened.

Worksheet also has a useful property that is not present in the example above and is protected. Protected is set to "1" to make all cells of the sheet page non-editable. However, XML spreadsheets cannot support password protection, which means that users can actually invalidate them by clicking Unprotect Sheet Protection in Excel review. In addition, the style has cell control, and the style has a higher protection priority than the worksheet protected.

6.Table
Table is a sub-label of worksheet, which is the label of the specific display data. Structurally, I guess a worksheet should be able to have multiple table, but after many tests failed to find a way to put multiple table in a worksheet, temporarily think of a worksheet to store a table.

Table has two properties that are important, expandedcolumncount and ExpandedRowCount. This will make it possible for Excel to read the data in a few rows of columns as it opens, Expandedcolumncount and ExpandedRowCount can be larger than the number of columns and rows in the actual active area, but it must never be small, otherwise excel cannot open. For example, a four-row four-column transcript sheet 4*4 a valid area, if Expandedcolumncount changed to 3,excel cannot be opened, and Expandedcolumncount to 5,excel can open normally.

7. Worksheetoptions
From the end tag of the table to the end tag of the worksheet, there is also a label: Worksheetoptions. This tag can define many properties of the sheet page, such as when the sheet page is opened, where the mouse is positioned, and the print Area setting, and also the sheet page permission lock limit is defined here, note: When the sheet page chooses to lock, The user can not do any operation on the sheet, including changing the row height column width, adding rows or columns, deleting rows or columns, etc., but usually when the user chooses to lock the sheet page, they just want the content not to be modified, the rest is like changing the row Height column width, adding rows or columns, deleting rows or columns, etc. In this case, Excel also provides the appropriate method, is also set up here, like the following common permissions control, the need to add the tag here:

XML code

2. <allowformatcells/>: Whether to allow the formatting of cells to be adjusted
4. <allowsizecols/>: Whether to allow column widths to be changed
6. <allowsizerows/>: Whether to allow row heights to be changed
8. <allowinsertcols/>: Insert column is allowed
10. <allowinsertrows/>: Insert row is allowed
12. <allowinserthyperlinks/>: Allow hyperlinks to be inserted
14. <allowdeletecols/>: Whether to allow column deletion
16. <allowdeleterows/>: Delete rows are allowed
18. <allowsort/>: Whether to allow sorting
20. <allowfilter/>: Allow AutoFilter to be used
22. <allowusepivottables/>: Whether to allow the use of PivotChart

Note: Locking the sheet page here is a lock on the entire sheet page, but the customer needs to be locked sheet part of the page to be able to be modified, such as: The sample report in the first layer after the report ran out of the manual fill area. How should such a situation be achieved? It's really simple, just a little bit of modification in one place:
Add a line <protection ss:protected= "0" to the styles <Style> labels that need to be modified area/>, for example:

XML code

2. <Style ss:id="normal">
4. <Borders>
6. <Border ss:position="Bottom" ss:linestyle="continuous" ss:weight="1" />
8. <Border ss:position="left" ss:linestyle="continuous" ss:weight="1"/>
10. <Border ss:position="right" ss:linestyle="continuous" ss:weight="1" />
12. <Border ss:position="Top" ss:linestyle="continuous" ss:weight="1"/>
14. </Borders>
16. <Font ss:fontname= "song body" x:charset="134" ss:size="One" ss:color="#000000" />
18. <Protection ss:protected="0"/>
20. </Style>

8.row&cell
Row generally sets properties for rows, such as row height, adaptive row height, and so on, and cell sets the cell's properties, such as merging cells, cell styles, whether formulas are used, and so on.
The common format is as follows:
<row ss:autofitheight= "0" ss:height= ">"
<cell ss:styleid= "(the ID of the style here)" Ss:formula= "(here is the formula)" ><data ss:type= "(Here is the data type, usually string or number)" > ( The data shown here) </Data></Cell>
</Row>
The style is described earlier, and the cell determines which style to use by setting the value of the Styleid property.
The data tag to indicate what type is in the cell, this is important, because only the cell value of type number here can participate in subsequent calculations, or even if the value in the cell looks as if it is a digit, but if its type is not numbered, cannot be calculated with other values.
As you can see here, you may notice where to set the width of the column? You may think that in the <Cell> tag Riga ss:width tag, in fact, this is not right, why? Because the width of the whole column of Excel is as wide as it is, unlike the width of the different cells in the same column in the HTML table, it is not possible to set the width of the cell in the <Cell>,<table> label under Excel,<row> The label defines the width of each column individually, as shown in the next sheet page:

XML code

2. <Worksheet ss:name="Sheet1">
4. <Table ss:expandedcolumncount="3" ss:expandedrowcount= "2" x:fullcolumns="1"
6. x:fullrows= "1" ss:defaultcolumnwidth= "ss:defaultrowheight=" " 13.5">
8. <Column ss:autofitwidth="0" ss:width= "74.25"/>
10. <Row ss:autofitheight="0" ss:height= "+" >
12. <Cell><data ss:type="number">134</data>< /Cell>
14. </Row>
16. </Table>
18. <worksheetoptions xmlns="Urn:schemas-microsoft-com:office:excel">
20. ......
22. </worksheetoptions>
24. </Worksheet>

4. Development steps
Having learned the syntax of an Excel Tabular XML Spreadsheet, we can develop Excel reports directly in the PL/SQL package, just as we do with HTML reports.


4.1 Making Excel templates
We can first use Excel to make a template of the report we want, and then save as an XML file, get a lot of out-of-the-box code, especially the definition of report style. This can greatly reduce the workload for development. And any demand that the customer puts forward can be developed as long as they can show it in Excel.

4.2 Writing public Programs
After we get the code generated by the Excel template, we can write programs to perform the output of the report. An attachment (CUX_EXCEL_REPORT_TEMPLATE.PCK) is an example of a complete report template package.

The following are the main procedure for public packages.

1.PROCEDURE Output_xml_header
The function of this procedure is to output the header of the XML, which can be copied directly from Excel saved as the resulting XML file, that is, from the beginning of the <?xml version= "1.0"?> always copy to the end of the Styles tab. So the definition of the report header is complete. It is important to note that the Styleid generated by Excel are all sxx in the form of a poor readability. If necessary, you can name the style's ID as an easy-to-read title, such as Gray, Green, title, and so on.
2. PROCEDURE output_xml_ending
The function of this procedure "</Workbook>" end tag.
3.

SQL code

1. procedure output_xml_sheet_header (p_sheet_title  in varchar2,  
2.                                     P_COL          in number,  
3.                                     P_ROW          in number)   

The purpose of this procedure is to define a sheet page, where P_sheet_title is the name of the sheet page. P_col is the maximum number of columns in the valid data range for this sheet page. P_row is the maximum number of rows in the valid data range for this sheet page. Copy the definition Code of any sheet page in the XML template code and replace the worksheet Name property with the corresponding parameter variable, the Expandedcolumncount and ExpandedRowCount properties of the table.
4. PROCEDURE output_xml_sheet_ending
This procedure function outputs </Table> ends the label and defines the sheet page for mouse positioning, page locking, and so on, and finally ends the sheet page. That is, from the end tag of the table to the end tag of the worksheet in the Copy template code.
5.

SQL code

1. procedure output_xml_one_row (p_data  < Span class= "Op" >in g_string_array,  
2.                                p_type  in g_string_array,  
3.                               p_style IN  g_string_array)   

The purpose of this procedure is to output a row of data within a sheet page, that is, the contents of a row tag.
Where P_data holds data for each cell, P_type stores the data type for each cell, P_style stores the Styleid for each cell.
For details on how to enter a row of data, see the definition of the procedure in CUX_EXCEL_REPORT_TEMPLATE.PCK.

4.3 Main program for writing reports
With public procedures, the development of Excle reports is basically consistent with the logic of developing HTML reports. You only need to separate the data, data type and style ID into three arrays respectively, and then pass three arrays to the public package to complete the output, please see the process_request process in the CUX_EXCEL_REPORT_TEMPLATE.PCK template for details.

4.4 Defining a report
As with other reports: Define executables, concurrency programs, parameters, and so on.
Attention:
1. Select XML for the output type of the concurrent program.
4. Frequently Asked Questions

4.1 Excel Formulas
When Excel is converted to XML, the formulas in the two files are quite different.
In the formula for the file in Excel, you can use a string such as "B4", "A5", and in the XML file to be converted to "r[n]c[m".
It is important to note that the N and M here are not the row and column ordinal of the cell, but rather the line offset relative to the current cell position. When the offset is 0 o'clock, you can write "R" or "C" directly. Where the right and down offsets are positive, and the left and up offsets are negative. Therefore, "RC" represents the current cell, "R[1]c" represents the cell below the current cell, "rc[-1" represents the cell to the left of the current cell, and so on.

4.2 Excel Restrictions
(1) The name of the sheet page cannot be duplicated or too long. Otherwise the report will run without error, but it will be error when opened, this is the limit of Excel.
(2) The number of sheet pages can not be more than 5,000, more than the words Excel cannot open (this limit because the data is not enough, I did not test.) Just on the internet to see that there is such a saying, so put it out in the hearts of everyone there are numbers on the line. ）。

PS: template code and documentation in the attachment.

EBS Multi-sheet page Excel dynamic report development process