Method, device, system and medium for extracting table data from damaged xls documents

Abstract

This application discloses a method, device, system, and medium for extracting table data from a damaged xls document. The method comprises: scanning the damaged xls document's binary data starting at an initial offset in a memory buffer, determining a first offset at the most likely start of a SST and a second offset at the most likely start of a worksheet; reading a target shared string from the memory buffer based on the first offset and storing it in a shared string list array; reading a third offset at the most likely start of a cell from the memory buffer based on the second offset; determining attribute information for each cell based on the third offset and the shared string list array, storing the attribute information in a worksheet data list array, and traversing the worksheet data list array to output the target table data. This application comprehensively scans the damaged xls document at the binary level, maximizing the extraction of valid table data from the damaged xls document.

Description

Technical Field

[0001] The present application relates to, but is not limited to, the field of data processing technology, and in particular to a method, device, system, and medium for extracting table data from a damaged xls document. Background Art

[0002] XLS documents are a commonly used office document format. While using XLS documents, they can become corrupted due to viruses, power outages, office software crashes, and other factors. These corrupted XLS documents can become unavailable for opening, viewing, or editing, leading to document data loss and impacting user productivity. To recover table content from damaged XLS documents, a common method is to parse the OLE format information in the damaged XLS document and attempt to read the document's Workbook data stream to retrieve the table data. However, if the XLS document is severely damaged (e.g., its integrity is compromised), a valid Workbook data stream cannot be retrieved, making it impossible to read the document's table data. Summary of the Invention

[0003] The embodiments of the present application provide a method, device, system and medium for extracting table data from a damaged xls document, which can maximize the extraction of valid table data from the damaged xls document.

[0004] In a first aspect, an embodiment of the present application provides a method for extracting table data from a damaged xls document, comprising:

[0005] Obtaining binary data of the damaged xls document and writing the binary data into a memory buffer;

[0006] Scanning the binary data from an initial offset position of the memory buffer to determine a first offset position of a start of a shared string table SST with the greatest probability, wherein the initial offset position corresponds to an offset of 0 in the memory buffer;

[0007] Reading a target shared string from the memory buffer based on the first offset position, and storing the target shared string into a shared string list array;

[0008] Scanning the binary data from the initial offset position, determining a second offset position at the start of the worksheet with the greatest probability, and reading a third offset position at the start of the cell with the greatest probability from the memory buffer based on the second offset position, wherein the number of the second offset positions is at least one, and the number of the cell is at least one;

[0009] Starting a binary scan from the third offset position, determining attribute information of each of the cells in combination with the shared string list array, and storing the attribute information in a worksheet data list array, wherein the attribute information includes a row number, a column number, and a cell value of the corresponding cell;

[0010] 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.

[0011] In some embodiments, scanning the binary data from an initial offset position of the memory buffer to determine a first offset position of a most likely start of a shared string table SST includes:

[0012] Based on a first binary character sequence corresponding to a first Record ID of the SST, scanning the binary data starting from the initial offset position to obtain a plurality of candidate offset positions that match the first binary character sequence, wherein the first Record ID includes a main Record ID and / or an extended Record ID;

[0013] Reading a first sequence from the memory buffer based on any of the candidate offset positions and the first byte value, and reading a second sequence from the memory buffer based on any of the candidate offset positions and the second byte value, wherein 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 string is 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;

[0014] establishing a target condition based on the first sequence, the second sequence, the first threshold, and the second threshold corresponding to each candidate offset position, wherein 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;

[0015] The candidate offset position that meets the target condition is determined as the first offset position, wherein 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.

[0016] In some embodiments, when the first offset includes the first position and the second position, reading the target shared string from the memory buffer based on the first offset and storing the target shared string in the shared string list array includes:

[0017] determining a first data byte number in the information block corresponding to the first position, and determining a second data byte number in the information block corresponding to the second position;

[0018] Establishing a temporary buffer in a memory, wherein a length of the temporary buffer is greater than or equal to the sum of the number of bytes of the first data and the number of bytes of the second data, and less than the document length of the damaged xls document;

[0019] Initialize all bytes of the temporary buffer to 0;

[0020] Locating, in the memory buffer, an offset position corresponding to the data portion in the information block corresponding to the first position, and copying byte contents equal to the first number of bytes to the temporary buffer;

