Shulou(Shulou.com)06/01 Report--

Preface

The development method of multi-Sheet page EXCEL report described in this paper is roughly the same as the method of developing reports such as HTML,PDF, except that the report output is a XML file, but this XML file supports EXCEL to open directly.

This approach has the following two obvious advantages:

(1) flexibility.

If the customer is very strict with the display style of the report, then it is very convenient to use this way, for example, the client of my project is a German, and the display style of the report is extremely strict, even to the color of each column. Border, column width, font and so on. In this way, you can write the display style code of EXCEL in the same way as CSS development, and then apply different styles to output data.

(2) data processing is very convenient.

When the report has to do a lot of calculation, classified summary and other operations on the output data, if the calculation and classified summary are carried out directly when writing the code, once the amount of data is large, it will cause the report to run very slowly and affect the efficiency. With this method, you only need to take the most basic data from the system, then write all kinds of formulas for calculation and classification, etc., and use the function of EXCEL to calculate automatically after the report comes out. On the one hand, this can improve the performance of the report, on the other hand, it can make the data of the whole report dynamic. After the basic data is modified in the report, the data obtained through calculation and classification will also change.

3.EXCEL spreadsheet

The report output file is a XML file, and the XML file is very similar to our common file, except that the ordinary XML file is an unformatted resource file, while the XML file is a formatted resource file. Just like we open any excel file and choose the format to save as XML spreadsheet 2003 (* .xml). Then open the xml file with notepad and you can see the true face of the EXCEL table.

The following is a brief introduction to the XML format file of the EXCEL table.

3.1 exampl

(1) when you open the EXCEL file in XML format, you can see the following structure diagram:

(2) the structure of a single Sheet page is as follows:

3.2 grammatical structure

1. Title

The first two lines are headers, and like XML Publisher, we can add control over the coding in the xml tag of the first line.

2. Workbook

The Workbook tag wraps the entire spreadsheet, somewhat similar to the html tag in the HTML language. The Worksheet tag wraps one of the sheet pages, which is similar to the body tag in the HTML language, except that there can be multiple "body" in the XML spreadsheet.

3. "Head"

We can see that there is a lot of content from the beginning tag of Workbook to the first Worksheet, including DocumentProperties, OfficeDocumentSettings, ExcelWorkbook and Styles tags. These contents are equivalent to head tags in the HTML language, controlling the entire spreadsheet. We can ignore the first three, in which the ActiveSheet tag in ExcelWorkbook determines which sheet pages are displayed by default when the XML spreadsheet is opened.

4.Styles

The most important thing in "Head" is the Styles tag. As the name implies, Styles is the style of a spreadsheet. 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 following Style.

The XML spreadsheet markup language is similar to the HTML language. The attributes of the tag are separated by spaces, the attribute names and values are connected by "=", and the attribute values need to be enclosed in double quotation marks. The difference is that each attribute name is preceded by a "ss:".

The only attribute that the Style tag must set is ID, which is used to distinguish between different Style. Style can have many child tags, such as Border that controls cell borders, Font that controls fonts, Interior that controls cell background, and Protection that controls cell locking.

5.Worksheet

The Name of Worksheet is the name displayed on the sheet page, which must be present and cannot be repeated, or excel cannot be opened.

Another useful attribute of Worksheet, which is not presented in the above example, is Protected. Setting Protected to "1" makes all cells of this sheet page uneditable. However, XML spreadsheets do not support password protection, that is, users can actually invalidate it by clicking unprotect worksheet in Excel Review. In addition, there is cell control in Style, and the Protection priority of Style is higher than that of Worksheet.

6.Table

Table is a child tag of Worksheet and is a tag that displays specific data. Structurally, I guess there can be more than one Table in a Worksheet, but after many tests, I can't find a way to put more than one Table in a Worksheet. For the time being, I think that a Worksheet can store at most one Table.

Table has two important attributes, ExpandedColumnCount and ExpandedRowCount. This will determine that when Excel opens, it reads the data in the file according to the criteria of several rows and columns. ExpandedColumnCount and ExpandedRowCount can be greater than the number of columns and rows in the actual valid area, but must not be small, otherwise excel cannot be opened. For example, there are 4 valid areas in a four-line and four-column report card sheet page. If ExpandedColumnCount is changed to 3PowerExcel, it cannot be opened, while ExpandedColumnCount can be opened normally if it is changed to 5PowerExcel.

7. WorksheetOptions

Between the closing tag of Table and the closing tag of Worksheet, there is another tag: WorksheetOptions. This tag can define many properties of the Sheet page, such as where the mouse is positioned when the Sheet page is opened, and the print area setting And the Sheet page permission locking limit is also defined here. Note here: when the Sheet page is selected to lock, the user cannot do anything about the Sheet, including changing the row height and column width, adding rows or columns, deleting rows or columns, etc., but usually when the user chooses to lock the Sheet page, he only wants the content not to be modified, the rest like changing the row height and column width, adding rows or columns, deleting rows or columns, and so on. These permissions do not need to be locked. In this case, EXCEL also provides a corresponding method, which is also set here. Just like the following common permission controls, you can add the tag here if you need that one:

