CN104572666A - Method for analyzing and importing Excel into database in batch based on Jxl - Google Patents
Method for analyzing and importing Excel into database in batch based on Jxl Download PDFInfo
- Publication number
- CN104572666A CN104572666A CN201310480068.XA CN201310480068A CN104572666A CN 104572666 A CN104572666 A CN 104572666A CN 201310480068 A CN201310480068 A CN 201310480068A CN 104572666 A CN104572666 A CN 104572666A
- Authority
- CN
- China
- Prior art keywords
- excel
- data
- database
- importing
- jxl
- 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
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/258—Data format conversion from or to a database
Abstract
The invention relates to a method for analyzing and importing Excel into a database in batch based on Jxl. Data importing is involved in most of service systems, and the file formats for data importing are most Excel. Data in an Excel file are firstly analyzed and are subsequently imported into the database, and then data importing is completed. When Excel data are settled, the data importing can be operated directly according to fields and the data by adding a field row corresponding to the database and adopting the splicing function of Jxl and SQL sentences, and moreover field analysis does not need to implement to each object. Excel can be imported into the database in batch, the code quantity is greatly reduced, the workload is greatly reduced, fields do not need to compare, and the error rate is greatly reduced.
Description
Technical field
The present invention relates to and a kind ofly resolve Excel based on Jxl batch and import the method for database.The invention belongs to field of computer technology.
Background technology
In most of operation system, all can relate to the importing of data, and the file layout used when importing data is mostly Excel.By the data in Excel file, first to resolve Excel, afterwards to the data importing of having resolved in database, complete the importing of data.General importing needs to carry out field analysis to each dissimilar object, and each object needs to write an introduction method, and size of code is increased greatly, and work efficiency also can reduce, and artificial contrast's field also can increase error rate simultaneously.As long as but when arrangement Excel data, add the field row that lastrow is corresponding with database, use the splicing function of Jxl and SQL statement, just directly can carry out data importing according to field and data, and field analysis need not be carried out to each object.So just can import Excel in batches and can import database simultaneously, and size of code greatly reduces, workload greatly reduces, and simultaneously without comparison field, also makes error rate greatly reduce.
Summary of the invention
When using Excel type file to carry out data importing in operation system, add the field row that a line is corresponding with object database, Jxl and SQL statement is used to splice function, batch can be realized import Excel and can database be imported simultaneously, and size of code greatly reduces, workload greatly reduces, and simultaneously without comparison field, also makes error rate greatly reduce.
Concrete steps are as follows:
Step 1: Query Database object field, adds the field row that lastrow is corresponding with it in Excel file.
Step 2: import local Excel file by the Wookbook in Jxl, then uses the particular job table in Sheet acquisition Excel, finally circulates, and resolves the often capable all cell in Excel.
Step 3: splicing SQL statement, uses " insert " statement, first splices in Excel and newly adds field row, then to Excel often row circulate, splicing is the data that parse of row often, by SQL statement by data data inserting storehouse, complete importing.
Embodiment
Embodiment one
1. use Myeclips 8.6 development platform to develop design, use expert data library software MySql to carry out data management.
2. Query Database object field, adds the field row that a line is corresponding with it in Excel.
3. by the Wookbook in Jxl obtain local Excel path (as D: excel.xls) and to open, then use getSheet() circulation obtains all working table in Excel, finally circulates, resolve often to go all cell in Excel.
4. splice SQL statement, use " insert " statement, first splice in Excel and newly add field row, the required table name importing table of splicing afterwards, finally again to Excel often row circulate, splicing is the data that parse of row often, by SQL statement by data data inserting storehouse, complete importing.
In addition to the implementation, the present invention can also have other embodiments.All employings are equal to the technical scheme of replacement or equivalent transformation formation, all drop on the protection domain of application claims.
Claims (3)
1. Query Database object field, adds the field row that a line is corresponding with it in Excel.
2. by the Wookbook in Jxl obtain local Excel path (as D: excel.xls) and to open, then getSheet(is used) circulate all working table obtained in Excel, finally circulate, resolve the often capable all cell in Excel.
3. splice SQL statement, use " insert " statement, first splice in Excel and newly add field row, the required table name importing table of splicing afterwards, finally again to Excel often row circulate, splicing is the data that parse of row often, by SQL statement by data data inserting storehouse, complete importing.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310480068.XA CN104572666A (en) | 2013-10-15 | 2013-10-15 | Method for analyzing and importing Excel into database in batch based on Jxl |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310480068.XA CN104572666A (en) | 2013-10-15 | 2013-10-15 | Method for analyzing and importing Excel into database in batch based on Jxl |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN104572666A true CN104572666A (en) | 2015-04-29 |
Family
ID=53088760
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201310480068.XA Pending CN104572666A (en) | 2013-10-15 | 2013-10-15 | Method for analyzing and importing Excel into database in batch based on Jxl |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104572666A (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105373630A (en) * | 2015-12-18 | 2016-03-02 | 河南思维自动化设备股份有限公司 | Automatic identification and creation method for LKJ basic data filling table |
| CN106257443A (en) * | 2015-06-16 | 2016-12-28 | 华为软件技术有限公司 | A kind of data lead-in method and device |
| CN106776962A (en) * | 2016-12-05 | 2017-05-31 | 济南浪潮高新科技投资发展有限公司 | A kind of general Excel data import multiple database physical table methods |
| CN109783554A (en) * | 2018-12-13 | 2019-05-21 | 重庆金融资产交易所有限责任公司 | Excel document analytic method, device and computer readable storage medium |
| CN113448841A (en) * | 2021-05-27 | 2021-09-28 | 工银科技有限公司 | Data loading method and device based on multiple processes |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101989278A (en) * | 2009-08-06 | 2011-03-23 | 上海杉达学院 | Database inquiry device with automatic splicing function |
| EP2472461A1 (en) * | 2010-12-30 | 2012-07-04 | Tata Consultancy Services Ltd. | Configurable catalog builder system |
| CN102981946A (en) * | 2011-09-07 | 2013-03-20 | 阿里巴巴集团控股有限公司 | Extract Transform and Load (ETL) smoke test method |
-
2013
- 2013-10-15 CN CN201310480068.XA patent/CN104572666A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101989278A (en) * | 2009-08-06 | 2011-03-23 | 上海杉达学院 | Database inquiry device with automatic splicing function |
| EP2472461A1 (en) * | 2010-12-30 | 2012-07-04 | Tata Consultancy Services Ltd. | Configurable catalog builder system |
| CN102981946A (en) * | 2011-09-07 | 2013-03-20 | 阿里巴巴集团控股有限公司 | Extract Transform and Load (ETL) smoke test method |
Non-Patent Citations (1)
| Title |
|---|
| 李林珊，等: ""基于JXL 的数据导入导出处理"", 《软件开发与应用》 * |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106257443A (en) * | 2015-06-16 | 2016-12-28 | 华为软件技术有限公司 | A kind of data lead-in method and device |
| CN105373630A (en) * | 2015-12-18 | 2016-03-02 | 河南思维自动化设备股份有限公司 | Automatic identification and creation method for LKJ basic data filling table |
| CN106776962A (en) * | 2016-12-05 | 2017-05-31 | 济南浪潮高新科技投资发展有限公司 | A kind of general Excel data import multiple database physical table methods |
| CN109783554A (en) * | 2018-12-13 | 2019-05-21 | 重庆金融资产交易所有限责任公司 | Excel document analytic method, device and computer readable storage medium |
| CN113448841A (en) * | 2021-05-27 | 2021-09-28 | 工银科技有限公司 | Data loading method and device based on multiple processes |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111382174B (en) | Multi-party data joint query method, device, server and storage medium | |
| CN110908997B (en) | Data blood relationship construction method and device, server and readable storage medium | |
| CN104899295B (en) | A kind of heterogeneous data source data relation analysis method | |
| CN102682118B (en) | Multidimensional data model access method and device | |
| CN104572666A (en) | Method for analyzing and importing Excel into database in batch based on Jxl | |
| CN102279749A (en) | Method and equipment for merging codes | |
| CN110597842B (en) | Service data query method and system | |
| US20200125776A1 (en) | Collision check data processing method and apparatus, electronic device, and storage medium | |
| CN104216961B (en) | A kind of data processing method and device | |
| CN104133772A (en) | Automatic test data generation method | |
| CN103793422A (en) | Methods for generating cube metadata and query statements on basis of enhanced star schema | |
| CN109508355A (en) | A kind of data pick-up method, system and terminal device | |
| CN104424018A (en) | Distributed calculating transaction processing method and device | |
| CN108170752A (en) | metadata management method and system based on template | |
| CN105069109A (en) | Dilatation method and system of distributed database | |
| CN108829884A (en) | data mapping method and device | |
| CN105975489A (en) | Metadata-based online SQL code completion method | |
| CN103678396B (en) | A kind of data back up method and device based on data model | |
| CN104504221A (en) | Evaluation data processing method and system | |
| CN112148713B (en) | Method and device for data migration between heterogeneous databases | |
| CN102855278B (en) | A kind of emulation mode and system | |
| CN102968305B (en) | Logical process method, device and evaluation system | |
| CN104536897A (en) | Automatic testing method and system based on keyword | |
| CN102446164A (en) | Analytic method, device and operation system of report formula | |
| CN103631816A (en) | Method for configuring and applying service model across data sources and equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20150429 |
|
| WD01 | Invention patent application deemed withdrawn after publication |