Method and device for modifying Excel graph data and storage medium

Abstract

The invention discloses a method and a device for modifying Excel graph data and a storage medium. The method comprises the following steps: creating an Excel template in an Excel file; using an EasyExcel library to import the data into a table of the Excel template, and using the EasyExcel library to import the data into the table of the Excel template; the suffix of the Excel file is modified into zip, so that the Excel file is converted into a compressed package file; the compressed package file is decompressed, an xml file for storing graph data is found, and the graph data is stored in a specified label of the xml file; a modification step: analyzing the xml file, determining the position of a specified label in the graph data, and modifying the content of the specified label in the graph data through a POI (Point of Interest) technology; according to the method for modifying the data of the Excel graph, the program can automatically adjust the corresponding graph without manual intervention when the data is updated, and work efficiency is greatly improved. The method for modifying the data of the Excel graph comprises the following steps of: judging whether the next graph exists or not, and if yes, repeating the modifying step to modify the next graph until all the graphs needing to be modified are processed.

Description

Technical Field

[0001] The invention relates to a method, a device and a storage medium for modifying Excel chart data, and belongs to the technical field of system software report form. Background Art

[0002] Excel is widely used in all walks of life, especially for data processing, statistical analysis and other functions.

[0003] The function of exporting data to Excel is widely available on major Internet platforms. There are also many related technical patents, such as the patent "CN112733503A-A method for generating Excel from HTML tables based on POI". It reads the data in the HTML table, generates a new Excel file through the plug-in POI provided by Apache, and writes the data into the Excel file.

[0004] At the same time, there are also related solutions for the function of importing data into Excel and generating tables and legends. For example, "CN109255114A-System and method for generating Excel tables and legends based on data configuration". It prepares Excel templates and data in advance and automatically generates Excel tables and legends using command lines.

[0005] The Excel export function of most platforms only involves the export of data, but not the export of graphs. If Excel users want to view graphs that are more intuitive than data, they need to perform secondary processing on the exported Excel, which is a very time-consuming and labor-intensive process.

[0006] The existing methods of generating Excel tables and graphs require a lot of configuration of the template in advance, and even require the mastery of Excel command line execution and other skills. This not only increases the technical burden of programmers, but also cannot be automated. At the same time, because the Excel file is generated by template configuration, the exported file contains configuration content, which affects the use and editing of Excel.

[0007] When generating Excel charts, it is not possible to specify the type and related features. There is currently no effective solution in the field of system software reports for customers who want customized export of charts. Summary of the invention

[0008] The purpose of the present invention is to overcome the deficiencies in the prior art and to provide a method, device and storage medium for modifying Excel chart data. When the data is updated, the program can automatically adjust the corresponding chart without manual intervention, thereby greatly improving work efficiency.

[0009] To achieve the above object, the present invention is implemented by adopting the following technical solutions: In a first aspect, the present invention provides a method for modifying Excel chart data, comprising: Creating an Excel template in an Excel file, wherein the Excel template includes at least one table and one graph; Use the EasyExcel library to import data into the table of the Excel template; Modify the suffix of the Excel file to zip to convert the Excel file into a compressed file; Decompress the compressed file and find the XML file storing the graph data, where the graph data is stored in a specified tag of the XML file; The modification step includes: parsing the XML file, determining the location of the specified tag in the graph data, and modifying the content of the specified tag in the graph data by using the POI technology; Determine whether there is a next graph. If so, repeat the modification steps to modify the next graph until all graphs that need to be modified are processed.

[0010] Furthermore, the table, as a data source of the graph, includes at least two columns of data, and each column is provided with a label for data filling.

[0011] Further, the use of the EasyExcel library to import data into the table of the Excel template includes: The starting position for data filling is determined according to the label, and the data is filled into the table of the Excel template.

[0012] Furthermore, the parsing of the XML file, determining the position of the specified tag in the graph data, and modifying the content of the specified tag in the graph data by using the POI technology include: Open the XML file by using POI technology, and modify the content of the title in the graph, specifically: find the tag where the title is located by traversing the hierarchical structure of the objects in the graph, and modify the tag value; The data source of the graph is modified by using POI technology, specifically: according to the position of the data source in the xml file, the corresponding tag is found, and the tag value is modified to specify a new data range.

