Real-time synchronization and efficient uploading method based on Excel document index data

Abstract

The invention is suitable for the technical field of data processing, and provides a real-time synchronization and efficient uploading method based on Excel document index data, and the method comprises the following steps: receiving any continuous region selected by a user, the any continuous region comprising a date column, and automatically expanding based on direction judgment and boundary detection to obtain a complete data range; when the expanded complete data range contains null rows or null columns, identifying each null row or null column as an independent index group; index names are extracted from the first row or the first column of the complete data range, frequency is automatically deduced according to date column intervals, and the index names are written into a metadata table; compressing and uploading the expanded complete data; and performing synchronization and bidirectional updating of the Excel and the server, and during synchronization, executing a unique identification mechanism and event-driven updating. And through intelligent frame selection, automatic expansion, multi-index identification and other mechanisms, manual operation is significantly reduced, and the data uploading efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a real-time synchronization and efficient uploading method based on Excel document indicator data. Background Art

[0002] Currently, data collection and uploading methods mainly rely on users manually selecting data and importing it into the system, such as traditional Excel data import tools (such as Microsoft Power Query, ETL tools, etc.) or achieving data synchronization through VBA macros. These methods usually require manual operation by the user, making it difficult to automatically expand the data range. In addition, when synchronizing data, there is a lack of an effective identification mechanism, which makes it difficult to track data after it is updated. In addition, existing patents, such as CN106250472A, describe an Excel data import method, but it mainly relies on a fixed data area and cannot intelligently expand and automatically synchronize updated data. In addition, the existing technology relies on file path or sheet name matching. If the file is renamed or moved, the synchronization link is interrupted. For example, a company uses Power Query to import data regularly, but historical data is lost due to file name changes. In addition, there is also the problem of insufficient support for multiple indicators. When there are multiple scattered indicator areas in Excel (such as revenue, cost, and profit data listed separately in financial statements), existing tools need to upload them one by one and cannot automatically identify indicator groups separated by adjacent blank rows / columns. Therefore, it is necessary to provide a real-time synchronization and efficient upload method based on Excel document indicator data to solve the above problems. Summary of the Invention

[0003] In view of the deficiencies in the prior art, the purpose of the present invention is to provide a real-time synchronization and efficient uploading method based on Excel document indicator data to solve the problems existing in the above-mentioned background technology.

[0004] The present invention is implemented in this way, based on a real-time synchronization and efficient uploading method of Excel document indicator data, the method comprising the following steps:

[0005] Receive any continuous area selected by the user, wherein the continuous area includes a date column.

[0006] Automatic expansion based on direction determination and boundary detection to obtain the complete data range;

[0007] Perform multi-indicator separation: When the expanded complete data range contains blank rows or columns, identify each blank row or column as an independent indicator group;

[0008] Perform attribute parsing: Extract the indicator name from the first row or column of the complete data range, automatically infer the frequency based on the date column interval, and write it to the metadata table;

[0009] Compress and upload the expanded complete data;

[0010] Perform synchronization and bidirectional updates between Excel and the server. During synchronization, a unique identification mechanism and event-driven updates are implemented.

[0011] As a further solution of the present invention: the step of automatically expanding the complete data range based on direction determination and boundary detection specifically includes:

[0012] The indicator direction is determined by the data distribution of adjacent cells in any continuous area. If the date column extends continuously downward and the value column is parallel to the date column, it is determined to be a vertical indicator and the indicator direction is vertical expansion; if the date row extends horizontally and the value row is parallel to the date row, it is determined to be a horizontal indicator and the indicator direction is horizontal expansion;

[0013] Traverse the non-empty cells along the determination direction until N empty cells appear consecutively. N is a fixed value, the boundary of the data area is determined, and the complete data range is obtained.

[0014] As a further solution of the present invention: the step of compressing and uploading the expanded complete data specifically includes:

[0015] Data compression: The expanded complete data is stored in sparse matrix format by column, and only non-null values and their coordinates are uploaded;

[0016] Batch upload: Using a block transfer protocol, a single request can upload multiple indicator data blocks to reduce network latency;

[0017] Format compatibility: Automatically identify numeric values, percentages, and time series formats through type inference algorithms and convert them into standardized types on the server.

[0018] As a further solution of the present invention, the synchronization and bidirectional update of Excel and the server, during synchronization, the steps of executing the unique identification mechanism and event-driven update specifically include:

