A file conversion method and system for realizing secondary linkage

Abstract

The present invention provides a file conversion method and system for realizing secondary linkage, comprising: extracting all header data of the acquired file except the header of the secondary linkage drop-down box, and creating a new master workbook; extracting header data and option values ​​of all drop-down box types except the header of the secondary linkage drop-down box of the acquired file, as well as header data and option values ​​of the primary drop-down box and the secondary drop-down box in the secondary linkage drop-down box, and using the header data of the secondary drop-down box as the option value of the primary drop-down box; creating a new drop-down box workbook based on the header data of each drop-down box type, and using the option value corresponding to the header data of each drop-down box type as the content in the corresponding drop-down box workbook, and extracting the content in the drop-down box workbook as the option data in the drop-down box corresponding to the header data in the master workbook. The dynamic presentation of header information is realized.

Description

Technical Field

[0001] The present invention belongs to the technical field of file conversion, and in particular relates to a file conversion method and system for realizing secondary linkage. Background Art

[0002] The statements in this section merely provide background information related to the present invention and do not necessarily constitute prior art.

[0003] At present, since the PhpExcel class library based on Microsoft's OpenXML standard and PHP language has stopped updating, most newly developed projects have begun to use the PhpSpreadsheet class library to read and write Office Excel documents in different formats. The field ranges of most current import and export functions are fixed in advance. Users can only fill in content under fixed fields when importing, and can only export data under fixed fields when exporting. When setting the secondary linkage drop-down box, most of them use the INDIRECT function to implement it.

[0004] Currently, the field ranges of most import and export functions are fixed in advance and rigid in form, which are not suitable for projects where users can add extended fields themselves. When setting up a secondary linkage drop-down box, the INDIRECT function is mostly used to implement it. However, if the data in the second-level cell of such a secondary linkage drop-down box starts with a number, the data in the second-level drop-down box cannot be displayed when the second-level drop-down box is selected after the first-level drop-down box is selected. Summary of the invention

[0005] In order to solve the technical problems existing in the above-mentioned background technology, the present invention provides a file conversion method and system for realizing secondary linkage, thereby realizing dynamic presentation of header information.

[0006] In order to achieve the above object, the present invention adopts the following technical solution:

[0007] A first aspect of the present invention provides a method for realizing secondary linkage file conversion, which comprises:

[0008] Get the file or field table file to be exported;

[0009] Extract all the header data of the acquired file except the header of the secondary linkage drop-down box, and create a new master workbook;

[0010] Extract the header data and option values ​​of all drop-down box types except the header of the secondary linkage drop-down box, as well as the header data and option values ​​of the primary drop-down box and the secondary drop-down box in the secondary linkage drop-down box, and use the header data of the secondary drop-down box as the option value of the primary drop-down box;

[0011] Based on the header data of each drop-down box type, a new drop-down box workbook is created, and the option values ​​corresponding to the header data of each drop-down box type are used as the content in the corresponding drop-down box workbook. At the same time, the content in the drop-down box workbook is extracted as the option data in the drop-down box corresponding to the header data in the main workbook.

[0012] Furthermore, if the acquired file is a file to be exported, it is also necessary to acquire the data to be exported in the file to be exported and add the data to the cells in the master workbook.

[0013] Furthermore, it also includes: obtaining the address information of the file to be imported, processing the address information to obtain the uploaded file name, and determining whether the file type is correct. If the file type is correct, obtaining the number of columns of data in the file to be imported, if the number of columns is equal to the number of table headers in the main workbook, extracting the data of each row in the table to be imported, converting the style of each row of data, and inserting it into the database.

[0014] Furthermore, the steps for extracting all header data except the header of the secondary linkage drop-down box are:

[0015] Obtaining attribute information of all fields except fields that do not need to be displayed and fields in the secondary linkage drop-down box from the obtained file, and concatenating them to obtain header data of each field, thereby obtaining a text array of header data except fields in the secondary linkage drop-down box;

[0016] Determine the letter array corresponding to the header data of each field in the header data text array, and remove the letters corresponding to the secondary linkage drop-down box field;

[0017] After the values ​​in the letter array are changed into the key names of the header data text array, each element in the header data text array is the header data of a master workbook.

[0018] A second aspect of the present invention provides a file conversion system for realizing secondary linkage, comprising:

[0019] The file acquisition module is configured to: acquire the file to be exported or the field table file;

[0020] A header data acquisition module is configured to: extract all header data of the acquired file except the header of the secondary linkage drop-down box, and create a new master workbook;

[0021] The option value acquisition module is configured to: extract the header data and option values ​​of all drop-down box types except the header of the secondary linkage drop-down box, as well as the header data and option values ​​of the primary drop-down box and the secondary drop-down box in the secondary linkage drop-down box, and use the header data of the secondary drop-down box as the option value of the primary drop-down box;

[0022] The conversion module is configured to: create a new drop-down box workbook based on the header data of each drop-down box type, and use the option value corresponding to the header data of each drop-down box type as the content in the corresponding drop-down box workbook, and at the same time extract the content in the drop-down box workbook as the option data in the drop-down box corresponding to the header data in the main workbook.