[0013] Furthermore, the graph is a line graph, and in the data source of the line graph, the x-axis is set to the first column of the table, and the y-axis is set to the second column of the table.

[0014] Furthermore, the method also includes: converting the modified compressed package file back into an Excel file, and exporting the final result.

[0015] In a second aspect, the present invention provides a device for modifying Excel chart data, comprising: A template creation module, used to create an Excel template in an Excel file, wherein the Excel template includes at least one table and one graph; An import module, used to import data into a table of the Excel template using the EasyExcel library; A modification module, used for modifying the suffix of the Excel file to zip, so as to convert the Excel file into a compressed package file; A decompression module, used to decompress the compressed package file and find the XML file storing the graph data, wherein the graph data is stored in a specified tag of the XML file; A modification module, used to parse the XML file, determine the location of a specified tag in the graph data, and modify the content of the specified tag in the graph data by using POI technology; The judgment module is used to judge whether there is a next graph. If so, the next graph is modified by the modification module until all the graphs that need to be modified are processed.

[0016] In a third aspect, the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of any of the aforementioned methods.

[0017] In a fourth aspect, the present invention provides a computer device, comprising: Memory, for storing computer programs / instructions; A processor is used to execute the computer program / instructions to implement the steps of any of the aforementioned methods.

[0018] In a fifth aspect, the present invention provides a computer program product, comprising a computer program / instruction, which implements the steps of any one of the aforementioned methods when executed by a processor.

[0019] Compared with the prior art, the present invention has the following beneficial effects: The present invention provides a method, device and storage medium for modifying Excel graph data. The method imports data into a table of the Excel template by using the EasyExcel library, converts the Excel file into a compressed package file, decompresses the compressed package file, and finds the XML file storing the graph data. The content of a specified tag in the graph data is modified by the POI technology. When the data is updated, the program can automatically adjust the corresponding graph without manual intervention, thereby greatly improving work efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1is a flow chart of a method for modifying Excel chart data in an embodiment of the present invention; Figure 2 is a schematic diagram of an Excel template for modifying Excel chart data in an embodiment of the present invention; Figure 3 It is a schematic diagram of the location of the xml file storing the image data after the Excel template is modified into zip and decompressed in an embodiment of the present invention; Figure 4 It is a schematic diagram of the contents of the XML file chart1.xml in an embodiment of the present invention; Figure 5 It is a schematic diagram of the label position of the chart title in chart1.xml in an embodiment of the present invention; Figure 6 It is a schematic diagram of the label positions of the x-axis of the graph and the data source of the data in xml in chart1.xml in an embodiment of the present invention; Figure 7 It is a schematic diagram of the result after the present invention modifies the graph data. DETAILED DESCRIPTION

[0021] The present invention will be further described below in conjunction with the accompanying drawings. The following embodiments are only used to more clearly illustrate the technical solution of the present invention, and cannot be used to limit the protection scope of the present invention.

[0022] Embodiment 1, this embodiment introduces a method for modifying Excel chart data, comprising: Creating an Excel template in an Excel file, wherein the Excel template includes at least one table and one graph; Use the EasyExcel library to import data into the table of the Excel template; Modify the suffix of the Excel file to zip to convert the Excel file into a compressed file; Decompress the compressed file and find the XML file storing the graph data, where the graph data is stored in a specified tag of the XML file; The modification step includes: parsing the XML file, determining the location of the specified tag in the graph data, and modifying the content of the specified tag in the graph data by using the POI technology; Determine whether there is a next graph. If so, repeat the modification steps to modify the next graph until all graphs that need to be modified are processed.

[0023] like Figure 1 As shown, the method for modifying Excel chart data provided in this embodiment specifically involves the following steps: S10: Create Excel templates based on customized requirements.

[0024] Create Excel templates based on user's customized requirements.