Whether cell formatting is allowed: column width is allowed to be changed; row height is allowed to be changed; column insertion is allowed; row insertion is allowed; hyperlink is allowed; column deletion is allowed: row deletion is allowed Whether sorting is allowed: whether automatic filtering is allowed: whether the PivotChart is allowed

Note: locking the Sheet page here is locking the entire Sheet page, but the customer requires that part of the locked Sheet page be modified, such as the area in the first layer of the sample report that is manually filled in after the report runs out. How should this be achieved? In fact, it is very simple. You only need to make some changes in one place:

Add a line to the style label of the area to be modified, for example:

8.Row&Cell

Row generally sets the properties of the row, such as row height, whether the row height is adaptive, and so on. Cell sets the properties of the cell, such as merging cells, cell styles, whether to use formulas, and so on.

Common formats are as follows:

(here is the data displayed)

Style has been introduced earlier, and Cell determines which style to use by setting the property value of StyleID.

Data tag to indicate what type of data is in the cell, which is important, because only the cell value of type Number here can participate in the subsequent calculation, otherwise even if the value in the cell looks like a number, its type, if not Number, cannot be calculated with other values.

As you may notice here, where do you set the width of the column? You may think of adding ss:Width tags to the tags. In fact, this is wrong. Why? Because the width of the entire column of EXCEL is the same width, unlike the width of different cells in the same column in the HTML table, you cannot set the width of the cell in EXCEL. The width of each column cannot be defined separately under the tag, as shown in the following sheet page:

134.

4. Development steps

After understanding the syntax of EXCEL tables XML spreadsheets, we can develop EXCEL reports directly in PL/SQL Package as we do with HTML reports. 4.1 make Excel template

We can first use Excel to make the template of the report we want, and then save it as a XML file to get a lot of ready-made code, especially the definition of report style. This can greatly reduce the workload of the development. And any requirements put forward by customers can be developed as long as they can be expressed in EXCEL.

4.2 write public programs

Once we have the code generated by the Excel template, we can write a program to perform the output of the report. Attachment (CUX_EXCEL_REPORT_TEMPLATE.pck) is an example of a complete report template package.

The following is the main Procedure of the public package.

1.PROCEDURE output_xml_header

The purpose of this PROCEDURE is to output the header of XML, which can be obtained directly from COPY in the XML file saved by EXCEL, that is, from the beginning to the end of the Styles tag. This completes the definition of the report header. It should be noted that the StyleID generated by Excel is all in sXX format and is not readable. If necessary, you can name the ID of Style with your own easy-to-read names, such as gray, green, title, and so on.

2. PROCEDURE output_xml_ending

The role of this PROCEDURE is the "" ending tag.

3.

PROCEDURE OUTPUT_XML_SHEET_HEADER (P_SHEET_TITLE IN VARCHAR2, P_COL IN NUMBER, 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 for the valid data region of this Sheet page. P_row is the maximum number of rows of valid data regions for this Sheet page. Copy the definition code of any Sheet page in the XML template code and replace the Name property of Worksheet and the ExpandedColumnCount and ExpandedRowCount properties of Table with the corresponding parameter variables.

4. PROCEDURE output_xml_sheet_ending

The function of this PROCEDURE outputs the end tag and defines the mouse positioning, page locking and other information of the sheet page, and finally ends the Sheet page. That is, from the end tag of Table to the end tag of Worksheet in the COPY template code.

5.

PROCEDURE OUTPUT_XML_ONE_ROW (P_DATA ING_ STRING_ARRAY, P_TYPE ING_ STRING_ARRAY, P_STYLE ING_ 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.

P_data stores the data of each cell, p_type stores the data type corresponding to each cell, and p_style stores the StyleID corresponding to each cell.

For more information on how to enter a row of data, see the definition of the PROCEDURE in CUX_EXCEL_REPORT_TEMPLATE.pck.

4.3 the main program for writing reports

With the common program, the logic of developing EXCLE reports is basically the same as that of developing HTML reports. You only need to store the data, data type and style ID in three arrays respectively, and then transfer the three arrays to the common package to complete the output. For more information, please see the process_request process in the CUX_EXCEL_REPORT_TEMPLATE.pck template.

4.4 define a report

Like other reports: define executables, concurrent programs, parameters, and so on.

Note:

1. The output type of the concurrent program is XML.

4. common problem

4.1Formula Excel

After EXCEL is converted to XML, there is a big difference in how to write the formula in the two files.

Strings such as "B4" and "A5" can be used directly in the formula in the EXCEL file, while they can be converted to the form "R [n] C [m]" in the XML file.

In particular, it is important to note that the n and m here are not the row and column numbers of the cell, but the row offset relative to the current cell position. When the offset is 0, you can write "R" or "C" directly. Where the right and downward offsets are positive, and the left and upward 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 naming of Sheet pages cannot be repeated or too long. Otherwise, the report will not report an error when it runs, but it will report an error when it is opened, which is the restriction of excel.

(2) the number of Sheet pages cannot exceed 5000. If the number exceeds, the Excel cannot be opened. (this limit is not tested because there is not enough data. It's just that I saw such a saying on the Internet, so I just posted it in everyone's mind. ).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.