[0023] Furthermore, it also includes a data export module, which is configured as follows: if the acquired file is a file to be exported, it is also necessary to acquire the data to be exported in the file to be exported, and add the data to the cells in the main workbook.

[0024] Furthermore, it also includes a data import module, which is configured to: obtain the address information of the file to be imported, process the address information to obtain the uploaded file name, and determine whether the file type is correct. If the file type is correct, obtain the number of columns of data in the file to be imported. If the number of columns is equal to the number of table headers in the main workbook, extract the data of each row in the table to be imported, convert the style of each row of data, and insert it into the database.

[0025] Furthermore, the steps for extracting all header data except the header of the secondary linkage drop-down box are:

[0026] Obtaining attribute information of all fields except fields that do not need to be displayed and fields in the secondary linkage drop-down box from the obtained file, and concatenating them to obtain header data of each field, thereby obtaining a text array of header data except fields in the secondary linkage drop-down box;

[0027] Determine the letter array corresponding to the header data of each field in the header data text array, and remove the letters corresponding to the secondary linkage drop-down box field;

[0028] After the values ​​in the letter array are changed into the key names of the header data text array, each element in the header data text array is the header data of a master workbook.

[0029] A third aspect of the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps in the above-mentioned method for realizing secondary linkage file conversion.

[0030] The fourth aspect of the present invention provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the steps in the method for realizing secondary linkage file conversion as described above are implemented.

[0031] Compared with the prior art, the present invention has the following beneficial effects:

[0032] The present invention provides a file conversion method for realizing secondary linkage, which independently sets a database table to store fixed basic attributes and extended attributes of a server, thereby realizing dynamic presentation of header information in a table file.

[0033] The invention provides a file conversion method for realizing secondary linkage, which solves the problem that when setting a secondary linkage drop-down box in an Excel table file, if the data in the second-level cell starts with a number, the second-level drop-down box cannot be displayed when the first-level drop-down box is selected and then the second-level drop-down box is selected, by using OFFSET(), COUNTA() and MATCH() functions instead of the INDIRECT() function. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] The accompanying drawings in the specification, which constitute a part of the present invention, are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute improper limitations on the present invention.

[0035] Figure 1 This is a flowchart of obtaining an import template file according to the first embodiment of the present invention;

[0036] Figure 2 is a flowchart of importing data according to the first embodiment of the present invention;

[0037] Figure 3 This is a flow chart of exporting data according to the first embodiment of the present invention. DETAILED DESCRIPTION

[0038] The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

[0039] It should be noted that the following detailed descriptions are all illustrative and intended to provide further explanation of the present invention. Unless otherwise specified, all technical and scientific terms used herein have the same meanings as those commonly understood by those skilled in the art to which the present invention belongs.

[0040] It should be noted that the terms used herein are only for describing specific embodiments and are not intended to limit exemplary embodiments according to the present invention. As used herein, unless the context clearly indicates otherwise, the singular form is also intended to include the plural form. In addition, it should be understood that when the terms "comprising" and / or "including" are used in this specification, it indicates the presence of features, steps, operations, devices, components and / or combinations thereof.

[0041] Embodiment 1

[0042] This embodiment provides a file conversion method for realizing secondary linkage, which specifically includes the following steps:

[0043] Step 1: Get the field table file and generate the drop-down box workbook and the main workbook. Specifically include:

[0044] Step 101, obtain the field table, and obtain all the undeleted header data ($title) of the Excel file except the header that does not need to be displayed and the header of the secondary linkage drop-down box by calling the getExcelTitle() method.

[0045] like Figure 1 As shown, the attribute information of all fields except those that do not need to be displayed and the fields of the secondary linkage drop-down box are obtained from the obtained file, and the header data of each field is obtained, thereby obtaining the header data text array except the fields of the secondary linkage drop-down box; the letter array corresponding to the header data of each field in the header data text array is determined, and the letters corresponding to the fields of the secondary linkage drop-down box are removed; after the values ​​in the letter array are changed into the key names of the header data text array, each element in the header data text array is the header data of a master workbook. Specifically:

[0046] From the field table, by calling the getEecelTitle() method, get all the undeleted header data ($title) of the Excel file except the headers that do not need to be displayed (such as the "Online Status", "Monitoring Status" and "Creation Time" fields in the field table do not need to be used as header information of the Excel file) and the headers of the secondary linkage drop-down box (such as the "Computer Room" and "Cabinet" fields have a secondary linkage relationship).