[0025] In this embodiment, the Excel template contains only one table as a data source and one line chart as a chart to be modified. Figure 2 shown.

[0026] The table as the data source has two columns of data, month and data, arranged in horizontal lines, and the table header is followed by labels {.month} and {.data} respectively. The function of the label is to provide EasyExcel with data filling.

[0027] As the modified object, the line chart has the title "Here is the title", and the content will be modified later. The data source of the line chart, the x-axis is set to the first column of the table: month, and the data range is C2 to C2; the y-axis is set to the second column of the table: data, and the data range is C3 to C3. After exporting, the data range will be modified to the part of the table with data, which is used to display all the data in the table. At the same time, the format of the chart can be set according to the customer's customized needs. For example, here we set the text on the line chart to bubble style, and these styles will be retained after Excel export.

[0028] S20: Import data into Excel template using EasyExcel.

[0029] The two tags {.month} and {.data} in the Excel template are used by EasyExcel to determine the starting position of the filling data. In the Java program, random data from January 2022 to December 2022 are generated for filling. After filling, the result file is sent to S50 in the form of a file stream, and POI makes subsequent modifications to the graph data.

[0030] S30: Change the template file suffix from xlsx to zip.

[0031] Excel files can be converted into compressed files by changing the suffix to zip format. After unzipping the compressed file, we can find that Excel stores data in the xml file format. The data of the chart in the example Excel template is stored in: xl / charts / chart1.xml. Figure 3 shown.

[0032] S40: Unzip the zip file and find the file starting with chart in the xl / charts / directory.

[0033] Find the xl / charts / directory and open chart1.xml. We find that the chart data is stored in the c:chart tag area. The c:title tag under c:chart stores the title data, and the c:plotArea tag under c:chart stores the chart data source data. Figure 4 shown.

[0034] The principle of POI modifying Excel chart data is to modify the label values ​​under the corresponding labels in these two areas.

[0035] Open the tag c:title, we can see that the title content is saved in its descendant tag a:t, which is the target we want to modify. Figure 5 shown.

[0036] Open the tag c:plotArea, we can see that it has a sub-tag c:lineChart, that is, the code chart type is a line chart. Under this sub-tag, find the tag c:ser. Under the c:ser tag, find the sub-tag c:cat, and the value of its descendant tag c:f is the data source of the x-axis. Under the c:ser tag, find the sub-tag c:val, and the value of its descendant tag c:f is the data source of the line chart data. These two data sources are the targets we want to modify. Figure 6 shown.

[0037] S50: Modify the title of the graph through POI.

[0038] Accept the file stream from S20 and convert it into a workbook object using the method provided by POI. The workbook object corresponds to our Excel file and represents the entire Excel file.

[0039] In the case of this patent, the template Excel file only creates one sheet, and the corresponding subscript is 0 (the starting subscript starts from 0). POI provides a method to obtain the corresponding sheet object from the workbook object through the subscript. We obtain the first sheet object from the workbook object through the subscript 0 and give it the alias sheet1.

[0040] In the table object sheet1, there is a drawing object. All the relevant information of all the graphs is stored in this drawing object. We can get the drawing object from sheet1 through the getDrawingPatriarch method and give it the alias draw1. The drawing object draw1 provides a getCharts method to get all the graph objects.

[0041] getCharts returns all the graph objects. In this patent case, there is only one graph, and the corresponding subscript is 0. We use subscript 0 to get the object of the graph we want to modify. So far, we have finally found the object of the graph we want to modify, and we give it the alias chart1.

