CN104572048A

CN104572048A - XML-based EXCEL report generation method in JSP environment

Info

Publication number: CN104572048A
Application number: CN201310489798.6A
Authority: CN
Inventors: 不公告发明人
Original assignee: ZHENJIANG DINGTUO TECHNOLOGY INFORMATION Co Ltd
Current assignee: ZHENJIANG DINGTUO TECHNOLOGY INFORMATION Co Ltd
Priority date: 2013-10-18
Filing date: 2013-10-18
Publication date: 2015-04-29

Abstract

The invention relates to an XML-based EXCEL report generation method in a JSP environment. The method is an XML-based EXCEL report implementation manner under the JSP language environment. The report implementation manner utilizes the feature that XML data and format are separated and the strong and rich format control and data processing ability of EXCEL. By the method, programming can be simplified, and development difficulty and cost are lowered. Compared with other EXCEL report generation methods, the method has the advantage that the flexibility and controllability of the method are increased greatly.

Description

Based on the EXCEL report form generation method of XML in a kind of JSP environment

Technical field

Being existing based on stopping of the form dynamically generated in JSP linguistic based on the EXCEL report form generation method of XML in a kind of JSP environment, is the applying portion in computer realm.

Background technology

Along with the acceleration of the Process of Enterprise Informalization, Manufacturing Executive System MES (Manufacturing Execution System) obtains apply more and more widely with enterprise resource manage system (Enterprise Resource Planning).In the middle of these systematic difference, report printing function is wherein one of requisite function, because form is as a kind of important information carrier, and the irreplaceable effect of the person of plaing in the middle of the information management, statistical study of enterprise.And in the application system of any one enterprise, Problem of Report also one of them difficult point often, especially in the middle of the system of systems constructed based on JSP language.Such as in the middle of the Information-based Item performance history that this bears certain steel mill, manufacturer just needs to realize so a kind of reporting system, and concrete condition is:

1) the mechanical property raw data of steel is positioned in the middle of SQLSERVER 2000 database, will wherein some statistical conversion time, need to require to carry out computing to raw data wherein according to different analytic statisticss, then by the data genaration initiating electron data sheet after computing, can be with function representation: Yij=F (Xij), Yij is the data of electronic report forms cell, and Xij is the original mechanical performance data in SQLSERVER2000 database, and F is mapping relations.

2) data of electronic report forms also likely need to carry out secondary conventional processing with information analysis and final printing reports according to different.

3) spreadsheet report pattern need meet the report style of these factory's internal rules, can oneself define.

According to above-mentioned requirements, it is most suitable that the raw data in database is generated EXCEL file after calculating.So just can control and data-handling capacity by powerful by Excel, abundant form, the requirement that user freely defines report style and data carried out to secondary treating can be met.Both to some extent solved the flexibility problem of form, and in turn saved many development amounts, reduce the scale of program design, simplify the maintenance of program, thus substantially increase the efficiency of system development.And as the widely used software of one, also there is not train problems in EXCEL.In JSP environment, several conventional method below is in general had to generate Excel file.The storing process utilizing SQLSERVER to carry is derived and is generated EXCEL file.Simple and convenient but the very flexible of the method, can not define statement form in advance, and as header area, gauge outfit district, table tail region etc., need to revise afterwards again, define its report style, post-processed is cumbersome.And manufacturer requires that the report data generated needs through intermediate treatment, so method can not meet the demands.2, utilize third party can generate the plug-in unit of EXCEL.Shortcoming is pin function general finite, can not meet special requirement, and generally will charge, and can not, well for B/S structure, need extra time study to understand pin function in addition.Consider the demand of system and we determine that oneself exploitation is used for generating the program of EXCEL file after careful research, because so i.e. economy but also facilitate, be most suitable method under the prerequisite meeting system requirements.

Summary of the invention

An a kind of EXCEL report form generation method of base XML in JSP environment, the XML format of first carrying out EXCEL is resolved; Form is generally decomposed into header area, gauge outfit district, table tagma and table tail region, also the content of form can be divided into two parts:

(1) static description part (header area, gauge outfit district and table tail region): static description has partly defined at statement form

After be constant, as the size of font, whether font has underscore, and be single underscore or double underline, whether font has

Subscript or subscript, font is left-justify, Right Aligns or placed in the middle etc.