[0047] Specifically, in the getEecelTitle() method, the attribute information (id, name, whether it is required and whether it is unique) of all undeleted fields except the fields that do not need to be displayed and the fields of the secondary linkage drop-down box is obtained from the field table shown in Table 1 (each line in Table 1 is a field), wherein the field table is a table containing all header data information; then, according to the obtained attribute information, the header text is spliced ​​through a foreach loop to determine whether it is required, optional or unique, that is, the attribute information of each field is spliced ​​through a foreach loop to obtain the header data of each field, thereby obtaining the header data text array ($excel_title) except the fields of the secondary linkage drop-down box (specifically, through a foreach loop, it is cyclically determined whether each header information obtained is required, unique and its special circumstances, and different headers are generated according to different conditions). The data is spelled with the corresponding header information; for example, the header data of "Server IP Address" is a special case, which is spliced ​​(required and the data must be unique, not filled in for multi-node physical machines), and finally forms the header data of the "Server IP Address" field "Server IP Address (required and the data must be unique, not filled in for multi-node physical machines)"; if the header data of "Server Name" is required and unique, it is spliced ​​(required and the data must be unique), and finally forms the header data of the "Server Name" field "Server Name (required and the data must be unique)"); calculate how many header fields ($title_number) there are (specifically, use the count() function to calculate the number of elements in the header array $excel_title, and obtain the number of headers $title_number, such as: $title_number=count($excel_title));According to the IDs of all fields in the field table except those that do not need to be displayed, determine the column letter corresponding to each field (for example, the column letter corresponding to ID 1 is A), determine the column letter array ($letter) corresponding to the header data of each field in the above header data text array in the Excel file to be generated, remove the letters corresponding to the secondary linkage drop-down box field, for example, remove two letters (G and H) (where G represents the number of columns in the "computer room" header in the Excel file, and H represents the number of columns in the "cabinet" header in the Excel file. These two letters are not fixed and are determined according to the position of the secondary linkage header in the Excel file. Because when the header information was obtained before, the secondary linkage header information of "computer room" and "cabinet" was excluded , so the column numbers representing "computer room" and "cabinet" in the excel file should be removed to ensure that when the header information is filled into the header of the excel file, the headers of the columns (G and H) where "computer room" and "cabinet" are empty, thus reserving space for the secondary linkage setting of "computer room" and "cabinet" later); use the array_slice() function to obtain the first $title_number bits of data from 0 in the $letter array to form a new array ($letter_arr), and finally use the array_combine() function to change the values ​​in the $letter_arr array into the key names of the $excel_title array to obtain the final header data array ($title). ;

[0048] Table 1, Field table

[0049]

[0050]

[0051] Step 102: Create a new master workbook based on all header data except the header of the secondary linkage drop-down box; extract the header data and option values ​​of all drop-down box types except the header of the secondary linkage drop-down box, as well as the header data and option values ​​of the primary drop-down box and the secondary drop-down box in the secondary linkage drop-down box, and use the header data of the secondary drop-down box as the option value of the primary drop-down box; create a new drop-down box workbook based on the header data of each drop-down box type, and use the option value corresponding to the header data of each drop-down box type as the content in the corresponding drop-down box workbook, and extract the content in the drop-down box workbook as the option data in the drop-down box corresponding to the header data in the master workbook. Specifically:

[0052] (1) Based on the field table, the getSelectValue() method is called to obtain the header information of all drop-down box types except the secondary linkage drop-down box field and their corresponding option values ​​($data).

[0053] Specifically, in the getSelectValue() method, the option values ​​of all the drop-down box type fields that have not been deleted except the secondary linkage drop-down box field are obtained from the field table to form an array ($all_value). If the option values ​​of some drop-down box type fields are not fixed in the option value column in the field table, such as the "Department" field managed by the server, because the option values ​​in the "Department" field may change, it cannot be fixed to the option value column in the field table and can only be formed into a separate "Department" table, so it is necessary to obtain the option value of the "Department" field from the "Department" table separately, and replace the option value of the department found in the field table, so as to obtain the final option value of the drop-down box type field ($data_col).Get the ID and name of all undeleted fields except the fields that do not need to be displayed and the fields in the secondary linkage drop-down box from the field table to form an array ($field), extract the name column from the array through the array_column() function to form a new one-dimensional array ($title = [server number, server name, server IP address, server type, server form, server brand, cluster, department]), calculate how many header fields ($title_number) there are in the $title array; then determine a letter array ($letter = [A, B, C, D, E, F, I, J, K])( Remove the letters corresponding to the fields in the secondary linkage drop-down box, for example, remove two letters (G and H), etc.), then use the array_slice() function to get the first $title_number data of the $letter array starting from A to form a new array ($letter_arr = [A, B, C, D, E, F, I, J]), and finally use the array_combine() function to change the values ​​in the $letter_arr array into the key names of the $title array to obtain the new header data of all undeleted fields except the fields that do not need to be displayed and the fields in the secondary linkage drop-down box (such as $cols = [ 'A'=>Server number, 'B'=>Server name, 'C'=>Server IP address, 'D'=>Server type, 'E'=>Server form, 'F'=>Server brand, 'I'=>Cluster, 'J'=>Department]), traverse the array $cols through the foreach loop, extract the elements of the drop-down box type in the array to form a new array, and thus obtain the final drop-down box header data array ($col=['D'=>Server type, 'E'=>Server form, 'F'=>Server brand, 'I'=>Cluster, 'J'=>Department]); then through array_ The flip() function is used to swap the key values ​​of the array $col (that is, the key names and key values ​​of the array are swapped. For example, after the key values ​​of the array $A are swapped, it will become the style of the array $B, $A=array('Name'=>'Zhang San', 'Age'=>'25'), $B=array('Zhang San'=>'Name', '25'=>'Age')), forming a new array ($col_back), and then using the array_combine() function to change the values ​​in the $col_back array into the key names of the $data_col array, forming a new array and assigning the array to $data_cols. Finally, the header $col in the form of a drop-down box is assigned to $data['col'], the drop-down box option value $data_cols is assigned to $data['data_cols'], and then $data is output.The getSelectValue() method will eventually output a two-dimensional array $data, which contains two elements, namely the one-dimensional array $data['col'] and the one-dimensional array $data['data_cols']. $data['col'] represents the required header information array in the form of a drop-down box, and $data['data_cols'] represents the required drop-down box option value array of the header in the form of a drop-down box.

