Table data extraction method, device and system for damaged xls document and medium

Abstract

The invention discloses a table data extraction method, device and system for a damaged xls document and a medium, and the method comprises the steps: starting to scan binary data of the damaged xls document at an initial offset position of a memory buffer area, and determining a first offset position of an SST starting position with the maximum possibility and a second offset position of a worksheet starting position with the maximum possibility; reading the target shared character string from the memory buffer area based on the first offset position and storing the target shared character string in a shared character string list array; based on the second offset position, reading a third offset position at the starting position of the cell with the maximum possibility from the memory buffer area; and determining attribute information of each cell in combination with the third offset position and the shared character string list array, storing the attribute information in the worksheet data list array, and traversing the worksheet data list array to output target table data. According to the method, the damaged xls document is comprehensively scanned on the binary system level, and effective table data in the damaged xls document can be extracted to the maximum extent.

Description

Technical Field

[0001] This application relates to, but is not limited to, the field of data processing technologies, and particularly relates to a method, apparatus, system, and medium for extracting tabular data from a damaged xls document. Background Art

[0002] The xls document is an office document format commonly used in daily life. During the use of the xls document, it may be damaged due to reasons such as viruses, power outages, and office software crashes. After being damaged, the xls document may become unable to be opened, viewed, and edited again, resulting in the loss of document data and affecting the work efficiency of users. Based on this, in order to recover the tabular content in the damaged xls document, a common method is to parse the ole format information of the damaged xls document and try to read the Workbook data stream of the document to read the tabular data. However, if the damage degree of the xls document is relatively serious (for example, the integrity of the document is damaged), it will be impossible to obtain a valid Workbook data stream, and thus impossible to read the tabular data of the xls document. Summary of the Invention

[0003] Embodiments of this application provide a method, apparatus, system, and medium for extracting tabular data from a damaged xls document, which can maximize the extraction of valid tabular data from the damaged xls document.

[0004] In a first aspect, embodiments of this application provide a method for extracting tabular data from a damaged xls document, including: Obtain the binary data of the damaged xls document and write the binary data into a memory buffer; Scan the binary data starting from the initial offset position of the memory buffer to determine the first offset position at the start of the most likely shared string table SST, where the initial offset position corresponds to an offset of 0 in the memory buffer; Read the target shared string from the memory buffer based on the first offset position and store the target shared string into an array of shared string lists; Scan the binary data starting from the initial offset position to determine the second offset position at the start of the most likely worksheet, and based on the second offset position, read the third offset position at the start of the most likely cell from the memory buffer, where the number of the second offset positions is at least 1, and the number of the cells is at least 1; Perform a binary scan starting from the third offset position, and determine the attribute information of each cell in combination with the array of shared string lists, and store the attribute information into an array of worksheet data lists, where the attribute information includes the row number, column number, and cell value of the corresponding cell; After closing the damaged xls document and releasing the memory buffer, traverse the worksheet data list array and output the target table data in a preset format.

[0005] In some embodiments, starting from the initial offset position of the memory buffer, scan the binary data to determine the first offset position at the start of the most likely shared string table (SST), including: Based on the first binary character sequence corresponding to the first Record ID of the SST, scan the binary data starting from the initial offset position to obtain a plurality of candidate offset positions that match the first binary character sequence, where the first Record ID includes a main Record ID and / or an extended Record ID; Based on any one of the candidate offset positions and the first byte value, read a first sequence in the memory buffer respectively, and based on any one of the candidate offset positions and the second byte value, read a second sequence in the memory buffer respectively. When the candidate offset position is the actual offset position of the SST in the memory buffer, the corresponding first sequence represents the total number of times the strings referenced in all cells of the damaged xls document, and the corresponding second sequence represents the number of unique strings actually stored in the SST; Based on the first sequence, second sequence, first threshold, and second threshold corresponding to each candidate offset position, establish a target condition, where the target condition is used to indicate that the second sequence is less than or equal to the first sequence, and the second sequence is less than or equal to the second threshold, and the first sequence is less than or equal to the first threshold; Determine the candidate offset position that satisfies the target condition as the first offset position, where the first offset position includes the first position corresponding to the main Record ID and / or the second position corresponding to the extended Record ID.

[0006] In some embodiments, when the first offset position includes the first position and the second position, based on the first offset position, read the target shared string from the memory buffer and store the target shared string into the shared string list array, including: Determine the first data byte number of the information block corresponding to the first position, and determine the second data byte number in the information block corresponding to the second position; Create a temporary buffer in the memory, where the length of the temporary buffer is greater than or equal to the sum of the first data byte number and the second data byte number, and less than the document length of the damaged xls document; Initialize all bytes of the temporary buffer to 0; Locate the offset position corresponding to the data part in the information block corresponding to the first position in the memory buffer, and copy the byte content with the number of bytes being the first number of bytes to the temporary buffer; Locate the offset position corresponding to the data part in the information block corresponding to the second position in the memory buffer, and copy the byte content with the number of bytes being the second number of bytes to the temporary buffer. Determine the target shared string as the byte content in the temporary buffer except for the byte content with a value of 0; Read and parse each of the target shared strings from the temporary buffer one by one, and store them in the shared string list array in the form of Unicode strings.