(2) dynamic data filling part (table tagma): this be report data present part, be the core of whole form.

Any one EXCEL file newly-built, after the form of the corresponding format that completes, saves as XML file wherein, like this

Just obtain the xml code of EXCEL file.

As this section of xml code describes the essential information of EXCEL file below, as version, creation-time, author, pattern letter

Breath, last holding time etc., for the static state of form describes part.

<mso-application progid="Excel.Sheet">

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

42Z</Created>

</DocumentProperties>

</OfficeDocumentSettings>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

</Style>

</Styles>

And one section of xml code below describes the situation of this Zhang Gong's tabulation of sheet1, for dynamic data describes part.Wherein

Xml code describe the line number of this worksheet, columns, the height of cell and width, and the Table Header information in table tagma

With data message etc. in table.

<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="3" x:FullColumns="1"

x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">

<Row>

<Cell><Data ss:Type=" String " > first row </Data></CellGre atT.GreaT.GT

<Cell><Data ss:Type=" String " > secondary series </Data></CellGre atT.GreaT.GT

<Cell><Data ss:Type=" String " > the 3rd arranges </Data></CellGre atT.GreaT.GT

<Cell><Data ss:Type=" String " > the 4th arranges </Data></CellGre atT.GreaT.GT

</Row>

<Row>

</Row>

<Row>

</Row>

</Table>

2. by analysis to XML structure, in time will creating such EXCEL form, just relatively easy.After report style is determined, static description part is changeless, therefore exports as it is, if as required by programming well corresponding multidate information just can generate the file of XML format.Such as change the <Row> not adding runic of Dynamic profiling part ... namely </Row> part changes the gauge outfit that sheet 1 shows tagma, and change adds the <Row> of bolded section ... </Row> and line number descriptor can increase/reduce, the data message in change table tagma.Here is the JSPbean creation procedure framework of an XML file:

package reportdata;

import JSP.io.*;

import JSP.io.FileOutputStream;

public class CreateExcel {

String str="";

Int sheet1rowcount=8; Total line number of //sheet1

Int sheet2rowcount=5; Total line number of //sheet2

Int sheet3rowcount=4; Total line number of //sheet3

Public CreateExcel () { // building method }

Public String createHead () // create the static descriptor in top }

Public String createsheet1 () // create sheet 1 to show

Public String createsheet 2 () // create sheet 2 to show

Public String createsheet 3 () // create sheet 3 to show

Public String createBottom () // create the static descriptor in bottom }

By above analysis, can FOUR EASY STEPS be divided into: the first step, required raw data is taken out from database; Second step, processes (realizing computation process by JSP program) to raw data and is converted to XML information format; 3rd step, utilizes XML character stream to generate XML file; 4th step, is converted to formal EXCEL report file.

After generation XML data file, because it has been a document data bank, therefore can carries out off-line operation, which reduces network traffics, improve system effectiveness.

Claims

1. in a JSP environment based on the EXCEL report form generation method of XML: the method mainly by right XML format resolve automatic report generation.

2. in JSP environment according to claim 1 based on the EXCEL report form generation method of XML, its principal character is: the XML format of EXCEL is resolved, form is generally decomposed into header area, gauge outfit district, table tagma and table tail region, also the content of form can be divided into two parts.

3. in JSP environment according to claim 2 based on the EXCEL report form generation method of XML, its static description part (header area, gauge outfit district and table tail region): static description part is constant after statement form has defined, as the size of font, whether font has underscore, single underscore or double underline, whether font has subscript or subscript, and font is left-justify, Right Aligns or placed in the middle etc.

4. in JSP environment according to claim 2 based on the EXCEL report form generation method of XML, its dynamic data filling part (table tagma): this be report data present part, be the core of whole form.

5. in JSP environment according to claim 2 based on the EXCEL report form generation method of XML, according to the object point of its outside, generate specific XML format.

6., according to claim 5, its JSPbean creation procedure framework, generates specific XML.

7., according to claim 6, required raw data is taken out from database and reads its data stream.

8., according to claim 7, (realizing computation process by JSP program) is processed to raw data and is converted to XML information format.

9. according to Claim 8, utilize XML character stream to generate the XML file of specific JSP form.

10., according to claim 9, during display statement form, be converted to formal EXCEL report file.