Method for quickly creating an Excel file, quick creation device, and storage medium

Abstract

The present invention provides a method for quickly creating an Excel file, a quick creation device, and a computer-readable storage medium. The method includes: obtaining a data model, where the data model includes a data structure; setting data in the data model, the data including at least one array, sorting the array according to the data structure in the data model, and obtaining the positioning of the array in the data structure according to the sorting; obtaining an Excel template file and rendering the data model in the Excel template file; writing the positioning of the array in the data structure into the Excel template file; generating an Excel output file according to the Excel template file, and exporting the Excel output file. This facilitates the user to only change the values in the subsequent process and generate an Excel file with the required style according to the preset style generated by the data in the Excel template file, simplifies the code writing, eliminates the complicated style writing logic, and ensures that the appearance style can be freely configured. The present invention is also applicable to the field of blockchain technology.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and particularly to a method for quickly creating an Excel file, a quick creation device, and a computer-readable storage medium. Background Art

[0002] In the prior art, the following methods are mainly used for quickly creating an Excel file: Apache POI: The documentation is incomplete, the usage threshold is high, and a large amount of code needs to be written for reading and writing Excel files; Freemarker: Only supports text and has great limitations, is complex to use, and also requires maintaining an XML structure and is basically not iterable; OpenOffice: Requires deploying the OpenOffice software, has poor portability, and requires understanding the complex OpenOffice API; HTML browser export: Depends on the implementation of the browser, has poor portability, and the HTML exports a CVS format file, not a formal Excel format, and cannot be well compatible with Microsoft Excel; Jacob, winlib: Developed based on the Windows platform, with poor portability and complex usage; EasyExcel: Unable to edit styles and unable to add additional text information to the Excel.

[0003] Based on the above analysis, it can be found that there are more or less defects in portability, compatibility, and usability in the prior art for quickly creating an Excel file; portability means that the technology can be used on platforms such as window and linux; compatibility means whether the exported Excel file can be normally opened by software such as Microsoft Excel, WPS, Pages, etc., and the styles will not be disordered; usability means that the process of implementing the export of Excel is simple, does not depend on other software services, and the called api is simple. Summary of the Invention

[0004] The present invention provides a method for quickly creating an Excel file, a quick creation device, and a computer-readable storage medium. Its main purpose is to set data in a data model and render the data model into an Excel template file, and create and export an Excel output file in the rendered Excel template file, thereby realizing the portability, compatibility, and usability of the Excel output file.

[0005] To achieve the above object, the present invention provides a method for quickly creating an Excel file, and the method includes:

[0006] Obtain a data model, where the data model includes a data structure;

[0007] Set data in the data model, where the data includes at least one array, sort the array according to the data structure in the data model, and obtain the location of the array in the data structure based on the sorting;

[0008] Obtain an Excel template file and render the data model in the Excel template file;

[0009] Write the location of the array in the data structure into the Excel template file;

[0010] Generate an Excel output file based on the Excel template file and export the Excel output file.

[0011] Preferably, the step of setting data in the data model, where the data includes at least one array, sorting the array according to the data structure in the data model, and obtaining the location of the array in the data structure based on the sorting includes:

[0012] Set two or more elements in the data model and determine whether the two or more elements are the same;

[0013] When two or more elements are the same, arrange the elements in order to form array data;

[0014] When two or more elements are different, obtain the keyword of the current element and map it to the array corresponding to the keyword to form data combining a chain and an array.

[0015] Preferably, the step of setting data in the data model, where the data includes at least one array, sorting the array according to the data structure in the data model, and obtaining the location of the array in the data structure based on the sorting further includes:

[0016] Set multiple elements in the data model and compare the number of elements with the preset number of elements;

[0017] When the multiple elements exceed the preset number of elements, expand the array;

[0018] When the multiple elements do not exceed the preset number of elements, execute the step of determining whether the multiple elements are the same.

[0019] Preferably, the step of expanding the array when the multiple elements exceed the preset number of elements includes:

[0020] When the multiple elements exceed the preset number of elements, obtain the current array length, and create a new array length that is a power of 2 times the current array length;

[0021] Extract the elements in the current array and transfer the elements to the new array.

[0022] Preferably, the step of obtaining the Excel template file and rendering the data model in the Excel template file includes:

[0023] Obtain the nodes and tree of the virtual DOM in the data model according to the rendering function;

[0024] Render the nodes and tree of the virtual DOM to the Excel template file.

[0025] Preferably, the step of generating an Excel output file according to the Excel template file and exporting the Excel output file includes:

[0026] Create an Excel output file according to the rendered Excel template file and write the control logic of the data model, wherein the control logic of the data model includes: horizontal / vertical merging of cells, dropdown boxes, links, background colors of cells, border display, horizontal / vertical function formula calculation values;

[0027] Describe the export process and export the Excel output file.

[0028] Preferably, the step of describing the export process and exporting the Excel output file includes:

[0029] Obtain the data change situation of the mapping of the nodes and tree of the virtual DOM;

[0030] Describe the export process in combination with the control logic of the data model;

[0031] Export the Excel output file.

[0032] In addition, to achieve the above object, the present invention also provides a device for quickly making an Excel file. The quick-making device includes: a memory and a processor. The memory includes a program for quickly making an Excel file. When the program for quickly making an Excel file is executed by the processor, the following steps are implemented:

[0033] Obtain a data model, wherein the data model includes a data structure;

[0034] Set two or more elements in the data model and determine whether the two or more elements are the same;

[0035] When the two or more elements are different, obtain the keyword of the current element and map it to an array of corresponding keywords to form data combined with a chain and an array;

