The content of the invention
It is an object of the invention to provide a kind of parsing of BPA electric network datas and management method and system based on EXCEL, should
Method and system are conducive to improving the efficiency of data management during electrical network analysis.
For achieving the above object, the technical scheme is that:A kind of parsing of BPA electric network datas and pipe based on EXCEL
Reason method, comprises the following steps:
S1, based on EXCEL character string bit manipulation functions fast resolving format BPA electric network datas;
S2, using the Automatic Function generation method based on auxiliary line information, realize the automatic filling and automatically of data card
Calculate;
S3, BPA electric network data Classification Managements are carried out based on method for fast searching;
Data after parsing are automatically converted to BPA data by S4, the BPA data card generation techniques based on Formatting Output
Form, and output formatting BPA data.
Further, in step sl, using the data of the MIDB functions extract character string given positions using EXCEL,
And judge corresponding with reference to the conditional functions such as IF, AND, SUBSTITUTE characters replacement function and LEN string lengths discriminant function
Whether field has is filled in arithmetic point to carry out the parsing of data, realizes that the structuring to BPA data cards is parsed.
Further, in step s 2, EXCEL supports the relative position pattern of formula, based on this feature, for data
Position using table name definitely application it is corresponding with ranks with by the way of come charging formula, be combined with secondary row mark it is right
Extension position and the required precision of field are answered, is realized and is realized a kind of data parsing of card with a formula, and utilize EXCEL
Automatic filling process in the automatic filling of relative position principle, realizing a kind of parsing of data card only needs a line generation
Code, the function of remaining element lattice by filling and aid in line information to automatically generate and calculate automatically.
Further, in step s3, the bulk power grid data for being based on EXCEL are realized certainly based on the data card classification for aiding in row
It is dynamic to classify and collect, first process is numbered to all data cards according to data card type analysis result, according to each data
Card Type generates a group index, and finally using INDIRECT, tri- functions of ADDRESS, VLOOKUP retrieve corresponding classification number
Extract from overall file according to card and according to order;Using quick binary search method, quick large-scale data is realized
Automatically classify and collect.
Further, in step s 4, the Formatting Output method of data after the parsing for being specified according to data form, realizes
Data are automatically converted to data card and collect output after parsing, the BPA files that generation can be calculated directly.
Present invention also offers a kind of parsing of BPA electric network datas and management system based on EXCEL, at least includes:
Data input cell, for the importing and classification of BPA electric network datas;
Data resolution unit, for the corresponding data card of classification parsing;
Formatting Output unit, for being formatted output to the electric network data after adjustment.
Further, said system also includes:Data Management Unit, for carrying out retrieval and inquisition and batch to electric network data
Modification.
The invention has the beneficial effects as follows there is provided a kind of BPA electric network datas parsing based on EXCEL and management method and being
System, the method and system realize that the fast resolving of large scale electric network data is processed based on EXCEL functions, can be quick according to demand
Adjustment parsing content and batch data adjustment, and the BPA formatted datas that can be directly used for calculating are derived, realize BPA electrical network numbers
According to high efficient and flexible parsing and process, effectively raise the efficiency of data management during electrical network analysis.
Specific embodiment
Below in conjunction with the accompanying drawings and specific embodiment the present invention is described in further detail.
BPA electric network data parsing and management method of the present invention based on EXCEL, as shown in figure 1, comprising the following steps:
S1, based on EXCEL character string bit manipulation functions fast resolving format BPA electric network datas.
Because the BPA data forms of electrical network are the divisions using the numbered positions of data row as data card field location(With
From data card number arrange to how many row to define a field), while with the floating number data form of fortran as number
According to form(For F*.* is specified in the character string for giving a length as the form of floating number, first * represents that data are long
Degree, second * represents precision, is forced to think to need to remove the data filled in if filling in and not adding arithmetic point in data
With 10 precision power obtaining last data), additionally due to Chinese takes 2 eight bit character width, and due to different
The length and number of words of name custom Chinese not can determine that.The parsing for solving data card with the technology of tradition programming has two difficulties
Point, one, due to main flow programming language(Such as C is serial)String processing function in processes the step-by-step of Chinese character string parsing on compare
It is loaded down with trivial details, modification trouble(Need the process of the word length of consideration Chinese);Two, due to power system technology progress, new forms of energy and direct current
New model continue to bring out, the continuous development dataset of BPA softwares is stuck in constantly upgrading, new data card occurs and is accomplished by journey
Sequence is write and is compiled again, easily error, effective also poor.A kind of MIDB of application EXCEL is proposed for this present invention
Function(The extraction of character string given position data is realized, by eight bit character length), and with reference to conditional functions such as IF, AND,
SUBSTITUTE character replacement functions(For replacing arithmetic point)And LEN(String length discriminant function)Judge that respective field is
It is no have fill in arithmetic point to carry out the parsing of data, realize that the structuring to BPA data cards is parsed(The ginseng that correlation function is adopted
Number, for data can apply the relative and absolute reference relation of EXCEL automatically filling without the concern for parameter when programming, only
The position length and precision information for formulating character string is needed, therefore only needs adjustment corresponding when parsing different field or more change card
The descriptive information of field can just reuse all codes).
S2, using the Automatic Function generation method based on auxiliary line information, realize the automatic filling and automatically of data card
Calculate.
EXCEL supports the relative position pattern of formula, based on this feature, for the position of data definitely should using table name
With it is corresponding with ranks with mode come charging formula, be combined with secondary row and mark the extension position and essence of corresponding field
Degree requirement, realizes the data parsing that a kind of card is realized with a formula, and using the phase in the automatic filling process of EXCEL
Principle to the automatic filling in position, realizing a kind of parsing of data card only needs line code, the function of remaining element lattice by
Automatically filling and auxiliary line information are automatically generated and calculated, and greatly improve the efficiency of programming efficiency and test and debugging.
S3, BPA electric network data Classification Managements are carried out based on method for fast searching.
Because electric network data scale data card line number is likely to be breached 100,000 row ranks, different types of data card often interlocks
Fill in, need to classify data card in parsing.For traditional programming technique, a time institute of traversal is generally used
Then some data cards, dynamic statistics and categorical data Card Type carry out data parsing.The efficiency of this method processing data is most
Height, but need to write related content and compiled again again during newly-increased data card, it is impossible to adapt to the sweetly disposition of data, journey
The efficiency of sequence response changes in demand is low.And if using being classified using the automatic screening of EXCEL or the function of sequence, needed
Manually to operate in a large number, execution efficiency is very low, be also easy to introduce mistake.The present invention proposes a kind of data based on auxiliary row
Card classification is realized the bulk power grid data automatic classification based on EXCEL and is collected, first according to data card type analysis result to institute
There is data card to be numbered process, a group index is generated according to each data card type(For the numbering for meeting corresponding data card
Round numbers, for incongruent intermediate value for taking upper and lower two load data cards numbering, the side of index is corresponding such number
According to the numbering of card, side is the line number that the card is located hereof), finally using INDIRECT(Using address search data),
ADDRESS(Using table name, OK, row number generates address), VLOOKUP(The qualified record of search and range position)Three letters
Number retrieves corresponding categorical data card and is extracted from overall file according to order.Because EXCEL retrieval functions are interior
Put, it is impossible to carry out embedded modification, the lookup of each corresponding record is required for entering line retrieval to all data, for big
Precise search efficiency is very low for scale data, therefore present invention employs corresponding quick binary search method, and in data
Retrieval position deviation during little data deviation is introduced during numbering to avoid EXCEL fuzzy searches is asked
Topic, finally realizes quick large-scale data and classifies automatically and collect.The method of this patent effectively make use of is changed with space
The principle of time, by cumulative index, and sequential index technology and two points of quick-searchings, effectively realize the fast of mass data
Speed retrieval, while and make use of built-in function reliability and program update motility the characteristics of.
Data after parsing are automatically converted to BPA data by S4, the BPA data card generation techniques based on Formatting Output
Form, and output formatting BPA data.
According to the Formatting Output method of data after the parsing that data form specifies, realize data after parsing and convert automatically
For data card and collect output, generate the BPA files that can directly calculate.
Present invention also offers a kind of parsing of BPA electric network datas and management system based on EXCEL, as shown in Fig. 2 bag
Include:
Data input cell 1, for the importing and classification of BPA electric network datas.
Data resolution unit 2, for the corresponding data card of classification parsing.
Formatting Output unit 3, for being formatted output to the electric network data after adjustment.After by modification adjustment
Electric network data be formatted as BPA data cards and concentrate output, generate the file that can directly calculate;It is defeated using string formatization
The mode for going out by parsing after the data after practical personnel change adjustment be converted into BPA data cards form and collect and be output as
The BPA files that can directly calculate.
Data Management Unit 4, for carrying out retrieval and inquisition and batch modification to electric network data.For data card to be classified,
Field parsing item by item, and the management operations such as the retrieval and inquisition and batch modification of electric network data, screening are realized, system is according to BPA
Data card classification of type parsing related data card, the analysis program of each data card realizes only writing by the setting of auxiliary information
The function of one cell, other cells are realized with the mode of automatic filling, are capable of achieving data based on EXCEL platforms and parse
Parallel high-speed process.
It is more than presently preferred embodiments of the present invention, all changes made according to technical solution of the present invention, produced function is made
During with scope without departing from technical solution of the present invention, protection scope of the present invention is belonged to.