[0021] Locating, in the memory buffer, the offset position corresponding to the data portion in the information block corresponding to the second position, copying byte content equal to the second number of bytes to the temporary buffer, and determining the byte content in the temporary buffer except for the byte value of 0 as the target shared string;

[0022] Each target shared string is read and parsed one by one from the temporary buffer, and stored in the shared string list array in the form of a Unicode string.

[0023] In some embodiments, scanning the binary data from the initial offset position, determining a second offset position of the most likely start of the worksheet, and reading a third offset position of the most likely start of the cell from the memory buffer based on the second offset position includes:

[0024] Scanning the binary data from the initial offset position based on a second binary character sequence corresponding to a second Record ID in the worksheet to obtain a plurality of second offset positions that match the second binary character sequence;

[0025] Save all the second offset positions to the worksheet offset position list array;

[0026] Inserting the initial offset position as a new second candidate offset position into the front end of the worksheet offset position list array;

[0027] Determining a worksheet data area, wherein the worksheet data area includes binary data between any second offset position in the worksheet offset position list array in the memory buffer and a corresponding next second offset position, or the worksheet data area includes binary data between any second offset position in the worksheet offset position list array in the memory buffer and an end position of the memory buffer, the number of the worksheet data areas is the same as the number of the second offset positions, and different worksheet data areas correspond to different worksheets;

[0028] Searching for a fourth offset position in each of the worksheet data regions that matches a third binary character sequence corresponding to a third Record ID of the row information;

[0029] For any of the worksheet data areas, calculating the fourth offset position of the information block corresponding to the next row of information based on any of the fourth offset positions and the third data byte number of the information block corresponding to the corresponding third Record ID;

[0030] Record all of the fourth offset positions, and match the third Record ID corresponding to each of the fourth offset positions with a preset Record ID value set, and determine the fourth offset position corresponding to the third Record ID that is successfully matched as the third offset position, wherein the number of the third offset positions is multiple.

[0031] In some embodiments, starting a binary scan from the third offset position, determining attribute information of each cell in combination with the shared string list array, and storing the attribute information in a worksheet data list array includes:

[0032] Reading the third Record ID of the cell corresponding to each third offset position from the memory buffer;

[0033] When the third Record ID belongs to the Record ID value set, determining the corresponding third offset position as a valid target offset position, locating each target offset position in the memory buffer, reading the row number, the column number, and the target Record ID of the cell corresponding to each target offset position, and determining the cell value of the corresponding cell based on each target Record ID and the shared string list array;

[0034] 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 reading operation of the attribute information of the cell is terminated.

[0035] In some embodiments, determining the cell value of the corresponding cell based on each of the target Record IDs and the shared string list array includes:

[0036] Determining a target type of a corresponding cell based on the target Record ID and a preset mapping table, wherein the mapping table represents a mapping relationship between the target Record ID and the cell type;

[0037] When the target type is a shared string cell, the value stored in the data position of the record of the cell corresponding to the target Record ID is parsed to obtain a target index, and the string corresponding to the target index in the shared string list array is determined as the final cell value;

[0038] When the target type is a cell that directly stores a character string, the value stored in the data position of the corresponding Record of the cell is determined as the cell value.

[0039] In some embodiments, each piece of attribute information is uniquely associated with one worksheet. After storing the attribute information in the worksheet data list array, the method further includes:

[0040] Looping through the worksheet data list array, and when it is detected that the attribute information of any two worksheets is exactly the same, deleting any of the worksheets;

[0041] When it is detected that the similarity of all the attribute information in any two worksheets is greater than a preset threshold, the worksheets corresponding to the attribute information with a smaller amount are deleted.

[0042] In a second aspect, an embodiment of the present application provides a control device comprising at least one control processor and a memory for communicating with the at least one control processor; the memory stores instructions that can be executed 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 table data extraction method for the damaged xls document as described in the first aspect.

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

[0044] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium storing computer-executable instructions, wherein the computer-executable instructions are used to execute the table data extraction method of the damaged xls document as described in the first aspect.