[0036] When the two or more elements are the same, arrange the elements in order to form array data;

[0037] Sort the array according to the data structure in the data model, and obtain the location of the array in the data structure based on the sorting;

[0038] Obtain an Excel template file and render the data model in the Excel template file;

[0039] Write the location of the array in the data structure into the Excel template file;

[0040] Generate an Excel output file according to the Excel template file and export the Excel output file.

[0041] Preferably, the steps of setting two or more elements in the data model include:

[0042] Set multiple elements in the data model and compare the number of elements with the preset number of elements;

[0043] When the multiple elements exceed the preset number of elements, expand the array;

[0044] When the multiple elements do not exceed the preset number of elements, execute the step of judging whether the multiple elements are the same.

[0045] In addition, to achieve the above object, the present invention also provides a computer-readable storage medium, which includes a program for quickly creating an Excel file. When the program for quickly creating an Excel file is executed by a processor, the steps of the method for quickly creating an Excel file as described above are implemented.

[0046] The method for quickly creating an Excel file, the quick production device and the computer-readable storage medium proposed by the present invention organize data by adopting a data model, render the organized data in the data model into an Excel template file, so as to present the data regularly by using the style of Excel. Finally, an Excel output file is created based on the rendered Excel template file, and the Excel output file is exported. So that the user only needs to change the numerical value in the subsequent process, and according to the preset style generated by the data in the Excel template file, the required style of Excel file can be generated, and the code writing is simplified, the complicated style writing logic is eliminated, and the appearance style can be freely configured. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Figure 1 It is a schematic diagram of the application environment of the preferred embodiment of the method for quickly creating an Excel file of the present invention;

[0048] Figure 2 For Figure 1 It is a schematic diagram of the module of the preferred embodiment of the program for quickly creating an Excel file in

[0049] Figure 3 This is a flowchart of a preferred embodiment of the method for quickly creating an Excel file according to the present invention.

[0050] The realization of the purpose, functional features and advantages of the present invention will be further described in conjunction with the embodiments with reference to the accompanying drawings. Specific embodiments

[0051] It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0052] The present invention provides a method for quickly creating an Excel file, which is applied to a device for quickly creating an Excel file. Refer to Figure 1 As shown, it is a schematic diagram of the application environment of a preferred embodiment of the method for quickly creating an Excel file according to the present invention.

[0053] In this embodiment, the device for quickly creating an Excel file includes: a processor 12, a memory 11, a communication bus 13 and a network interface 14.

[0054] The memory 11 includes at least one type of readable storage medium. The at least one type of readable storage medium can be a non-volatile storage medium such as a flash memory, a hard disk, a multimedia card, a card-type memory, etc. In some embodiments, the readable storage medium may be an internal storage unit of the device for quickly creating an Excel file, such as the hard disk of the device for quickly creating an Excel file. In other embodiments, the readable storage medium may also be an external memory 11 of the device for quickly creating an Excel file, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) card, a Flash Card, etc. equipped on the device for quickly creating an Excel file.

[0055] In this embodiment, the readable storage medium of the memory 11 is generally used to store the program 10 for quickly creating an Excel file installed in the device for quickly creating an Excel file. The memory 11 can also be used to temporarily store the data that has been output or will be output.

[0056] The processor 12 may be a central processing unit (CPU), a microprocessor or other data processing chips in some embodiments, and is used to run the program code stored in the memory 11 or process data, such as executing the program 10 for quickly creating an Excel file.

[0057] Those skilled in the art can understand, Figure 1The structure of the terminal shown does not constitute a limitation on the terminal, and it may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0058] The network interface 14 may optionally include a standard wired interface, a wireless interface (such as a WI-FI interface), and is generally used to establish a communication connection between the Excel file quick production device and other electronic devices.

[0059] The communication bus 13 is used to enable connection communication between these components.

[0060] Figure 1 Only the Excel file quick production device with components 11 - 14 is shown, but it should be understood that it is not required to implement all the shown components, and more or fewer components can be implemented alternatively.

[0061] Optionally, the Excel file quick production device may further include a user interface. The user interface may include an input unit such as a keyboard, a voice input device such as a microphone and other devices with voice recognition functions, a voice output device such as a speaker, headphones, etc. Optionally, the user interface may also include a standard wired interface and a wireless interface.

[0062] Optionally, the Excel file quick production device may further include a display, which may also be referred to as a display screen or a display unit. In some embodiments, it may be an LED display, a liquid crystal display, a touch liquid crystal display, and an organic light-emitting diode (OLED) toucher, etc. The display is used to display the information processed in the Excel file quick production device and to display a visual user interface.

[0063] Optionally, the Excel file quick production device further includes a touch sensor. The area provided by the touch sensor for the user to perform a touch operation is called a touch area. In addition, the touch sensor described here may be a resistive touch sensor, a capacitive touch sensor, etc. Moreover, the touch sensor not only includes a contact type touch sensor, but may also include a proximity type touch sensor, etc. In addition, the touch sensor may be a single sensor, or may be multiple sensors arranged in an array, for example.

[0064] In addition, the area of the display of the Excel file quick production device may be the same as or different from the area of the touch sensor. Optionally, the display and the touch sensor are stacked to form a touch display screen. The device detects the touch operation triggered by the user based on the touch display screen.

[0065] Optionally, the device for quickly creating the Excel file may further include a radio frequency (RF) circuit, a sensor, an audio circuit, etc., which will not be elaborated here.

[0066] In an alternative embodiment, it is also possible to upload the result of the method for quickly creating an Excel file to the blockchain.