[0054] It should be noted that 101 and 102 are in a corresponding relationship. 101 obtains all the undeleted header data ($title) except the headers that do not need to be displayed and the headers of the secondary linkage drop-down box. These header data will be filled into the Excel file as headers; 102 obtains all the undeleted drop-down box type header data ($data['col']) except the header of the secondary linkage drop-down box and the corresponding drop-down box option values ​​($data['data_cols']), and the drop-down box option values ​​in these data need to be associated with the corresponding drop-down box header columns in 101. To make the corresponding drop-down To associate the option value of the drop-down box with the corresponding drop-down box header, it is necessary to match the drop-down box type header data ($data['col']) obtained in 102 with the drop-down box type header in the header data ($title) obtained in 101, so as to associate the option value data of the drop-down box type ($data['data_cols']) with the drop-down box column in the corresponding excel file. Therefore, the reason why the drop-down box type header data is obtained in 102 is just to facilitate the association of the option value of the drop-down box with the drop-down box header column in the excel file, which has no practical significance and only serves as a bridge.

[0055] (2) In the getServerExcelTemplate() method, determine the import template name ($subject) and file type ($type), pass in $subject, $type, table header data array $title, table header information array in the form of a drop-down box $data['col'], and table header drop-down box option value array $data['data_col'] as parameters, and call the generateSelectTemplate() method to generate the Excel file of the import template.

[0056] In the getServerExcelTemplate() method, determine the import template name variable $subject (for example, server import template 20220112) and the file type variable $type (for example, .xlsx), and call the getEecelTitle() method to obtain the header array $title of all undeleted headers in the Excel file except the headers that do not need to be displayed and the secondary linkage drop-down box headers. Call the getSelectValue() method to obtain the header array $data['col'] of all undeleted drop-down box type headers in the Excel file except the secondary linkage drop-down box headers. Call the getSelectValue() method to obtain the option value array $data['data_col'] of all undeleted drop-down box type headers in the Excel file except the secondary linkage drop-down box headers.

[0057] Specifically, in the getServerExcelTemplate() method, pass $subject, $type, $title, $data['col'], and $data['data_cols'] as parameters to the generateSelectTemplate() method.

[0058] 1. Determine the storage address and name ($export) of the generated Excel file through $subject and $type;

[0059] 2. Use the file_exists() function to determine whether an Excel file with this name already exists in this address. If so, use the unlink() function to delete the file.