[0045] The present application provides a method, apparatus, device, and medium for extracting table data from a damaged xls document. The method comprises: obtaining binary data from the damaged xls document and writing the binary data into a memory buffer; scanning the binary data from an initial offset position in the memory buffer to determine a first offset position at the start of a shared string table SST with the greatest probability, wherein the initial offset position corresponds to an offset of 0 in the memory buffer; reading a target shared string from the memory buffer based on the first offset position and storing the target shared string in a shared string list array; scanning the binary data from the initial offset position to determine a first offset position at the start of a shared string table SST with the greatest probability; The method comprises the following steps: first, reading a second offset position at the beginning of the worksheet, and reading a third offset position at the beginning of the cell with the greatest possibility from the memory buffer based on the second offset position, wherein 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 of the cells in combination with the shared string list array, and storing the attribute information in a worksheet data list array, wherein 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 worksheet data list array, and outputting the target table data in a preset format. Compared with the existing method of relying on the integrity of xls to read table 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 can maximize the extraction of valid table data in the damaged xls document. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] Figure 1 This is a flowchart of the steps of a method for extracting table data from a damaged xls document provided by an embodiment of the present application;

[0047] Figure 2 is a structural diagram of a control device provided by another embodiment of the present application;

[0048] Figure 3 This is a module diagram of a table data extraction system provided in another embodiment of the present application. DETAILED DESCRIPTION

[0049] In order to make the purpose, technical solutions and advantages of this application more clear, the following further describes this application in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0050] It is understood that although the device schematics illustrate functional module divisions and the flowcharts illustrate logical sequences, in certain circumstances, the steps shown or described may be performed in a sequence that differs from the module divisions in the device or the sequence in the flowcharts. The terms "first," "second," and the like in the specification, claims, or accompanying drawings are used to distinguish similar items and are not necessarily used to describe a specific sequence or precedence.

[0051] XLS documents are a commonly used office document format. While using XLS documents, they can become corrupted due to viruses, power outages, office software crashes, and other factors. These corrupted XLS documents can become unavailable for opening, viewing, or editing, leading to document data loss and impacting user productivity. To recover table content from damaged XLS documents, a common method is to parse the OLE format information in the damaged XLS document and attempt to read the document's Workbook data stream to retrieve the table data. However, if the XLS document is severely damaged (e.g., its integrity is compromised), a valid Workbook data stream cannot be retrieved, making it impossible to read the document's table data.

[0052] In order to solve the above-mentioned problems, the embodiments of the present application provide a method, apparatus, device and medium for extracting table data from a damaged xls document. The method comprises: obtaining binary data of the damaged xls document and writing the binary data into a memory buffer; scanning the binary data from an initial offset position of the memory buffer to determine a first offset position of the most likely starting point of a shared string table SST, wherein the initial offset position corresponds to an offset of 0 in the memory buffer; reading a target shared string from the memory buffer based on the first offset position and storing the target shared string in a shared string list array; scanning the binary data from the initial offset position to determine a first offset position of the most likely starting point of a shared string table SST. Determine the second offset position of the most likely start of the worksheet, and read the third offset position of the most likely start of the cell from the memory buffer based on the second offset position, wherein the number of the second offset positions is at least 1, and the number of the cells is at least 1; start binary scanning from the third offset position, determine the attribute information of each of the cells in combination with the shared string list array, and store the attribute information in the worksheet data list array, wherein 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. Compared with the existing method of relying on the integrity of xls to read table 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 can maximize the extraction of valid table data in the damaged xls document.

[0053] The embodiments of the present application are further described below with reference to the accompanying drawings.

[0054] refer to Figure 1 , Figure 1 This is a flowchart of a method for extracting table data from a damaged xls document provided by an embodiment of the present application. This embodiment of the present application provides a method for extracting table data from a damaged xls document, which includes but is not limited to the following steps:

[0055] Step S10: Obtain binary data of the damaged xls document and write the binary data into a memory buffer.

[0056] It can be understood that the method 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 xls format, providing effective support for subsequent effective table data acquisition of binary data.

[0057] It should be noted that the method for determining whether the damaged XLS document is in XLS format in this embodiment is to perform a binary search on the area of ​​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 XLS format.

[0058] Step S20 , scanning the binary data from the initial offset position of the memory buffer to determine the first offset position of the start of the shared string table SST with the greatest possibility, wherein the initial offset position corresponds to an offset of 0 in the memory buffer.

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

[0060] Specifically, in some embodiments, Figure 1 Step S20 includes but is not limited to the following steps:

[0061] Step S21: Scan binary data starting from an initial offset position based on a first binary character sequence corresponding to a first Record ID of the SST to obtain a plurality of candidate offset positions that match the first binary character sequence, wherein the first Record ID includes a main Record ID and / or an extended Record ID.