[0067] Specifically, corresponding summary information is obtained based on the result of the method for quickly creating an Excel file. Specifically, the summary information is obtained by performing a hashing process on the result of the method for quickly creating an Excel file, such as by using the sha256s algorithm. Uploading the summary information to the blockchain can ensure its security and fairness and transparency to users. Users can download the summary information from the blockchain to verify whether the result of the task scheduling method for processing all policies has been tampered with. The blockchain referred to in this example is a new application mode of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanism, and encryption algorithms. Blockchain, in essence, is a decentralized database, a string of data blocks generated by using cryptographic methods. Each data block contains information on a batch of network transactions, used to verify the validity of the information (anti-counterfeiting) and generate the next block. The blockchain may include a blockchain underlying platform, a platform product service layer, and an application service layer, etc.

[0068] In Figure 1 In the device embodiment shown, in a memory 11 as a computer storage medium, there may be included an operation of the device for quickly creating and a program 10 for quickly creating an Excel file; when a processor 12 executes the program 10 for quickly creating an Excel file stored in the memory 11, the following steps are implemented:

[0069] Obtain a data model, where the data model includes a data structure;

[0070] Set two or more elements in the data model and determine whether the two or more elements are the same;

[0071] When the two or more elements are different, obtain the keyword of the current element and map it to an array of corresponding keywords to form data combining a chain and an array;

[0072] When the two or more elements are the same, arrange the elements in order to form array data;

[0073] Sort the array according to the data structure in the data model, and obtain the positioning of the array in the data structure according to the sorting;

[0074] Obtain an Excel template file and render the data model in the Excel template file;

[0075] Write the location of the array in the data structure into the Excel template file;

[0076] Generate an Excel output file based on the Excel template file and export the Excel output file.

[0077] The data model includes data structures such as hash, array, stack\queue\linked list\tree\graph\heap\pojo, etc. Among them, this application is based on hash, and its created data model is to create a hashMap module. The hashMap template stores data according to the hashCode value of the key and can directly locate its value, so it has a very fast access speed, but the traversal order is uncertain. Multiple threads can write to the hashMap simultaneously at any time, which may lead to data inconsistency. Among them, the hashMap allows at most one record key to be null and allows multiple record values to be null.

[0078] The hashMap specifically uses the method of a hash table for storage. The backbone of the hash table is an array. When adding or searching for an element, we map the keyword of the current element to a certain position in the array through a certain function (hash function), and the operation can be completed by directly locating through the array subscript. When searching, the actual storage address is calculated through the hash function, and then it can be retrieved from the corresponding address in the array.

[0079] Specifically, set two or more elements in the data model and determine whether the two or more elements are the same; mainly when setting multiple elements or adding elements, how to establish the relationship between the elements in the hash table and the array, which is specifically divided into the scenarios of the same elements and different elements.

[0080] When two or more elements are different, obtain the keyword of the current element and map it to an array of corresponding keywords, forming data that combines a chain and an array; since the elements are different and the actual storage addresses obtained through the hash function are the same, which is a hash conflict, open addressing and chaining methods can be used to solve the problem. Open addressing method: when a conflict occurs for the current element, continue to find the next unoccupied storage address; in Java, hashMap uses the chaining method. The chaining method mainly combines an array with a linked list, that is, the keywords of elements with the same storage address are mapped to a certain position in the array through a certain function (hash function), forming a linked list, so that there is a linked list structure on each array element. When the data is hashed, the array subscript is obtained and the data is placed on the linked list of the corresponding subscript element. The system will call the hashCode() method of the key to obtain its hashCode value (this method applies to each Java object), and then locate the storage position corresponding to the key value through the last two operations of the hash algorithm (high-order operation and modulo operation).

[0081] Specifically, during the search, if the located array position does not contain a linked list (the next of the current entry points to null), then operations such as search and addition are very fast and can be completed with only one positioning of the array subscript, that is, retrieved from the corresponding address in the array; if the located array contains a linked list, for the addition operation, its time complexity is O(n). First, traverse the linked list. When there is the same storage address, the original linked list is overwritten or a new linked list is added. For the search operation, the linked list still needs to be traversed, and then compared one by one through the equals method of the key for search. Because for performance considerations, the fewer linked lists in the HashMap, the better the performance.

[0082] When two or more elements are the same, arrange the elements in order; there are only two physical storage structures for data structures: sequential storage structure and linked storage structure (structures such as stacks, queues, trees, and graphs are abstracted from the logical structure and mapped to memory, and there are also these two physical organization forms). When the elements are the same, the sequential storage structure is used to store the array, and when the elements are different, the linked storage structure is used to store the array combined with the linked list. During the search, find a certain element according to the subscript in the array, and the specific position where the element is located can be reached with one positioning.

[0083] That is, through the chaining method of hashMap, the array and the linked list are sorted and summarized, and the array is the main body, and the linked list is a branch based on the array main body, so that the array and the linked list are arranged in the form of a tree logical structure, that is, a certain element can be found according to the subscript in the array, and the specific position of the element can be reached with one positioning.

[0084] When multiple elements are set in the data model, compare the number of elements with the preset number of elements; when there are many hash bucket arrays, even a poor Hash algorithm will be relatively dispersed. When there are few hash bucket arrays, even a good Hash algorithm will have more collisions. Therefore, it is necessary to balance between space cost and time cost. In fact, it is to determine the size of the hash bucket array according to the actual situation and design a good hash algorithm on this basis to reduce Hash collisions.

