A method for automatically converting a web form into an Excel file

Abstract

The application discloses a kind of Web form automatic conversion Excel file's method, by being configured with two groups of specific parameters in Web management system form page configuration a button received to user's query demand and two groups of interceptor, corresponding two groups of interceptor, in combination with built-in in built-in data query in Web management system and the method of the data found is exported as Excel, after user enters main page, query condition will be screened, click query and transmit query condition to background, and query is carried out, and background will be according to the authority of user, and query condition, the data after query is displayed in page in page, cleverly use the logic of user click query, when user click export, actually repeat the query event of user, through additional two parameters, and two individual interceptors are added in background, the data logic of export, completely follow the logic of user click query, realize the data export effect of what you see is what you get.

Description

Technical Field

[0001] This invention relates to the field of computer technology, specifically a method for automatically converting Web spreadsheets into Excel files. Background Technology

[0002] With the gradual advancement of digitalization, informatization, and intelligentization in society, data is becoming an increasingly important resource, and users' demand for data visualization is becoming stronger. Tables, as one of the traditional and efficient data visualization methods, have always held an important position in data visualization work. Excel spreadsheets and web page tables are two very common display methods, and the conversion between them (such as exporting a web page table to Excel or displaying data from Excel in a web table) is a frequent need faced by data visualization developers.

[0003] In the Web management system of the manufacturing industry, there are a large number of tabular pages, and these pages often need to be exported offline to Excel for archiving or querying and editing. Different roles and different users may display different fields and columns, and the export also needs to be carried out according to the user's permissions. If the traditional POI export is used directly, it is difficult to achieve customization. If JXLS2 is used to export each page separately, the workload is huge, the development cycle is long, and the delivery is slow. At the same time, after the Web system has been used for a long time, the amount of data is huge. The traditional general export often uses the table data of the page to be transmitted to the backend for assembly and Excel export. Although this method solves the general export problem, it also faces the following problems: (1) The amount of data is limited. There is no problem in transmitting a small amount of data, but thousands, tens of thousands, or even hundreds of thousands of data cannot be transmitted quickly and easily through Request. A large amount of data can easily cause the browser to crash. (2) Image export is not supported. Images are often large. A hundred rows of tables and a hundred images may be tens of megabytes in size. Transmitting these data to the backend together may also cause network congestion, delays, and other problems. (3) It cannot solve the problem of customers distinguishing between numbers and text. For example, in real-world scenarios, workpiece numbers such as 010 and 00.10 must be plain text identifiers, while work hours / amounts such as 2.34 and 0.23 must be plain numeric identifiers. This is necessary for users to perform statistics, summaries, and formulaic operations in Excel. Simply transmitting data from the front end to the back end cannot solve the formatting problem. (4) The page will have some colors to indicate status, steps, progress, etc.

[0004] In summary, a universal export method needs to be developed. This method must meet the customer's need for exporting individual templates, i.e., WYSIWYG (What You See Is What You Get). The exported page must export all data without pagination, and the exported fields must be consistent with the fields seen by the user. The exported cell format, merging, colors, etc., must be consistent with what the user sees on the web page. At the same time, it must be efficient and not affect the user's normal use of the system. Summary of the Invention

[0005] The purpose of this invention is to provide a method for automatically converting Web tables into Excel files, in order to solve the problem mentioned in the background art that a large number of table-type pages in current manufacturing Web management systems cannot be quickly and directly exported as WYSIWYG Excel files.

[0006] To achieve the above objectives, the present invention provides the following technical solution: a method for automatically converting a Web table into an Excel file, applied in a Web management system, wherein the Web management system has built-in methods for data querying and exporting the queried data to Excel, the method comprising:

[0007] A button is configured on the form page of the web management system. After receiving a user's query request, this button passes the user's given query conditions along with two sets of specific parameters to the query method on the current page. The two sets of specific parameters are used by the interceptor to pre-determine the type of the query request. The button on the form page of the web management system also sets two sets of interceptors in the background of the web management system. The two sets of specific parameters are parameter one and parameter two, and the two sets of interceptors are the first interceptor and the second interceptor.

[0008] The first interceptor determines whether the query conditions with attached parameters include the first parameter. If it does not, it still executes the original query method and performs a normal database query according to pagination. If it does, it still executes the normal query method, but performs a database query without pagination. After the query is completed, it will be filled into the Freemarker template according to the original logic. After filling is completed, it will be rendered into an HTML fragment.