[0007] In some embodiments, scanning the binary data starting from the initial offset position to determine the second offset position at the most likely start of the worksheet, and reading the third offset position at the most likely start of the cell from the memory buffer based on the second offset position, includes: Based on the second binary character sequence corresponding to the second Record ID of the worksheet, scan the binary data starting from the initial offset position to obtain multiple second offset positions that match the second binary character sequence; Save all the second offset positions to the worksheet offset position list array; Insert the initial offset position as the new second candidate offset position to the front end of the worksheet offset position list array; Determine the worksheet data area. Among them, the worksheet data area includes the binary data between any one of the second offset positions in the worksheet offset position list array in the memory buffer and the corresponding next second offset position, or the worksheet data area includes the binary data between any one of the second offset positions in the worksheet offset position list array in the memory buffer and the end position of the memory buffer. The number of worksheet data areas is the same as the number of second offset positions, and different worksheet data areas correspond to different worksheets; Find the fourth offset position that matches the third binary character sequence corresponding to the third Record ID of the row information in each of the worksheet data areas; For any one of the worksheet data areas, calculate the fourth offset position of the information block corresponding to the next row information based on any one of the fourth offset positions and the third data byte number of the information block corresponding to the corresponding third Record ID; Record all the fourth offset positions, match the third Record ID corresponding to each fourth offset position with a preset Record ID value set, and determine the fourth offset position corresponding to the successfully matched third Record ID as the third offset position, where the number of the third offset positions is multiple.

[0008] In some embodiments, starting from the third offset position, perform a binary scan, and combine the shared string list array to determine the attribute information of each cell, and store the attribute information into the worksheet data list array, including: Read the third Record ID of the cell corresponding to each third offset position from the memory buffer; When the third Record ID belongs to the Record ID value set, determine the corresponding third offset position as a valid target offset position, locate each target offset position in the memory buffer, read the row number, column number, and target Record ID of the cell corresponding to each target offset position, and determine the cell value of the corresponding cell based on each target Record ID and the shared string list array; When the third Record ID does not belong to the Record ID value set, or the third Record ID is a preset termination value, abort the read operation of the attribute information of the cell.

[0009] In some embodiments, determining the cell value of the corresponding cell based on each target Record ID and the shared string list array includes: Determine the target type of the corresponding cell based on the target Record ID and a preset mapping table, where the mapping table represents the mapping relationship between the target Record ID and the cell type; When the target type is a shared string cell, parse the value stored in the data position of the Record of the cell corresponding to the corresponding target Record ID to obtain a target index, and determine the string corresponding to the target index in the shared string list array as the final cell value; When the target type is a cell directly storing a string, determine the value stored in the data position of the Record of the corresponding cell as the cell value.

[0010] In some embodiments, each piece of the attribute information is uniquely associated with a worksheet. After storing the attribute information into the worksheet data list array, the method further includes: Traverse the array of worksheet data lists in a loop. When it is detected that the attribute information of any two of the worksheets is exactly the same, delete any one of the worksheets; When it is detected that the similarity of all the attribute information in any two of the worksheets is greater than a preset threshold, delete the worksheet corresponding to the attribute information with a smaller quantity.

[0011] In a second aspect, an embodiment of the present application provides a control device, including at least one control processor and a memory for communicatively connecting with the at least one control processor; the memory stores instructions executable by the at least one control processor, and the instructions are executed by the at least one control processor so that the at least one control processor can execute the method for extracting table data of a damaged xls document as described in the first aspect.

[0012] In a third aspect, an embodiment of the present application further provides a table data extraction system, including the control device of the second aspect.

[0013] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, storing computer-executable instructions for executing the method for extracting table data of a damaged xls document as described in the first aspect.

[0014] The embodiments of the present application provide a method, apparatus, device, and medium for extracting tabular data from a damaged XLS document. The method includes: obtaining the binary data of the damaged XLS document and writing the binary data into a memory buffer; scanning the binary data starting from the initial offset position of the memory buffer to determine the first offset position at the start of the most likely shared string table (SST), where the initial offset position corresponds to an offset of 0 in the memory buffer; reading the target shared string from the memory buffer based on the first offset position and storing the target shared string into an array of shared string lists; scanning the binary data starting from the initial offset position to determine the second offset position at the start of the most likely worksheet, and based on the second offset position, reading the third offset position at the start of the most likely cell from the memory buffer, where the number of second offset positions is at least 1 and the number of cells is at least 1; performing binary scanning starting from the third offset position, and determining the attribute information of each cell in combination with the array of shared string lists, and storing the attribute information into an array of worksheet data lists, where the attribute information includes the row number, column number, and cell value of the corresponding cell; after closing the damaged XLS document and releasing the memory buffer, traversing the array of worksheet data lists and outputting the target tabular data in a preset format. Compared with the existing method of relying on the integrity of the XLS to read tabular data, the present application performs a comprehensive scan of the damaged XLS document at the binary level, effectively eliminating the dependence on the integrity of the XLS document and maximizing the extraction of valid tabular data from the damaged XLS document. Description of the Drawings

[0015] Figure 1 is a flowchart of the steps of the method for extracting tabular data from a damaged XLS document provided by an embodiment of the present application; Figure 2 is a structural diagram of a control device provided by another embodiment of the present application; Figure 3 is a schematic diagram of the modules of a tabular data extraction system provided by another embodiment of the present application. Detailed Embodiments

[0016] In order to make the objectives, technical solutions, and advantages of the present application clearer, the present application will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0017] It can be understood that although functional modules are divided in the device schematic diagram and the logical sequence is shown in the flowchart, in some cases, the steps shown or described can be executed in a different order from the module division in the device or the flowchart in the flowchart. Terms such as "first" and "second" in the specification, claims or the above-mentioned drawings are used to distinguish similar objects, and do not necessarily have to be used to describe a specific order or sequence.

