CN106547841A - A kind of data automatic switching method of Excel XML mappings - Google Patents
A kind of data automatic switching method of Excel XML mappings Download PDFInfo
- Publication number
- CN106547841A CN106547841A CN201610898335.9A CN201610898335A CN106547841A CN 106547841 A CN106547841 A CN 106547841A CN 201610898335 A CN201610898335 A CN 201610898335A CN 106547841 A CN106547841 A CN 106547841A
- Authority
- CN
- China
- Prior art keywords
- xml
- template
- excel
- data
- information
- 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/80—Information retrieval; Database structures therefor; File system structures therefor of semi-structured data, e.g. markup language structured data such as SGML, XML or HTML
- G06F16/84—Mapping; Conversion
- G06F16/86—Mapping to a database
Abstract
The invention discloses a kind of data automatic switching method of Excel XML mappings, problem is automatically generated by solve XML schema and map information, realize Excel to the automatic conversion of XML data by Excel XML mapping mechanisms, by being mapped using two-stage, XML schema is automatically generated according to Excel template pattern, the mapping path of each data cells is recorded automatically in framework generating process；Then Excel report datas are automatically converted into into the XML data file corresponding with formwork style by Excel XML mappings, the present invention solves the conversion of Excel XML datas present in prior art needs manual intervention, the problem for causing work efficiency underground, error rate high.
Description
Technical field
The invention belongs to technical field of data processing, and in particular to a kind of data automatic conversion side of Excel XML mappings
Method.
Background technology
Many times, many enterprises and institutions all can constantly come into contacts with Excel forms, but, send out in the course of the work
Existing, (1) Data Source Excel forms are in the majority：There is more than ten section office in some enterprises and institutions, and each section office are monthly needed to enterprise
Pipe section reported data, by enterprise management, section is responsible for statistical work.These section office are needed in 5 work in a few days, complete tens of Excel reports
Table reports work.How to ensure that these users can smoothly complete to make a report on, be a great problem.(2) form species is numerous
Many, report style is complicated, and statement form Jing often changes：The form that each section office report has more than 40 kinds, and these forms have letter
Single fixed form, the dynamic elongated list for also having complexity.There is the verification relation of complexity between the information of form, also, often
The end of the year in year, report style and statistical indicator can change a lot.How in this case, it is ensured that the collection of information is not
It is affected because of the change of report style, how ensures the standardization of information and the correctness collected, be the project face
The great challenge for facing.(3) data acquisition is poorly efficient：Before no enforcement project, or each section office fill up a form on paper
Mode carry out fill message, then fax to the statistical organization or be manually submitted to enterprise management section；Will in the form of Email
Excel forms are sent to statistical organization.Then by enterprise management section by Data Enter in a special management information system, ability
Enough enter the Macro or mass analysis of row information.Start the information of making a report on from enterprise, to statistical result is finally given, two weeks is needed by one month
Time.(4) acquisition process is with high costs：The cost for printing paper list, the cost for managing these paper lists and typing
Cost of personnel etc. so that the cost of past information gathering is remained high always.(5) under information reusable low degree：In enterprise
The information of report, or being paper fax, or being electronic document, or being Email, these information, it is impossible to be supplied directly to
The practicalities such as the enterprise information management system that the enterprise management section is currently in operation, DSS, and must by special messenger by these
Information is entered into the value of competence exertion information in application system respectively.
The continuous popularization of XML so that the data processing to XML is much easier compared with Excel；There is Excel and arrives in engineering field
The distinct methods of XML data conversion：(1) unformatted conversion：This method is reading Excel document with special storehouse.Excel
File format does not have official document, but some third-party institutions once carry out reverse-engineering to which.They also generate
For decoding the storehouse of Excel file.The benefit in terms of three can be brought using this method migration Excel file:File can be transported
, on any platform, these storehouses can be easily with server set into user oneself need not derive data for row.This method
The problem of presence is that not only developer needs by calling these API to realize the conversion of data form, while also needing to compile
Write XSLT further to change the XML after conversion, because calling the XML after API conversions to have many redundancies, such as cell.
(2) csv file：This method is need not directly to operate actual electrical form compared with first method, but is changed to operate CSV
File.CSV is the common format for exchanging electronic form file.Any appropriate electrical form can pass through csv file
Derive and import.Additionally, the third-party product that can much process electrical form also all supports CSV.For example, most of accounting are soft
Part bag can process csv file.Although csv file is not XML, with XML Explorer or XMLSpy instruments be easy to by
Which is converted into XML file.The algorithm of pure XSLT conversion methods is also contains on TopXML websites.Using csv file for service
The captivation of device is some larger.User is no longer limited on certain specific platform first, but has provided the user more
Selection.User can use Lotus 1-2-3 and other electrical form instruments now, it is also possible to using being much capable of identify that
Third party's instrument of CSV forms.This method uniquely has the disadvantage that user must spend extra step, first has to Excel
Data save as CSV, and CSV is converted into XML by the crossover tool of CSV to XML then, or even are also required to write XSLT to turning
XML after changing further is changed.(3)XMLSS：The Excel (include Excel 2002 and Excel XP) of latest edition can be by
Electrical form exports as XML type file.Although cannot select wherein to use which label, can obtain really one has
The XML document of effect.Simultaneously can also use including any one the XML tool for processing including XSLT style sheet it.This form
Referred to as XMLSSo.(4) Excel XMk, mapping Microsoft Office Excel 2003 and Exce12007 not only allow for by
Excel file saves as .XML form types, while the XML schema of oneself can be defined (XSD) is added to workbook, carries out
The data separating of Excel tables, improves to the support using the machine XML.XML schema is added to after Excel workbooks, so that it may
To create an XML mapping, it is possible to import or derive data, make data become more meaningful.Therefore, user can be imported
Meet the data of self-defined XML vocabulary or framework, then the data are write back using identical XML schema.This method compared with
Other methods are more convenient, however it is necessary that writing XML schema (Schema).Schema and XSLT are required for professional to write,
Because must comply with some syntax gauges.
Although Excel can be converted to XML by first three methods, it is found that the XML for finally giving is literary using in
Shelves and it is intended that differ greatly.Perhaps the inside can include some unwanted cells, or or even XML vocabularies not
Finally want.Although Data Format Transform can be carried out using XSLT style sheet, however it is necessary that developer is each
Excel convert documents write XSLT.Although it is not too complicated to write XSLT, undoubtedly increase the development time of system.
" the Excel XML mappings " data transfer device equally introduced carries out data by the XML mapping mechanisms of Excel
Conversion, it is convenient compared with other methods；However it is necessary that for the needs of oneself to be fetched XML data, writing self-defined XML framves
Structure (XSD).It is more complicated due to writing XML schema, in systems development process, if line number will be entered to multiple Excel forms
According to conversion, then need to spend the very long development time.
It can be seen that coming, four kinds of data transfer devices all have a problem that:Data conversion needs manual intervention, needs
XSLT or Schemao are write for each Excel file.
Based on background above, herein the data transfer device of " Excel XML mappings " is improved, realizes that Excel is arrived
The data automatic conversion of XML；Whole transformation process need not write Schema without the need for manual intervention, and the Schema is basis
Excel template is automatically created.This method not only can improve system development efficiency, reduce the application system development time；Simultaneously
Make collection of the system to Excel forms more flexible, it is real to realize closing the loose misfortune of Excel collections, improve the extension of system
Property.
The content of the invention
It is an object of the invention to provide a kind of data automatic switching method of Excel XML mappings, solves prior art
Present in Excel XML datas conversion need manual intervention, the problem for causing work efficiency underground, error rate high.
The technical solution adopted in the present invention is, a kind of data automatic switching method of Excel XML mappings, specifically according to
Following steps are implemented：
Step 1, according to Excel template Automatic generation of information XML schema information and map information；
Step 2, data automatic conversion；
Step 3, execution XML data export function, produce the corresponding XML data file of Excel forms；
Step 4, to mapping cell in it is seamless importing or therefrom derive XML data file.
The characteristics of of the invention, also resides in,
Step 1 is specially：
Step (1.1), Excel template pattern is represented with tree, using each data cells as tree leaf
Child node；
Step (1.2), self-defined XML schema is generated according to formwork style structure tree, be so achieved that two-stage maps：One
It is the mapping set from formwork style to formwork structure, two is mapping of the formwork structure tree to XML schema.
Realize first order mapping is set from formwork style to formwork structure in step (1.2), it is specific as follows：
Step a, the essential information for obtaining template, template basic information include：Template type, auxiliary coordinate type, template
Data region information；
Step b, depth is carried out to the coordinate item of whole data area using recursive algorithm according to the template basic information of step a
Degree first traversal, dynamic generate formwork structure tree, specially：If template type is crosstab：By data area start element
Root of the lattice as formwork structure tree, when the start element lattice are sky, then root node title is intended certainly, and the positive lower-left of start element lattice
First order child node of the square cell as the formwork structure tree；When horizontal coordinate item columns is 2, then first order child node unit
Lattice front-right cell is the subelement lattice of the cell, is the second level child node of the tree, when horizontal coordinate item columns is 3,
By that analogy；When horizontal coordinate item traverses most right cell, begin stepping through vertical coordinate item, and using vertical coordinate item as
Descendants's cell of the most right each coordinate unit lattice of horizontal coordinate item, is traveled through, vertical coordinate item is traveled through similar to right
Horizontal coordinate item is traveled through, and unique difference is horizontal coordinate item first traversal to the right, and vertical coordinate item is downward preferential time
Go through, if template type is Classifying Sum table：Classifying Sum table is traveled through similar to the traversal to crosstab, only gone back
Classifying Sum item is traveled through, if template type is sequence list：Vertical coordinate item, formwork structure tree are begun stepping through directly
Root node title from intend.
The mapping from formwork structure tree to XML schema is realized in step (1.2), Schema is reversely generated by XML, specifically
It is as follows：
A, generation framework element：All leafy nodes of XML file are mapped as the element of Schema, the entitled XML's of element
Node title, data type are character type；
B, generation framework sequence：By the node of same level, a sequence is summarized as, sibling is placed on into a sequence
In；
C, generation framework complicated type：One sequence pair answers a complicated type；
Follow the reverse generation that the above three then realizes XML to Schema.
Step 2Excel realizes data automatic conversion by Excel XML mapping mechanisms to XML data automatic conversion, specifically
Method is that step is poly- as follows by calling the partial objects in Excel com components, realizing data automatic conversion：
Step (2.1), loading framework：In Excel, XMLMap objects represent one or more frameworks and its to electronic watch
The mapping of lattice, will load new XML mappings, then a new framework must be added in XMLMaps set；
Step (2.2), foundation mapping：After adding framework in workbook, cell or scope are mapped to into XML schema
In element, using the XPath sentences that element or attribute are specified in framework, element and attribute are mapped to into electronics from the framework
The cell of form；
Step (2.3), derivation data：After loading framework and foundation mapping, using the Export methods of Workbook objects,
Structure creates document.
Step 3 is specific as follows：
Step (3.1), the essential information for firstly generating template, template Schema information and cell map information：
A, generation template basic information：Realize the function of template basic information is generated according to formwork style, generate template base
The function of this information be generate template associated description information, template basic information include the start element case of tables of data put,
The columns of the line number and vertical coordinate of the ranks number of tables of data, the type of tables of data, auxiliary coordinate type and abscissa, wherein,
The type of tables of data includes sequence list, crosstab and Classifying Sum table, auxiliary coordinate type include abscissa, vertical coordinate, both
Have, both without；
B, generation template Schema information：Realize the function of template framework is generated according to formwork style；
C, generation Template Map information：Realize generating the function in template framework path and the map information of modular unit lattice；
D, generation inquiry form：Realize report query function；
Step (3.2), extraction XML data：The function of extracting XML data is to realize Excel turning automatically to XML data
Change, data automatic conversion will be realized, first have to perform the Excel template corresponding to Excel forms generation template framework information
With generation Template Map information, template Schema information and Template Map information are generated；Then, the template Schema information of generation is added
It is added to the workbook of Excel forms；Again, the mapping of cell and template framework path is set up according to Template Map information；Most
Afterwards, XML data export function is performed, produces the XML data file corresponding to Excel forms.
The invention has the beneficial effects as follows, a kind of data automatic switching method of Excel XML mappings, by solving XML framves
Structure and map information automatically generate problem, realize Excel to the automatic conversion of XML data by Excel XML mapping mechanisms,
By (formwork style to formwork structure tree and formwork structure tree are to XML schema) is mapped using two-stage, according to Excel template pattern
XML schema is automatically generated, the mapping path of each data cells is recorded automatically in framework generating process；Then by Excel
Excel report datas are automatically converted into the XML data file corresponding with formwork style by XML mappings.
Specific embodiment
With reference to specific embodiment, the present invention is described in detail.
A kind of data automatic switching method of Excel XML mappings of the present invention, by Excel XML mapping mechanisms, realizes
The conversion step of Excel to XML data is poly-：First, self-defined XML schema is write according to actual needs；Secondly, will write
XML schema be added in the book opened；Then, use " XML source " that cell is mapped to framework element；Finally,
Just XML data can be derived to seamless importing in the cell of mapping or therefrom.When XML schema is added to book,
Excel can create an object for being referred to as XML mappings.Cell or scope just can be mapped to XML schema by these XML mappings
In element.When importing or derive XML data, Excel is also using these mappings by the content of mapping range and framework
Element links together.Book can be mapped comprising many XML, and each mapping is independently of each every other mapping.
Realize that Excel to XML data change by MS Excel XML mapping mechanisms according to described above, it is not ugly
Go out：The whole process that Excel is converted to XML is needed into manual intervention, operator is not required nothing more than and is write XML schema
(Schema), while mapping must be set up by hand, cell is bound with framework element.Therefore Excel to be realized to XML data
Automatic conversion, it is necessary to solve XML schema and map information automatically generates problem.The emphasis of this paper is exactly by more than solution
XML schema and map information automatically generate problem, then realize that Excel arrives XML data by Excel XML mapping mechanisms
Automatic conversion.Its basic thought is that (formwork style to formwork structure tree and formwork structure tree are to XML framves by being mapped using two-stage
Structure), XML schema is automatically generated according to Excel template pattern, reflecting for each data cells is recorded automatically in framework generating process
Rays footpath；Then Excel report datas are automatically converted into into the XML number corresponding with formwork style by Excel XML mappings
According to file.
Comprise the following steps that：
Step 1, according to Excel template Automatic generation of information XML schema information and map information；
Step 2, data automatic conversion；
Step 3, execution XML data export function, produce the corresponding XML data file of Excel forms；
Step 4, to mapping cell in it is seamless importing or therefrom derive XML data file.
Wherein,
Step 1 is specially：
Step (1.1), Excel template pattern is represented with tree, using each data cells as tree leaf
Child node；
Step (1.2), self-defined XML schema is generated according to formwork style structure tree, be so achieved that two-stage maps：One
It is the mapping set from formwork style to formwork structure, two is mapping of the formwork structure tree to XML schema.
Realize first order mapping is set from formwork style to formwork structure in step (1.2), it is specific as follows：
Step a, the essential information for obtaining template, template basic information include：Template type, auxiliary coordinate type, template
Data region information；
Step b, depth is carried out to the coordinate item of whole data area using recursive algorithm according to the template basic information of step a
Degree first traversal, dynamic generate formwork structure tree, specially：If template type is crosstab：By data area start element
Root of the lattice as formwork structure tree, when the start element lattice are sky, then root node title is intended certainly, and the positive lower-left of start element lattice
First order child node of the square cell as the formwork structure tree；When horizontal coordinate item columns is 2, then first order child node unit
Lattice front-right cell is the subelement lattice of the cell, is the second level child node of the tree, when horizontal coordinate item columns is 3,
By that analogy；When horizontal coordinate item traverses most right cell, begin stepping through vertical coordinate item, and using vertical coordinate item as
Descendants's cell of the most right each coordinate unit lattice of horizontal coordinate item, is traveled through, vertical coordinate item is traveled through similar to right
Horizontal coordinate item is traveled through, and unique difference is horizontal coordinate item first traversal to the right, and vertical coordinate item is downward preferential time
Go through.If template type is Classifying Sum table：Classifying Sum table is traveled through similar to the traversal to crosstab, only gone back
Classifying Sum item is traveled through, if template type is sequence list：Vertical coordinate item, formwork structure tree are begun stepping through directly
Root node title from intend.
The mapping from formwork structure tree to XML schema is realized in step (1.2), Schema is reversely generated by XML, specifically
It is as follows：
A, generation framework element：All leafy nodes of XML file are mapped as the element of Schema, the entitled XML's of element
Node title, data type are character type；
B, generation framework sequence：By the node of same level, a sequence is summarized as, sibling is placed on into a sequence
In；
C, generation framework complicated type：One sequence pair answers a complicated type；
Follow the reverse generation that the above three then realizes XML to Schema.
Step 2Excel realizes data automatic conversion by Excel XML mapping mechanisms to XML data automatic conversion, specifically
Method is that step is poly- as follows by calling the partial objects in Excel com components, realizing data automatic conversion：
Step (2.1), loading framework：In Excel, XMLMap objects represent one or more frameworks and its to electronic watch
The mapping of lattice, will load new XML mappings, then a new framework must be added in XMLMaps set；
Step (2.2), foundation mapping：After adding framework in workbook, cell or scope are mapped to into XML schema
In element, using the XPath sentences that element or attribute are specified in framework, element and attribute are mapped to into electronics from the framework
The cell of form；
Step (2.3), derivation data：After loading framework and foundation mapping, using the Export methods of Workbook objects,
Structure creates document.
Step 3 is specific as follows：
Step (3.1), the essential information for firstly generating template, template Schema information and cell map information：
A, generation template basic information：Realize the function of template basic information is generated according to formwork style, generate template base
The function of this information be generate template associated description information, template basic information include the start element case of tables of data put,
The columns of the line number and vertical coordinate of the ranks number of tables of data, the type of tables of data, auxiliary coordinate type and abscissa, wherein,
The type of tables of data includes sequence list, crosstab and Classifying Sum table, auxiliary coordinate type include abscissa, vertical coordinate, both
Have, both without；
B, generation template Schema information：Realize the function of template framework is generated according to formwork style；
C, generation Template Map information：Realize generating the function in template framework path and the map information of modular unit lattice；
D, generation inquiry form：Realize report query function；
Step (3.2), extraction XML data：The function of extracting XML data is to realize Excel turning automatically to XML data
Change, data automatic conversion will be realized, first have to perform the Excel template corresponding to Excel forms generation template framework information
With generation Template Map information, template Schema information and Template Map information are generated；Then, the template Schema information of generation is added
It is added to the workbook of Excel forms；Again, the mapping of cell and template framework path is set up according to Template Map information；Most
Afterwards, XML data export function is performed, produces the XML data file corresponding to Excel forms.
Below above-mentioned steps are described in detail：
First, Schema information and map information are automatically generated：
(1), the core concept of Schema information automatic generating calculation：
First Excel template pattern is represented with tree, and is tied using each data cells as the leaf of tree
Point；Secondly self-defined XML schema is generated according to formwork style structure tree.Realize that two-stage maps：One is from formwork style to mould
The mapping of hardened Broussonetia papyrifera, two is mapping of the formwork structure tree to XML schema.
(2), realize the algorithm design of two-stage mapping：
Realize the algorithm of first order mapping is set from formwork style to formwork structure：First have to obtain the essential information of template,
Template basic information includes：
A, template type：By to most of Excel statement analysiss, Excel report datas region is probably divided into three
Class：The characteristics of sequence list, crosstab and Classifying Sum table, sequence list is that top is coordinate item, and lower section is data；The spy of crosstab
Point is that mid portion is that data, top and left side are its coordinate item, wherein top coordinate item is commonly referred to horizontal coordinate item, left side
Coordinate item is commonly referred to vertical coordinate item；The characteristics of Classifying Sum table is a kind of special crosstab, and each sorting item has a conjunction
Meter item.
B, auxiliary coordinate type：In some reporting systems, in order to simplify the generation of complex report forms (multidimensional form), form
Complex report forms are changed into two-dimentional form using auxiliary item by designer.Auxiliary coordinate item is divided into four classes：Row coordinate, row coordinate, two
Person or nothing.
C, template data area information：Template data region is with closed area as mark, the i.e. unit in closed area
Lattice are data area.Template data area information includes：The position of data area start element lattice, i.e., the starting in worksheet
Position (row and column coordinate number)；The ranks number of data area coordinate item, the i.e. row of the columns of horizontal coordinate item and vertical coordinate item
Number and the line number and columns of data area.
Secondly, depth-first time is carried out to the coordinate item of whole data area using recursive algorithm according to template basic information
Go through, dynamic generates formwork structure tree, its main thought is as follows:
If template type is crosstab：(1) using data area start element lattice as formwork structure tree root, when this rises
Beginning cell is sky, then root node title can be from plan, such as project or template name.And the positive lower left unit of start element lattice
First order child node of the lattice as the formwork structure tree.(2) when horizontal coordinate item columns is 2, then first order child node cell
(Merge Cells) front-right cell is the subelement lattice of the cell, is the second level child node of the tree.When level is sat
Mark item columns is 3, by that analogy.(3) when horizontal coordinate item traverses most right cell, then vertical coordinate item is begun stepping through,
And vertical coordinate item is traveled through as descendants's cell of the most right each coordinate unit lattice of horizontal coordinate item.And to vertical seat
Mark item is traveled through similar to traveling through to horizontal coordinate item, and unique difference is horizontal coordinate item first traversal to the right, and
The downward first traversal of vertical coordinate item.
If template type is Classifying Sum table：Classifying Sum table is traveled through similar to the traversal to crosstab, only
But also Classifying Sum item to be traveled through.If template type is sequence list：Vertical coordinate item is begun stepping through directly.This mould
The root node title of hardened Broussonetia papyrifera can be from plan, such as project or template name.
Realize the algorithm from formwork structure tree to the mapping of the second level of self-defined XML schema：
The algorithm that Schema is reversely generated by XML is as follows：
(1) generate framework element (Element):All leafy nodes of XML file are mapped as the element of Schema, element
Node title of the name (name) for XML, data type are character type (str i ng).
(2) generate framework sequence (Sequence)：By the node of same level, a sequence is summarized as.Fraternal will tie
Point is put in one sequence.
(3) generate framework complicated type (ComplexType)：One sequence pair answers a complicated type.
Follow as above three reverse generations for being capable of achieving XML to Schema.
Map information is automatically generated：
Realizing setting in first order mapping process, by adding in recursive algorithm from formwork style to formwork structure
The parameter of recording unit lattice map information, can be with automatically generated data cell map information.
2nd, data automatic conversion：
Excel is finally to realize that data turn automatically by Excel XML mapping mechanisms to XML data automatic conversion component
Change, concrete grammar is：By calling the partial objects in Excel com components, data automatic conversion is realized.Step is poly- as follows:
(1) load framework：In Excel, XMLMap objects represent one or more frameworks and its reflecting to electrical form
Penetrate.New XML mappings are loaded, then a new framework must be added in XMLMaps set.
(2) set up mapping：After adding framework in workbook, next need for cell or scope to be mapped to XML
Element in framework.Using the XPath sentences that element or attribute are specified in framework, element and attribute can be reflected from the framework
It is mapped to the cell of electrical form.
(3) data are derived：After loading framework and foundation mapping, it is possible to use the Export methods of Workbook objects, structure
Create document.
3rd, the design of data automatic conversion component and realization：
(1) component use case description：The major function of data automatic conversion component realizes Excel report datas to XML
Automatic conversion, shows to the research of XML data automatic switching method according to Excel described above, Excel to be realized is arrived
The automatic conversion of XML, it is necessary to generate essential information, template Schema information and the cell map information of template.
(1.1), generate template basic information：Realize the function of template basic information is generated according to formwork style, generate mould
The major function of plate essential information is the associated description information for generating template, and template basic information includes the start element of tables of data
Case is put, the type of the ranks number of tables of data, tables of data (sequence list, crosstab and Classifying Sum table), auxiliary coordinate type (horizontal stroke
Coordinate, vertical coordinate, both have, both without) and abscissa line number and vertical coordinate columns.
(1.2), generate template Schema information：Realize the function of template framework is generated according to formwork style.
(1.3), generate Template Map information：Realize generating the work(in template framework path and the map information of modular unit lattice
Energy.
(1.4), generate inquiry form：Realize report query function.
(1.5), extract XML data：The major function for extracting XML data is to realize Excel turning automatically to XML data
Change, be the key function of component.Data automatic conversion is realized, first has to perform the Excel template corresponding to Excel forms
Generate template framework information and generate Template Map information, generate template Schema information and Template Map information；Then, will generate
Template Schema information be added to the workbook of Excel forms；Again, cell and template frame are set up according to Template Map information
The mapping in structure path；Finally, XML data export function is performed, produces the XML data file corresponding to Excel forms.
4th, component Dynamic behavior model：
(1), template registration sequential chart：Template is registered and is reflected for generating template basic information, template Schema information and template
Information is penetrated, while after only registered template, data conversion could be carried out to the form based on this template, after conversion
XML data is to meet template framework (Schema).Template basic information to be generated, template Schema information and Template Map information are needed
Object DataConvertor, Template, Schema and SerializeHelper is wanted to cooperate, concrete cooperation is as follows：
First, the correlation technique of Template classes is called according to Excel template pattern, template basic information is generated, is returned
TemplateInfo objects；Secondly, template framework is generated according to formwork style and TemplateInfo object reference Schema classes
Information and framework map information, return SchemaInfo information and CelIMapInfo objects；Again, call
TemplateInfo and Ce11MapInfo objects, sequence are turned to XML form by the sequencing method of SerializeHelper classes；
Finally, by TemplateInfo, category of the SchemaInfo and Ce11MapInfo XML informations as TemplateEntity objects
Property value, and return TemplateEntity objects.
(2), extract XML data sequential chart：
The Core Feature that XML data is data automatic conversion component is extracted, but the data of certain form will be extracted, it is necessary to first
Template corresponding to which is registered, XML data extraction can not be otherwise carried out.Realize that XML data abstraction function needs right
As DataConvertor, Report, Ce11Map and SerializeHelper are cooperated, and concrete cooperation is as follows：
First, Report files and TemplateEntity objects are passed to into Report objects；Secondly, Report objects are adjusted
Ce11Map objects are used, is mapped with cell is set up to Report files addition framework；Finally, the XML numbers of Report objects are called
According to deriving method, XML data is derived, and is returned it into.
(3), generate inquiry form sequential chart：
The expanded function that inquiry form is data automatic conversion component is generated, and form is formed for XML is closed.Realize looking into
Asking report generation needs DataConvertor, and Report cooperations, concrete cooperation are as follows：
First, TemplateEntity objects and ReportEntity objects are passed to into Report objects；Secondly, call
The inquiry report form generation method of Report objects, generates form；Finally, the inquiry form for being generated is returned.
5th, the realization of data automatic conversion component：
(1), the general frame of data automatic conversion component：Excel to XML data automatic conversion component by template registration,
Data are extracted and three module compositions of report query.Wherein：Template Registering modules complete Excel template relevant information and generate work(
Energy；Data extraction module completes Excel data extraction functions；Report query module completes Excel Making Dynamic Report Forms functions.
(2), object serialization and unserializing：Data automatic conversion component includes template basic information, modular unit lattice
Three kinds of XML configuration documentations of map information and form essential information, wherein template basic information and modular unit lattice map information are used
In description template, data extraction and query generation, and form essential information are carried out for converging to classification for creating dynamic template
Total template carries out formwork style and regenerates.In order to more easily be processed to these configuration documentations, object is converted thereof into,
Then object is operated.XML document is related to serialize the concept with unserializing with the mutual conversion of object, wherein will be right
As the process for being converted into XML is referred to as the serializing of object, the process that XML is converted to object is referred to as into the unserializing of object.
Based on this Excel to XML data automatic conversion component, inventor successfully develops the conventional data based on Excel
Gather and collect platform (hereinafter referred to as platform), in Shandong colliery enterprise management section commencement of commercial operation four months, in run duration
The favorable comment of user is obtained.Platform utilizes XML by spreadsheet software Microsoft Exce12003 and large data library management
System MS SQL Server2005 carry out integrated.The platform gives full play to the application level of Excel, by design template, definition
Enterprises scattered Excel forms easily, are rapidly managed concentratedly, and are passed through by the operation of the Simple visual such as formula between table
The form of custom built forms carries out data summarization analysis, to support decision-making.The platform is easy to the system integration, can adapt to business event
Change and the efficiency for improving information, collecting and analyze.
Claims (6)
1. the data automatic switching method that a kind of Excel XML map, it is characterised in that specifically implement according to following steps：
Step 1, according to Excel template Automatic generation of information XML schema information and map information；
Step 2, data automatic conversion；
Step 3, execution XML data export function, produce the corresponding XML data file of Excel forms；
Step 4, to mapping cell in it is seamless importing or therefrom derive XML data file.
2. the data automatic switching method that a kind of Excel XML according to claim 1 map, it is characterised in that step 1
Specially：
Step (1.1), Excel template pattern is represented with tree, using each data cells as tree leaf knot
Point；
Step (1.2), self-defined XML schema is generated according to formwork style structure tree, be so achieved that two-stage maps：One be from
Mapping of the formwork style to formwork structure tree, two is mapping of the formwork structure tree to XML schema.
3. the data automatic switching method that a kind of Excel XML according to claim 2 map, it is characterised in that described
Realize first order mapping is set from formwork style to formwork structure in step (1.2), it is specific as follows：
Step a, the essential information for obtaining template, template basic information include：Template type, auxiliary coordinate type, template data
Area information；
Step b, depth is carried out to the coordinate item of whole data area using recursive algorithm according to the template basic information of step a
Degree first traversal, dynamic generate formwork structure tree, specially：If template type is crosstab：By data area start element
Root of the lattice as formwork structure tree, when the start element lattice are sky, then root node title is intended certainly, and the positive lower-left of start element lattice
First order child node of the square cell as the formwork structure tree；When horizontal coordinate item columns is 2, then first order child node unit
Lattice front-right cell is the subelement lattice of the cell, is the second level child node of the tree, when horizontal coordinate item columns is 3,
By that analogy；When horizontal coordinate item traverses most right cell, begin stepping through vertical coordinate item, and using vertical coordinate item as
Descendants's cell of the most right each coordinate unit lattice of horizontal coordinate item, is traveled through, vertical coordinate item is traveled through similar to right
Horizontal coordinate item is traveled through, and unique difference is horizontal coordinate item first traversal to the right, and vertical coordinate item is downward preferential time
Go through, if template type is Classifying Sum table：Classifying Sum table is traveled through similar to the traversal to crosstab, only gone back
Classifying Sum item is traveled through, if template type is sequence list：Vertical coordinate item, formwork structure tree are begun stepping through directly
Root node title from intend.
4. the data automatic switching method that a kind of Excel XML according to claim 2 map, it is characterised in that described
The mapping from formwork structure tree to XML schema is realized in step (1.2), Schema is reversely generated by XML, it is specific as follows：
A, generation framework element：All leafy nodes of XML file are mapped as the element of Schema, the node of the entitled XML of element
Title, data type are character type；
B, generation framework sequence：By the node of same level, a sequence is summarized as, sibling is put in one sequence；
C, generation framework complicated type：One sequence pair answers a complicated type；
Follow the reverse generation that the above three then realizes XML to Schema.
5. the data automatic switching method that a kind of Excel XML according to claim 1 map, it is characterised in that described
Step 2Excel realizes data automatic conversion by Excel XML mapping mechanisms to XML data automatic conversion, and concrete grammar is logical
The partial objects called in Excel com components are crossed, data automatic conversion is realized, step is poly- as follows：
Step (2.1), loading framework：In Excel, XMLMap objects represent one or more frameworks and its arrive electrical form
Mapping, will load new XML mappings, then a new framework must be added in XMLMaps set；
Step (2.2), foundation mapping：After adding framework in workbook, cell or scope are mapped in XML schema
Element and attribute, using the XPath sentences that element or attribute are specified in framework, are mapped to electrical form from the framework by element
Cell；
Step (2.3), derivation data：After loading framework and foundation mapping, using the Export methods of Workbook objects, structure wound
Build document.
6. the data automatic switching method that a kind of Excel XML according to claim 1 map, it is characterised in that described
Step 3 is specific as follows：
Step (3.1), the essential information for firstly generating template, template Schema information and cell map information：
A, generation template basic information：Realize the function of template basic information is generated according to formwork style, generate template and believe substantially
The function of breath be generate template associated description information, template basic information include the start element case of tables of data put, data
The columns of the line number and vertical coordinate of the ranks number of table, the type of tables of data, auxiliary coordinate type and abscissa, wherein, data
The type of table includes sequence list, crosstab and Classifying Sum table, auxiliary coordinate type include abscissa, vertical coordinate, both have,
Both without；
B, generation template Schema information：Realize the function of template framework is generated according to formwork style；
C, generation Template Map information：Realize generating the function in template framework path and the map information of modular unit lattice；
D, generation inquiry form：Realize report query function；
Step (3.2), extraction XML data：The function of extracting XML data is the automatic conversion for realizing Excel to XML data,
Data automatic conversion is realized, first has to generation template framework information and generation be performed to the Excel template corresponding to Excel forms
Template Map information, generates template Schema information and Template Map information；Then, the template Schema information of generation is added to
The workbook of Excel forms；Again, the mapping of cell and template framework path is set up according to Template Map information；Finally, hold
Row XML data export function, produces the XML data file corresponding to Excel forms.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610898335.9A CN106547841A (en) | 2016-10-14 | 2016-10-14 | A kind of data automatic switching method of Excel XML mappings |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610898335.9A CN106547841A (en) | 2016-10-14 | 2016-10-14 | A kind of data automatic switching method of Excel XML mappings |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN106547841A true CN106547841A (en) | 2017-03-29 |
Family
ID=58369107
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610898335.9A Pending CN106547841A (en) | 2016-10-14 | 2016-10-14 | A kind of data automatic switching method of Excel XML mappings |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106547841A (en) |
Cited By (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107609302A (en) * | 2017-09-28 | 2018-01-19 | 广州明珞汽车装备有限公司 | A kind of Product Process structure generation method and system |
| CN107609131A (en) * | 2017-09-18 | 2018-01-19 | 中国银行股份有限公司 | A kind of report file generation method and device |
| CN108197789A (en) * | 2017-12-26 | 2018-06-22 | 中国神华能源股份有限公司 | Processing method and storage medium to the data for assessing railway transport capacity |
| CN109543157A (en) * | 2018-10-23 | 2019-03-29 | 深圳市海勤科技有限公司 | Android scheme OSD language transfer method |
| CN109684609A (en) * | 2018-11-28 | 2019-04-26 | 陕西天诚软件有限公司 | A kind of Excel template generation and data conversion and introduction method based on ASP.NET MVC |
| CN110597708A (en) * | 2019-08-02 | 2019-12-20 | 北京奇艺世纪科技有限公司 | Test case file conversion method and device, electronic equipment and storage medium |
| CN110705226A (en) * | 2019-08-22 | 2020-01-17 | 平安科技（深圳）有限公司 | Spreadsheet creating method and device and computer equipment |
| CN110765741A (en) * | 2019-09-09 | 2020-02-07 | 重庆金融资产交易所有限责任公司 | Data processing method and device, computer equipment and storage medium |
| CN111831382A (en) * | 2020-07-20 | 2020-10-27 | 杭州品茗安控信息技术股份有限公司 | Data entry method, device, equipment and medium for engineering cost software |
| CN112560415A (en) * | 2020-12-18 | 2021-03-26 | 深圳市元征科技股份有限公司 | Data processing method, device, equipment and medium |
| CN112685601A (en) * | 2021-01-31 | 2021-04-20 | 重庆渝高科技产业(集团)股份有限公司 | Data extraction method and system for engineering measurement list |
| CN117540704A (en) * | 2024-01-10 | 2024-02-09 | 智慧足迹数据科技有限公司 | Data reverse perspective conversion method, device, equipment and medium of data table |
| CN117540704B (en) * | 2024-01-10 | 2024-04-30 | 智慧足迹数据科技有限公司 | Data reverse perspective conversion method, device, equipment and medium of data table |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101122899A (en) * | 2007-09-18 | 2008-02-13 | 杭州华三通信技术有限公司 | Report generation method and device |
| CN102270226A (en) * | 2011-06-28 | 2011-12-07 | 用友软件股份有限公司 | Budget data summarization system |
-
2016
- 2016-10-14 CN CN201610898335.9A patent/CN106547841A/en active Pending
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101122899A (en) * | 2007-09-18 | 2008-02-13 | 杭州华三通信技术有限公司 | Report generation method and device |
| CN102270226A (en) * | 2011-06-28 | 2011-12-07 | 用友软件股份有限公司 | Budget data summarization system |
Non-Patent Citations (1)
| Title |
|---|
| 白仲贵: ""Excel到XML数据自动转换"", 《中国优秀硕士学位论文全文数据库 信息科技辑》 * |
Cited By (18)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107609131B (en) * | 2017-09-18 | 2020-04-17 | 中国银行股份有限公司 | Report file generation method and device |
| CN107609131A (en) * | 2017-09-18 | 2018-01-19 | 中国银行股份有限公司 | A kind of report file generation method and device |
| CN107609302A (en) * | 2017-09-28 | 2018-01-19 | 广州明珞汽车装备有限公司 | A kind of Product Process structure generation method and system |
| CN107609302B (en) * | 2017-09-28 | 2021-12-28 | 广州明珞汽车装备有限公司 | Method and system for generating product process structure |
| CN108197789A (en) * | 2017-12-26 | 2018-06-22 | 中国神华能源股份有限公司 | Processing method and storage medium to the data for assessing railway transport capacity |
| CN109543157A (en) * | 2018-10-23 | 2019-03-29 | 深圳市海勤科技有限公司 | Android scheme OSD language transfer method |
| CN109543157B (en) * | 2018-10-23 | 2021-02-05 | 深圳市海勤科技有限公司 | OSD language conversion method of android scheme |
| CN109684609A (en) * | 2018-11-28 | 2019-04-26 | 陕西天诚软件有限公司 | A kind of Excel template generation and data conversion and introduction method based on ASP.NET MVC |
| CN109684609B (en) * | 2018-11-28 | 2022-10-04 | 陕西天诚软件有限公司 | ASP (active Server Page) and NET (Internet vehicle networking) MVC (model view controller) based Excel template generation and data conversion and import method |
| CN110597708A (en) * | 2019-08-02 | 2019-12-20 | 北京奇艺世纪科技有限公司 | Test case file conversion method and device, electronic equipment and storage medium |
| CN110705226A (en) * | 2019-08-22 | 2020-01-17 | 平安科技（深圳）有限公司 | Spreadsheet creating method and device and computer equipment |
| CN110765741A (en) * | 2019-09-09 | 2020-02-07 | 重庆金融资产交易所有限责任公司 | Data processing method and device, computer equipment and storage medium |
| CN111831382A (en) * | 2020-07-20 | 2020-10-27 | 杭州品茗安控信息技术股份有限公司 | Data entry method, device, equipment and medium for engineering cost software |
| CN111831382B (en) * | 2020-07-20 | 2022-07-08 | 杭州品茗安控信息技术股份有限公司 | Data entry method, device, equipment and medium for engineering cost software |
| CN112560415A (en) * | 2020-12-18 | 2021-03-26 | 深圳市元征科技股份有限公司 | Data processing method, device, equipment and medium |
| CN112685601A (en) * | 2021-01-31 | 2021-04-20 | 重庆渝高科技产业(集团)股份有限公司 | Data extraction method and system for engineering measurement list |
| CN117540704A (en) * | 2024-01-10 | 2024-02-09 | 智慧足迹数据科技有限公司 | Data reverse perspective conversion method, device, equipment and medium of data table |
| CN117540704B (en) * | 2024-01-10 | 2024-04-30 | 智慧足迹数据科技有限公司 | Data reverse perspective conversion method, device, equipment and medium of data table |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106547841A (en) | A kind of data automatic switching method of Excel XML mappings | |
| Abiteboul et al. | Non first normal form relations: An algebra allowing data restructuring | |
| CN103729337B (en) | report conversion method and device | |
| US20050183002A1 (en) | Data and metadata linking form mechanism and method | |
| US20120303645A1 (en) | System and method for extraction of structured data from arbitrarily structured composite data | |
| CN110119395B (en) | Method for realizing association processing of data standard and data quality based on metadata in big data management | |
| CN106372044A (en) | Method for generating typed dimension XBRL (Extensible Business Reporting Language) report based on report form | |
| CN108694214A (en) | Generation method, generating means, readable medium and the electronic equipment of data sheet | |
| CN102193906A (en) | Method for automatically introducing examination paper in WORD format into database system | |
| CN102208057A (en) | Method, platform and system for processing audit information | |
| CN107220274A (en) | One kind visualization data-interface fairground implementation method | |
| CN103092631A (en) | Database application system development platform and development method | |
| CN102117289A (en) | Method and device for extracting comment content from webpage | |
| CN104616134A (en) | Statement analysis report pushing method and device | |
| Gruber et al. | Semantic web technologies applied to numismatic collections | |
| EP1745390A2 (en) | Data and metadata linking form mechanism and method | |
| CN101606151A (en) | The method and apparatus in opening relationships type list data storehouse | |
| US20060101329A1 (en) | Pivot analysis with XML/XSL mechanism | |
| CN115510834A (en) | Laboratory data digital management system and method | |
| CN115080594A (en) | Method and system for carrying out multi-dimensional analysis on data and electronic equipment | |
| CN104021211B (en) | Method for generating notes of soil spatial data graph | |
| CN104346393A (en) | Building method of atomic data element models | |
| Kelly | A matrix editor for a metaCASE environment | |
| CN107609155B (en) | Construction method of data asset model based on XBRL standard | |
| TWM578817U (en) | Processing system for converting data of data system into relational data format |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20170329 |