[0009] The second interceptor receives the HTML fragment from the first interceptor and determines whether the second parameter is present in the query conditions with attached parameters. If it is not present, the original query method is still executed and the HTML fragment with paginated results is returned to the web client for rendering and display. If it is present, the HTML fragment without pagination is entered into the general Excel export method.

[0010] As a preferred technical solution, the first parameter is data-target="export", and the second parameter is data-filename="export filename", where data-filename is the title name of the page currently being viewed by the user.

[0011] As a preferred technical solution, the general method for exporting HTML fragments without pagination to Excel includes the following steps:

[0012] 1) Extract the Table section from the HTML fragment;

[0013] 2) First, iterate through the HTML fragments according to the table, and then iterate through the tags in the second-level traversal;

[0014] 3) If an HTML snippet contains rowspan to merge cells, calculate the rowspan value and plot the number of merged rows in the sheet; if an HTML snippet contains colspan to merge cells, calculate the colspan value and plot the number of merged columns in the sheet.

[0015] 4) After drawing the sheet, start filling the cells in the sheet with data in the order of traversal;

[0016] 5) Based on the HTML fragment, if the attribute data-type="String" in the tb element is filled with plain text, data-type="integer" is filled with number, data-type="double" is filled with decimal, data-type="date" is filled with date, and data-type="img" tag is filled with img, then the img processing method will be entered separately.

[0017] 6) The img passed on the front-end page is only a URL. The binary array object of the img is obtained from the server through the URL value in the tag. The binary array object is then inserted into the cell of the sheet as an image attribute.

[0018] 7) Determine if the table has a data-style attribute tag. The data-style tag represents the background color / text color of the table. Use data-style="backcolor:red" to mark the background color of the corresponding cell; use data-style="fontcolor:red" to mark the text color in the cell, convert the color to CellStyle, and load it into the current cell.

[0019] 8) After the cell is processed, it is loaded into the SXSSFWorkbook object we initialized, generating an Excel file;

[0020] 9) Return to interceptor two, use the Response to return an Excel file, and name it the value of data-filename passed from the front-end page;

[0021] 10) The user receives and downloads the Excel file, and the result is a WYSIWYG Excel file without pagination.

[0022] As a preferred technical solution, before the second interceptor finishes processing and switches to the general Excel export method, it is also necessary to load the POI component and initialize the Excel file.

[0023] As a preferred technical solution, Excel initialization includes initializing SXSSFWorkbook, SXSSFSheet, and CellStyle, initializing the time formatting of the Excel export template, and determining whether to center the formatting position.

[0024] Compared with the prior art, the beneficial effects of the present invention are:

[0025] By configuring a button to receive user query requests, two sets of specific parameters, and two corresponding interceptors on the web management system's form page, and combining this with the built-in data query and export methods for queried data to Excel within the web management system, after the user enters the main page, they will filter the query conditions and click "query" to transmit the query conditions to the backend for querying. The backend will then display the queried data in pages according to the user's permissions and query conditions. This cleverly utilizes the user's "click to query" logic. When the user clicks "export," it actually repeats the user's query event. Through the two additional parameters and the two interceptors added to the backend, the exported data logic completely follows the user's "click to query" logic, satisfying the WYSIWYG (What You See Is What You Get) data requirement.

[0026] (1) The HTML table that was originally intended to be exported to the page was converted into an Excel file, achieving a complete replication of the HTML table format. The file name also matches the name of the page currently being queried by the user. At the same time, the data sent by the user is still limited to a small number of query conditions and two additional parameters. The Excel conversion logic fully utilizes the server's computing power, resulting in high efficiency, large data export volume, and support for exporting a large number of images.

[0027] (2) For general export, it needs to meet the customer's need for exporting individual templates, i.e., what you see is what you get. The page that the customer wants to export needs to export all the data without pagination, the exported fields are consistent with the fields seen by the user, and the exported cell format, merging, color, etc. are consistent with what the user sees on the web page. At the same time, it needs to be efficient and not affect the user's normal use of the system. Attached Figure Description

[0028] Figure 1 This is a flowchart of a method for automatically converting a Web table into an Excel file according to the present invention;

[0029] Figure 2 This invention provides a built-in method for data querying and exporting the queried data to Excel within the Web management system. Detailed Implementation