[0018] The xls document is an office document format commonly used in daily life. During the use of the xls document, it may be damaged due to reasons such as viruses, power outages, and office software crashes. After being damaged, the xls document may not be able to be opened, viewed, and edited again, resulting in the loss of document data and affecting the work efficiency of users. Based on this, in order to restore the table content in the damaged xls document, a common method is to parse the ole format information of the damaged xls document and try to read the Workbook data stream of the document to read the table data. However, if the damage degree of the xls document is relatively serious (for example, the integrity of the document is damaged), it will not be possible to obtain a valid Workbook data stream, and thus it will not be possible to read the table data of the xls document.

[0019] To solve the above problems, an embodiment of the present application provides a method, apparatus, device, and medium for extracting tabular data from a damaged xls document. The method includes: obtaining binary data of the damaged xls document and writing the binary data into a memory buffer; scanning the binary data starting from the initial offset position of the memory buffer to determine the first offset position at the start of the most likely shared string table SST, where the initial offset position corresponds to an offset of 0 in the memory buffer; reading the target shared string from the memory buffer based on the first offset position and storing the target shared string into an array of shared string lists; scanning the binary data starting from the initial offset position to determine the second offset position at the start of the most likely worksheet, and based on the second offset position, reading the third offset position at the start of the most likely cell from the memory buffer, where the number of the second offset positions is at least 1 and the number of the cells is at least 1; starting a binary scan from the third offset position, determining the attribute information of each cell in combination with the array of shared string lists, and storing the attribute information into an array of worksheet data lists, where the attribute information includes the row number, column number, and cell value of the corresponding cell; after closing the damaged xls document and releasing the memory buffer, traversing the array of worksheet data lists and outputting the target tabular data in a preset format. Compared with the existing method of relying on the integrity of the xls to read tabular data, the present application performs a comprehensive scan of the damaged xls document at the binary level, effectively eliminating the dependence on the integrity of the xls document and maximizing the extraction of valid tabular data from the damaged xls document.

[0020] The following further elaborates on the embodiments of the present application with reference to the accompanying drawings.

[0021] Reference Figure 1 , Figure 1 FIG. is a flowchart of the steps of a method for extracting tabular data from a damaged xls document provided by an embodiment of the present application. An embodiment of the present application provides a method for extracting tabular data from a damaged xls document, and the method includes but is not limited to the following steps: Step S10: Obtain binary data of the damaged xls document and write the binary data into a memory buffer.

[0022] It can be understood that the way of writing the binary data of the damaged xls document into the memory buffer in this embodiment is: opening the damaged xls document in binary mode, loading all the binary data of the damaged xls document into the memory buffer, and at the same time confirming whether the damaged xls document is in the xls format, providing effective support for subsequent effective acquisition of tabular data from the binary data.

[0023] It should be noted that in this embodiment, the method for determining whether the damaged xls document is in the xls format is to perform a binary search on the first 2048 bytes of the memory buffer to confirm the existence of the following binary character sequence {0x57, 0x00, 0x6F, 0x00, 0x72, 0x00, 0x6B, 0x00, 0x62, 0x00, 0x6F, 0x00, 0x6F, 0x00, 0x6B, 0x00}. If the above binary character sequence exists, it is determined that the damaged xls document is in the xls format.

[0024] Step S20: Start scanning the binary data from the initial offset position of the memory buffer to determine the first offset position at the start of the most likely shared string table SST, where the initial offset position corresponds to an offset of 0 in the memory buffer.

[0025] It can be understood that in an xls document, the shared string table SST is used to store the strings that appear repeatedly in the worksheet. Searching and parsing the SST from the memory buffer first can provide effective support for subsequent acquisition of the tabular data in the cells.

[0026] Specifically, in some embodiments, Figure 1 Step S20 includes but is not limited to the following steps: Step S21: Based on the first binary character sequence corresponding to the first Record ID of the SST, start scanning the binary data from the initial offset position to obtain multiple candidate offset positions that match the first binary character sequence, where the first Record ID includes the main Record ID and / or the extended Record ID; Step S22: Based on any candidate offset position and the first byte value, read the first sequence in the memory buffer respectively, and based on any candidate offset position and the second byte value, read the second sequence in the memory buffer respectively. When the candidate offset position is the actual offset position of the SST in the memory buffer, the corresponding first sequence represents the total number of strings referenced in all cells of the damaged xls document, and the corresponding second sequence represents the number of unique strings actually stored in the SST; Step S23: Based on the first sequence, second sequence, first threshold, and second threshold corresponding to each candidate offset position, establish a target condition, where the target condition is used to indicate that the second sequence is less than or equal to the first sequence, the second sequence is less than or equal to the second threshold, and the first sequence is less than or equal to the first threshold; Step S24: Determine the candidate offset position that meets the target condition as the first offset position, where the first offset position includes the first position corresponding to the main Record ID and / or the second position corresponding to the extended Record ID.

[0027] It is understandable that in the xls file format, different types of data are stored in the form of information blocks (Records), and each type of information block has a fixed header format. The header format includes 4 bytes. The first 2 bytes are the Record ID, and the next 2 bytes adjacent to the Record ID are the Record Length (indicating the byte length of the data content of the information block). Thus, by adding 4 bytes and the Record Length to the offset of the current Record, the offset of the next Record can be jumped to.

[0028] It should be noted that this embodiment does not limit the specific way of reading values from the memory buffer, and the little-endian method is adopted in this embodiment.