[0085] When the number of multiple elements exceeds the preset number of elements, expansion is carried out; in order to control the map when the hash bucket array is very large, so that the probability of Hash collision is small and the space occupied by the hash bucket array (Node[] table) is small. First, the initial length length of the hash bucket array (Node[] table) (the default value is 16), the Load factor is the load factor (the default value is 0.75), and the threshold is the number of Nodes (key-value pairs) that the HashMap can accommodate at most. threshold = length * Load factor. That is to say, after the length of the array is defined, the larger the load factor, the more key-value pairs it can accommodate.

[0086] Combined with the definition formula of the load factor, it can be seen that the threshold is the maximum number of elements allowed corresponding to the Load factor and length (array length). When this number is exceeded, resize (expansion) is performed again. The capacity of the expanded HashMap is twice the previous capacity. The default load factor of 0.75 is a balanced choice for space and time efficiency. If there is a lot of memory space and high requirements for time efficiency, the value of the load factor Load factor can be reduced; on the contrary, if the memory space is tight and the requirements for time efficiency are not high, the value of the load factor loadFactor can be increased, and this value can be greater than 1.

[0087] When the number of multiple elements exceeds the preset number of elements, obtain the current array length. When the length of the new array is 2 to the power of the length of the previous array: if the array is expanded, the array length changes, and the storage position index = h & (length - 1), and index may also change. It is necessary to recalculate index, traverse each linked list in the old array one by one, and transfer it to the new expanded array. The calculation of the array index position is obtained by performing a hash scrambling operation on the hashcode of the key value and then performing a bit operation with length - 1 to obtain the final array index position.

[0088] Among them, the length of the array in HashMap must always be a power of 2. For example, the binary representation of 16 is 10000, so length - 1 is 15, and the binary is 01111. Similarly, after expansion, the length of the array is 32, the binary representation is 100000, and length - 1 is 31, with the binary representation being 011111. This can ensure that the low bits are all 1, and there is only one difference after expansion, that is, there is an extra 1 in the leftmost bit. In this way, when passing through h&(length - 1), as long as the leftmost different bit corresponding to h is 0, it can be ensured that the new array index obtained is the same as the old array index (greatly reducing the rearrangement of the data positions in the old array that has been well hashed before). Moreover, since the array length is a power of 2 and the low bits of length - 1 are all 1, the obtained array index index will be more uniform.

[0089] For the above & operation, the high bits will not affect the result (the hash function may use various bit operations to make the low bits more dispersed). Only the low bits need to be concerned. If all the low bits are 1, then for the low - bit part of h, any change in any bit will affect the result. That is to say, to obtain the storage position index = 21, there is only this one combination for the low bits of h. This is also the reason why the array length is designed to be a power of 2. If it is not a power of 2, that is, the low bits are not all 1. At this time, to make index = 21, the low - bit part of h is no longer unique, and the probability of hash collision will become greater.

[0090] Extract the elements in the current array and transfer the elements to the new array: The elements in the current array are transferred to the new array, increasing the number of Nodes (key - value pairs) that HashMap can accommodate, and the initial length length of the hash bucket array (Node[] table) is increased. Keeping the length of the hash bucket array (Node[] table) as a power of 2 and the low bits of length - 1 all being 1 will make the obtained array index index more uniform.

[0091] When multiple elements do not exceed the preset number of elements, execute the step of judging whether the multiple elements are the same.

[0092] In this embodiment, through expansion, when there are many hash bucket arrays, even a relatively poor Hash algorithm will be relatively dispersed in calculation. Optimization is done during modulo operation and expansion, while reducing conflicts. Of course, to avoid affecting the performance of HashMap, the array length should not be set too long.

[0093] Excel cells have various appearance styles, and the requirements for appearance styles are not very high. Moreover, the styles are almost regular, which simplifies code writing, eliminates the complicated style writing logic, and ensures that the appearance styles can be freely configured.

[0094] Render the hashMap template in the Excel template file, mainly for the storage method of data in the hashMap template in the Excel template file. After rendering, the arranged data logic in the hashMap template is written into the Excel template file, so that users only need to change the values in the follow-up, and according to the preset styles generated from the data in the Excel template file, they can generate the Excel file with the required styles.

[0095] Specifically, based on the rendering function, nodes and trees of the virtual DOM are constructed from the data model; each element is a node, and each node is arranged in a tree shape according to the inheritance relationship, and all content is tracked by establishing a DOM node tree.

[0096] Render the nodes and trees of the virtual DOM to the Excel template file. During the rendering process, after detecting the data source, the changes in the data source can be accurately perceived, and re-rendering can be performed as needed. After re-rendering, a new set of nodes and trees will be generated. By comparing the new nodes and trees with the old ones, the changes that should be applied to the real DOM can be finally obtained, and finally the changes are made through the Patch function (creating a patch object).

[0097] The method of writing data and exporting the Excel template file: First, a Excel template file is formulated, then the styles are read from the Excel template file, and then these styles are reused in the same data space, and the ExportMain.Export() method is used for export.

[0098] Specifically, a configuration file is created based on the rendered Excel template file, and the control logic of the data model is written in the configuration file: The hashMap only edits the linked list structure existing between the array and the linked list. To write the array and the linked list into the Excel file, it is also necessary to set the corresponding changes of the cells in the Excel file according to the linked list structure of the array and the linked list (obtain the data change situation of the nodes and trees mapping of the virtual DOM). Specifically, rules are formulated in the configuration file, that is, the horizontal / vertical merging of cells, dropdown boxes, links, background colors of cells, border displays, horizontal / vertical function formula calculations of values are written in the configuration file, so that the export process can be described by combining the control logic of the data model in the configuration file, enabling what data to be configured, where to output the data, and how to output in the configuration file.