[0062] Step S22: Reading a first sequence from the memory buffer based on any candidate offset position and the first byte value, and reading a second sequence from the memory buffer based on any candidate offset position and the second byte value, wherein 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 string is 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;

[0063] Step S23: establishing a target condition based on the first sequence, the second sequence, the first threshold, and the second threshold corresponding to each candidate offset position, wherein 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;

[0064] Step S24: Determine the candidate offset position that meets the target condition as the first offset position, wherein 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.

[0065] As you can understand, in the XLS file format, different types of data are stored as information blocks (records). Each information block has a fixed header format, consisting of 4 bytes. The first 2 bytes are the Record ID, and the next 2 bytes following 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 current record offset, you can jump to the next record offset.

[0066] It should be noted that this embodiment does not limit the specific method of reading values ​​from the memory buffer. This embodiment adopts little-endian reading.

[0067] In this embodiment, for the Record corresponding to the SST, the Record ID value in the header format is 0x00fc, and the next two bytes are the Record Length. A binary scan is performed starting from the initial offset position at offset 0 in the memory buffer Buffer to search for the first binary character sequence { 0xfc, 0x00} of the first Record ID corresponding to the SST. Because only two bytes constitute this search feature sequence, multiple candidate offset positions are usually obtained. These multiple candidate offset positions need to be verified to confirm the most likely true offset position of the SST (Shared String Table). The verification algorithm is as follows: If the current candidate offset position of the suspected SST is SST_Offset, then 4 bytes are read from the offset position SST_Offset + 0x4 in the buffer in little-endian order to determine the first sequence B (unsigned int type). 4 bytes are read from the offset position SST_Offset + 0x8 in the buffer in little-endian order to determine the second sequence C (unsigned int type). When a candidate offset position meets the target condition, that is, 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 (C <= B, C <= C_Max, B <= B_Max), the current candidate offset position is considered to be the true offset of the SST (that is, the first offset).

[0068] Specifically, in this embodiment, the first threshold value B_Max is 500,000, and the second threshold value C_Max is 100,000. These two limit values ​​can be subsequently modified based on further analysis of damaged samples.

[0069] Furthermore, for some large data-intensive xls documents, their SSTs may contain not only a primary record ID of 0x00fc but also additional extended record IDs (0x003C or 0x00FF). These primary and extended record IDs correspond to different records. Records in xls documents are stored linearly and continuously. Therefore, to maximize the amount of table data extracted from damaged xls documents, this embodiment, after obtaining the first offset of the primary record corresponding to the primary record ID, continues with a binary scan to check whether the next record ID is 0x003c or 0x00ff. If so, the first offsets corresponding to these two extended records (the first offset SST_Offset and the second offsets SST_Continue_Offset and SST_Ext_Offset) are recorded, providing an effective data foundation for subsequent extraction of the complete target shared string.

[0070] 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.

[0071] 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:

[0072] Step S31, determining the number of first data bytes in the information block corresponding to the first position, and determining the number of second data bytes in the information block corresponding to the second position;

[0073] Step S32: establishing a temporary buffer in the memory, wherein 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;

[0074] Step S33, initializing all bytes in the temporary buffer to 0;

[0075] Step S34, locating the offset position corresponding to the data portion in the information block corresponding to the first position in the memory buffer, and copying the byte content equal to the first number of bytes to the temporary buffer;

[0076] Step S35, locating the offset position corresponding to the data portion in the information block corresponding to the second position in the memory buffer, copying the byte content equal to the second number of bytes to the temporary buffer, and determining the byte content in the temporary buffer except the byte value of 0 as the target shared string;

[0077] Step S36 , reading and parsing each target shared string from the temporary buffer one by one, and storing them in the shared string list array in the form of Unicode strings.

[0078] It can be understood that, referring to the description of the above embodiment, when the first offset position includes the first position and the second position, that is, 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, which respectively determine the first data byte number of the information block corresponding to the first position and the second data byte number in the information block corresponding to the second position. The first data byte number is the Record Length of the main Record ID corresponding to the SST, and the second data byte number is the Record Length of the extended Record ID corresponding to the SST. A temporary buffer is established in the memory, wherein 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 is less than the document length of the damaged xls document. This ensures that the length of the temporary buffer is not less than the sum of the Record Lengths of the individual Records corresponding to the SST. However, given that the data of the damaged xls document may not be reliable, this embodiment needs to limit the length of the temporary buffer to less than a limit value. In this embodiment, this limit value is the document length of the current damaged xls document.