[0029] In this embodiment, for the Record corresponding to SST, the value of the Record ID in the header format is 0x00fc, and the next 2 bytes are the Record Length. Starting from the initial offset position with an offset of 0 in the memory buffer Buffer, a binary scan is performed to find the first binary character sequence {0xfc, 0x00} of the first Record ID corresponding to SST. Since this search feature sequence is composed of only two bytes, multiple candidate offset positions are usually obtained. It is necessary to verify the above multiple candidate offset positions to confirm a real offset position that is most likely to be SST (Shared String Table). The verification algorithm is as follows: If the current candidate offset position suspected to be SST is SST_Offset, then 4 bytes are read in little-endian order from the position with an offset of SST_Offset + 0x4 in Buffer to determine the first sequence B (unsigned int type), and 4 bytes are read in little-endian order from the position with an offset of SST_Offset + 0x8 in Buffer to determine the second sequence C (unsigned int type). When a certain candidate offset position meets the target conditions, that is, the second sequence is less than or equal to the first sequence, and the second sequence is less than or equal to the second threshold, and the first sequence is less than or equal to the first threshold (C <= B, C <= C_Max, B <= B_Max), then the current candidate offset position is considered to be the real offset of SST (i.e., the first offset).

[0030] Specifically, the first threshold B_Max in this embodiment is 500000, and the second threshold C_Max is 100000. These two limit values can be corrected subsequently according to further analysis of damaged samples.

[0031] In addition, for some xls documents with a large amount of data, in addition to the main Record ID with a value of 0x00fc corresponding to its SST, there may also be additional extended Record IDs (with values of 0x003C or 0x00FF). The main Record ID and the extended Record ID correspond to different Records respectively. And the Records in the xls document are stored linearly and continuously. Therefore, in the process of extracting table data from a damaged xls document, in order to obtain as much table data as possible, in this embodiment, after obtaining the first offset position of the main Record corresponding to the main Record ID, binary scanning and probing are continued to check whether the next Record ID is 0x003c and 0x00ff. If so, the first offset positions corresponding to these two extended Records (the first position SST_Offset, the second position SST_Continue_Offset, and SST_Ext_Offset) need to be recorded to provide an effective data basis for extracting the complete target shared string subsequently.

[0032] Step S30, read the target shared string from the memory buffer based on the first offset position, and store the target shared string into the shared string list array.

[0033] Specifically, in the case where the first offset bit includes a first position and a second position, Figure 1 Step S30 includes but is not limited to the following steps: Step S31, determine the first data byte count of the information block corresponding to the first position, and determine the second data byte count in the information block corresponding to the second position; Step S32, establish a temporary buffer in the memory, where the length of the temporary buffer is greater than or equal to the sum of the first data byte count and the second data byte count, and less than the document length of the damaged xls document; Step S33, initialize all bytes of the temporary buffer to 0; Step S34, locate the offset position corresponding to the data part in the information block corresponding to the first position in the memory buffer, and copy the byte content with the number of bytes being the first byte count to the temporary buffer; Step S35, locate the offset position corresponding to the data part in the information block corresponding to the second position in the memory buffer, and copy the byte content with the number of bytes being the second byte count to the temporary buffer, and determine the byte content other than the value 0 in the temporary buffer as the target shared string; Step S36, read and parse each target shared string from the temporary buffer one by one, and store them in the shared string list array in the form of Unicode strings.

[0034] It can be understood that, referring to the description of the above embodiments, when the first offset position includes a first position and a second position, that is, when the current damaged xls document is relatively large, the first offset position corresponding to the SST includes SST_Offset, SST_Continue_Offset, and SST_Ext_Offset. The first data byte count of the information block corresponding to the first position is determined respectively, and the second data byte count in the information block corresponding to the second position is determined. The first data byte count is the Record Length of the main Record ID corresponding to the SST, and the second data byte count is the RecordLength of the extended Record ID corresponding to the SST. A temporary buffer is established in the memory. The length of the temporary buffer is greater than or equal to the sum of the first data byte count and the second data byte count, and less than the document length of the damaged xls document. In this way, it can be ensured that the length of the temporary buffer is not less than the sum of the Record Lengths of each Record corresponding to the SST. Given that the data of the damaged xls document may not be reliable, in this embodiment, the length of the temporary buffer needs to be limited to be less than a limit value. In this embodiment, this limit value is the document length of the current damaged xls document.

[0035] Specifically, after determining the first position and the second position in this embodiment, before saving the target shared string corresponding to the SST to the temporary buffer, all bytes of the temporary buffer are initialized to 0 in advance, that is, the temporary buffer is all written with 0x0, which can eliminate the random values in the memory and avoid residual data interfering with subsequent parsing. After implementing the operation of clearing the temporary buffer, in this embodiment, the offset position corresponding to the data part in the information block corresponding to the first position is located in the memory buffer, and the byte content with the number of bytes being the first byte count is copied to the temporary buffer; and the offset position corresponding to the data part in the information block corresponding to the second position is located in the memory buffer, and the byte content with the number of bytes being the second byte count is copied to the temporary buffer. The byte content in the temporary buffer except for the byte content with the value of 0 is determined as the target shared string; finally, from the temporary buffer, each target shared string is read and parsed one by one and stored in the shared string list array in the form of a Unicode string.

[0036] In addition, in some embodiments, when the first offset position includes the first position, only the offset position corresponding to the data part in the information block corresponding to the first position is located in the memory buffer, and the byte content with the number of bytes being the first byte count is copied to the temporary buffer.

[0037] In addition, in view of the possible disorder and loss of data in the damaged xls document, this embodiment also performs a validity check on the shared string list array SST_String_List. The methods of validity check include but are not limited to: checking whether the length of the first string in SST_String_List is empty, and the length of SST_String_List, that is, whether the number of strings in SST_String_List is within a preset range. In this embodiment, the preset range is 10 - 10000.