[0019] When Excel data is synchronized to the server, the UUID is embedded in the Excel document properties;

[0020] Use Excel VBA to monitor Sheet rename events and trigger server-side metadata updates to ensure uninterrupted synchronization links.

[0021] Write the indicator ID in the cell comment in the upper left corner of the data area, and the server will associate the data version through the ID.

[0022] As a further solution of the present invention, the synchronization and bidirectional update of Excel and the server, during synchronization, the steps of executing the unique identification mechanism and event-driven update further include:

[0023] The server-side data is synchronized to Excel. When the server returns data, it carries the version number. Excel compares the local version and only downloads the incremental data.

[0024] When the data sent by the server is written into Excel, the data source ID and update timestamp are automatically recorded in the corresponding cell comments, forming a two-way binding relationship.

[0025] As a further solution of the present invention: the method also includes an exception handling and fault tolerance mechanism, and the specific steps are:

[0026] Version snapshot: Generate a data snapshot before each upload. If the upload fails, it will automatically roll back to the previous version.

[0027] Log tracking: records operation logs and supports data recovery by time point;

[0028] Conflict detection: If the server data is modified by a third party during synchronization, a conflict resolution interface will pop up for the user to choose to keep the local or server version.

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

[0030] 1. Improve upload efficiency: Through mechanisms such as intelligent selection, automatic expansion, and multi-indicator identification, manual operations are significantly reduced and the efficiency of data upload is improved.

[0031] 2. Enhance data integrity: By parsing indicator attributes, ensure that uploaded data has complete key information such as name and frequency to avoid human errors.

[0032] 3. Improve the reliability of data synchronization: adopt a unique identification mechanism and annotate the storage indicator ID to ensure the uniqueness and traceability of the data.

[0033] 4. Support bidirectional data flow: data can be uploaded from Excel to the server, and data can also be synchronized from the server back to Excel, achieving seamless data management. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Figure 1 The flowchart of the real-time synchronization and efficient uploading method based on Excel document indicator data.

[0035] Figure 2 The present invention is a flowchart for expanding the data range in the real-time synchronization and efficient uploading method of Excel document indicator data.

[0036] Figure 3 This is a flowchart for compressing uploaded data in a real-time synchronization and efficient uploading method based on Excel document indicator data.

[0037] Figure 4 This is a flowchart for data synchronization update in the real-time synchronization and efficient uploading method based on Excel document indicator data.

[0038] Figure 5 This is a flowchart for exception handling in the real-time synchronization and efficient uploading method based on Excel document indicator data. DETAILED DESCRIPTION

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

[0040] The specific implementation of the present invention is described in detail below with reference to specific embodiments.

[0041] like Figure 1 As shown, an embodiment of the present invention provides a real-time synchronization and efficient uploading method based on Excel document indicator data, the method comprising the following steps:

[0042] S100, receiving any continuous area selected by the user, wherein the any continuous area includes a date column,

[0043] S200, automatically expands based on direction determination and boundary detection to obtain the complete data range;

[0044] S300, performing multi-index separation: when the expanded complete data range contains blank rows or blank columns, each blank row or blank column is identified as an independent indicator group;

[0045] S400, perform attribute parsing: extract the indicator name from the first row or first column of the complete data range, automatically infer the frequency based on the date column interval, and write it into the metadata table;

[0046] S500 compresses and uploads the expanded complete data;

[0047] S600 performs synchronization and bidirectional updates between Excel and the server. During synchronization, it implements a unique identification mechanism and event-driven updates.

[0048] In an embodiment of the present invention, user input is simplified, and the user only needs to select any continuous area (such as 3 rows × 2 columns). The arbitrary continuous area contains a date column. The embodiment of the present invention will automatically expand based on direction determination and boundary detection to obtain a complete data range. When the expanded complete data range contains blank rows or columns, each blank row or column will be automatically identified as an independent indicator group. Then attribute parsing will be performed: the indicator name will be extracted from the first row or first column of the complete data range, the frequency will be automatically inferred based on the date column interval (such as day, month, quarter), and written to the metadata table. The expanded complete data is then compressed and uploaded, and Excel and the server are synchronized and updated in both directions. During synchronization, a unique identification mechanism and event-driven update are executed, and UUID identification files are used to annotate and store indicator IDs to ensure the uniqueness and traceability of the data. It also supports two-way data flow, which can upload data from Excel to the server and synchronize data from the server back to Excel to achieve seamless data management.