[0079] Specifically, after determining the existence of the first position and the second position, this embodiment pre-initializes all bytes in the temporary buffer to 0 before saving the target shared string corresponding to the SST to the temporary buffer, that is, writing all 0x0 to the temporary buffer, which can eliminate random values ​​in the memory and prevent residual data from interfering with subsequent parsing. After clearing the temporary buffer, this embodiment locates the offset position corresponding to the data portion in the information block corresponding to the first position in the memory buffer, and copies the byte content of the first byte number to the temporary buffer; and locates the offset position corresponding to the data portion in the information block corresponding to the second position in the memory buffer, and copies the byte content of the second byte number to the temporary buffer, and determines the byte content in the temporary buffer except for the byte value of 0 as the target shared string; finally, each target shared string is read and parsed one by one from the temporary buffer, and stored in the shared string list array as a Unicode string.

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

[0081] In addition, given that data in a damaged xls document may be corrupted or lost, this embodiment also performs a validity check on the shared string list array SST_String_List. The validity check includes, but is not limited to, checking whether the first string in SST_String_List is empty and whether the length of SST_String_List, i.e., the number of strings in SST_String_List, is within a preset range, which in this embodiment is 10-10,000.

[0082] Step S40, scanning the binary data starting from the initial offset position, determining the second offset position of the most likely start of the worksheet, and reading the third offset position of the most likely start of the cell from the memory buffer based on the second offset position, wherein the number of second offset positions is at least 1 and the number of cells is at least 1.

[0083] Specifically, in some embodiments, Figure 1 Step S40 includes but is not limited to the following steps:

[0084] Step S41, based on the second binary character sequence corresponding to the second Record ID in the worksheet, scanning the binary data starting from the initial offset position to obtain a plurality of second offset positions that match the second binary character sequence;

[0085] Step S42, saving all second offset positions to the worksheet offset position list array;

[0086] Step S43: inserting the initial offset position as a new second candidate offset position into the front end of the worksheet offset position list array;

[0087] Step S44: determining a worksheet data region, wherein the worksheet data region includes binary data between any second offset position in the worksheet offset position list array in the memory buffer and the corresponding next second offset position, or the worksheet data region includes 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 regions is the same as the number of second offset positions, and different worksheet data regions correspond to different worksheets;

[0088] Step S45, searching in each worksheet data area for a fourth offset position that matches the third binary character sequence corresponding to the third Record ID of the row information;

[0089] Step S46, for any worksheet data area, calculate the fourth offset position of the information block corresponding to the next row of information based on any fourth offset position and the third data byte number of the information block corresponding to the corresponding third Record ID;

[0090] Step S47, record all fourth offset positions, and match the third RecordID 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, wherein the number of third offset positions is multiple.

[0091] It will be appreciated that this embodiment takes into account the possibility that the data stored in the damaged xls document may be out of order. By using the second binary character sequence corresponding to the second record ID of the worksheet (corresponding to { 0x09, 0x08, 0x10, 0x00, 0x00, 0x06, 0x10, 0x00}), the binary data is scanned starting from the initial offset position in the memory buffer. After obtaining multiple second offset positions Sheet_Offset that match the second binary character sequence, all second offset positions are saved to the worksheet offset position list array Sheet_Offset_List. Here, each second offset position corresponds to one worksheet. If 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 be truncated or have disordered or duplicated worksheets, in order to help users extract useful table data as much as possible, the offset 0 of the buffer (i.e., the initial offset position) is also inserted as a second offset position Sheet_Offset to the front of the Sheet_Offset_List. At this time, if the length of the Sheet_Offset_List (i.e., the number of Sheet_Offsets) is n, then the data content of the current damaged xls document is divided into n areas in the buffer.

[0092] It is understandable that, after detecting and determining a worksheet data area, wherein the worksheet data area includes binary data between any second offset position 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 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; searching for a fourth offset position Row_Offset that matches a third binary character sequence (corresponding to {0x08, 0x02, 0x10, 0x00}) corresponding to the third Record ID of the row information in each worksheet data area; treating the Row_Offset as the starting position of a Record, for any worksheet data area, calculating 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 number (i.e., Record Length) of the information block corresponding to the corresponding third Record ID; recording all fourth offset positions, and adding the third Record ID corresponding to each fourth offset position to the row information. The ID is matched with a preset set of Record ID values, and the fourth offset position corresponding to the third Record ID that successfully matches is determined as the third offset position, where there are multiple third offset positions. That is, after reading a Row_Offset, this embodiment matches the Record ID of the Record corresponding to the Row_Offset with the preset set of Record ID values. If the match is successful, the Record corresponding to the successfully matched Record ID is determined by this embodiment to be a Record that is valuable for the user to extract, that is, the information block corresponding to the cell.

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