[0038] Step S40: Scan the binary data starting from the initial offset position to determine the second offset position at the start of the most likely worksheet. Based on the second offset position, read the third offset position at the start of the most likely cell from the memory buffer, where the number of second offset positions is at least 1 and the number of cells is at least 1.

[0039] Specifically, in some embodiments, Figure 1 Step S40 includes but is not limited to the following steps: Step S41: Scan the binary data starting from the initial offset position based on the second binary character sequence corresponding to the second Record ID of the worksheet to obtain multiple second offset positions that match the second binary character sequence; Step S42: Save all the second offset positions to the worksheet offset position list array; Step S43: Insert the initial offset position as the new second candidate offset position at the front end of the worksheet offset position list array; Step S44: Determine the worksheet data area, where the worksheet data area includes the binary data between any second offset position and the corresponding next second offset position in the worksheet offset position list array in the memory buffer, or the worksheet data area includes the binary data between any second offset position in the worksheet offset position list array in the memory buffer and the end position of the memory buffer. The number of worksheet data areas is the same as the number of second offset positions, and different worksheet data areas correspond to different worksheets; Step S45: Search for the fourth offset position that matches the third binary character sequence corresponding to the third Record ID of the row information in each worksheet data area; Step S46: For any worksheet data area, calculate the fourth offset position of the information block corresponding to the next row information based on the third data byte count of the information block corresponding to any fourth offset position and the corresponding third Record ID; Step S47: Record all the fourth offset positions, match the third RecordID corresponding to each fourth offset position with a preset Record ID value set, and determine the fourth offset positions corresponding to the successfully matched third Record IDs as the third offset positions, where the number of third offset positions is multiple.

[0040] It can be understood that in this embodiment, considering that the data stored in the damaged xls document may be out of order, the second binary character sequence corresponding to the second Record ID of the worksheet (corresponding to {0x09, 0x08, 0x10, 0x00, 0x00, 0x06, 0x10, 0x00}) is used to scan the binary data starting from the initial offset position of the memory buffer. After obtaining multiple second offset positions Sheet_Offset that match the second binary character sequence, all the second offset positions are saved to the worksheet offset position list array Sheet_Offset_List. Here, one second offset position corresponds to one worksheet. When there are multiple second offset positions, it means that the current damaged xls document includes multiple worksheets. In this embodiment, considering that the damaged xls document may have situations such as file truncation, worksheet disorder, or duplication, in order to help the user extract useful table data as much as possible, the offset 0 of the Buffer (i.e., the initial offset position) is also used as a second offset position Sheet_Offset and inserted at the front of Sheet_Offset_List. At this time, if the length of Sheet_Offset_List (i.e., the number of Sheet_Offset) is n, then the data content of the current damaged xls document in the Buffer is divided into n regions.

[0041] It can be understood that when a worksheet data area is detected, where the worksheet data area includes binary data between any second offset position and the corresponding next second offset position in the worksheet offset position list array in the memory buffer, or the worksheet data area includes binary data between any second offset position and the end position of the memory buffer in the worksheet offset position list array in the memory buffer, the number of worksheet data areas is the same as the number of second offset positions, and different worksheet data areas correspond to different worksheets; search for a fourth offset position Row_Offset that matches the third binary character sequence corresponding to the third Record ID of the row information (corresponding to {0x08, 0x02, 0x10, 0x00}) in each worksheet data area; regard Row_Offset as the start position of a Record, and for any worksheet data area, calculate the fourth offset position of the information block corresponding to the next row information based on any fourth offset position Row_Offset and the third data byte count (i.e., Record Length) of the information block corresponding to the corresponding third Record ID; record all the fourth offset positions, and match the third Record ID corresponding to each fourth offset position with a preset Record ID value set, and determine the fourth offset position corresponding to the successfully matched third Record ID as the third offset position, where the number of third offset positions is multiple. That is to say, in this embodiment, after reading a Row_Offset, the Record ID of the Record corresponding to the Row_Offset is matched with the preset Record ID value set. When the match is successful, the Record corresponding to the successfully matched Record ID is the Record determined to be of extraction value for the user in this embodiment, that is, the information block corresponding to the cell.

[0042] Specifically, the Record ID value set of this embodiment includes 0x0201, 0x0202, 0x00be, 0x0203, 0x0204, 0x027e, 0x00bd, 0x00fd, 0x0205, 0x0406, etc.

[0043] Step S50: Perform a binary scan starting from the third offset position, and determine the attribute information of each cell in combination with the shared string list array, and store the attribute information in the worksheet data list array, where the attribute information includes the row number, column number, and cell value of the corresponding cell.

[0044] Specifically, the attribute information corresponding to each cell includes three key pieces of information: row number, column number, and cell value. One piece of attribute information is uniquely associated with one worksheet. In this embodiment, all the attribute information corresponding to each worksheet is stored by defining a two-dimensional array, namely the worksheet data list array.

[0045] In addition, in view of the unreliability of the data in the damaged xls file, the attribute information determined in this embodiment may have situations such as extremely large row numbers and column numbers. To avoid such situations, before writing the row number, column number, and cell data value (in string form) of the attribute information corresponding to each cell into the corresponding worksheet data list array, this embodiment needs to first perform a filtering operation on the row number and column number. In this embodiment, cells with a row number greater than 500000 or a column number greater than 1000 are regarded as abnormal cells, and the attribute information corresponding to the abnormal cells will not be stored in the worksheet data list array to avoid misleading users later.