[0099] Generate and export an Excel output file from the configuration file: That is, based on the hashMap, further formulate rules for the possible cell editing methods in the Excel module. Among them, the Excel template file solidifies some common export rules into individual configuration items, and the control logic for exporting data (where to output? How to output?) is completely defined by the configuration file. To meet most scenarios, these configuration items describing the export rules are defined to an appropriate granularity and can be freely combined and used in various scenarios. The export logic implemented using the configuration completely ignores the underlying code logic. In an environment with changing requirements, only the relevant parameters configured in the configuration file need to be modified, avoiding the trouble brought by modifying the code logic and recompiling and deploying.

[0100] The device for quickly creating an Excel file proposed in the above embodiment sorts the data by using the hashMap template, renders the sorted data in the hashMap template into the Excel template file, presents the data regularly using the style of Excel, and finally creates an Excel output file based on the rendered Excel template file and exports the Excel output file. This facilitates the user to only change the values in the subsequent process and generate the required style of Excel file according to the preset style generated by the data in the Excel template file, simplifies the code writing, eliminates the complex style writing logic, and ensures that the appearance style can be freely configured.

[0101] In other embodiments, the program 10 for quickly creating an Excel file can also be divided into one or more modules. One or more modules are stored in the memory 11 and executed by the processor 12 to complete the present invention. The module referred to in the present invention means a series of computer program instruction segments that can complete specific functions. Refer to Figure 2 as shown, for Figure 1 the program module diagram of the preferred embodiment of the program 10 for quickly creating an Excel file in. The program 10 for quickly creating an Excel file can be divided into: a model creation module 110, a data writing module 120, a template creation module 130, a data rendering module 140, and a file export module 150. The functions or operation steps implemented by the modules 110-150 are all similar to those described above and will not be elaborated here. Exemplarily, for example:

[0102] The model creation module 110 obtains a data model, where the data model includes a data structure;

[0103] The data writing module 120 sets data in the data model. The data includes at least one array, sorts the array according to the data structure in the data model, and obtains the positioning of the array in the data structure according to the sorting;

[0104] The template creation module 130 obtains an Excel template file and renders the data model in the Excel template file;

[0105] The data rendering module 140 writes the positioning of the array in the data structure into the Excel template file;

[0106] The file export module 150 generates an Excel output file based on the Excel template file and exports the Excel output file.

[0107] In addition, the present invention also provides a method for quickly creating an Excel file. Refer to Figure 3 As shown, it is a flowchart of a preferred embodiment of the method for quickly creating an Excel file according to the present invention. This method can be executed by a device, and the device can be implemented by software and / or hardware.

[0108] In this embodiment, the method for quickly creating an Excel file includes: step S10 - step S50.

[0109] Step S10: Obtain a data model, where the data model includes a data structure;

[0110] The data model includes data structures such as hash, array, stack\queue\linked list\tree\graph\heap\pojo, etc. Among them, this application is based on hash, and its created data model is a createHashMap module. The hashMap template stores data according to the hashCode value of the key and can directly locate its value. Therefore, it has a very fast access speed, but the traversal order is uncertain. Multiple threads can write to the hashMap simultaneously at any time, which may lead to data inconsistency. Among them, the hashMap allows at most one record key to be null and allows multiple record values to be null.

[0111] Step S20: Set data in the data model, where the data includes at least one array, sort the array according to the data structure in the data model, and obtain the positioning of the array in the data structure according to the sorting;

[0112] The hashMap specifically uses the method of a hash table for storage. The backbone of the hash table is an array. When adding or searching for a certain element, we map the keyword of the current element to a certain position in the array through a certain function (hash function), and the operation can be completed by directly positioning through the array subscript. When searching, the actual storage address is calculated through the hash function, and then it is taken out from the corresponding address in the array.

[0113] Specifically, set two or more elements in the data model and determine whether two or more elements are the same; mainly when setting multiple elements or adding elements, how to establish the relationship between the elements in the hash table and the array, which is specifically divided into the schemes of the same elements and different elements.

[0114] When two or more elements are the same, obtain the key of the current element and map it to the array corresponding to the key to form data combined with a chain and an array; since the elements are different and the actual storage addresses obtained through the hash function are also the same, which is a hash conflict, open addressing method and chaining method can be used to solve the problem. Open addressing method: when a conflict occurs for the current element, continue to find the next unoccupied storage address; the hashMap in Java uses the chaining method. The chaining method mainly combines an array with a linked list, that is, the elements with the same storage address extract the key and map it to a certain position in the array through a certain function (hash function), that is, a linked list is formed, so that there is a linked list structure on each array element. When the data is hashed, the array subscript is obtained, and the data is placed on the linked list of the corresponding subscript element. The system will call the hashCode() method of the key to obtain its hashCode value (this method is applicable to each Java object), and then locate the storage position corresponding to the key value through the last two operations of the hash algorithm (high-order operation and modulo operation).

[0115] Specifically, when searching, if the array position located does not contain a linked list (the next of the current entry points to null), then operations such as searching and adding are very fast, and only one positioning of the array subscript is required to complete the operation, that is, take it out from the corresponding address in the array; if the array located contains a linked list, for the adding operation, its time complexity is O(n). First, traverse the linked list. When there is the same storage address, the original linked list is overwritten or a new linked list is added. For the searching operation, the linked list still needs to be traversed, and then compared one by one through the equals method of the key to search. Because for performance considerations, the fewer linked lists in the HashMap, the better the performance.