[0094] Step S50, starting binary scanning from the third offset position, determining the attribute information of each cell in combination with the shared string list array, and storing the attribute information in the worksheet data list array, wherein the attribute information includes the row number, column number and cell value of the corresponding cell.

[0095] Specifically, the attribute information corresponding to each cell includes three key information: row number, column number, and cell value. One attribute information is uniquely associated with a worksheet. This embodiment defines a two-dimensional array, namely a worksheet data list array, to store all attribute information corresponding to each worksheet.

[0096] Furthermore, given the unreliability of data in damaged xls files, the attribute information determined in this embodiment may contain abnormally large row or column numbers. To avoid this, this embodiment filters the row and column numbers before writing the row and column numbers, and the cell data value (in string format), corresponding to each cell's attribute information, into the corresponding worksheet data list array. In this embodiment, cells with row numbers greater than 500,000 or column numbers greater than 1,000 are considered abnormal cells, and the attribute information corresponding to these abnormal cells is not stored in the worksheet data list array to avoid misleading the user.

[0097] Specifically, in some embodiments, Figure 1 Step S50 includes but is not limited to the following steps:

[0098] Step S51, reading the third RecordID of each cell corresponding to the third offset position from the memory buffer;

[0099] Step S52: When the third Record ID belongs to the Record ID value set, the corresponding third offset position is determined as a valid target offset position, each target offset position is located in the memory buffer, the row number, column number, and target Record ID of the cell corresponding to each target offset position is read, and the cell value of the corresponding cell is determined based on each target Record ID and the shared string list array;

[0100] Step S53: When the third Record ID does not belong to the Record ID value set, or the third Record ID is a preset end value, the reading operation of the cell attribute information is terminated.

[0101] It can be understood that after determining the third offset position Cells_Offset of each cell, the third Record ID of the cell corresponding to each third offset position is read from the Buffer, and the third Record ID is compared 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. This operation provides effective support for the subsequent acquisition of valid cell attribute information.

[0102] Specifically, when the third Record ID belongs to the Record ID value set, the corresponding third offset position is determined as a valid target offset position, and each target offset position is located in the Buffer, and the row number, column number and target Record ID of the cell corresponding to each target offset position are read. Since different target Record IDs of cells correspond to different cell types, the data content stored in the Record corresponding to different cell types has different meanings. Therefore, 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 of 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 of unsigned int type from the offset (Record_Offset + 0x4) as the row number of the current cell, and read 2 bytes of unsigned int type from the offset (Record_Offset + 0x6) of the Buffer as the column number of the current cell.

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

[0104] It should be noted that, in step S52 of this embodiment, determining the cell value of the corresponding cell based on each target Record ID and the shared string list array includes but is not limited to the following steps:

[0105] Step S521: determining the target type of the corresponding cell based on the target Record ID and a preset mapping table, wherein the mapping table represents a mapping relationship between the target Record ID and the cell type;

[0106] Step S522: When the target type is a shared string cell, the current cell value of the cell corresponding to the corresponding 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;

[0107] Step S523: When the target type is a cell that directly stores a character string, the value stored in the data position of the Record of the corresponding cell is determined as the cell value.

[0108] It can be understood that, referring to the description of the above embodiment, different target Record IDs correspond to different cell types. This embodiment determines the target type of the cell corresponding to the target Record ID 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 corresponding 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 that directly stores a string, the value stored in the data position of the Record of the corresponding cell is determined as the cell value.

[0109] In addition, in some embodiments, each piece of attribute information is uniquely associated with a worksheet. After executing step S50 of storing the attribute information in the worksheet data list array, the method for extracting table data from a damaged xls document in this embodiment further includes but is not limited to the following steps:

[0110] Step S54, looping through the worksheet data list array, and when it is detected that the attribute information of any two worksheets is exactly the same, deleting any of the worksheets;

