A template-based Excel document validity period protection method

Abstract

The present invention discloses a method for protecting the validity period of an Excel document based on a template, and the specific steps are as follows: Step 1, template making, specifically: creating a new blank Excel document as template T; setting a VB script and a VB project password for template T; setting auxiliary pages for template T, which are respectively used to enable macro prompts, time-out prompts, time-out prompts, validity period settings, and three blank pages; Step 2, template use, specifically: copying the original document to the template page by page; deleting redundant blank pages in the template; updating the effective start time and effective end time in the validity period setting page; setting the macro prompt page to a display state, and the remaining pages to a strong hidden state; and saving the template as a target file. The present invention can dynamically set the document validity period by constructing an auxiliary page, avoiding the problem that the VB project cannot be dynamically updated, and meeting people's use needs for controlling the validity period of Excel documents.

Description

Technical Field

[0001] The invention relates to document use period control, in particular to a template-based Excel document validity period protection method. Background Art

[0002] Microsoft Excel is a spreadsheet software developed by Microsoft. It has an intuitive operating interface, excellent calculation functions and rich chart tools. It is a popular data processing software today.

[0003] Excel itself provides a data-level validity period protection solution, which is implemented by setting functions. However, there is currently no default technical support for the validity period of Excel files themselves. Summary of the invention

[0004] The purpose of the present invention is to provide a template-based Excel document validity period protection method to solve the problems raised in the above background technology.

[0005] To achieve the above object, the present invention provides the following technical solutions:

[0006] A template-based Excel document validity period protection method, the specific steps are as follows:

[0007] Step 1: Template creation: Create a blank Excel file on a computer with Microsoft Excel installed, named Template T, and set the VB script and VB project passwords for Template T.

[0008] Open template T and create 4 new pages, one for macro enablement prompt, time not expired prompt, time expiration prompt, and validity period setting. In the macro enablement prompt page, enter prompt information to remind users to enable macros in order to view the document normally. In the time not expired prompt page, enter prompt information to remind users that the effective start time of the document has not yet arrived. In the time expiration prompt page, enter prompt information to remind users that the effective deadline of the document has passed. In the validity period setting page, select any 2 cells to record the effective start time and the effective deadline.

[0009] At the same time, the VB script reads the effective start time and effective end time in the validity period setting page, and compares them with the current system time: if the current system time is within the effective start time and the effective end time, the auxiliary page is hidden and other pages are displayed; if the current system time is before the effective start time, the time-not-expired prompt page is displayed and other pages are hidden; if the current system time is after the effective end time, the time-expired prompt page is displayed and other pages are hidden; the VB project password is used to protect the project from being viewed and tampered with.

[0010] Step 2, template usage, specifically: copy the original document to the template page by page; record the number of pages of the original document as N, if N is less than or equal to 3, delete N blank pages in the template; if N is greater than 3, delete 3 blank pages in the template; update the effective start time and effective end time in the validity period setting page; set the macro prompt page to display state, and the remaining pages to strong hidden state; save the template as a target file.

[0011] As a further improvement of the present invention: in step 1, the font color in the validity period setting page is set to white, and the font size is set to 6; the page title in the template is encoded using UUID.

[0012] Compared with the prior art, the beneficial effects of the present invention are as follows: the present invention proposes a method of auxiliary pages, which updates the effective start time and end time of the document in the auxiliary page in a strongly hidden state, and the VB script obtains the effective start time and end time of the document by accessing the specified cells in the auxiliary page, thereby achieving the effect of setting the validity period. The Excel document protected by the present invention can be opened and used normally only within the effective start time and end time, and cannot be opened and used normally in other cases, thereby meeting people's usage needs. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] Figure 1 Create a flow chart for the template of the Excel document validity period protection method based on the template.

[0014] Figure 2 A flow chart for using a template for a method of protecting the validity period of an Excel document based on a template. DETAILED DESCRIPTION

[0015] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0016] In the description of the present invention, it should be noted that the terms "center", "upper", "lower", "left", "right", "vertical", "horizontal", "inner", "outer", etc., indicating the orientation or positional relationship, are based on the orientation or positional relationship shown in the drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as limiting the present invention. In addition, the terms "first", "second", and "third" are used for descriptive purposes only, and cannot be understood as indicating or implying relative importance.

[0017] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "installed", "connected", and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0018] Embodiment 1:

[0019] See also Figure 1-2 , a template-based Excel document validity period protection method has the following specific steps:

[0020] Step 1: Template creation: Create a blank Excel file on a computer with Microsoft Excel installed, named Template T, and set the VB script and VB project passwords for Template T.

[0021] Open template T and create 4 new pages, one for macro enablement prompt, time not expired prompt, time expiration prompt, and validity period setting. In the macro enablement prompt page, enter prompt information to remind users to enable macros in order to view the document normally. In the time not expired prompt page, enter prompt information to remind users that the effective start time of the document has not yet arrived. In the time expiration prompt page, enter prompt information to remind users that the effective deadline of the document has passed. In the validity period setting page, select any 2 cells to record the effective start time and the effective deadline.

[0022] At the same time, the VB script reads the effective start time and effective end time in the validity period setting page, and compares them with the current system time: if the current system time is within the effective start time and the effective end time, the auxiliary page is hidden and other pages are displayed; if the current system time is before the effective start time, the time-not-expired prompt page is displayed and other pages are hidden; if the current system time is after the effective end time, the time-expired prompt page is displayed and other pages are hidden; the VB project password is used to protect the project from being viewed and tampered with.