[0116] When two or more elements are different, arrange the elements in order; there are only two physical storage structures for data structures: sequential storage structure and linked storage structure (structures such as stacks, queues, trees, and graphs are abstracted from the logical structure and mapped to memory, and there are also these two physical organization forms). When the elements are the same, use the sequential storage structure to store the array. When the elements are different, use the linked storage structure to store the array in the form of an array combined with a linked list. When searching, search for a certain element in the array according to the subscript, and one positioning can reach the specific position where the element is located.

[0117] That is, through the chaining method of the hashMap, the array and the linked list are sorted and summarized. The array is the main trunk, and the linked list is a branch based on the array main trunk, so that the array and the linked list are arranged in the form of a tree logical structure. That is, in the array, a certain element can be found according to the subscript, and the specific position of the element can be reached with one positioning.

[0118] When setting multiple elements in the data model, compare the number of elements with the preset number of elements; if there are many hash bucket arrays, even a poor Hash algorithm will be relatively scattered. If there are few hash bucket arrays, even a good Hash algorithm will have more collisions. Therefore, it is necessary to balance between space cost and time cost. In fact, it is to determine the size of the hash bucket array according to the actual situation, and on this basis, design a good hash algorithm to reduce Hash collisions.

[0119] When the number of multiple elements exceeds the preset number of elements, expand the array; in order to control the map when the hash bucket array is very large, so that the probability of Hash collision is small and the space occupied by the hash bucket array (Node[] table) is small. First, the initial length length of the hash bucket array (Node[] table) (the default value is 16), the Load factor is the load factor (the default value is 0.75), and the threshold is the number of Nodes (key-value pairs) that the HashMap can accommodate at most. threshold = length * Load factor. That is to say, after the length of the array is defined, the larger the load factor, the more key-value pairs it can accommodate.

[0120] Combined with the definition formula of the load factor, it can be seen that the threshold is the maximum number of elements allowed corresponding to the Load factor and length (array length). When this number is exceeded, resize (expand) is performed again. The capacity of the expanded HashMap is twice the previous capacity. The default load factor of 0.75 is a balanced choice for space and time efficiency. If there is a lot of memory space and high requirements for time efficiency, the value of the load factor Load factor can be reduced; on the contrary, if the memory space is tight and the requirements for time efficiency are not high, the value of the load factor loadFactor can be increased, and this value can be greater than 1.

[0121] When multiple elements exceed the preset number of elements, obtain the current array length, and create a new array length that is a power of 2 times the length of the current array: If the array is expanded, the array length changes, and the storage position index = h & (length - 1), and index may also change. It is necessary to recalculate index, traverse the data in the old array one by one in a linked list, and transfer it to the newly expanded array. The calculation of the array index position is obtained by performing a hash scrambling operation on the hashcode of the key value, and then performing a bitwise operation with length - 1 to obtain the final array index position.

[0122] Among them, the array length of HashMap must always be a power of 2. For example, the binary representation of 16 is 10000, then length - 1 is 15, and the binary is 01111. Similarly, the length of the expanded array is 32, the binary representation is 100000, and length - 1 is 31, and the binary representation is 011111. It can be ensured that the low bits are all 1, and there is only one difference after expansion, that is, there is an extra 1 in the leftmost bit. In this way, when passing h & (length - 1), as long as the leftmost different bit corresponding to h is 0, it can be ensured that the new array index obtained is the same as the old array index (greatly reducing the rearrangement of the data positions in the old array that has been well hashed before). Moreover, since the array length is a power of 2 and the low bits of length - 1 are all 1, the obtained array index index will be more uniform.

[0123] For the above & operation, the high bits will not affect the result (the hash function may use various bitwise operations to make the low bits more scattered). Only the low bits need to be concerned. If all the low bits are 1, then for the low - bit part of h, any change in any bit will affect the result. That is to say, to obtain the storage position index = 21, there is only this one combination for the low bits of h. This is also the reason why the array length is designed to be a power of 2. If it is not a power of 2, that is, the low bits are not all 1. At this time, to make index = 21, the low - bit part of h is no longer unique, and the probability of hash collision will become greater.

[0124] Extract the elements in the current array and transfer the elements to the new array: The elements in the current array are transferred to the new array, which increases the number of Nodes (key - value pairs) that HashMap can accommodate, and the initial length length of the hash bucket array (Node[] table) is increased. Keeping the length of the hash bucket array (Node[] table) as a power of 2 and the low bits of length - 1 all being 1 will make the obtained array index index more uniform.

[0125] When the number of multiple elements does not exceed the preset number of elements, perform the step of determining whether the multiple elements are the same.

[0126] In this embodiment, through expansion, the problem of relatively scattered calculations even with a relatively poor Hash algorithm when there are many hash bucket arrays is solved. Optimization is performed during modulo operation and expansion, while reducing collisions. Of course, in order to avoid affecting the performance of the HashMap, the array length should not be set too long.

[0127] Step S30: Obtain an Excel template file and render the data model in the Excel template file;

[0128] Excel cells have various appearance styles, and the requirements for appearance styles are not very high, and the styles are almost regular, which simplifies code writing, eliminates the cumbersome style writing logic, and ensures that the appearance styles can be freely configured.

[0129] Rendering the hashMap template in the Excel template file is mainly for the storage method of the data in the hashMap template in the Excel template file. After rendering, the arranged data logic in the hashMap template is written into the Excel template file, so that the user only needs to change the values in the follow-up, and according to the preset styles generated from the data in the Excel template file, the required Excel file can be generated.

[0130] Step S40: Write the positioning of the array in the data structure into the Excel template file;