[0049] like Figure 2 As shown, in the embodiment of the present invention, the step of automatically expanding the complete data range based on direction determination and boundary detection specifically includes:

[0050] S201, determining the indicator direction based on the data distribution of adjacent cells in any continuous region. If the date column extends continuously downward and the value column is parallel to the date column, it is determined to be a vertical indicator, and the indicator direction is vertical expansion; if the date row extends horizontally and the value row is parallel to the date row, it is determined to be a horizontal indicator, and the indicator direction is horizontal expansion;

[0051] S202, traverse the non-empty cells along the determination direction until N empty cells appear continuously, where N is a fixed value, N is configurable, and the default value is 2, to determine the data area boundary and obtain the complete data range.

[0052] like Figure 3 As shown, in the embodiment of the present invention, the step of compressing and uploading the expanded complete data specifically includes:

[0053] S501, Data Compression: The expanded complete data is stored in a sparse matrix format by column, and only non-null values and their coordinates are uploaded to reduce the amount of data transmitted. For example, if only 10% of the cells in a column have values, the compression rate is 90%.

[0054] S502, batch upload: using a block transfer protocol (such as HTTP / 2 multiplexing), a single request can upload multiple indicator data blocks to reduce network latency;

[0055] S503, Format Compatibility: Automatically identify formats such as numbers, percentages, and time series through type inference algorithms and convert them into standardized server-side types (such as Double and Timestamp).

[0056] like Figure 4 As shown, in the embodiment of the present invention, the synchronization and bidirectional update of Excel and the server are performed, and during synchronization, the steps of executing the unique identification mechanism and event-driven update specifically include:

[0057] S601: When Excel data is synchronized to the server, a unique identification mechanism is implemented: a UUID (e.g., x-sync-id:550e8400-e29b-41d4-a716-446655440000) is embedded in the Excel document properties. This allows historical data to be matched using the UUID even if the file is renamed or moved.

[0058] S602, event-driven update: Use Excel VBA to monitor the sheet rename event (Workbook_SheetRename) to trigger the server-side metadata update to ensure that the synchronization link is not interrupted;

[0059] S603, annotation association: write the indicator ID (such as <!--Indicator ID:1001-->) in the cell annotation in the upper left corner of the data area, and the server associates the data version through the ID.

[0060] like Figure 4 As shown, in the embodiment of the present invention, the synchronization and bidirectional update of Excel and the server, during synchronization, the steps of executing the unique identification mechanism and event-driven update also include:

[0061] S605, differential update: The server-side data is synchronized to Excel. When the server returns data, it carries the version number (such as version: 20231001). Excel compares the local version and only downloads the incremental data.

[0062] S606, reverse comment injection: When the data sent by the server is written into Excel, the data source ID and update timestamp are automatically recorded in the corresponding cell comment, forming a two-way binding relationship.

[0063] like Figure 5 As shown, in the embodiment of the present invention, the method further includes an exception handling and fault tolerance mechanism, and the specific steps are:

[0064] S701, version snapshot: Generate a data snapshot (based on hash value verification) before each upload. If the upload fails, it will automatically roll back to the previous version;

[0065] S702, log tracking: records operation logs (e.g., 2023-10-01 14:00:00 | User A | Upload indicator ID: 1001 Version: 2), supporting data recovery by time point;

[0066] S703, conflict detection: If the server data is modified by a third party during synchronization, a conflict resolution interface pops up for the user to choose to keep the local or server version.

[0067] In summary, this invention intelligently determines indicator direction and automatically expands based on data distribution, reducing manual user operations. It also enhances the intelligence of data selection, reduces user intervention, and improves operational efficiency. It supports multi-indicator selection, allowing the system to identify and expand connected data even when there are blank rows or columns between indicators. It also enhances data upload flexibility and supports complex data structures. A real-time synchronization mechanism automatically scans indicator data when Excel is opened and immediately uploads any changes detected to the server, ensuring real-time data and avoiding the lag associated with manual synchronization.

[0068] The above is only a detailed description of the preferred embodiments of the present invention, which is not intended to limit the present invention. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