[0023] The specific operation is to open the VB project of template T and define the worksheet open function in ThisWorkbook. The logic is as follows:

[0024] Get the document's effective start time NotBefore and end time NotAfter from the specified cell of the auxiliary page "Validity Period Settings"; if the effective start time and effective end time are empty, it will prompt that the document is damaged, otherwise continue.

[0025] Get the system current time Today.

[0026] If Today is less than NotBefore, the time-out prompt page will be displayed, other pages will be hidden, and a prompt "The authorization start time for this document has not yet arrived!" will be displayed;

[0027] If Today is greater than NotAfter, the time-expired prompt page will be displayed, other pages will be hidden, the contents of non-auxiliary pages will be cleared, and a prompt "This document has expired!" will be displayed;

[0028] If Today is between NotBefore and NotAfter, the non-auxiliary page is displayed and the auxiliary page is hidden.

[0029] Template T only needs to be created once and can be used repeatedly.

[0030] Step 2, template usage: copy the original document to the template page by page; record the number of pages of the original document as N, if N is less than or equal to 3, delete N blank pages in the template; if N is greater than 3, delete 3 blank pages in the template; update the effective start time and effective end time in the validity period setting page; set the macro prompt page to display state, and the remaining pages to strong hidden state; save the template as a target file.

[0031] The specific operation is to use the third-party library Spire.xls.free to load the template file to get templateWorkbook, get the validity setting page validitySheet through the page title, set it to strong hidden, and set the effective start time and effective end time of the document in the specified cell, use the third-party library Spire.xls.free to load the file to be protected to get sourceWorkbook, copy all pages to templateWorkbook, traverse all pages of templateWorkbook, set the macro prompt page to display state, and display the remaining pages as strong hidden state, and save templateWorkbook to the target path.

[0032] Spire.xls.free is an Excel processing tool developed by e-iceblue. It can realize Excel creation, processing, conversion and other operations without installing Microsoft Excel.

[0033] The present invention proposes a method for auxiliary pages, in which the effective start time and end time of a document are updated in an auxiliary page in a strongly hidden state, and a VB script obtains the effective start time and end time of the document by accessing a specified cell in the auxiliary page, thereby achieving the effect of setting the validity period. The Excel document protected by the present invention can be opened and used normally only within the effective start time and end time, and cannot be opened and used normally in other cases, thus meeting people's usage needs.

[0034] Embodiment 2:

[0035] On the basis of Example 1, in step 1, the font color in the validity period setting page is set to white, and the font size is set to 6 so as to be difficult to be noticed by the naked eye.

[0036] At the same time, in order to prevent the page title in the template from conflicting with the page title of the document to be protected, the page title in the template is encoded using UUID.

[0037] It will be apparent to those skilled in the art that the invention is not limited to the details of the exemplary embodiments described above and that the invention can be implemented in other specific forms without departing from the spirit or essential features of the invention. Therefore, the embodiments should be considered exemplary and non-limiting in all respects, and the scope of the invention is defined by the appended claims rather than the foregoing description, and it is intended that all variations falling within the meaning and scope of the equivalent elements of the claims be included in the invention. Any reference numeral in a claim should not be considered as limiting the claim to which it relates.

[0038] In addition, it should be understood that although the present specification is described according to implementation modes, not every implementation mode contains only one independent technical solution. This description of the specification is only for the sake of clarity. Those skilled in the art should regard the specification as a whole. The technical solutions in each embodiment may also be appropriately combined to form other implementation modes that can be understood by those skilled in the art.

Claims

1. A template-based Excel document validity period protection method, characterized in that: The specific steps are as follows: Step 1, template creation: Create a new blank Excel file on a computer with Microsoft Excel installed, record it as template T, open template T, create 4 new pages, one for enabling macro prompts, one for time-out prompts, one for time-out prompts, and one for validity period settings; Step 2: Use the template, specifically: copy the original document page by page into the template; update the effective start time and effective end time in the validity period setting page; set the macro prompt page to display state, and the other pages to strong hidden state; Save the template as a target file; In step 1, in the Enable Macro Prompt page, enter prompt information to remind users to enable macros in order to view the document normally; in the Time Not Arrived prompt page, enter prompt information to remind users that the effective start time of the document has not arrived; in the Time Expired prompt page, enter prompt information to remind users that the effective deadline of the document has passed; in the Validity Period Setting page, select any two cells to record the effective start time and effective deadline respectively; In step 1, the VB script reads the effective start time and effective end time in the validity period setting page and compares them with the current system time: if the current system time is within the effective start time and effective end time, the auxiliary page is hidden and other pages are displayed; If the current system time is before the effective start time, the time-out reminder page will be displayed and other pages will be hidden; If the current system time is after the effective deadline, the time expiration reminder page will be displayed and other pages will be hidden; The VB project password protects the project from being viewed and tampered with.

2. A template-based Excel document validity period protection method according to claim 1, characterized in that: In step 1, set the VB script and VB project passwords for template T.

3. A template-based Excel document validity period protection method according to claim 2, characterized in that: In step 2, the number of pages of the original document is recorded as N. If N is less than or equal to 3, N blank pages in the template are deleted; if N is greater than 3, 3 blank pages in the template are deleted.

4. The template-based Excel document validity period protection method according to claim 1, characterized in that: In step 1, set the font color in the validity period setting page to white, and set the font size to 6.

5. The template-based Excel document validity period protection method according to claim 1, characterized in that: In step 1, the page title in the template is encoded using UUID.

Images