[0060] 3. Get the directory name part of the path through the dirname() function, and then check whether the directory name part of the obtained path is a directory through the is_dir() function. If not, create a directory file of the corresponding path through the mkdir() function and grant 777 permissions (for a file, the operation objects are divided into file owners, groups, and others. A user, group, or others are divided into three permissions - read, write, and execute, and the corresponding values ​​of these three permissions are 4, 2, and 1, respectively. 777 is actually divided into three objects. The first 7 means that the file owner's permission is 7, that is, 4 (readable) + 2 (writable) + 1 (executable) permissions. The file owner can read, write, and execute this file; the second 7 represents the permissions of the group to which the file owner belongs, which is also 7; the third represents the permissions of other people outside the user or group, which is also 7; if the permission of a file is 751, then the group does not have write permissions, and other people do not have read and write permissions);

[0061] 4. Instantiate Spreadsheet(), obtain the active workbook through getActiveSheet(), and set the workbook name to the file name $subject using the setTitle() function. Determine the array of letters ($titleRow) corresponding to the columns of the table headers in the Excel file, remove the two letters (G and H) represented by the secondary linked dropdown field. Then, use the getStyle(), getFont(), and setBold() functions to set the cell style, cell text style, and text boldness of the Excel file, and determine the style of the table header data;

[0062] 5. Use a for loop to loop and set according to the number of $title (for ($a = 0; $a < count($title); $a++). Through the for loop, set the number of loops according to the number of table headers (count($title)), and insert each table header information in the table header array $title into the table headers of the corresponding columns in the Excel file). Use the getStyle(), getNumberFormat(), and setFormatCode() functions to set the cell format to text, use the getColumnDimension() and setWidth() functions to set the cell width, and finally use the setActiveSheetIndex() and setCellValue() functions to insert the corresponding table header information into the corresponding cells;

[0063] 6. Traverse the table header array $data['col'] in the form of a dropdown box through a foreach loop, create a new workbook for each table header of the dropdown box type, set the workbook name to the table header name of this dropdown box type, and then traverse the option values $data['data_cols'] of the table header of the dropdown box type through a foreach loop. Use the getSheetByName() and setCellValue() functions to insert the corresponding option values of the table header of the dropdown box type into the corresponding dropdown box workbook. Finally, use a for loop to set the connection between the cells of each column of the dropdown box type and the corresponding dropdown box workbook, and extract the data of the option values of the table header of the dropdown box type in the dropdown box workbook as the option data in the dropdown box;

[0064] 7. Perform the secondary linkage drop-down box settings separately, obtain all undeleted ID and name information arrays ($one) from the "primary drop-down box header" database table (such as the computer room information table), obtain all undeleted name information and the "primary drop-down box header" information array ($two) from the "secondary drop-down box header" database table (such as the cabinet information table), loop the array $two separately through foreach, and judge which "primary drop-down box header" data this "secondary linkage drop-down box" header data belongs to according to the "primary drop-down box header" information, and assign this "secondary linkage drop-down box" header data to the corresponding "primary drop-down box header" data sub-element to form the final required secondary linkage dynamic array ($two_stage_linkage); then insert the headers of the "first-level drop-down box header" and "second-level drop-down box header" in the G1 and H1 cells of the excel file through the setActiveSheetIndex() and setCellValue() functions, and then create a "second-level linkage" workbook, and then traverse the second-level linkage array $two_stage_linkage through the foreach loop to fill in the name information of the first-level drop-down box option data in column A of the "second-level linkage" workbook in sequence, and fill in the name of the second-level drop-down box data belonging to each first-level drop-down box option data to the back of the row where the name of the corresponding first-level drop-down box option data is located.Finally, through the for loop, the cells in column G are associated with the "Secondary Linkage" workbook, and the data of the first-level drop-down box option data name in column A of the "Secondary Linkage" workbook is extracted as the option data in the "First-level Drop-down Box Header" drop-down box in column G. The first-level drop-down box is set with "=OFFSET(Secondary Linkage!$A$1,0,0,'.count($two_stage_linkage).',1)" as the parameter through the setFormula1() function; the cells in column H are associated with the "Secondary Linkage" workbook, and the data of the second-level drop-down box option data name after the first-level drop-down box option data name in column A of the "Secondary Linkage" workbook is extracted as the "Secondary Drop-down Box Header" in column H. The option data in the drop-down box is relative to the selected value of the "first-level drop-down box header", and the second-level drop-down box is set through the setFormula1() function with "=OFFSET(second-level linkage'.'!$A$1,MATCH('.$subject.'!$F2,OFFSET(second-level linkage!$A$1,0,0,'.count($data).',1),0)-1,1,1,COUNTA(OFFSET(second-level linkage'.'!$A$1,MATCH('.$subject.'!$F2,OFFSET(second-level linkage!$A$1,0,0,'.count($data).',1),0)-1,1,1,100)))" as the parameter. Among them, the function of the offset function: returns a reference to a cell or a range of cells with a specified number of rows and columns. The returned reference can be a single cell or a range of cells. The number of rows and columns to be returned can be specified. The syntax of the offset function is = OFFSET (reference area, number of rows to move down / up, number of columns to move left / right, number of rows in the new area, number of columns in the new area). Take the formula of the first-level drop-down menu as an example, OFFSET (second-level linkage! $A$1,0,0,'.count($two_stage_linkage).',1). It means taking the "second-level linkage" workbook as the benchmark, taking cell A1 as the starting point, and the following two zeros, that is, the rows and columns will not be moved; the number of rows in the new area will still be count($two_stage_linkage) rows, and the number of columns in the new area will be 1 column.

[0065] The function of the match function: The function can search for a specified item in a cell range (range: two or more cells on a worksheet. The cells in a range can be adjacent or non-adjacent.) and then return the relative position of the item in the cell range. In plain words, it finds the position of the content you specify. The syntax of the match function = MATCH (search value, search range, search method).

[0066] The function of counta: counts the number of cells that are not empty in a range. The syntax of counta = COUNTA(value1,[value2],...). value1 is required. It is the first parameter representing the value to be counted; value2,... are optional. They represent other parameters representing the values ​​to be counted. A maximum of 255 parameters can be included.

[0067] OFFSET(secondary linkage'.'!$A$1,MATCH('.$subject.'!$G2,OFFSET(secondary linkage!$A$1,0,0,'.count($data).',1),0)-1,1,1,COUNTA(OFFSET(secondary linkage'.'!$A$1,MATCH('.$subject.'!$G2,OFFSET(secondary linkage!$A$1,0,0,'.count($data).',1),0)-1,1,1,100))). Indicates that the "Secondary Linkage" workbook is used as the benchmark; $A1 is used as the starting point; MATCH('.$subject.'!$G2,OFFSET(Secondary Linkage!$A$1,0,0,'.count($data).',1),0)-1 indicates the number of rows moved down / up; 1 indicates moving one column to the right; 1 indicates the number of rows in the new area; COUNTA(OFFSET(Secondary Linkage'.'!$A$1,MATCH('.$subject.'!$G2,OFFSET(Secondary Linkage!$A$1,0,0,'.count($data).',1),0)-1,1,1,100)) indicates the number of columns in the new area;

[0068] The $G2 in the formula MATCH('.$subject.'!$G2,OFFSET(Secondary linkage!$A$1,0,0,'.count($data).',1),0)-1 determines the column to be moved to the corresponding first-level drop-down box in the Excel file (in my case, this is column G); it means taking the "Secondary linkage" workbook as the benchmark, $G2 is the search value; OFFSET(Secondary linkage!$A$1,0,0,'.count($data).',1) is the search range; 0 is the search method (exact search).

[0069] COUNTA(OFFSET(secondary linkage'.'!$A$1,MATCH('.$subject.'!$G2,OFFSET(secondary linkage!$A$1,0,0,'.count($data).',1),0)-1,1,1,100) means OFFSET(secondary linkage'.'!$A$1,MATCH('.$subject.'!$F2,OFFSET(secondary linkage!$A$1,0,0,'.count($data) .',1),0)-1,1,1,100) is the calculation range. In essence, it is to calculate how many "secondary drop-down box data" there are after finding the corresponding "first-level drop-down box data" starting from $A1. Therefore, the fifth parameter of the OFFSET function here uses a relatively large number of 100 (it is impossible for a "first-level drop-down box data" to have 100 "secondary drop-down box data". This value is determined based on the actual situation). Thus, it is clear how many columns of the "secondary drop-down box data" referenced by the first $A1 in front are.

[0070] 8. Finally, save the file through the save() function, close the connection through the disconnectWorksheets() function, and destroy the variables.

[0071] Step 2: Get the address information of the file to be imported, process the address information to get the uploaded file name, and determine whether the file type is correct. If the file type is correct, get the number of columns in the data in the file to be imported. If the number of columns is equal to the number of table headers in the master workbook, extract the data of each row in the table to be imported, convert the style of each row of data, and insert it into the database. Figure 2 As shown, specifically including:

[0072] Step 201, in the uploadServers() method, accept the address information of the file passed in by the front end, process the address information to obtain the uploaded file name, and determine whether the file type is correct; if the file type is correct, use the getHighestDataRow() and getHighestDataColumn() functions to obtain the number of rows ($rows) and columns ($cols) of the data in this excel file respectively; obtain the header data ($title) of the current import template excel file from the field table, calculate the number of header data ($title_number), and compare the number of header data ($title_number) with the number of columns ($cols) of the uploaded file. If they are equal, it means that the uploaded excel import file is the latest import template. In the cooperation of the for loop and the number of rows ($rows) of the uploaded file, extract the data of each row in the excel import file through the getCell() and getValue() functions, then solve the rich text problem and exclude the data filled in but deleted in the table, which results in the rows with all the obtained row data being null, and finally combine the data of each row into the excel import file data array ($data). Determine whether $data is empty. If $data is empty, it means that the imported file is an empty file.

[0073] Step 202, perform a foreach loop on the two-dimensional array $data and pass each data as a parameter to the checkRule method to determine whether the value of each field in this data meets the requirements of the mandatory, unique and format requirements of this field. If it does not meet the requirements, output the error reason and form a failure reason array $fail_data. If all meet the requirements, output this data and form an importable data array $success_data; then use getCell(), setValue(), getStyle(), getFont(), getColor(), setARGB() functions and the uploaded file array The number $cols fills in the red "failure reason" header data in the next column of the last column of the Excel file, and inserts each row of data in the failure reason array $fail_data into the corresponding row of the "failure reason" column through the foreach loop, and sets it to red through the getCell(), setValue(), getStyle(), getFont(), getColor() and setARGB() functions; then determine the name of the failure reason file $file_name, determine the storage address $file through $file_name, and save this file to this address through the save($file) function. Then output the number of importable items $data['import_number'], the number of unimportable items $data['no_import_number'], the data that can be imported $data['import_data'] and the failure reason file address $data['curl'].

[0074] Step 203, in the importServers() method, accept the array object $data['import_data'] composed of the importable data passed in by the front end, traverse the $data['import_data'] array through the foreach loop, convert and split the field values ​​in each piece of data, convert this piece of data into a style that adapts to the database, and then insert these data into the data table, and insert the corresponding default data into the database table together. For example, what is imported is the server information, and there is a field for the server import time in the database, but this field is generally not displayed in the import template, and the user does not need to fill it in manually, but this data in the database table must be filled in, so when the server information filled in by the user is imported into the database, the default import time information that must be filled in must be inserted into the database table together.

[0075] Step 3. If the file obtained is a file to be exported, you also need to obtain the data to be exported in the file to be exported and add the data to the cells in the main workbook. Specifically:

[0076] In the exportExcel() method, determine the name ($subject) and file type ($type) of the file to be exported; then call the getEecelTitle() method to obtain all undeleted header data ($title) of the Excel file except for the headers that do not need to be displayed (such as the "online status", "monitoring status" and "creation time" fields in the field table do not need to be header information of the Excel file) and the headers of the secondary linkage drop-down box (such as the "belonging computer room" and "belonging cabinet" fields have a secondary linkage relationship); then call the getSelectValue() method to obtain the header data of the drop-down box type and its corresponding option value ($arr); call the getData() method to obtain the data to be exported ($data); call the getDataKeys() method to obtain the key name ($param) corresponding to the data to be exported; finally, use $subject, $type, $title, $arr['col'], $arr['data_col'], $data, $param as parameters to call the generateSelectTemplate() method to generate the exported Excel file. Figure 3 As shown, specifically including:

[0077] Step 301, the getEecelTitle() method and the getSelectValue() method are the same as those when exporting the template file.

[0078] Step 302, in the getData() method, first obtain all the basic information (id + fixed basic attribute information) of the servers that have not been deleted ($list), extract the server ids in the array $list through the array_column function to form a new one-dimensional array ($server_id), and then use this new one-dimensional array to obtain the data filled in the user extended attributes of these servers from the extended attribute value table ($server_extend_list), and finally use the foreach loop to splice the array $list and the array $server_extend_list into a unified overall output ($data).

[0079] Step 303, in the getDataKeys method, obtain the key corresponding to the server data to be exported, first obtain all the non-deleted attribute IDs and names ($server_metadata) except for the non-displayed fields from the server attribute table, extract all the server IDs of the array $server_metadata through the array_column() function to form a new one-dimensional array ($server_metadata_id), and use the array_splice() function to replace the corresponding ID in the $server_metadata_id array with the key name array $server_metadata_name of the server fixed attributes prepared in advance to form a new one-dimensional array ($param) output.

[0080] Step 304, in the generateSelectTemplate() method, it is similar to obtaining the import template file, but with one more step, the exported server data $data is inserted into the corresponding column of the excel file with $param through a foreach loop. The generateSelectTemplate() method is a method for generating an excel file. The $data array variable required by this method represents the exported server data. After the excel file is generated in the generateSelectTemplate() method, it is determined whether the $data array is an empty array. If it is empty, it means that the user only downloads the import template file, and the code will no longer be executed, and the download address of the generated import template file will be directly output; if the $data array is not empty, it means that the server data is exported. At this time, the data in the $data array needs to be inserted into the corresponding column in the generated excel file (master workbook) with $param (the header key name when exporting data) through a foreach loop, and then the address of the exported excel file is output.

[0081] The present invention sets up a database table separately to store fixed basic attributes and extended attributes of a server, and obtains the id, name, required and unique information of all undeleted fields except fields that do not need to be displayed and fields in a secondary linkage drop-down box from the database table through a getEecelTitle() method; then, according to the obtained information, the header text is spliced ​​through a foreach loop to determine whether it is required, optional or unique, so as to obtain a final header data text array ($title), thereby realizing dynamic presentation of header information in an excel file.

[0082] The present invention solves the problem that when setting a secondary linkage drop-down box in an Excel file, if the data in the second-level cell starts with a number, the second-level drop-down box cannot be displayed when the first-level drop-down box is selected and then the second-level drop-down box is selected by using OFFSET(), COUNTA() and MATCH() functions instead of the INDIRECT() function.

[0083] Embodiment 2

[0084] This embodiment provides a file conversion system for realizing secondary linkage, which specifically includes the following modules:

[0085] The file acquisition module is configured to: acquire the file to be exported or the field table file;

[0086] A file conversion module is configured to: extract all header data of the acquired file except for headers that do not need to be displayed and headers of secondary linkage drop-down boxes, as well as header data of drop-down box types and their option values, use all header data except for headers that do not need to be displayed and headers of secondary linkage drop-down boxes as headers of the table to be generated, and based on the header data of each drop-down box type, create a new workbook for the table to be generated, and use the option values ​​corresponding to the header data of each drop-down box type as the content in the workbook.

[0087] The file conversion module includes a file conversion module to be exported, which is configured to: convert the file to be exported into an export table, specifically: after extracting all header data of the file to be exported except the header that does not need to be displayed and the header of the secondary linkage drop-down box, as well as the header data of the drop-down box type and its option value, it is necessary to obtain the data to be exported in the file to be exported and its corresponding key name, and based on the correspondence between the key name and the header, add the data to the cells in the export table.

[0088] The file conversion module includes a field table conversion module, which is configured to: convert the field table into an import template table;

[0089] The import template table is used to convert the data in the file to be imported into an import table for import. The specific steps are: obtain the address information of the file to be imported, process the address information to obtain the uploaded file name, and determine whether the file type is correct. If the file type is correct, obtain the number of rows and columns of data in the file to be imported. If the number of columns is equal to the number of headers in the import template table, extract the data of each row in the table to be imported, compare the data of each row with the key name of the header data text array, and add the data of each row that matches the comparison to the importable data array. After the importable data array is converted in style, it is inserted into the data table.

[0090] It should be noted here that the various modules in this embodiment correspond one-to-one to the various steps in Example 1, and the specific implementation process is the same, which will not be repeated here.

[0091] Embodiment 3

[0092] This embodiment provides a computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the steps in the file conversion method for realizing secondary linkage as described in the first embodiment above are implemented.

[0093] Embodiment 4

[0094] This embodiment provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the steps in a method for realizing secondary linkage file conversion as described in the first embodiment are implemented.

[0095] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of hardware embodiments, software embodiments, or embodiments combining software and hardware. Furthermore, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage and optical storage, etc.) containing computer-usable program code.

[0096] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0097] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0098] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0099] A person skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing the relevant hardware through a computer program, and the program can be stored in a computer-readable storage medium, and when the program is executed, it can include the processes of the embodiments of the above-mentioned methods. The storage medium can be a disk, an optical disk, a read-only memory (ROM) or a random access memory (RAM), etc.