[0111] Step S55 : when it is detected that the similarity of all the attribute information in any two worksheets is greater than a preset threshold, the worksheet corresponding to the smaller amount of attribute information is deleted.

[0112] It's understandable that this embodiment takes into account that damaged xls documents may contain the same worksheet data, which, due to reasons such as office software saving errors, may be repeatedly stored in multiple areas of the damaged xls document's binary data. Furthermore, the worksheet data in each area may be identical or partially identical. To ensure the accuracy of the target table data, this embodiment requires data identification and cleaning after obtaining all worksheet data and storing it in the worksheet data list array, Sheet_Data_List. This embodiment loops through the worksheet data list array, Sheet_Data_List. If two identical worksheet data are identified, only one is retained. If two identical Sheet_Data items are identified with a difference exceeding a preset threshold (60% in this embodiment), the one containing more data is retained.

[0113] 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.

[0114] It can be understood that compared with the existing method of relying on the integrity of xls to read table data, this embodiment 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 can maximize the extraction of valid table data in the damaged xls document under different degrees of damage.

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

[0116] It is understood that in some embodiments, the table data extraction method of the damaged xls document of this embodiment is applied to the table data extraction system. Figure 3 As 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, wherein the buffer management module 310 is used to perform each binary scanning operation in the buffer, 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 the cell in combination with the worksheet search result, the cell reading module 360 ​​is used to read the attribute information of the cell in combination with the SST, and the output module 370 is used to output all the attribute information parsed from the buffer in a preset format.

[0117] like Figure 2 As shown, Figure 2 : is a structural diagram of a control device provided in one embodiment of the present application. The present invention also provides a control device 200, comprising:

[0118] The processor 210 may be implemented as a general-purpose central processing unit (CPU), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is configured to execute relevant programs to implement the technical solutions provided in the embodiments of the present application.

[0119] 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). The memory 220 can store an operating system and other application programs. When the technical solutions provided in the embodiments of this specification are implemented through software or firmware, the relevant program code is stored in the memory 220, and the processor 210 calls and executes the table data extraction method for the damaged xls document in the embodiments of this application.

[0120] Input / output interface 230, used to implement information input and output;

[0121] Communication interface 240, used to implement communication interaction between the apparatus and other devices, which can be achieved through wired means (such as USB, network cable, etc.) or wireless means (such as mobile network, WiFi, Bluetooth, etc.);

[0122] bus 250 , which transmits information between various components of the device (e.g., processor 210 , memory 220 , input / output interface 230 , and communication interface 240 );

[0123] The processor 210 , the memory 220 , the input / output interface 230 and the communication interface 240 are connected to each other in communication within the device via the bus 250 .

[0124] In addition, an embodiment of the present application further provides a table data extraction system, including the control device 200 of the above embodiment.

[0125] In addition, an embodiment of the present application further provides a storage medium, which is a computer-readable storage medium and stores a computer program. When the computer program is executed by a processor, the above-mentioned method for extracting table data from a damaged xls document is implemented.

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

[0127] Those skilled in the art will appreciate that all or some of the steps and systems disclosed above can be implemented as software, firmware, hardware, or any suitable combination thereof. Some or all of the physical components may be implemented as software executed by a processor, such as a central processing unit, digital signal processor, or microprocessor, or as hardware, or as an integrated circuit, such as an application-specific integrated circuit. Such software may be distributed on computer-readable media, which may include computer storage media (or non-transitory media) and communication media (or transient media). As is well known to those skilled in the art, the term computer storage media 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 includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technologies, CD-ROM, digital versatile disks (DVDs) or other optical disk storage, magnetic cassettes, magnetic tape, 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. Furthermore, as is well known to those skilled in the art, communication media typically includes computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transport mechanism, and may include any information delivery media.

[0128] The above is a specific description of the preferred implementation of the present invention, but the present invention is not limited to the above implementation. Those skilled in the art can also make various equivalent modifications or substitutions under the shared conditions that do not violate the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of the present invention.

Claims