[0030] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0031] Please see Figure 1 This invention provides a technical solution: a method for automatically converting a web table into an Excel file, applied in a web management system. The web management system has built-in methods for data querying and exporting the queried data to Excel. The method includes:

[0032] A button is configured on the form page of the web management system. After receiving a user's query request, this button passes the user's given query conditions along with two sets of specific parameters to the query method on the current page. The two sets of specific parameters are used by the interceptor to pre-determine the type of the query request. The button on the form page of the web management system also sets two sets of interceptors in the background of the web management system. The two sets of specific parameters are parameter one and parameter two, and the two sets of interceptors are the first interceptor and the second interceptor.

[0033] The first interceptor determines whether the query conditions with attached parameters include the first parameter. If it does not, it still executes the original query method and performs a normal database query according to pagination. If it does, it still executes the normal query method, but performs a database query without pagination. After the query is completed, it will be filled into the Freemarker template according to the original logic. After filling is completed, it will be rendered into an HTML fragment.

[0034] The second interceptor receives the HTML fragment from the first interceptor and determines whether the second parameter is present in the query conditions with attached parameters. If it is not present, the original query method is still executed and the HTML fragment with paginated results is returned to the web client for rendering and display. If it is present, the HTML fragment without pagination is entered into the general Excel export method.

[0035] Please see Figure 2 The specific operating steps are as follows:

[0036] 1) The user clicks "Export".

[0037] 2) Pass the query conditions, data-target="export", and data-filename="export filename" to the query method of the current page.

[0038] 3) The query method follows the original query logic, assembling query conditions and fields, etc.

[0039] 4) Query method: Before actually querying the database, enter interceptor one.

[0040] 5) Interceptor 1 determines the query method based on whether the parameter contains `data-target="export"`. If it doesn't, the original query method is used, and the database query is performed normally with pagination. If it does, the normal query method is used, but the database query is performed without pagination.

[0041] 6) The queried data will be filled into the FreeMarker template according to the original logic. After filling, it will be rendered into an HTML fragment and enter interceptor two.

[0042] 7) Interceptor 2 determines the output based on whether the parameter contains `data-target="export"`. If it doesn't, the original query method is used to return the paginated HTML fragment to the web client for rendering. If it does, the unpaginated HTML fragment is exported to Excel using the general export method.

[0043] 8) First, load the POI component, initialize Excel elements such as SXSSFWorkbook, SXSSFSheet, and CellStyle, initialize the time formatting of the Excel export template, and determine whether to center, left, right, or bottom alignment.

[0044] 9) A common method for exporting Excel data is to first extract the Table section from the HTML snippet.

[0045] 10) First, iterate through the HTML fragments according to the table, and then iterate through the tags in the second-level traversal.

[0046] 11) If an HTML fragment contains rowspan to merge cells, calculate the rowspan value and draw the number of merged rows in the sheet.

[0047] 12) If an HTML fragment contains a colspan for merging cells, calculate the colspan value and plot the number of merged columns in the sheet.

[0048] 13) After drawing the sheet, start filling the cells in the sheet with data in the order of traversal.

[0049] 14) Based on the HTML fragment, if the attributes data-type="String" in the tb element are filled with plain text, data-type="integer" with number format, data-type="double" with decimal format, data-type="date" with date format, and data-type="img" tag, then the img processing method will be entered separately.

[0050] 15) The img passed on the front-end page is just a URL. The binary array object of the img is obtained from the server through the URL value in the tag. The binary array object is then inserted into the cell of the sheet as an image attribute.

[0051] 16) Determine if the table (tb) has a `data-style` attribute tag. The `data-style` tag represents the background color / text color of the table. Use `data-style="backcolor:red"` to mark the background color of the corresponding cell. Use `data-style="fontcolor:red"` to mark the text color within the cell. Convert the color to a `CellStyle` and load it into the current cell.

[0052] 17) After the cell is processed, it is loaded into the SXSSFWorkbook object we initialized to generate an Excel file.

[0053] 18) Return to interceptor two, use the Response to return an Excel file, and name it the value of data-filename passed from the front-end page.

[0054] 19) The user receives and downloads the Excel file, and the result is a WYSIWYG Excel file without pagination.

[0055] In summary, after a user enters the main page, they filter their search criteria and click "Search." This sends the search results to the backend for execution. The backend then displays the retrieved data in paginated format based on the user's permissions and the search criteria. The system cleverly leverages the user's "Search" click logic. When the user clicks "Export," the system essentially repeats the user's search event, appending two parameters: `data-target="export"` and `data-filename="export filename"`.