[0100] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A file conversion method for realizing secondary linkage, characterized in that: include: Get the file or field table file to be exported; Extract all the header data of the acquired file except the header of the secondary linkage drop-down box, and create a new master workbook; Extract the header data and option values ​​of all drop-down box types except the header of the secondary linkage drop-down box, as well as the header data and option values ​​of the primary drop-down box and the secondary drop-down box in the secondary linkage drop-down box, and use the header data of the secondary drop-down box as the option value of the primary drop-down box; Based on the header data of each drop-down box type, a new drop-down box workbook is created, and the option values ​​corresponding to the header data of each drop-down box type are used as the content in the corresponding drop-down box workbook. At the same time, the content in the drop-down box workbook is extracted as the option data in the drop-down box corresponding to the header data in the main workbook.

2. A method for realizing secondary linkage file conversion as claimed in claim 1, characterized in that: If the obtained file is a file to be exported, it is also necessary to obtain the data to be exported in the file to be exported and add the data to the cells in the master workbook.

3. A method for realizing secondary linkage file conversion as claimed in claim 1, characterized in that: Also includes: Get the address information of the file to be imported, process the address information to get the uploaded file name, and determine whether the file type is correct. If the file type is correct, get the number of columns of data in the file to be imported. If the number of columns is equal to the number of table headers in the master workbook, extract the data of each row in the table to be imported, convert the style of each row of data, and insert it into the database.