[0042] In chart1, a series of getXX() and setXX() methods are provided to obtain the corresponding attributes in XML. chart1 can be understood as Figure 6 The tag c:chart in POI. The title content that needs to be modified is located in the xml starting from the c:chart tag: c:chart>c:title>c:tx>c:rich>a:p>a:r>a:t. Here, > means the parent tag is on the left and the child tag is on the right. For example, A>B means that B is the child tag of A. So in POI, the get methods provided by the object chart1 are: chart1.getTitle().getTx().getRich().getPArray(0).getRArray(0). Finally, by calling setT(), the title of the chart is modified. The complete code is: chart1.getTitle().getTx().getRich().getPArray(0).getRArray(0).setT("New title content"). We can find a pattern, from the chart1 object to the final get method of the setT method, there is a one-to-one correspondence between the tag c:chart in the xml and the tag a:t where the title is located. This also shows that the modification of the Excel chart title is indeed to modify the value of the corresponding tag in the xml file. POI provides an interface to help us modify the xml, and we don't need to write code to modify the xml file. So far, we have completed the modification of the tag name.

[0043] S60: Modify the data source of the graph through POI From S50, we know that the data source can be modified through the chart object chart1 and the position of the data source in xml relative to the tag c:chart.

[0044] By observing the xml file, we find that the location of the x-axis data source is c:chart>c:plotArea>c:lineChart>c:ser>c:cat>c:strRef>c:f. The meaning of this paragraph is explained in S50. Therefore, through the getXX and setXX methods provided by POI, we can finally modify the content of the x-axis data source. The data source content format is model!$C$2:$N$2, which is the data source format specified by Excel. It means that the data source is taken from the table object named model, the coordinates (C,2) and the horizontal label (N,2). Similarly, we can modify the data source of the graph data, which will not be repeated here.

[0045] S70: Does the next chart exist? In this case, we only have one graph that needs to modify data. In actual production, we often have to process data from many graphs. In S50, the getCharts method of the drawing object draw1 returns the objects of all graphs in the table sheet1. We only modified the data of the graph chart1 with the subscript 0 in the sheet1 with the table subscript 0. We can traverse all tables through the table subscripts, and traverse all graphs through the graph subscripts. Combining S50 and S60, after traversing the graph to obtain the corresponding graph object, we can modify the values ​​we want to modify through a series of getXX() and setXX(). Only after all the graphs have been modified, the final result is finally exported. For example Figure 7 shown.

[0046] Figure 7 In the figure, we can see that C2 to N2 are filled with year and month values ​​through EasyExcel, and C3 to N3 are filled with corresponding data.

[0047] The title of the line chart has been changed to: Data trend chart from January to December 2022, and the data source has also been changed to the corresponding data range.

[0048] By adopting the above method, you can quickly generate specified charts for different data and export them as Excel files. At work, you often need to process a large amount of data and its corresponding charts. Manual operation not only consumes a lot of time and human resources, but also increases labor costs. When the data changes, manual adjustment of the chart is highly repetitive and inefficient. Automatically processing data and generating charts through the program can greatly reduce the manual burden. When the data is updated, the program can automatically adjust the corresponding chart without manual intervention, greatly improving work efficiency.

[0049] Embodiment 2: This embodiment provides a device for modifying Excel chart data, including: A template creation module, used to create an Excel template in an Excel file, wherein the Excel template includes at least one table and one graph; An import module, used to import data into a table of the Excel template using the EasyExcel library; A modification module, used for modifying the suffix of the Excel file to zip, so as to convert the Excel file into a compressed package file; A decompression module, used to decompress the compressed package file and find the XML file storing the graph data, wherein the graph data is stored in a specified tag of the XML file; A modification module, used to parse the XML file, determine the location of a specified tag in the graph data, and modify the content of the specified tag in the graph data by using POI technology; The judgment module is used to judge whether there is a next graph. If so, the next graph is modified by the modification module until all the graphs that need to be modified are processed.

[0050] The specific functional implementation of each of the above modules can be found in the relevant contents of the method in Example 1 and will not be elaborated here.

[0051] Embodiment 3, this embodiment provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of any one of the methods described in Embodiment 1 are implemented.

[0052] Embodiment 4: This embodiment provides a computer device, including: Memory, for storing computer programs / instructions; A processor, configured to execute the computer program / instructions to implement the steps of any one of the methods described in Example 1.

[0053] Embodiment 5: This embodiment provides a computer program product, including a computer program / instruction, which implements the steps of any method described in Embodiment 1 when executed by a processor.