[0131] Specifically, obtain the nodes and trees of the virtual DOM in the data model according to the rendering function; each element is a node, and each node is arranged in a tree shape according to the inheritance relationship, and all contents are tracked by establishing a DOM node tree.

[0132] Render the nodes and trees of the virtual DOM to the Excel template file. During the rendering process, after detecting the data source, the changes in the data source can be accurately perceived through the positioning of the array in the data structure, and re-rendering can be performed according to the needs. After re-rendering, a new node and tree will be generated. By comparing the new node and tree with the old node and tree, the changes that should be applied to the real DOM can be finally obtained, and finally the changes are made through the Patch function (create a patch object).

[0133] Step S50: Generate an Excel output file according to the Excel template file and export the Excel output file.

[0134] The method of writing data and exporting it together with an Excel template file: First, create an Excel template file, then read the styles from the Excel template file, and then reuse these styles in the same data space, and use the ExportMain.Export() method to export.

[0135] Specifically, create a configuration file based on the rendered Excel template file, and write the control logic of the data model in the configuration file: The hashMap only edits the linked list structure existing between the array and the linked list. To write the array and the linked list into the Excel file, it is also necessary to set the corresponding changes of the cells in the Excel file according to the linked list structure of the array and the linked list. Specifically, formulate rules in the configuration file, that is, write the horizontal / vertical merging of cells, dropdown boxes, links, background colors of cells, border displays, and horizontal / vertical function formula calculations of values in the configuration file, so as to describe the export process by the configuration file, so that what data can be configured in the configuration file, where to output the data, and how to output it. That is, obtain the node of the virtual DOM and the data change situation of the tree mapping; combine the control logic of the data model to describe the export process; export the Excel output file.

[0136] Describe the export process by the configuration file, generate an Excel output file from the configuration file and export it: That is, on the basis of the hashMap, formulate rules for the possible cell editing methods in the Excel module. Among them, the Excel template file solidifies some common export rules into individual configuration items, and the control logic of exporting data (where to output? how to output?) is completely defined by the configuration file. To meet most scenarios, these configuration items describing the export rules are defined to an appropriate granularity and can be freely combined and used in various scenarios. The export logic implemented by the configuration completely does not need to care about the underlying code logic. In an environment with changing requirements, only the relevant parameters configured in the configuration file need to be modified, avoiding the trouble brought by modifying the code logic and recompiling and deploying.

[0137] It should be noted that in the above embodiments, data is sorted by using a data model, and the data sorted in the data model is rendered into an Excel template file to regularly present the data by using the styles of Excel. Finally, an Excel output file is created based on the rendered Excel template file, and the Excel output file is exported. This facilitates the user to only change the values in the subsequent process and generate an Excel file with the required style according to the preset style generated from the data in the Excel template file. Moreover, it simplifies the code writing, eliminates the complex style writing logic, and ensures that the appearance style can be freely configured. This solution is also compatible. Specifically, it is a file generated based on the Microsoft Office Excel template file, and the original structure of the document will not be changed during the file generation process. Only (loop) replacement of tags is performed, and the generated file supports software such as Microsoft Excel, WPS, and Pages.

[0138] In addition, an embodiment of the present invention further provides a computer-readable storage medium, which includes a program for quickly creating an Excel file. When the program for quickly creating an Excel file is executed by a processor, the following operations are implemented:

[0139] Obtain a data model, where the data model includes a data structure;

[0140] Set data in the data model, and the data includes at least one array,

[0141] Sort the array according to the data structure in the data model, and obtain the positioning of the array in the data structure according to the sorting;

[0142] Obtain an Excel template file, and render the data model in the Excel template file;

[0143] Write the positioning of the array in the data structure into the Excel template file;

[0144] Generate an Excel output file according to the Excel template file, and export the Excel output file.

[0145] Preferably, the step of setting data in the data model, where the data includes at least one array, sorting the array according to the data structure in the data model, and obtaining the positioning of the array in the data structure according to the sorting includes:

[0146] Set two or more elements in the data model, and determine whether the two or more elements are the same;

[0147] When two or more elements are the same, arrange the elements in order to form array data;

[0148] When two or more elements are different, obtain the keyword of the current element and map it to an array of corresponding keywords to form data combining a chain and an array.

[0149] Preferably, for the step of setting data in the data model, where the data includes at least one array, sorting the array according to the data structure in the data model, and obtaining the positioning of the array in the data structure according to the sorting, the step further includes:

[0150] Set multiple elements in the data model, and compare the number of elements with a preset number of elements;

[0151] When the number of multiple elements exceeds the preset number of elements, expand the array;

[0152] When the number of multiple elements does not exceed the preset number of elements, perform the step of determining whether the multiple elements are the same.

[0153] Preferably, for the step of expanding the array when the number of multiple elements exceeds the preset number of elements, the step includes:

[0154] When the number of multiple elements exceeds the preset number of elements, obtain the current array length, and create a new array with a length that is a power of 2 times the length of the current array;

[0155] Extract the elements in the current array and transfer the elements to the new array.

[0156] Preferably, for the step of obtaining an Excel template file and rendering the data model in the Excel template file, the step includes:

[0157] Obtain the nodes and tree of the virtual DOM in the data model according to the rendering function;

[0158] Render the nodes and tree of the virtual DOM to the Excel template file.

[0159] Preferably, for the step of generating an Excel output file according to the Excel template file and exporting the Excel output file, the step includes:

[0160] Create an Excel output file according to the rendered Excel template file and write the control logic of the data model, where the control logic of the data model includes: horizontal / vertical merging of cells, dropdown boxes, links, background color of cells, border display, and horizontal / vertical function formula calculation of values;