[0046] Specifically, in some embodiments Figure 1 Step S50 includes but is not limited to the following steps: Step S51, read the third RecordID of the cell corresponding to each third offset position from the memory buffer; Step S52, when the third Record ID belongs to the Record ID value set, determine the corresponding third offset position as the valid target offset position, locate each target offset position in the memory buffer, read the row number, column number, and target Record ID of the cell corresponding to each target offset position, and determine the cell value of the corresponding cell based on each target Record ID and the shared string list array; Step S53, when the third Record ID does not belong to the Record ID value set, or the third Record ID is the preset termination value, abort the operation of reading the attribute information of the cell.

[0047] It can be understood that after determining the third offset position Cells_Offset of each cell, read the third Record ID of the cell corresponding to each third offset position from the Buffer, and compare the third Record ID with the reference ID in the Record ID value set to confirm the validity of the third offset position. If the third Record ID belongs to the Record ID value set, it indicates that the Record corresponding to the current cell stores valid cell data, and this operation provides effective support for obtaining the attribute information of the valid cell later.

[0048] Specifically, when the third Record ID belongs to the Record ID value set, the corresponding third offset position is determined as the valid target offset position. Locate each target offset position in the Buffer, and read the row number, column number, and target Record ID of the cell corresponding to each target offset position. Since different target Record IDs of the cell correspond to different cell types, and the meanings represented by the data content parts stored in the Record corresponding to different cell types are different, it is necessary to first obtain the target Record ID and combine it with the shared string list array to determine the cell value of the corresponding cell; and the row number part and column number part corresponding to the Record corresponding to the cell correspond to the actual row number and column number of the cell. Therefore, after determining the target offset position Record_Offset, directly read 2 bytes as the row number of the current cell in unsigned int type from the position of the offset (Record_Offset + 0x4), and read 2 bytes as the column number of the current cell in unsigned int type from the position of the offset (Record_Offset + 0x6) of the Buffer.

[0049] In addition, when the third Record ID does not belong to the Record ID value set, or the third Record ID is a preset termination value (the termination value in this embodiment can be 0x000a), the operation of reading the attribute information of the cell is aborted, which means that the extraction of the cell data of the current worksheet is completed.

[0050] It should be noted that the determination of the cell value corresponding to each target Record ID and the shared string list array in step S52 of this embodiment includes, but is not limited to, the following steps: Step S521: Determine the target type of the corresponding cell based on the target Record ID and the preset mapping table, where the mapping table represents the mapping relationship between the target Record ID and the cell type; Step S522: When the target type is a shared string cell, parse the cell value of the current cell corresponding to the target Record ID to obtain the target index, and determine the string corresponding to the target index in the shared string list array as the final cell value; Step S523: When the target type is a cell that directly stores a string, determine the value stored in the data position of the Record corresponding to the cell as the cell value.

[0051] It can be understood that, referring to the description of the above embodiments, the cell types corresponding to different target Record IDs are different. In this embodiment, the target type of the cell corresponding to the target Record ID is determined through a preset mapping table. When the target type is a shared string cell (for example, the target Record ID is 0x00fd), the value stored in the data position of the Record of the cell corresponding to the target Record ID is parsed to obtain the target index, and the string corresponding to the target index in the shared string list array is determined as the final cell value; when it is determined that the target type is a cell directly storing a string, the value stored in the data position of the Record of the corresponding cell is determined as the cell value.

[0052] In addition, in some embodiments, each piece of attribute information is uniquely associated with a worksheet. After storing the attribute information into the worksheet data list array in step S50, the method for extracting table data of the damaged xls document in this embodiment further includes but is not limited to the following steps: Step S54: Traverse the worksheet data list array in a loop. When it is detected that the attribute information of any two worksheets is exactly the same, delete any one of the worksheets; Step S55: When it is detected that the similarity of all the attribute information in any two worksheets is greater than a preset threshold, delete the worksheet corresponding to the attribute information with a smaller quantity.

[0053] It can be understood that this embodiment takes into account that in a damaged xls document, due to reasons such as incorrect saving by office software, the same worksheet data may be repeatedly saved in multiple regions of the binary data of the damaged xls document, and the worksheet data in each region may be the same or partially the same. To ensure the accuracy of the target table data, after obtaining all the worksheet data and storing it into the worksheet data list array Sheet_Data_List, data identification and cleaning operations need to be performed. In this embodiment, the worksheet data list array Sheet_Data_List is traversed in a loop. If two completely identical worksheet data are identified, only one of them will be retained. If two worksheet data Sheet_Data with a similarity exceeding the preset threshold (the value in this embodiment is 60%) are identified, the one containing more data information will be retained.

[0054] Step S60: After closing the damaged xls document and releasing the memory buffer, traverse the worksheet data list array and output the target table data in a preset format.

[0055] It can be understood that, compared with the existing method of relying on the integrity of xls to read table data, in this embodiment, a comprehensive scan of the damaged xls document is performed at the binary level, effectively eliminating the dependence on the integrity of the xls document, and being able to maximize the extraction of valid table data from the damaged xls document under different degrees of damage.

[0056] It should be noted that since the ultimate goal of this application is to extract as much available table data as possible for the user from the damaged xls document, in the embodiments of this application, all types of data are uniformly converted into strings (i.e., the preset format of this application) to facilitate subsequent output and utilization.

