CN103150380A - Table format customizable Excel table analysis method - Google Patents
Table format customizable Excel table analysis method Download PDFInfo
- Publication number
- CN103150380A CN103150380A CN2013100803725A CN201310080372A CN103150380A CN 103150380 A CN103150380 A CN 103150380A CN 2013100803725 A CN2013100803725 A CN 2013100803725A CN 201310080372 A CN201310080372 A CN 201310080372A CN 103150380 A CN103150380 A CN 103150380A
- Authority
- CN
- China
- Prior art keywords
- file
- excel
- xml
- information
- configuration file
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Abstract
The invention discloses a table format customizable Excel table analysis method which comprises the steps of defining an XML (Extensive Makeup Language) format configuration file corresponding to an Excel table to be resolved according to water diversion business requirements, uploading an Excel file to a Web server through a browser, resolving the file, obtaining a target data sheet of the file and information of the corresponding XML configuration file according to a filename of the uploaded file, resolving the configuration file, analyzing contents of the uploaded file through the information of the configuration file, packaging the information to be warehoused into a data object, warehousing data with Hibernate, and uploading a file backup to the server. According to the method, general resolution of complicated Excel is realized through the configurable file, the method is particularly applicable to processing river basin water diversion business tables, and a precise error information prompt mechanism and a transaction management mechanism ensures accuracy and consistency of the data of a database.
Description
Technical field
The present invention relates to a kind of general, customizable tabular Excel table analytic method, water basin combination is transferred business, is used for solving complex forms, and the Data Analysis problem that the different water of form is transferred business Excel form belongs to computing machine report form processing technical field.
Background technology
Along with the fast development of infotech, the Water Conservancy Information level also is greatly improved.In routine office work, there are the problems such as data are comprehensive, working way is backward, office efficiency is low in watershed management person at aspects such as water resources decision-making, water regulation management, brought inconvenience for the management of water resources decision-making and water regulation.For the great lot of water resources data that guarantee different basins input database and gather calculating accurately and efficiently, need to provide standardized data form for the water conservancy working person who is responsible for the data typing.The list that presents with the conventional web form has affected user's experience, and the mode of carrying out the batch data typing by the Excel form has obtained more and more general employing in office.Yet the form required due to the water conservancy business is complicated, data volume is large, strongly professional, and traditional Excel reports analysis mode can not satisfy the processing demands of water conservancy business far away.Therefore, be necessary to provide a kind of general Excel form analysis method to realize the efficient parsing of the customizable complicate list form of basin water accent business.
Summary of the invention
Goal of the invention: for problems of the prior art, the present invention is based on existing Excel form analytic technique, a kind of general, customizable tabular Excel table analytic method is provided, and the present invention can process the parsing problem of this type of Excel form efficiently, accurately.
Technical scheme: a kind of customizable tabular Excel table analytic method comprises the steps:
Step 1: transfer business demand according to water, the configuration file of the XML form that the Excel table of the required parsing of definition is corresponding;
Step 2: by browser with the Excel File Upload to Web server, call POI(document analysis class libraries) resolve this document, and its form with document flow is stored in the File object, if successfully resolved enters step 3; Otherwise, enter step 7;
Step 3: search index file (file layout is XML) according to the filename of upload file, obtain the target matrix of this document and corresponding XML profile information according to index file, call Dom4j(XML and resolve class libraries) resolve this XML configuration file, successful enter step 4, otherwise enter step 7;
Step 4: obtain the supplementarys such as corresponding data literary name section, special joint coordinate according to the XML configuration file, thereby the Excel Information encapsulation that the resolution file content also will need to put in storage becomes data object, if success enters step 5; Otherwise enter step 7;
Step 5: use Hibernate to realize data loading; If success enters step 6; Otherwise, enter step 7;
Step 6: upload file is backed up to Web server, process ends;
Step 7: the misregistration daily record is also returned to friendly bomp information, process ends to the user.
Wherein, transfer business demand according to water in step 1, the configuration file of the XML form of definition has two classes:
The first kind is the index file of XML form, and the citation form of its storage is a plurality of target database table names of a kind of Excel filename form responding and an XML configuration filename; Equations of The Second Kind is that the Microsoft Excel of XML form is resolved secondary file, and the content of its storage is the database table field information, special joint coordinate information etc.
In step 2, upload file is called Java document analysis class libraries POI and is resolved.
Use index file with the target matrix information of the form storage upload file of key-value pair and corresponding XML profile information in step 3, then search configuration file according to this configuration filename, and use Java to call Dom4j(XML and resolve class libraries) resolve this configuration file.
Step 4 is core content of the present invention, because need are resolved complicated and diversified basin water accent business Excel form, resolves in auxiliary configuration file to have comprised a large amount of special joint information.In conjunction with the database table field information of XML configuration file, by the reflex mechanism of Java, Excel form data dynamic seal is dressed up data object in step 4; Realize that in conjunction with the special joint coordinate information of XML configuration file single Excel imports the business demand of multiple database table.
When using Hibernate to realize data loading in step 5, if the data loading failure is arranged, by the transaction management mechanism of Spring, database is carried out rollback, to keep the consistance of data.
The present invention adopts technique scheme, has following beneficial effect: by configurable file, realized that the customizable complicated Excel form of basin water accent business imports the business demand of multiple database table; Dynamically import form, importing process is transparent to the user, and the user need not the manual form condition of selecting to import; Form support is complicated, the importing of the Excel that form is different; Accurate error message prompting mechanism and transaction management mechanism, accuracy and the data consistency of maintenance logging data database data.
Description of drawings
Fig. 1 is the method flow diagram of the embodiment of the present invention.
Embodiment
Below in conjunction with specific embodiment, further illustrate the present invention, should understand these embodiment only is used for explanation the present invention and is not used in and limits the scope of the invention, after having read the present invention, those skilled in the art all fall within the application's claims limited range to the modification of the various equivalent form of values of the present invention.
As shown in Figure 1, customizable tabular Excel table analytic method comprises the steps:
Step 1: transfer business demand according to water, the configuration file of the XML form that the Excel table of the required parsing of definition is corresponding.Its configuration file has two classes: the first kind is the index file of XML form, and the citation form of its storage is a plurality of target database table names of a kind of Excel filename form responding and an XML configuration filename; Equations of The Second Kind is that the Microsoft Excel of XML form is resolved secondary file, and the content of its storage is the database table field information, special joint coordinate information etc.
For example, a kind of configuration mode of filename type is as follows:
Its corresponding file is by name: the water plan daily paper; Corresponding database table is by name: BurPlanYear, Glxj, Zlxj, Hj; Corresponding configuration file is by name: the water plan daily paper is resolved .xml.
For example, a kind of database table field configuration information is as follows:
Wherein, the property value under the name node is the Column Properties value of Execl, and the property value under the code node is the method name of entity class, corresponding to the field name that deposits in after database.
For example, a kind of profile information of special joint coordinate information is as follows:
Wherein, the property value under start node representative begins reflex mechanism by Java with data importing Ji Ku from the 7th row of Excel table; The representative of property value under the glxj node begins data importing master stream subtotal database from the 13rd row of Excel table; The representative of property value under the zlxj node begins data importing tributary subtotal database from the 27th row of Excel table; Property value representative under the hj node begins data importing aggregate information database from the 30th row of Excel table.
Step 2: by browser with the Excel File Upload to Web server, call Java document analysis class libraries POI and resolve this document, and its form with document flow is stored in the File object, if successfully resolved enters step 3; Otherwise, enter step 7;
Step 3: search index file (file layout is XML) according to the filename of upload file, obtain the target matrix information of upload file and corresponding XML profile information according to index file, call Dom4j(XML and resolve class libraries) resolve this XML configuration file, successful enter step 3, otherwise enter step 6.Wherein use index file with the target matrix information of the form storage upload file of key-value pair and corresponding XML profile information, then search configuration file according to this configuration filename, and use Java to call Dom4j and resolve this configuration file.
Step 4: obtain the supplementarys such as corresponding data literary name section, special joint coordinate according to the XML configuration file, thereby the Excel Information encapsulation that the resolution file content also will need to put in storage becomes data object, if success enters step 5; Otherwise enter step 7; Wherein in conjunction with the database table field information of XML configuration file, by the reflex mechanism of Java, Excel form data dynamic seal is dressed up data object; Realize that in conjunction with the special joint coordinate information of XML configuration file single Excel imports the business demand of multiple database table.
Step 5: use Hibernate to realize data loading; If success enters step 6; Otherwise, enter step 7;
Step 6: upload file is backed up to Web server, process ends;
Step 7: the misregistration daily record is also returned to friendly bomp information, process ends to the user.
The present invention solves the problem of the general parsing of complicated Excel by configuration file.If when new Excel template occurring afterwards, do not need to revise source program, only need corresponding configuration file corresponding to Excel template that increase to get final product, this type of form is customizable thus, program can realize according to configuration file the dynamic warehouse-in of transparence.
Claims (6)
1. a customizable tabular Excel table analytic method, is characterized in that, comprises the steps:
Step 1: transfer business demand according to water, the configuration file of the XML form that the Excel table of the required parsing of definition is corresponding;
Step 2: by browser with the Excel File Upload to Web server, resolve the Excel file should, and its form with document flow is stored in the File object, if successfully resolved enters step 3; Otherwise, enter step 7;
Step 3: search index file according to the filename of upload file, obtain the target matrix of this document and corresponding XML profile information according to index file, resolve described XML configuration file, successful enter step 4, otherwise enter step 7;
Step 4: obtain the supplementarys such as corresponding data literary name section, special joint coordinate according to the XML configuration file, thereby the Excel Information encapsulation that the resolution file content also will need to put in storage becomes data object, if success enters step 5; Otherwise enter step 7;
Step 5: use Hibernate to realize data loading; If success enters step 6; Otherwise, enter step 7;
Step 6: upload file is backed up to Web server, process ends;
Step 7: the misregistration daily record is also returned to friendly bomp information, process ends to the user.
2. customizable tabular Excel table analytic method according to claim 1, is characterized in that, transfer business demand according to water in step 1, the configuration file of the XML form of definition has two classes:
The first kind is the index file of XML form, and the citation form of its storage is a plurality of target database table names of a kind of Excel filename form responding and an XML configuration filename; Equations of The Second Kind is that the Microsoft Excel of XML form is resolved secondary file, and the content of its storage is database table field information, special joint coordinate information.
3. customizable tabular Excel table analytic method according to claim 1, is characterized in that, in step 2, upload file is called Java document analysis class libraries POI and resolved.
4. customizable tabular Excel table analytic method according to claim 1, is characterized in that, uses index file with the target matrix information of the form storage upload file of key-value pair and corresponding XML profile information in step 3; Call XML is resolved class libraries Dom4j analyzing XML configuration file.
5. customizable tabular Excel table analytic method according to claim 1, is characterized in that, in conjunction with the database table field information of XML configuration file, by the reflex mechanism of Java, Excel information dynamic seal dressed up data object in step 4; Realize that in conjunction with the special joint coordinate information of XML configuration file single Excel imports the business demand of multiple database table.
6. customizable tabular Excel according to claim 1 shows analytic method, it is characterized in that, when using Hibernate to realize data loading in step 5, if the data loading failure is arranged, by the transaction management mechanism of Spring, database is carried out rollback, to keep the consistance of data.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2013100803725A CN103150380A (en) | 2013-03-13 | 2013-03-13 | Table format customizable Excel table analysis method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2013100803725A CN103150380A (en) | 2013-03-13 | 2013-03-13 | Table format customizable Excel table analysis method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN103150380A true CN103150380A (en) | 2013-06-12 |
Family
ID=48548457
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2013100803725A Pending CN103150380A (en) | 2013-03-13 | 2013-03-13 | Table format customizable Excel table analysis method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103150380A (en) |
Cited By (21)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104156340A (en) * | 2014-07-11 | 2014-11-19 | 公安部交通管理科学研究所 | Data report encapsulation and representation method |
| CN104267953A (en) * | 2014-09-27 | 2015-01-07 | 昆明钢铁集团有限责任公司 | Control and method for importing Word test questions based on browser |
| CN105760382A (en) * | 2014-12-16 | 2016-07-13 | 深圳云之家网络有限公司 | Method and device for importing excel data into database |
| CN105867886A (en) * | 2015-01-19 | 2016-08-17 | 阿里巴巴集团控股有限公司 | Method and device for writing table |
| CN106021215A (en) * | 2016-05-18 | 2016-10-12 | 广东源恒软件科技有限公司 | Automatic extraction method and system of finance and tax data |
| CN106126667A (en) * | 2016-06-25 | 2016-11-16 | 国云科技股份有限公司 | A kind of method realizing dynamically filling EXCEL sequence |
| CN106227855A (en) * | 2016-07-28 | 2016-12-14 | 努比亚技术有限公司 | A kind of transacter, system and method |
| CN106648763A (en) * | 2016-12-06 | 2017-05-10 | 明博教育科技股份有限公司 | Batch data importing component and importing method thereof |
| CN106776962A (en) * | 2016-12-05 | 2017-05-31 | 济南浪潮高新科技投资发展有限公司 | A kind of general Excel data import multiple database physical table methods |
| CN106776843A (en) * | 2016-11-28 | 2017-05-31 | 浪潮软件集团有限公司 | Method for importing excel file based on xml analysis |
| CN106802905A (en) * | 2015-11-26 | 2017-06-06 | 中国直升机设计研究所 | A kind of synergistic data exchange method of isomorphism PLM system |
| CN106919618A (en) * | 2015-12-28 | 2017-07-04 | 航天信息股份有限公司 | Excel data lead-in methods and system |
| CN109213635A (en) * | 2018-09-11 | 2019-01-15 | 许继集团有限公司 | A kind of the damage tables of data restorative procedure and system of wave recording device |
| CN109508447A (en) * | 2017-09-14 | 2019-03-22 | 长沙博为软件技术股份有限公司 | A kind of list data analysis method of the high col width of fixed row |
| CN109949877A (en) * | 2019-03-15 | 2019-06-28 | 北京科技大学 | A kind of data fusion method and system based on Experiment of Material Science |
| CN110765129A (en) * | 2019-09-19 | 2020-02-07 | 中国地质大学（武汉） | High-performance online expense settlement statistical method and device |
| CN111339041A (en) * | 2020-03-10 | 2020-06-26 | 中国建设银行股份有限公司 | File parsing and warehousing and file generating method and device |
| CN111367975A (en) * | 2018-12-25 | 2020-07-03 | 中国移动通信集团浙江有限公司 | Multi-protocol data conversion processing method and device |
| CN111611242A (en) * | 2020-04-30 | 2020-09-01 | 北京航天云路有限公司 | Method for importing Excel data into database |
| CN112988397A (en) * | 2021-04-20 | 2021-06-18 | 浙江贵仁信息科技股份有限公司 | Rapid analysis method and conversion server for water conservancy model file |
| CN113448960A (en) * | 2020-03-27 | 2021-09-28 | 北京沃东天骏信息技术有限公司 | Method and device for importing form file |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1328687C (en) * | 2003-06-11 | 2007-07-25 | 中兴通讯股份有限公司 | Centralized broad spectrum report generation method based on expandable sign language |
-
2013
- 2013-03-13 CN CN2013100803725A patent/CN103150380A/en active Pending
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1328687C (en) * | 2003-06-11 | 2007-07-25 | 中兴通讯股份有限公司 | Centralized broad spectrum report generation method based on expandable sign language |
Non-Patent Citations (3)
| Title |
|---|
| 杨瑞发: "《一种导出复杂Excel报表的设计与实现》", 《计算机系统应用》, vol. 22, no. 2, 28 February 2013 (2013-02-28), pages 155 - 157 * |
| 阳奇等: "《基于Hibernate搜索的数据库全文检索系统》", 《计算机工程》, vol. 36, no. 4, 28 February 2010 (2010-02-28), pages 74 - 75 * |
| 黄锦等: "《基于Excel的报表生成工具的设计与实现》", 《成都信息工程学院学报》, vol. 24, no. 6, 31 December 2009 (2009-12-31), pages 577 - 581 * |
Cited By (28)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104156340A (en) * | 2014-07-11 | 2014-11-19 | 公安部交通管理科学研究所 | Data report encapsulation and representation method |
| CN104156340B (en) * | 2014-07-11 | 2017-08-11 | 公安部交通管理科学研究所 | A kind of encapsulation of data sheet and show method |
| CN104267953A (en) * | 2014-09-27 | 2015-01-07 | 昆明钢铁集团有限责任公司 | Control and method for importing Word test questions based on browser |
| CN105760382A (en) * | 2014-12-16 | 2016-07-13 | 深圳云之家网络有限公司 | Method and device for importing excel data into database |
| CN105867886A (en) * | 2015-01-19 | 2016-08-17 | 阿里巴巴集团控股有限公司 | Method and device for writing table |
| CN105867886B (en) * | 2015-01-19 | 2020-03-10 | 阿里巴巴集团控股有限公司 | Method and device for writing table |
| CN106802905B (en) * | 2015-11-26 | 2020-04-03 | 中国直升机设计研究所 | Collaborative data exchange method of isomorphic PLM system |
| CN106802905A (en) * | 2015-11-26 | 2017-06-06 | 中国直升机设计研究所 | A kind of synergistic data exchange method of isomorphism PLM system |
| CN106919618A (en) * | 2015-12-28 | 2017-07-04 | 航天信息股份有限公司 | Excel data lead-in methods and system |
| CN106021215A (en) * | 2016-05-18 | 2016-10-12 | 广东源恒软件科技有限公司 | Automatic extraction method and system of finance and tax data |
| CN106126667A (en) * | 2016-06-25 | 2016-11-16 | 国云科技股份有限公司 | A kind of method realizing dynamically filling EXCEL sequence |
| CN106227855A (en) * | 2016-07-28 | 2016-12-14 | 努比亚技术有限公司 | A kind of transacter, system and method |
| CN106776843A (en) * | 2016-11-28 | 2017-05-31 | 浪潮软件集团有限公司 | Method for importing excel file based on xml analysis |
| CN106776962A (en) * | 2016-12-05 | 2017-05-31 | 济南浪潮高新科技投资发展有限公司 | A kind of general Excel data import multiple database physical table methods |
| CN106648763A (en) * | 2016-12-06 | 2017-05-10 | 明博教育科技股份有限公司 | Batch data importing component and importing method thereof |
| CN109508447A (en) * | 2017-09-14 | 2019-03-22 | 长沙博为软件技术股份有限公司 | A kind of list data analysis method of the high col width of fixed row |
| CN109213635A (en) * | 2018-09-11 | 2019-01-15 | 许继集团有限公司 | A kind of the damage tables of data restorative procedure and system of wave recording device |
| CN109213635B (en) * | 2018-09-11 | 2020-10-30 | 许继集团有限公司 | Damaged data table repairing method and system of wave recording device |
| CN111367975A (en) * | 2018-12-25 | 2020-07-03 | 中国移动通信集团浙江有限公司 | Multi-protocol data conversion processing method and device |
| CN111367975B (en) * | 2018-12-25 | 2023-11-03 | 中国移动通信集团浙江有限公司 | Multi-protocol data conversion processing method and device |
| CN109949877A (en) * | 2019-03-15 | 2019-06-28 | 北京科技大学 | A kind of data fusion method and system based on Experiment of Material Science |
| CN110765129A (en) * | 2019-09-19 | 2020-02-07 | 中国地质大学（武汉） | High-performance online expense settlement statistical method and device |
| CN110765129B (en) * | 2019-09-19 | 2022-04-29 | 中国地质大学（武汉） | High-performance online expense settlement statistical method and device |
| CN111339041A (en) * | 2020-03-10 | 2020-06-26 | 中国建设银行股份有限公司 | File parsing and warehousing and file generating method and device |
| CN111339041B (en) * | 2020-03-10 | 2024-01-12 | 中国建设银行股份有限公司 | File analysis and storage method and device and file generation method and device |
| CN113448960A (en) * | 2020-03-27 | 2021-09-28 | 北京沃东天骏信息技术有限公司 | Method and device for importing form file |
| CN111611242A (en) * | 2020-04-30 | 2020-09-01 | 北京航天云路有限公司 | Method for importing Excel data into database |
| CN112988397A (en) * | 2021-04-20 | 2021-06-18 | 浙江贵仁信息科技股份有限公司 | Rapid analysis method and conversion server for water conservancy model file |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103150380A (en) | Table format customizable Excel table analysis method | |
| CN104077665B (en) | Electricity power engineering analysis of prices data gathering system and method | |
| CN107145480B (en) | Method for compiling XBRL report based on Word | |
| Gan et al. | How to deal with resource productivity: Relationships between socioeconomic factors and resource productivity | |
| CN104156832A (en) | Intersystem data verification method and device | |
| CN102339314A (en) | Cross-terminal data form description and presentation method based on extensive makeup language (XML) | |
| CN101430714B (en) | Content structuring process method and system based on model | |
| CN106649769B (en) | Semantic-based conversion method from XBRL data to OWL data | |
| CN102214243A (en) | Version management system for x extensible business reporting language (XBRL) classification standard | |
| CN101667172A (en) | Method for making spreadsheet by utilizing word | |
| CN103246753A (en) | Method for generating entity metadata model according to database structure | |
| CN105278961A (en) | Method and system for generating database table structure document | |
| CN102866986A (en) | Document format conversion system | |
| CN102521008B (en) | Program compiling device and method for compiling program | |
| CN102830974A (en) | Visual auxiliary development tool for rapid generation of JAVA codes | |
| US9652478B2 (en) | Method and apparatus for generating an electronic document schema from a relational model | |
| CN103678396A (en) | Data backup method and device based on data models | |
| CN104268713A (en) | Performance assessment computing method and system | |
| CN103885765A (en) | Method for coding XBRL (extensible business reporting language) elements | |
| CN105550176A (en) | Basic mapping method for relational database and XML | |
| CN103020162B (en) | CC2000 model conversion imports the method for D5000 system | |
| CN101329625A (en) | Expert system developing platform | |
| CN115617773A (en) | Data migration method, device and system | |
| CN102902707B (en) | The method of different editions binary data compatibility and versions of data converting system | |
| CN102184166A (en) | Method for converting Excel form into InfoPath form based on office software |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C02 | Deemed withdrawal of patent application after publication (patent law 2001) | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20130612 |