4. A method for realizing secondary linkage file conversion as claimed in claim 1, characterized in that: The steps for extracting all header data except the header of the secondary linkage drop-down box are as follows: Obtaining attribute information of all fields except fields that do not need to be displayed and fields in the secondary linkage drop-down box from the obtained file, and concatenating them to obtain header data of each field, thereby obtaining a text array of header data except fields in the secondary linkage drop-down box; Determine the letter array corresponding to the header data of each field in the header data text array, and remove the letters corresponding to the secondary linkage drop-down box field; After the values ​​in the letter array are changed into the key names of the header data text array, each element in the header data text array is the header data of a master workbook.

5. A file conversion system realizing secondary linkage, characterized in that: include: The file acquisition module is configured to: acquire the file to be exported or the field table file; A header data acquisition module is configured to: extract all header data of the acquired file except the header of the secondary linkage drop-down box, and create a new master workbook; The option value acquisition module is configured to: extract the header data and option values ​​of all drop-down box types except the header of the secondary linkage drop-down box, as well as the header data and option values ​​of the primary drop-down box and the secondary drop-down box in the secondary linkage drop-down box, and use the header data of the secondary drop-down box as the option value of the primary drop-down box; The conversion module is configured to: create a new drop-down box workbook based on the header data of each drop-down box type, and use the option value corresponding to the header data of each drop-down box type as the content in the corresponding drop-down box workbook, and at the same time extract the content in the drop-down box workbook as the option data in the drop-down box corresponding to the header data in the main workbook.