[0057] It can be understood that, in some embodiments, the method for extracting table data of the damaged xls document in this embodiment is applied to a table data extraction system. As Figure 3 shown, the table data extraction system includes a buffer management module 310, an SST search and positioning module 320, an SST reading module 330, a Sheet search and positioning module 340, a cell search and positioning module 350, a cell reading module 360, and an output module 370. Among them, the buffer management module 310 is used to perform a binary scan operation in the Buffer each time. The SST search and positioning module 320 and the SST reading module 330 are used to locate and read the SST from the Buffer. The Sheet search and positioning module 340 is used to locate and read the worksheet Sheet from the Buffer. The cell search and positioning module 350 is used to locate cells in combination with the worksheet search results. The cell reading module 360 is used to read the attribute information of cells in combination with the SST. The output module 370 is used to output all the attribute information parsed from the Buffer in a preset format.

[0058] As Figure 2 shown, Figure 2 is the structural diagram of a control device provided by an embodiment of this application. The present invention also provides a control device 200, including: A processor 210, which can be implemented in ways such as a general-purpose central processing unit (CPU), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided by the embodiments of this application; The memory 220 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM), etc. The memory 220 can store an operating system and other application programs. When implementing the technical solutions provided in the embodiments of this specification through software or firmware, the relevant program codes are stored in the memory 220, and the processor 210 is used to call and execute the method for extracting tabular data from the damaged xls document in the embodiments of this application; The input / output interface 230 is used to implement information input and output; The communication interface 240 is used to implement communication interaction between this device and other devices. It can communicate through a wired manner (such as USB, network cable, etc.) or through a wireless manner (such as mobile network, WIFI, Bluetooth, etc.); The bus 250 transmits information between various components of the device (such as the processor 210, the memory 220, the input / output interface 230, and the communication interface 240); Among them, the processor 210, the memory 220, the input / output interface 230, and the communication interface 240 are communicatively connected to each other inside the device through the bus 250.

[0059] In addition, the embodiments of this application also provide a tabular data extraction system, including the control device 200 in the above embodiments.

[0060] In addition, the embodiments of this application also provide a storage medium. The storage medium is a computer-readable storage medium. This storage medium stores a computer program, and when the computer program is executed by a processor, it implements the method for extracting tabular data from the damaged xls document described above.

[0061] As a non-transitory computer-readable storage medium, the memory can be used to store non-transitory software programs and non-transitory computer-executable programs. In addition, the memory can include a high-speed random access memory, and can also include non-transitory memory, such as at least one magnetic disk storage device, a flash memory device, or other non-transitory solid-state storage devices. In some embodiments, the memory can optionally include a memory remotely set relative to the processor, and these remote memories can be connected to the processor through a network. Examples of the above networks include but are not limited to the Internet, an enterprise intranet, a local area network, a mobile communication network, and combinations thereof. The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated, and may be located in one place, or may be distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solutions in this embodiment.

[0062] Those of ordinary skill in the art will understand that all or some of the steps and systems disclosed in the above methods can be implemented as software, firmware, hardware, and their appropriate combinations. Some physical components or all physical components can be implemented as software executed by a processor, such as a central processing unit, a digital signal processor, or a microprocessor, or implemented as hardware, or implemented as an integrated circuit, such as an application-specific integrated circuit. Such software can be distributed on a computer-readable medium, which can include a computer storage medium (or non-transitory medium) and a communication medium (or transitory medium). As is well known to those of ordinary skill in the art, the term computer storage medium includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information, such as computer-readable instructions, data structures, program modules, or other data. Computer storage media include, but are not limited to, RAM, ROM, EEPROM, flash memory or other memory technologies, CD-ROM, digital versatile disk (DVD) or other optical disk storage, magnetic cassettes, tapes, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store the desired information and can be accessed by a computer. In addition, as is well known to those of ordinary skill in the art, communication media typically include computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transmission mechanism, and can include any information delivery medium.

[0063] The above is a specific description of the preferred embodiments of the present invention, but the present invention is not limited to the above embodiments. Those skilled in the art can also make various equivalent deformations or substitutions without departing from the spirit of the present invention, and these equivalent deformations or substitutions are all included within the scope defined by the claims of the present invention.

Claims

1. A method for extracting tabular data from a damaged xls document, characterized in that, Including: Obtain the binary data of the damaged xls document and write the binary data into a memory buffer; Scan the binary data starting from the initial offset position of the memory buffer to determine the first offset position at the start of the most likely shared string table SST, where the initial offset position corresponds to an offset of 0 in the memory buffer; Read the target shared string from the memory buffer based on the first offset position and store the target shared string into an array of shared string lists; Scan the binary data starting from the initial offset position to determine the second offset position at the start of the most likely worksheet, and read the third offset position at the start of the most likely cell from the memory buffer based on the second offset position, where the number of the second offset positions is at least 1 and the number of the cells is at least 1; Perform binary scanning starting from the third offset position, determine the attribute information of each of the cells in combination with the array of shared string lists, and store the attribute information into an array of worksheet data lists, where the attribute information includes the row number, column number, and cell value of the corresponding cell; After closing the damaged xls document and releasing the memory buffer, traverse the array of worksheet data lists and output the target table data in a preset format.