[0054] The above is only a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the technical principles of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.

[0055] It should be understood by those skilled in the art that the embodiments of the present disclosure may be provided as methods, systems or computer program products. Therefore, the present disclosure may take the form of a complete hardware embodiment, a complete software embodiment or an embodiment combining software and hardware aspects. Moreover, the present disclosure may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0056] The present disclosure is described with reference to flowcharts and / or block diagrams of methods, devices (systems) and computer program products according to embodiments of the present disclosure. It should be understood that each process and / or block in the flowchart and / or block diagram and the combination of processes and / or blocks in the flowchart and / or block diagram can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0057] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0058] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0059] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present disclosure rather than to limit its protection scope. Although the present disclosure has been described in detail with reference to the above embodiments, ordinary technicians in the relevant field should understand that after reading the present disclosure, those skilled in the art can still make various changes, modifications or equivalent substitutions to the specific implementation methods of the invention, but these changes, modifications or equivalent substitutions are all within the protection scope of the disclosed claims to be approved.

Claims

1. A method for modifying Excel chart data, characterized in that: include: Creating an Excel template in an Excel file, wherein the Excel template includes at least one table and one graph; Use the EasyExcel library to import data into the table of the Excel template; Modify the suffix of the Excel file to zip to convert the Excel file into a compressed file; Decompress the compressed file and find the XML file storing the graph data, where the graph data is stored in a specified tag of the XML file; The modification step includes: parsing the XML file, determining the location of the specified tag in the graph data, and modifying the content of the specified tag in the graph data by using the POI technology; Determine whether there is a next graph. If so, repeat the modification steps to modify the next graph until all graphs that need to be modified are processed.

2. The method for modifying Excel chart data according to claim 1, characterized in that: The table, as the data source of the graph, contains at least two columns of data, and each column is provided with a label for data filling.

3. The method for modifying Excel chart data according to claim 2, characterized in that: The method of using the EasyExcel library to import data into the table of the Excel template includes: The starting position for data filling is determined according to the label, and the data is filled into the table of the Excel template.

4. The method for modifying Excel chart data according to claim 2, characterized in that: The step of parsing the XML file, determining the location of a specified tag in the graph data, and modifying the content of the specified tag in the graph data by using the POI technology includes: Open the XML file by using POI technology, and modify the content of the title in the graph, specifically: find the tag where the title is located by traversing the hierarchical structure of the objects in the graph, and modify the tag value; The data source of the graph is modified by using POI technology, specifically: according to the position of the data source in the xml file, the corresponding tag is found, and the tag value is modified to specify a new data range.

5. The method for modifying Excel chart data according to claim 4, characterized in that: The graph is a line graph, and in the data source of the line graph, the x-axis is set to the first column of the table, and the y-axis is set to the second column of the table.

6. The method for modifying Excel chart data according to claim 1, characterized in that: The method further includes: converting the modified compressed package file into an Excel file again, and exporting the final result.

7. A device for modifying Excel chart data, characterized in that: include: A template creation module, used to create an Excel template in an Excel file, wherein the Excel template includes at least one table and one graph; An import module, used to import data into a table of the Excel template using the EasyExcel library; A modification module, used for modifying the suffix of the Excel file to zip, so as to convert the Excel file into a compressed package file; A decompression module, used to decompress the compressed package file and find the XML file storing the graph data, wherein the graph data is stored in a specified tag of the XML file; A modification module, used to parse the XML file, determine the location of a specified tag in the graph data, and modify the content of the specified tag in the graph data by using POI technology; The judgment module is used to judge whether there is a next graph. If so, the next graph is modified by the modification module until all the graphs that need to be modified are processed.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the steps of the method described in any one of claims 1 to 6 are implemented.

9. A computer device, characterized in that: include: Memory, for storing computer programs / instructions; A processor, configured to execute the computer program / instructions to implement the steps of the method according to any one of claims 1 to 6.

10. A computer program product comprising a computer program / instructions, characterized in that When the computer program / instructions are executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images