[0069] It should be understood that, although the various steps in the flow chart of each embodiment of the present invention are shown in sequence according to the indication of the arrows, these steps are not necessarily performed in sequence according to the order indicated by the arrows. Unless otherwise specified herein, the execution of these steps is not strictly limited in order, and these steps can be performed in other orders. Moreover, at least a portion of the steps in each embodiment may include a plurality of sub-steps or a plurality of stages, and these sub-steps or stages are not necessarily performed at the same time, but can be performed at different times, and the execution order of these sub-steps or stages is not necessarily performed in sequence, but can be performed in turn or alternately with at least a portion of other steps or sub-steps or stages of other steps.

[0070] Those skilled in the art will appreciate that all or part of the processes in the above-mentioned embodiments can be implemented by instructing the relevant hardware through a computer program. The program can be stored in a non-volatile computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0071] Those skilled in the art will readily appreciate other embodiments of the present disclosure after considering the disclosure in the specification and examples. This application is intended to cover any variations, uses, or adaptations of the present disclosure that follow the general principles of the present disclosure and include common knowledge or customary techniques in the art not disclosed herein. The description and examples are to be considered merely as exemplary, and the true scope and spirit of the present disclosure are indicated by the claims.

Claims

1. A real-time synchronization and efficient uploading method based on Excel document indicator data, characterized in that: The method comprises the following steps: Receive any continuous area selected by the user, wherein the continuous area includes a date column. Automatic expansion based on direction determination and boundary detection to obtain the complete data range; Perform multi-indicator separation: When the expanded complete data range contains blank rows or columns, identify each blank row or column as an independent indicator group; Perform attribute parsing: Extract the indicator name from the first row or column of the complete data range, automatically infer the frequency based on the date column interval, and write it to the metadata table; Compress and upload the expanded complete data; Perform synchronization and bidirectional updates between Excel and the server. During synchronization, a unique identification mechanism and event-driven updates are implemented.

2. The real-time synchronization and efficient uploading method based on Excel document index data according to claim 1 is characterized in that: The step of automatically expanding the complete data range based on direction determination and boundary detection specifically includes: The indicator direction is determined by the data distribution of adjacent cells in any continuous area. If the date column extends continuously downward and the value column is parallel to the date column, it is determined to be a vertical indicator and the indicator direction is vertical expansion; if the date row extends horizontally and the value row is parallel to the date row, it is determined to be a horizontal indicator and the indicator direction is horizontal expansion; Traverse the non-empty cells along the determination direction until N empty cells appear consecutively. N is a fixed value, the boundary of the data area is determined, and the complete data range is obtained.

3. The real-time synchronization and efficient uploading method based on Excel document index data according to claim 1 is characterized in that: The steps of compressing and uploading the expanded complete data specifically include: Data compression: The expanded complete data is stored in sparse matrix format by column, and only non-null values and their coordinates are uploaded; Batch upload: Using a block transfer protocol, a single request can upload multiple indicator data blocks to reduce network latency; Format compatibility: Automatically identify numeric values, percentages, and time series formats through type inference algorithms and convert them into standardized types on the server.

4. The real-time synchronization and efficient uploading method based on Excel document index data according to claim 1 is characterized in that: The steps of synchronizing and bidirectionally updating Excel and the server, and executing a unique identification mechanism and event-driven update during synchronization, specifically include: When Excel data is synchronized to the server, the UUID is embedded in the Excel document properties; Use Excel VBA to monitor Sheet rename events and trigger server-side metadata updates to ensure uninterrupted synchronization links. Write the indicator ID in the cell comment in the upper left corner of the data area, and the server will associate the data version through the ID.

5. The real-time synchronization and efficient uploading method based on Excel document index data according to claim 4 is characterized in that: The steps of synchronizing and bidirectionally updating Excel and the server, and executing a unique identification mechanism and event-driven updating during synchronization, further include: The server-side data is synchronized to Excel. When the server returns data, it carries the version number. Excel compares the local version and only downloads the incremental data. When the data sent by the server is written into Excel, the data source ID and update timestamp are automatically recorded in the corresponding cell comments, forming a two-way binding relationship.

6. The real-time synchronization and efficient uploading method based on Excel document index data according to claim 1 is characterized in that: The method also includes an exception handling and fault tolerance mechanism, and the specific steps are: Version snapshot: Generate a data snapshot before each upload. If the upload fails, it will automatically roll back to the previous version. Log tracking: records operation logs and supports data recovery by time point; Conflict detection: If the server data is modified by a third party during synchronization, a conflict resolution interface will pop up for the user to choose to keep the local or server version.

Images