2. The method for extracting tabular data of a damaged xls document according to claim 1, wherein Scanning the binary data starting from the initial offset position of the memory buffer to determine the first offset position at the start of the most likely shared string table SST includes: Based on the first binary character sequence corresponding to the first Record ID of the SST, scan the binary data starting from the initial offset position to obtain a plurality of candidate offset positions that match the first binary character sequence, where the first Record ID includes a main Record ID and / or an extended Record ID; Read a first sequence from the memory buffer based on any one of the candidate offset positions and a first byte value, and read a second sequence from the memory buffer based on any one of the candidate offset positions and a second byte value. When the candidate offset position is the actual offset position of the SST in the memory buffer, the corresponding first sequence represents the total number of strings referenced in all cells of the damaged xls document, and the corresponding second sequence represents the number of unique strings actually stored in the SST; Establish a target condition based on the first sequence, second sequence, first threshold, and second threshold corresponding to each of the candidate offset positions, where the target condition is used to indicate that the second sequence is less than or equal to the first sequence, the second sequence is less than or equal to the second threshold, and the first sequence is less than or equal to the first threshold; Determine the candidate offset position that satisfies the target condition as the first offset position, where the first offset position includes the first position corresponding to the main Record ID and / or the second position corresponding to the extended Record ID.

3. The method for extracting tabular data of a damaged xls document according to claim 2, wherein When the first offset bit includes the first position and the second position, reading a target shared string from the memory buffer based on the first offset position and storing the target shared string into the shared string list array includes: Determining a first number of data bytes of an information block corresponding to the first position and determining a second number of data bytes in an information block corresponding to the second position; Establishing a temporary buffer in the memory, where the length of the temporary buffer is greater than or equal to the sum of the first number of data bytes and the second number of data bytes and less than the document length of the damaged xls document; Initializing all bytes of the temporary buffer to 0; Locating, in the memory buffer, an offset position corresponding to a data part in the information block corresponding to the first position and copying byte content with a byte count of the first number of bytes to the temporary buffer; Locating, in the memory buffer, an offset position corresponding to a data part in the information block corresponding to the second position and copying byte content with a byte count of the second number of bytes to the temporary buffer, and determining, as the target shared string, byte content in the temporary buffer other than bytes with a value of 0; Sequentially reading and parsing each of the target shared strings from the temporary buffer and storing them into the shared string list array in the form of Unicode strings.

4. The method for extracting tabular data of a damaged xls document according to claim 1, wherein Scanning the binary data starting from the initial offset position to determine a second offset position at the most likely start of a worksheet, and reading, based on the second offset position, a third offset position at the most likely start of a cell from the memory buffer, including: Scanning the binary data starting from the initial offset position based on a second binary character sequence corresponding to a second Record ID of the worksheet to obtain a plurality of second offset positions that match the second binary character sequence; Saving all of the second offset positions to a worksheet offset position list array; Inserting the initial offset position as a new second candidate offset position at the front end of the worksheet offset position list array; Determining a worksheet data area, where the worksheet data area includes binary data between any one of the second offset positions in the worksheet offset position list array in the memory buffer and the corresponding next second offset position, or the worksheet data area includes binary data between any one of the second offset positions in the worksheet offset position list array in the memory buffer and the end position of the memory buffer, the number of worksheet data areas is the same as the number of second offset positions, and different worksheet data areas correspond to different worksheets; Searching, in each of the worksheet data areas, for a fourth offset position that matches a third binary character sequence corresponding to a third Record ID of row information; For any of the worksheet data regions, calculate the fourth offset position of the information block corresponding to the next row information based on any of the fourth offset positions and the third data byte count of the information block corresponding to the corresponding third Record ID. Record all the fourth offset positions, match the third Record ID corresponding to each fourth offset position with a preset Record ID value set, and determine the fourth offset position corresponding to the successfully matched third Record ID as the third offset position, where the number of the third offset positions is multiple.

5. The method for extracting tabular data of a damaged xls document according to claim 4, wherein Start binary scanning from the third offset position, and determine the attribute information of each cell in combination with the shared string list array, and store the attribute information into the worksheet data list array, including: Read the third Record ID of each cell corresponding to the third offset position from the memory buffer. When the third Record ID belongs to the Record ID value set, determine the corresponding third offset position as a valid target offset position, locate each target offset position in the memory buffer, read the row number, column number and target Record ID of the cell corresponding to each target offset position, and determine the cell value of the corresponding cell based on each target Record ID and the shared string list array. When the third Record ID does not belong to the Record ID value set, or the third Record ID is a preset termination value, abort the read operation of the attribute information of the cell.

6. The method for extracting tabular data of a damaged xls document according to claim 5, wherein, Determine the cell value of the corresponding cell based on each target Record ID and the shared string list array, including: Determine the target type of the corresponding cell based on the target Record ID and a preset mapping table, where the mapping table represents the mapping relationship between the target Record ID and the cell type. When the target type is a shared string cell, parse the value stored in the data position of the Record of the cell corresponding to the corresponding target Record ID to obtain a target index, and determine the string corresponding to the target index in the shared string list array as the final cell value. When the target type is a cell directly storing a string, determine the value stored in the data position of the Record of the corresponding cell as the cell value.

7. The method for extracting tabular data of a damaged xls document according to claim 5, characterized in that, Each piece of the attribute information is uniquely associated with one worksheet. After storing the attribute information into the worksheet data list array, the method further includes: Circularly traverse the worksheet data list array. When it is detected that the attribute information of any two worksheets is exactly the same, delete any one of the worksheets. When the similarity of all the attribute information in any two of the worksheets is detected to be greater than a preset threshold, delete the worksheet corresponding to the attribute information with a smaller quantity.

8. A control device, characterized in that, It includes at least one control processor and a memory for communicatively connecting with the at least one control processor; the memory stores instructions executable by the at least one control processor, and the instructions are executed by the at least one control processor to enable the at least one control processor to execute the method for extracting table data of a damaged xls document as described in any one of claims 1 to 7.

9. A tabular data extraction system, characterized in that, It includes the control device as described in claim 8.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions for causing a computer to execute the method for extracting table data of a damaged xls document as described in any one of claims 1 to 7.

Images