1. A method for extracting table data from a damaged xls document, characterized in that: include: Obtaining binary data of the damaged xls document and writing the binary data into a memory buffer; Scanning the binary data from an initial offset position of the memory buffer to determine a first offset position of a start of a shared string table SST with the greatest probability, wherein the initial offset position corresponds to an offset of 0 in the memory buffer; Reading a target shared string from the memory buffer based on the first offset position, and storing the target shared string into a shared string list array; Scanning the binary data from the initial offset position, determining a second offset position at the start of the worksheet with the greatest probability, and reading a third offset position at the start of the cell with the greatest probability from the memory buffer based on the second offset position, wherein the number of the second offset positions is at least one, and the number of the cell is at least one; Starting a binary scan from the third offset position, determining attribute information of each of the cells in combination with the shared string list array, and storing the attribute information in a worksheet data list array, wherein the attribute information includes a row number, a column number, and a 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.

2. The method for extracting table data from a damaged xls document according to claim 1, wherein: Scanning the binary data from an initial offset position of the memory buffer to determine a first offset position of a start of a shared string table SST with the greatest probability includes: Based on a first binary character sequence corresponding to a first Record ID of the SST, scanning the binary data starting from the initial offset position to obtain a plurality of candidate offset positions that match the first binary character sequence, wherein the first Record ID includes a main Record ID and / or an extended Record ID; Reading a first sequence from the memory buffer based on any of the candidate offset positions and the first byte value, and reading a second sequence from the memory buffer based on any of the candidate offset positions and the second byte value, wherein 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 string is 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; establishing a target condition based on the first sequence, the second sequence, the first threshold, and the second threshold corresponding to each candidate offset position, wherein 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; The candidate offset position that meets the target condition is determined as the first offset position, wherein 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 table data from a damaged xls document according to claim 2, characterized in that: In a case where the first offset includes the first position and the second position, reading a target shared string from the memory buffer based on the first offset, and storing the target shared string in a shared string list array, comprises: determining a first data byte number in the information block corresponding to the first position, and determining a second data byte number in the information block corresponding to the second position; Establishing a temporary buffer in a memory, wherein a length of the temporary buffer is greater than or equal to the sum of the number of bytes of the first data and the number of bytes of the second data, and less than the document length of the damaged xls document; Initialize all bytes of the temporary buffer to 0; Locating, in the memory buffer, an offset position corresponding to the data portion in the information block corresponding to the first position, and copying byte contents equal to the first number of bytes to the temporary buffer; Locating, in the memory buffer, the offset position corresponding to the data portion in the information block corresponding to the second position, copying byte content equal to the second number of bytes to the temporary buffer, and determining the byte content in the temporary buffer except for the byte value of 0 as the target shared string; Each target shared string is read and parsed one by one from the temporary buffer, and stored in the shared string list array in the form of a Unicode string.

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

5. The method for extracting table data from a damaged xls document according to claim 4, characterized in that: Starting a binary scan from the third offset position, determining attribute information of each of the cells in combination with the shared string list array, and storing the attribute information in a worksheet data list array, including: Reading 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, determining the corresponding third offset position as a valid target offset position, locating each target offset position in the memory buffer, reading the row number, the column number, and the target Record ID of the cell corresponding to each target offset position, and determining 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, the reading operation of the attribute information of the cell is terminated.

6. The method for extracting table data from a damaged xls document according to claim 5, characterized in that: Determining a cell value of the corresponding cell based on each of the target Record IDs and the shared string list array includes: Determining a target type of a corresponding cell based on the target Record ID and a preset mapping table, wherein the mapping table represents a mapping relationship between the target Record ID and the cell type; When the target type is a shared string cell, the value stored in the data position of the record of the cell corresponding to the target Record ID is parsed to obtain a target index, and the string corresponding to the target index in the shared string list array is determined as the final cell value; When the target type is a cell that directly stores a character string, the value stored in the data position of the corresponding Record of the cell is determined as the cell value.

7. The method for extracting table data from a damaged xls document according to claim 5, wherein: Each piece of attribute information is uniquely associated with one worksheet. After storing the attribute information in the worksheet data list array, the method further includes: Looping through the worksheet data list array, and when it is detected that the attribute information of any two worksheets is exactly the same, deleting any of the worksheets; When it is detected that the similarity of all the attribute information in any two worksheets is greater than a preset threshold, the worksheets corresponding to the attribute information with a smaller amount are deleted.

8. A control device, characterized in that: It includes at least one control processor and a memory for communicating with the at least one control processor; the memory stores instructions that can be executed 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 table data extraction method for the damaged xls document as described in any one of claims 1 to 7.

9. A table data extraction system, characterized in that: Comprising the control device according to claim 8.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are used to enable a computer to execute the table data extraction method for a damaged xls document according to any one of claims 1 to 7.

Images