[0056] data-filename is the title name of the page the current user is on.

[0057] Meanwhile, two interceptors have been added to the backend. Interceptor 1: When a user queries, the interceptor checks for `data-target="export"` and if it exists, pagination is skipped. Interceptor 2: When returning data to the user, the interceptor no longer returns an HTML page. Instead, it converts the Freemarker template page that should be returned into an Excel file, names the Excel file after the value of `data-filename`, and then returns it to the user.

[0058] The advantages of this approach are that the exported data logic completely mirrors the user's query logic, ensuring a WYSIWYG (What You See Is What You Get) experience. Simultaneously, the HTML table, originally intended for the webpage, is converted to Excel, perfectly replicating the HTML table format. The filename also matches the name of the current user's query page. Furthermore, the data sent by the user remains limited to a few query conditions and two additional parameters. The Excel conversion logic fully utilizes the server's computing power, resulting in high efficiency, large data export volumes, and support for exporting numerous images.

[0059] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method for automatically converting Web tables to Excel files, characterized in that, This is applied in a web management system, which has built-in methods for data querying and exporting the queried data to Excel. The methods include: A button is configured on the form page of the Web management system. After receiving a user's query request, the button passes the user's given query conditions along with two sets of specific parameters to the query method of the current page. The two sets of specific parameters are used by the interceptor to pre-determine the type of the query request. At the same time, two sets of interceptors are set in the backend of the Web management system. The two sets of specific parameters are the first parameter and the second parameter, and the two sets of interceptors are the first interceptor and the second interceptor. The first interceptor determines whether the first parameter is present in the query conditions with attached parameters. If it is not present, the original query method is still executed, and the database query is performed normally according to pagination. If it is present, the normal query method is still executed, but the database query is performed without pagination. After the query is completed, the data is filled into the Freemarker template according to the original logic. After the data is filled, it is rendered into an HTML fragment. The second interceptor receives the HTML fragment from the first interceptor and determines whether the second parameter is present in the query conditions with attached parameters. If it is not present, the original query method is still executed and the paginated HTML fragment is returned to the web client for rendering and display. If it is present, the non-paginated HTML fragment is entered into the Excel general export method.

2. The method for automatically converting a Web table to an Excel file according to claim 1, characterized in that, The first parameter is data-target="export", and the second parameter is data-filename="export file name", where data-filename is the title name of the page currently being viewed by the user.

3. The method for automatically converting a Web table to an Excel file according to claim 1, characterized in that, The method for exporting non-paged HTML fragments to Excel using the general export method includes the following steps: 1) Extract the Table section from the HTML fragment; 2) First, iterate through the HTML fragments in the table, and then iterate through the tags in the second-level traversal; 3) If an HTML snippet contains rowspan to merge cells, calculate the rowspan value and plot the number of merged rows in the sheet; if an HTML snippet contains colspan to merge cells, calculate the colspan value and plot the number of merged columns in the sheet. 4) Fill the cells in the sheet with data in the order of traversal; 5) Based on the data-type="String" attribute of the tb element in the HTML fragment, fill it with plain text format, data-type="integer" with number format, data-type="double" with decimal format, data-type="date" with date format, and data-type="img" tag, then enter the img processing method separately. 6) The img passed on the front-end page is only a URL. The binary array object of the img is obtained from the server through the URL value in the tag. The binary array object is then inserted into the cell of the sheet as an image attribute. 7) Determine if the cell in tb has a background color / text color; and mark the corresponding cell background color and text color accordingly, then load them into the current cell; 8) After the current cell is processed, it is loaded into the initialized SXSSFWorkbook object and an Excel file is generated; 9) Return to interceptor two, use the Response to return an Excel file, and name it the value of data-filename passed from the front-end page; 10) The user receives and downloads the Excel file, and the result is a WYSIWYG Excel file without pagination.

4. The method for automatically converting a Web table to an Excel file according to claim 1, characterized in that, Before the second interceptor finishes processing and switches to the general Excel export method, the POI component needs to be loaded and the Excel file needs to be initialized.

5. The method for automatically converting a Web table to an Excel file according to claim 4, characterized in that, The initialization of Excel includes initializing SXSSFWorkbook, SXSSFSheet, and CellStyle, initializing the time formatting of the Excel export template, and determining whether to center the formatting position.

Images