6. A file conversion system for realizing secondary linkage as claimed in claim 5, characterized in that: It also includes a data export module, which is configured as follows: if the acquired file is a file to be exported, it is also necessary to acquire the data to be exported in the file to be exported, and add the data to the cells in the main workbook.

7. A file conversion system for realizing secondary linkage as claimed in claim 5, characterized in that: It also includes a data import module, which is configured to: obtain the address information of the file to be imported, process the address information to obtain the uploaded file name, and determine whether the file type is correct. If the file type is correct, obtain the number of columns of data in the file to be imported. If the number of columns is equal to the number of table headers in the main workbook, extract the data of each row in the table to be imported, convert the style of each row of data, and insert it into the database.

8. A file conversion system for realizing secondary linkage as claimed in claim 5, characterized in that: The steps for extracting all header data except the header of the secondary linkage drop-down box are as follows: Obtaining attribute information of all fields except fields that do not need to be displayed and fields in the secondary linkage drop-down box from the obtained file, and concatenating them to obtain header data of each field, thereby obtaining a text array of header data except fields in the secondary linkage drop-down box; Determine the letter array corresponding to the header data of each field in the header data text array, and remove the letters corresponding to the secondary linkage drop-down box field; After the values ​​in the letter array are changed into the key names of the header data text array, each element in the header data text array is the header data of a master workbook.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the steps in a method for realizing secondary linkage file conversion as described in any one of claims 1 to 4 are implemented.

10. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the steps in the method for realizing secondary linkage file conversion as described in any one of claims 1 to 4 are implemented.

Images