[0161] Describe the export process and export the Excel output file.

[0162] Preferably, for the step of describing the export process and exporting the Excel output file, the step includes:

[0163] Obtain the data change situation of the nodes and tree mappings of the virtual DOM;

[0164] Describe the derivation process in combination with the control logic description of the data model;

[0165] Export an Excel output file.

[0166] The specific implementation manner of the computer-readable storage medium of the present invention is substantially the same as the above-mentioned specific implementation manner of the method for quickly creating an Excel file and the electronic device, and will not be repeated here.

[0167] It should be noted that in this article, the terms "include", "comprise" or any other variant thereof are intended to cover non-exclusive inclusion, so that a process, device, article or method including a series of elements not only includes those elements, but also includes other elements not expressly listed, or further includes elements inherent to such process, device, article or method. Without further limitation, an element defined by the statement "including one..." does not exclude the existence of additional identical elements in the process, device, article or method including that element.

[0168] The serial numbers of the above-mentioned embodiments of the present invention are only for description and do not represent the superiority or inferiority of the embodiments. Through the description of the above embodiments, those skilled in the art can clearly understand that the above embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course, they can also be implemented by hardware, but in many cases, the former is a better implementation manner. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes several instructions for causing a terminal device (which may be a mobile phone, a computer, a server, or a network device, etc.) to execute the methods described in various embodiments of the present invention.

[0169] The above are only the preferred embodiments of the present invention, and do not limit the patent scope of the present invention accordingly. Any equivalent structure or equivalent process transformation made by using the specification and drawings of the present invention, or directly or indirectly applied in other related technical fields, shall be equally included in the patent protection scope of the present invention.

Claims

1. A method for quickly creating an Excel file, characterized in that, The method includes: Obtain a data model, where the data model includes a data structure; Set data in the data model, the data includes at least one array, sort the array according to the data structure in the data model, and obtain the location of the array in the data structure based on the sorting; Obtain an Excel template file and render the data model in the Excel template file; Write the location of the array in the data structure into the Excel template file; Generate an Excel output file according to the Excel template file and export the Excel output file; The step of setting data in the data model, where the data includes at least one array, sorting the array according to the data structure in the data model, and obtaining the location of the array in the data structure based on the sorting includes: Set two or more elements in the data model and determine whether the two or more elements are the same; When two or more elements are the same, arrange the elements in order to form array data; When two or more elements are different, obtain the keyword of the current element and map it to the array corresponding to the keyword to form data combining a chain and an array.

2. The method for quickly creating an Excel file according to claim 1, wherein, The step of setting data in the data model, where the data includes at least one array, sorting the array according to the data structure in the data model, and obtaining the location of the array in the data structure based on the sorting further includes: Set multiple elements in the data model and compare the number of elements with the preset number of elements; When the multiple elements exceed the preset number of elements, expand the array; When the multiple elements do not exceed the preset number of elements, execute the step of determining whether the multiple elements are the same.

3. The method for quickly creating an Excel file according to claim 2, wherein The step of expanding the array when the multiple elements exceed the preset number of elements includes: When the multiple elements exceed the preset number of elements, obtain the current array length, and create a new array length that is a power of 2 times the length of the current array; Extract the elements in the current array and transfer the elements to the new array.

4. The method for quickly creating an Excel file according to claim 1, wherein The step of obtaining an Excel template file and rendering the data model in the Excel template file includes: Obtain the nodes and tree of the virtual DOM in the data model according to the rendering function; Render the nodes and tree of the virtual DOM to the Excel template file.

5. The method for quickly creating an Excel file according to claim 4, wherein The step of generating an Excel output file according to the Excel template file and exporting the Excel output file includes: Create an Excel output file according to the rendered Excel template file and write the control logic of the data model, where the control logic of the data model includes: horizontal / vertical merging of cells, dropdown boxes, links, background colors of cells, border display, horizontal / vertical function formula calculation of values; Describe the export process and export the Excel output file.

6. The method for quickly creating an Excel file according to claim 5, wherein The step of describing the export process and exporting the Excel output file includes: Obtain the data change situation mapped by the nodes and tree of the virtual DOM; Describe the export process in combination with the control logic of the data model; Export the Excel output file.

7. A device for quickly creating an Excel file, characterized in that, The rapid production device includes: a memory and a processor. The memory includes a program for rapidly producing an Excel file. When the program for rapidly producing an Excel file is executed by the processor, the following steps are implemented: Obtain a data model, where the data model includes a data structure; Set two or more elements in the data model and determine whether the two or more elements are the same; When the two or more elements are different, obtain the keyword of the current element and map it to an array of corresponding keywords to form data combined with a chain and an array; When the two or more elements are the same, arrange the elements in order to form array data; Sort the array according to the data structure in the data model, and obtain the positioning of the array in the data structure according to the sorting; Obtain an Excel template file and render the data model in the Excel template file; Write the positioning of the array in the data structure into the Excel template file; Generate an Excel output file according to the Excel template file and export the Excel output file.

8. The rapid production device of the Excel file according to claim 7, characterized in that The step of setting two or more elements in the data model includes: Set multiple elements in the data model and compare the number of elements with the preset number of elements; When the multiple elements exceed the preset number of elements, expand the array; When the multiple elements do not exceed the preset number of elements, execute the step of determining whether the multiple elements are the same.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a program for rapidly producing an Excel file. When the program for rapidly producing an Excel file is executed by a processor, the steps of the method for rapidly producing an Excel file according to any one of claims 1 to 6 are implemented.

Images