A method to enable PowerPoint tables to support the use of formulas and functions from Excel spreadsheets.

Abstract

This invention proposes a method to enable PowerPoint tables to support the use of formulas and functions from Excel spreadsheets. This method receives a user-submitted PowerPoint file, extracts table objects from the PowerPoint, creates a mapping table in Excel with the same structure and data, converts the formulas in the PowerPoint table into a format recognizable by Excel, uses an Excel formula calculator to calculate the formulas in the mapping table, converts the results according to a preset format, and then fills them back into the original PowerPoint table. Finally, a PowerPoint document containing the updated table is generated and returned. This method makes PowerPoint tables fully compatible with Excel formulas and functions, allowing users to flexibly use Excel formulas and functions for data processing within PowerPoint without any additional learning curve.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of office software, in particular to a method for realizing PPT table supporting the use of formulas and functions in Excel tables, and is particularly suitable for scenarios requiring complex data processing and analysis in PPT. BACKGROUND

[0002] In office software, Excel is widely used for data processing and analysis due to its powerful formula and function capabilities. However, the table function in PPT is relatively limited and cannot flexibly use rich formulas and functions for complex data processing like multi-table data matching, data summarization, etc. This brings inconvenience to users when making PPT presentations containing complex data, especially in PPT template-based data rendering scenarios. Therefore, a method is needed to enable PPT tables to support the use of formulas and functions in Excel tables, to improve user efficiency and flexibility in data processing in PPT, and to bring convenience to users. SUMMARY

[0003] To solve the above technical problems, the present application proposes a method for realizing PPT table support for using formulas and functions in Excel tables.

[0004] The present application aims to enable support for formulas and functions in Excel tables in PPT tables. The specific steps are as follows:

[0005] S1: Receive the user's input PPT file and create a PPT object; specifically including the following sub-steps:

[0006] S11: Receive the user's input PPT file;

[0007] S12: Check the PPT file and type;

[0008] S13: Create a PPT file into a file stream object;

[0009] S14: Create a file stream object into a PPT object;

[0010] The PPT object performs processing operations on the PPT file.

[0011] S2: Traverse the table objects in the PPT object and store them in the table object List collection; specifically including the following sub-steps:

[0012] S21: Traverse each slide object in the PPT object;

[0013] Each slide object is an independent slide page in the PPT file; the PPT object is composed of multiple slides;

[0014] S22: traversing each shape object in the slide object;

[0015] The content in the slide object exists in the form of a shape object, such as a table, a text box, a picture, and a chart;

[0016] S23: determining whether the shape object is a table object and storing the table object in a table object List collection;

[0017] If the shape object is a table object, the shape object is forcibly converted into a table object and stored in the table object List collection;

[0018] If it is not a table object, the current shape object is skipped, and the next shape object is traversed.

[0019] S3: creating an Excel object according to the table object List collection; specifically including the following sub-steps:

[0020] S31: creating an Excel object;

[0021] An empty Excel object is initialized, and subsequent content will be filled by mapping PPT table data and processing formulas;

[0022] S32: traversing each element table object in the table object List collection;

[0023] S33: creating a Sheet object with the index serial number of the current table object as the Sheet object name of the Excel object;

[0024] The index serial number refers to the position number of the current table object in the table object List collection, which uniquely identifies the order of each PPT table object in the collection;

[0025] S34: traversing all row objects and all TableCell objects of the row objects of the current table object, and creating row objects and cell Cell objects corresponding to the Sheet object;

[0026] S35: converting the value of the TableCell object into the corresponding actual type according to the actual type of the value of the TableCell object, and setting the value of the cell Cell object as the converted value;

[0027] First, it is determined whether the value of the TableCell object is a formula type;

[0028] If the TableCell object is not a formula type, it is further determined whether it is an integer, a decimal, or a string type.

[0029] S4: Establish the corresponding relationship between the Excel table cell Cell object and the PPT table cell TableCell object, and store it in the cell formula mapping Map set; Specifically, the following sub-steps are included:

[0030] S41: Traverse each element table object in the table object List set;

[0031] S42: Obtain the corresponding Sheet object in the Excel object according to the index number of the current table object;

[0032] Each PPT table object corresponds to a Sheet object with the same name in the Excel object created by step S31, and the Sheet object is accurately positioned and obtained through the index number of the table object List set;

[0033] S43: Traverse all row objects and all TableCell objects of the row objects of the current table object;

[0034] S44: Determine whether the value of the TableCell object is a formula, and if it is a formula, establish a mapping relationship and store it in the cell formula mapping Map set;

[0035] Determine whether the value of the TableCell object in the Table table object traversed in step S43 is a formula, that is, check whether the value of the TableCell object matches the formula format through the regular expression.

[0036] S5: Obtain the Excel formula calculator according to the Excel object, and calculate the formula of the cell Cell object in the cell formula mapping Map set; Specifically, the following sub-steps are included:

[0037] S51: Obtain the formula calculator of the Excel object through the Excel object;

[0038] The formula calculator accepts the cell Cell object of the formula type, and calculates the actual value of the formula;

[0039] S52: Traverse each key in the cell formula mapping Map set, that is, the Excel cell Cell object;

[0040] S53: Call the evaluate method of the formula calculator, input the current Excel cell Cell object, obtain the calculation result, and according to the data type of the calculation result, set the type of the current Excel cell Cell object to the specified data type, and set the value of the current Excel cell Cell object to the calculation value of the calculation result of the data type;

[0041] S6: updating the value of the PPT table cell TableCell object in the cell formula mapping Map set to the value of the Excel table cell Cell object; specifically including the following sub-steps:

[0042] S61: traversing each element key-value pair in the cell formula mapping Map set;

[0043] S62: setting the value of the PPT table TableCell object to the formatted value of the Excel table Cell object;

[0044] For the TableCell object corresponding to the key Excel table cell Cell object in the cell formula mapping Map set, the calculated value of the Excel table cell Cell object is obtained, the data formatting tool is obtained, and the calculated value is formatted into the final value of the specified data format, such as percentage, and the value of the TableCell object is replaced from the formula to the final value.

[0045] S7: closing the Excel object and returning the result; specifically including the following sub-steps:

[0046] S71: closing the Excel object;

[0047] S72: generating a PPT file from the PPT object;

[0048] S73: returning the PPT file to the user.

[0049] Further, step S2 in retrieving the table grid object in the PPT object, by checking the type of the shape object, accurately filtering out non-table elements, improving processing efficiency and ensuring only table data is converted and calculated.

[0050] Further, in the step S3 of creating an Excel mapping object, when converting the type of the TableCell object value in PPT, first determine whether it is in formula format by regular expression, then determine the integer and decimal type in turn, and the rest are treated as strings, to ensure that the Excel cell type is consistent with the actual meaning of the data.

[0051] Further, in the step S4 of establishing a cell mapping relationship, only the TableCell object (value starts with "$") containing formula in PPT is mapped, and the corresponding cell in Excel is accurately matched through row and column index. The cell formula mapping Map set strictly takes the Excel cell Cell object as the key and the PPT TableCell object as the value, to ensure that the subsequent calculation results can be accurately backfilled to the original position.

[0052] Further, in the step S7 of generating and returning the PPT document, when the updated table is integrated, the original slide structure, non-table elements and format styles of the PPT are kept unchanged, and only the content of the formula cell is replaced.

[0053] The method for realizing the PPT table to support the use of the formula and function in the Excel table has the following beneficial effects: the method is based on the Excel formula calculator, solves the problem that the PPT table does not support the formula and function of the Excel table, makes the PPT table completely compatible with the formula and function of the Excel table, realizes the same flexible formula and supports a large number of functions as the Excel table. The user can use the formula and function of the Excel in the PPT table as using the Excel table, only needs to know how to use the formula and function of the Excel table, and no additional learning cost is needed. The method supports multiple PPT file formats, including file complete path, file input stream object, file object or file byte array, and ensures the compatibility with the existing PPT file. BRIEF DESCRIPTION OF DRAWINGS

[0054] Figure 1 The method for realizing the PPT table to support the use of the formula and function in the Excel table has the following beneficial effects: the method is based on the Excel formula calculator, solves the problem that the PPT table does not support the formula and function of the Excel table, makes the PPT table completely compatible with the formula and function of the Excel table, realizes the same flexible formula and supports a large number of functions as the Excel table. The user can use the formula and function of the Excel in the PPT table as using the Excel table, only needs to know how to use the formula and function of the Excel table, and no additional learning cost is needed. The method supports multiple PPT file formats, including file complete path, file input stream object, file object or file byte array, and ensures the compatibility with the existing PPT file.

[0055] Figure 2 The method for realizing the PPT table to support the use of the formula and function in the Excel table has the following beneficial effects: the method is based on the Excel formula calculator, solves the problem that the PPT table does not support the formula and function of the Excel table, makes the PPT table completely compatible with the formula and function of the Excel table, realizes the same flexible formula and supports a large number of functions as the Excel table. The user can use the formula and function of the Excel in the PPT table as using the Excel table, only needs to know how to use the formula and function of the Excel table, and no additional learning cost is needed. The method supports multiple PPT file formats, including file complete path, file input stream object, file object or file byte array, and ensures the compatibility with the existing PPT file. DETAILED DESCRIPTION

[0056] In order to further understand the purpose, structure, features and functions of the present application, the following detailed description is provided in conjunction with the embodiments.

[0057] As shown in the drawings, Figures 1-2 A method for realizing the PPT table to support the use of the formula and function in the Excel table, comprising the following steps:

[0058] S1: receiving the PPT file input by the user and creating a PPT object. Specifically, the following sub-steps are included:

[0059] S11: receiving the PPT file input by the user;

[0060] The PPT file supports file complete path, file input stream object, file object or file byte array; the user uploads the PPT file in multiple ways, such as selecting a local PPT file, directly pasting file content, etc. These different forms of file input are received for subsequent processing.

[0061] S12: verifying the PPT file and type;

[0062] Non-empty check: The PPT file is checked to determine whether it is null;

[0063] Magic number verification: The type of the PPT file is checked to determine whether the magic number of the PPT file is correct;

[0064] First, check whether the PPT file is null, that is, determine whether the user has successfully uploaded the file. If the file is null, it means that no file is transmitted, and an error message needs to be returned and the process ends.

[0065] Then, determine whether the file type is correct by checking the magic number of the file. The magic number is a fixed sequence of specific bytes in the file header, and different file formats have different magic numbers to determine the actual type of the file. For example, the magic number of a PDF file (.pdf) is "25504446 [%PDF]", and the magic number of a PPT file (.pptx) is "504B0304 [PK]", etc. If you need to check the "sales report.pptx" file, first check whether the file is null. If the file exists, continue to check the magic number of the file; if the magic number matches "504B0304 [PK]", it is confirmed that the file is a valid PPT file.

[0066] When the content and the type of the check are both passed, step S13 is entered; otherwise, when either the content or the type of the check fails, an error message is returned and the process ends.

[0067] S13: Create a PPT file into a file stream object;

[0068] Convert the PPT file that passes the check into a file stream object, which points to the beginning of the file. Start reading the file content byte by byte, which facilitates subsequent sequential reading and operation of the file content.

[0069] S14: Create a PPT object from the file stream object;

[0070] Use Apache POI to create a PPT object from the file stream object, and use the PPT object to perform various operations on the PPT file, such as reading the worksheet, cell data, etc.

[0071] For example, after creating a PPT object from the file stream object, access the slides (such as "Slide 1", "Slide 2", etc.) and tables (such as "Table 1", "Table 2", etc.) in "sales report.pptx" and their values.

[0072] S2: Traverse the tables in the PPT object gridThe objects are stored in the table object List collection. The specific steps include the following sub-steps:

[0073] S21: Traverse each slide object in the PPT object;

[0074] Each slide object is an independent slide page in the PPT file. The PPT object is composed of multiple slides, and each slide can contain one or more tables.

[0075] Each slide object in the PPT object is accessed one by one to find all pages containing tables for further processing of the content in the slide.

[0076] S22: Traverse the shape object in the slide object;

[0077] The content in the slide object exists in the form of a shape object, including tables, text boxes, pictures, charts, etc. Each shape object in each slide object is accessed one by one to find the table object therein.

[0078] S23: Determine whether the shape object is a table object and store it in the table object List collection;

[0079] Determine whether the shape object is a table object by the instanceof keyword. If true, it is determined to be a table object, so that the table to be processed is selected. Only the table object will be further processed. If it is a table object, the shape object is forcibly converted to a table object, and the table object is stored in the table object List collection. If it is not a table object, skip the current shape object and continue to traverse the next shape object.

[0080] In the object model of PPT, the shape object is an abstract base class that covers all shapes in PPT (such as tables, text boxes, pictures, charts, etc.). The table object is a subclass of the shape object. When traversing PPT elements, all shape objects are obtained. Even if a shape object is actually a table object, its type is still a shape object, and the methods specific to the table object (such as obtaining the number of rows, columns, and cell values) cannot be directly called. Therefore, the shape object needs to be converted to a table object by forced conversion to access the properties and methods specific to the table.

[0081] Step S2 is to traverse all slide objects in the PPT file and traverse the shape objects in each slide object to find the table objects therein and store them in the table object List collection.

[0082] S3: Create an Excel object according to the table object List collection. The specific steps include the following sub-steps:

[0083] S31: Create an Excel object;

[0084] Use Apache POI to create a new Excel object.

[0085] Initialize a blank Excel object, which will be filled with content gradually by mapping PPT table data and processing formulas.

[0086] S32: Iterate through each element of the List collection of table objects;

[0087] Iterate through all elements in the List collection of table objects, and process each element, i.e., each table object, one by one.

[0088] The List collection of table objects stores all the table objects in the PPT that need to be processed. Iterating through its elements is for the purpose of converting each PPT table into an Excel sheet "one-to-one".

[0089] S33: Create a Sheet object with the index number of the current table object as the name of the Sheet object in the Excel object;

[0090] First, obtain the index number of the current table object in the List collection of table objects, and use this index number as the name of the Sheet object.

[0091] The index number refers to the position number of the table object in the List collection of table objects, used to uniquely identify the order of each PPT table object in the collection. Since the List collection is an ordered collection (the storage order of elements is consistent with the order of addition), each table object is automatically bound with a position number that starts from 0 or 1 and increments, which is the index number.

[0092] Then, create a Sheet object with the same name as the index number in the Excel object created in step S31. This ensures that each Sheet object in the Excel object corresponds one-to-one with each table in the PPT, and that the name of the Sheet object matches the index number of the table object in the PPT, ensuring "traceability of origin" and facilitating quick access to the corresponding worksheet through the title later.

[0093] S34: Traverse all row objects and all TableCell objects of the current table object, and create the row objects and cell objects corresponding to the Sheet object;

[0094] The core of this step is to establish a structural mapping between the PPT table and the Excel worksheet. This means ensuring that the number of rows, columns, and cell positions in the Excel worksheet (Sheet object) are completely consistent with the PPT table (Table object), guaranteeing accurate correspondence for subsequent data and formulas. The specific approach is "hierarchical traversal + index mapping": first, traverse each row of the table object in the PPT, then traverse each column of each row, and finally create corresponding row and cell objects in the Excel Sheet object using the same row and column indices.

[0095] Specifically, the overall process involves iterating through the table objects in the List collection of table objects. For each table object in the PPT, the row objects are traversed in ascending order of row index, and the corresponding row objects of the Sheet objects in the Excel object are created according to the row index. For each row, the TableCell objects are traversed in ascending order of column index, and the corresponding cell objects of the Sheet objects in the Excel object are created according to the column index.

[0096] This step maps each row and column of data from the PPT table to the Sheet of the Excel object, ensuring that the number of rows and columns in the Excel worksheet is exactly the same as the PPT table, and that the cell positions are mapped one by one.

[0097] S35: Based on the actual type of the TableCell object's value, convert the TableCell object's value to the corresponding actual type, and set the value of the cell object to the converted value;

[0098] The values ​​of the TableCell object (including formulas, integers, decimals, strings, etc.) are of string type. Excel formula calculations require determining the specific type of the string. Therefore, it is necessary to convert the PPT string into a type that Excel can recognize.

[0099] First, obtain the value of the TableCell object. Then, use a regular expression to determine if the value of the TableCell object matches the formula format. If it is a formula format, obtain the data format and Excel formula in the value. Set the value of the cell object to an Excel formula, set the type of the cell object to a formula, and set the data format of the cell object to the data format obtained above.

[0100] The formula format is: $dataformat=Excel formula. $ is the formula marker. If the value of a TableCell object begins with $, the content of that cell will be processed according to the formula.

[0101] The Excel formula is a formula written after entering an equal sign (=) in an Excel cell, such as C3+D3, SUM(B2:B7), AVERAGE(B2:B7), etc.

[0102] The data format is one supported by Excel, such as 0.00 (maximum 2 decimal places) or 0.00% (maximum 2 decimal places for percentages). Its function is to convert the results of executing Excel formulas into the required data type.

[0103] The data format may or may not be specified.

[0104] The Excel formula must exist.

[0105] If the value of the TableCell object is an integer, then the value of the TableCell object is converted to an integer, the value of the Cell object is set to an integer, and the type of the Cell object is set to number.

[0106] If the value of the TableCell object is a decimal type, then convert the value of the TableCell object to a decimal, set the value of the cell object to a decimal, and set the type of the cell object to number.

[0107] Otherwise, set the value of the Cell object to the value of the TableCell object, and set the type of the Cell object to string.

[0108] Step S3 essentially involves taking a List collection of table objects as input and mapping the tables in the PPT to Excel, creating an Excel object that perfectly corresponds to the PPT tables.

[0109] S4: Establish the mapping between Excel table cell objects and PPT table cell objects (TableCell objects), and store the mapping in a cell formula mapping Map collection. This includes the following sub-steps:

[0110] S41: Iterate through each element of the List collection of table objects;

[0111] Iterate through all elements in the List collection of table objects, and process each element, i.e., each table object, one by one.

[0112] The List of table objects stores all the table objects in the PowerPoint presentation that need to be processed. Each table object is processed one by one, ensuring that no cell containing formulas is missed. Then, each element in the List of table objects is retrieved sequentially, and the subsequent mapping operation is performed individually for each table.

[0113] S42: Retrieve the corresponding Sheet object in the Excel object based on the index number of the current table object;

[0114] Each PPT table corresponds to a Sheet object with the same name in Excel (created in step S31). The Sheet object with the same name is accurately located and retrieved by the index number of the table object List collection, ensuring a one-to-one association between the Sheet object of the Excel object and the PPT table.

[0115] For example, if a table object in a List collection of table objects has an index number of "1", then the Sheet object named "1" is obtained based on that index number and used as the current Excel target Sheet.

[0116] S43: Iterate through all row objects and all TableCell objects of the current table object.

[0117] Iterate through all rows and all columns of the current table object, reading the content of each cell, i.e., the value of TableCell.

[0118] By iterating through each cell in the PPT table row by row and column by column, we can then filter out TableCell objects whose values ​​are formulas, which will require subsequent calculations.

[0119] S44: Determine if the value of the TableCell object is a formula. If it is a formula, establish a mapping relationship and store it in the cell formula mapping Map collection.

[0120] This step requires determining whether the value of a TableCell object is a formula. Then, for TableCell objects that are formulas, find their corresponding cells in Excel and record the correspondence between the two.

[0121] Determining whether the value of a TableCell object is a formula involves using regular expressions to check whether the value of the TableCell object in the PPT matches the formula format (corresponding to the formula format defined in step S35: "$data format=Excel formula").

[0122] If the value of the TableCell object is determined to be a formula, then based on the row and column position of the TableCell object within the Table object, the corresponding Cell object at the same row and column position is found in the Excel Sheet object obtained in S42. The Excel Cell object is used as the key, and the PPT TableCell object as the value, and these are stored in a cell formula mapping Map collection.

[0123] Here is an example scenario: Suppose there is a table in the PPT with index number 1, where the value of cell C4 is "$0.00%=C3 / C2", which corresponds to cell C4 of the sheet named "1" in Excel.

[0124] Step S41 iterates through the List collection of table objects, retrieves the table object with index number "1", then step S42 retrieves the Sheet object named "1" in Excel, then step S43 iterates through the row objects and TableCell objects of the table objects, finds cell C4, and uses a regular expression to determine that cell C4 is a formula. Based on its row and column position (row 4, column 4), it finds the corresponding Excel Sheet object's cell C4 and stores {Excel C4, PPT C4} in the cell formula mapping Map collection.

[0125] Step S4 essentially involves processing TableCell objects with formula values. Since these TableCell objects need to be updated after calculation in Excel, a Cell Formula Map collection is used to record these formula cells that need to be processed. Subsequent steps will require writing the calculation results of the Excel formulas back to the corresponding cells in the PPT. The mapping relationship of the Cell Formula Map collection is used to directly find the corresponding PPT cell object based on the calculated Cell object in Excel.

[0126] S5: Obtain the Excel formula calculator from the Excel object and calculate the formulas of the Cell objects in the Cell Formula Map collection. This includes the following sub-steps:

[0127] S51: Obtain the formula calculator for the Excel object.

[0128] The formula calculator accepts a Cell object of type Formula and calculates the actual value of the formula.

[0129] First, the formula calculator reads the global metadata of the Excel object, including the structure of all sheets (number of rows, number of columns), the distribution of cell types (formula type, numeric type, etc.), and data index relationships. This information is the foundation for parsing cell references in formulas and handling cross-sheet calculations. Simultaneously, the formula calculator loads Excel's native function library (such as SUM, VLOOKUP, IF, etc.) and initializes the formula parser and dependency handlers.

[0130] The formula parser is used to break down the syntax structure of the formula, and the dependency processor is used to identify other cells referenced in the formula (including direct and indirect references) to prepare for determining the subsequent calculation order.

[0131] S52: Iterate through each key in the cell formula mapping Map collection, which is the Excel cell object.

[0132] The goal of S52 is to ensure that all formula cells that need to be calculated are covered. The traversal logic needs to rely on the characteristics of the cell formula mapping Map collection while taking into account the calculation efficiency.

[0133] First, the key of the cell formula mapping Map collection is the Excel cell object, and the value is the corresponding PowerPoint TableCell object. Iteration primarily uses the EntrySet method, which directly retrieves the Cell object to be calculated while implicitly verifying the integrity of the mapping relationship. During iteration, each key is processed sequentially according to the natural order of the collection: first, the specific position of the Cell object in Excel is recorded (e.g., "Sheet1! Row 2 Column 3"), facilitating precise problem location in case of calculation errors later; in addition, a temporary counter is maintained during iteration to count the number of formula cells processed, providing a basis for tracking the progress of subsequent steps.

[0134] S53: Call the evaluate method of the formula calculator, pass in the current Excel cell object, get the calculation result, and set the type of the current Excel cell object to the specified data type according to the data type of the calculation result, and set the value of the current Excel cell object to the calculated value of the specified type of the calculation result.

[0135] When the `evaluate` method of the formula calculator is called, the current Excel cell object is passed as a parameter. Internally, the calculator first extracts the Excel formula text (e.g., "=SUM(A1:A10)") from the Cell object, and uses the built-in formula parser to break it down into syntax units such as function names, parameter lists, and cell references. The dependency handler then checks whether the referenced cell has already been calculated. If the referenced cell is a formula and has not been calculated, the `evaluate` method is recursively called to complete the dependency calculation; otherwise, the value is read directly. After the calculation is complete, the `evaluate` method returns the original calculation result, which may be a number, boolean, text, or an error type.

[0136] Specifically, the system determines the type of the calculation result. If it's a numeric type, it sets the type of the current Excel cell object to numeric and sets its value to the numeric calculated value of the result. If it's a text type, it sets the type to text and sets its value to the text calculated value of the result. If it's a Boolean type, it sets the type to Boolean and sets its value to the Boolean calculated value of the result. If it's an error type, it sets the type to error and sets its value to the incorrect calculated value of the result. This ensures that subsequent readings of the cell object retrieve the calculated result, not the original formula, and avoids result discrepancies caused by recalculating the formula.

[0137] Step S5 is the core of this method. By obtaining the formula calculator associated with the Excel object, it realizes the parsing and calculation of complex formulas in PPT, providing reliable data support for updating PPT cells with calculation results in subsequent steps.

[0138] S6: Update the value of the PPT table cell TableCell object in the cell formula mapping Map collection to the value of the Excel table cell Cell object. This includes the following sub-steps:

[0139] S61: Iterate through each key-value pair in the cell formula mapping Map collection;

[0140] The entrySet() method iterates through the cell formulas to map each key-value pair in the Map collection.

[0141] The cell formula mapping Map collection stores the mapping relationship between Excel formula cells and corresponding cells in PPT. These mappings need to be processed one by one to ensure that the calculation result of each formula can be updated in PPT.

[0142] Specifically, it retrieves the key-value pairs from the cell formula mapping Map collection one by one and performs an update operation for each mapping.

[0143] S62: Set the value of the PPT table TableCell object to the value of the formatted Excel table cell Cell object;

[0144] For the TableCell object corresponding to the Cell object of the Excel table cell in the cell formula mapping Map collection, get the calculated value of the Cell object of the Excel table cell, get the data formatting tool, format the calculated value into the final value of the specified data format, such as percentage, and replace the value of the TableCell object from the formula to the final value;

[0145] Specifically, first iterate through all text paragraph objects of the TableCell object, then iterate through each text block object of each text paragraph object, clearing the text in the text block objects. Then, retrieve the data formatting tool, format the calculated value of the Cell object of the Excel table cell to the final value of the specified data format, such as percentage, replace the value of the TableCell object from the formula to the final value, and set the text of the first text block object of the first text paragraph object to the final value.

[0146] Step S6 is to update the results calculated by the Excel formula calculator to the corresponding cells in the PPT, ultimately achieving the goal of "displaying the results of Excel formula calculations in a PPT table".

[0147] This step uses cell formulas to map the relationships in a Map collection, finds the corresponding cell in the PPT, writes the calculated value, and completes the update of the PPT cell.

[0148] Here is a specific example: Suppose a TableCell object has the value "$0.00#=B2*C2", and B2=0.7 and C2=0.9. The formula calculator object calculates the corresponding Cell object of the TableCell object, and the calculated value is 0.63. Then, the data formatting tool converts 0.63 to 63% and sets it as the value of the TableCell object as the final value to be displayed.

[0149] S7: Close the Excel object and return the results. This includes the following sub-steps:

[0150] S71: Close the Excel object;

[0151] Call the Excel object's close method to close the Excel object. If file streams or other resources are used, they also need to be closed.

[0152] For example, suppose we use the Apache POI library to process Excel files. After processing the data, we need to close the XSSFWorkbook object and its associated file streams.

[0153] Closing the Excel object is to release the resources associated with it, prevent resource leaks, and ensure the stability and efficiency of processing.

[0154] S72: Generate a PPT file from a PPT object;

[0155] Using the document generation function of the PPT processing library, create a new PPT document object based on the integrated slide and table data in memory. This object contains: the structure of all original slides (page layout, non-table elements), the shape object to be replaced with the updated table, and retains the original PPT formatting (font, color, style, etc.), updating only the content of formula cells in the table.

[0156] This step will repackage the PPT content, which includes the updated tables, into a single complete PPT file. The PPT file supports byte streams, as well as full file paths, file objects, and file output stream objects. It includes all modifications and conforms to PPT specifications.

[0157] S73: Return the PPT file to the user;

[0158] The processed PPT file is returned to the user, allowing them to view and use the processed data.

[0159] Depending on the application scenario, the PPT file can be returned to the user in an appropriate manner. For example, in a web application, a download link can be provided; in a backend service, a file byte stream can be returned via API.

[0160] In one example, the original PPT contains one slide with a table. Cell C3 contains the formula "$0.00%=B2*C2". After steps S1-S6, the table object corresponding to "1" in the table object List collection has been updated: the value of cell C3 has become "63%". Other elements (such as slide title, footer date, etc.) remain unchanged. Then, step S72 generates a new PPT document object containing the updated table and the original format. Finally, step S73 returns the new document to the front end as a byte stream. After the user downloads and opens the PPT, they can directly see that cell C3 displays "0.63", and other content and formatting are consistent with the original PPT.

[0161] The overall idea of ​​step S7 is to clean up resources and return the final result after completing the data processing. Specifically, after converting the intermediate technical processing into a PPT document that the user can directly use, the Excel object needs to be closed to release resources, and then the processed PPT file is returned to the user.

[0162] Through the above steps, this invention enables PPT tables to support formulas and functions from Excel tables.

[0163] The present invention has been described in the above-described embodiments; however, these embodiments are merely examples for implementing the present invention. It must be noted that the disclosed embodiments do not limit the scope of the present invention. Conversely, any modifications and refinements made without departing from the spirit and scope of the present invention are within the scope of patent protection of the present invention.

Claims

1. A method for enabling PPT table to support use of formulas and functions in Excel table, characterized in that, The method comprises the following steps: S1: receiving a PPT file input by a user, and creating a PPT object; specifically comprising the following sub-steps: S11: receiving a PPT file input by a user; S12: checking the PPT file and type; S13: creating the PPT file into a file stream object; S14: creating the file stream object into a PPT object; The PPT object performs processing operations on the PPT file; S2: traversing table objects in the PPT object and storing the table objects in a table object List collection; specifically comprising the following sub-steps: S21: traversing each slide object in the PPT object; Each slide object is an independent slide page in the PPT file; the PPT object is composed of multiple slides; S22: traversing each shape object in the slide object; The content in the slide object exists in the form of a shape object, such as a table, a text box, a picture, and a chart; S23: judging whether the shape object is a table object and storing the table object in the table object List collection; If the shape object is a table object, the shape object is forcibly converted into a table object and stored in the table object List collection; If it is not a table object, the current shape object is skipped, and the next shape object is continuously traversed; S3: creating an Excel object according to the table object List collection; specifically comprising the following sub-steps: S31: creating an Excel object; An empty Excel object is initialized, and subsequent contents are filled in by mapping PPT table data and processing formulas; S32: traversing each element table object in the table object List collection; S33: creating a Sheet object by taking the index serial number of the current table object as the Sheet object name of the Excel object; The index serial number refers to the position number of the current table object in the table object List collection, and uniquely identifies the order of each PPT table object in the collection; S34: traversing all row objects and all TableCell objects of the row objects of the current table object, and creating row objects and cell Cell objects corresponding to the Sheet object; S35: according to the actual type of the value of the TableCell object, converting the value of the TableCell object into the corresponding actual type, and setting the value of the cell Cell object as the converted value; First, judging whether the value of the TableCell object is a formula type; If the TableCell object is a formula type, setting the type of the cell Cell object as a formula; If the TableCell object is not a formula type, then continue to judge whether it is an integer, a decimal, or a string type; S4: establishing a corresponding relationship between Excel table cell Cell objects and PPT table cell TableCell objects, and storing the corresponding relationship in a cell formula mapping Map collection; specifically comprising the following sub-steps: S41: traversing each element table object in the table object List collection; S42: obtaining the corresponding Sheet object in the Excel object according to the index serial number of the current table object; Each PPT table object corresponds to a Sheet object of the same name in the Excel object created by step S31, and the Sheet object is accurately located and obtained through the index number of the table object List collection; S43: traverse all row objects and all TableCell objects of the current table object; S44: determine whether the value of the TableCell object is a formula, and if it is a formula, establish a mapping relationship and store it in the cell formula mapping Map collection; The determination of whether the value of the TableCell object in the Table table object traversed in step S43 is a formula is to check whether the value of the TableCell object matches the formula format through a regular expression; S5: obtain an Excel formula calculator according to the Excel object, and calculate the formula of the cell Cell object in the cell formula mapping Map collection; specifically including the following sub-steps: S51: obtain the formula calculator of the Excel object through the Excel object; The formula calculator accepts the cell Cell object of the formula type, and calculates the actual value of the formula; S52: traverse each key in the cell formula mapping Map collection, that is, the Excel cell Cell object; S53: call the evaluate method of the formula calculator, input the current Excel cell Cell object, obtain the calculation result, and set the type of the current Excel cell Cell object to the specified data type according to the data type of the calculation result, and set the value of the current Excel cell Cell object to the calculation value of the data type of the calculation result; The data type of the calculation result can be a numerical value, a Boolean value, a text or an error, and the specified data type is consistent with the data type of the calculation result; S6: update the value of the PPT table cell TableCell object in the cell formula mapping Map collection to the value of the Excel table cell Cell object; specifically including the following sub-steps: S61: traverse each element key-value pair in the cell formula mapping Map collection; S62: set the value of the PPT table TableCell object to the value of the formatted Excel table cell Cell object; Replace the value of the TableCell object corresponding to the key in the cell formula mapping Map collection from the formula to the calculation value of the cell Cell object; S7: close the Excel object and return the result; specifically including the following sub-steps: S71: close the Excel object; S72: generate a PPT file from the PPT object; S73: return the PPT file to the user. In step S23, it is determined whether the shape object is a table object, and if it is a table object, the table object is filtered out for processing, and only the table object will be further processed; 2. The method of claim 1, wherein the PPT table is implemented to support use of formulas and functions in an Excel table. If it is a table object, the shape object is forcibly converted into a table object, and the table object is stored in the table object List collection; ​ If it is not a table object, skip the current shape object and continue to traverse the next shape object; the storage order of the table object List set is consistent with the order of the table object in the PPT.

3. The method of claim 1 to enable PPT table support using formulas and functions in Excel tables, wherein, In step S33, the index number of the current table object in the table object List set is obtained, and the string form of the index number is directly used as the Sheet object name, which is one-to-one corresponding to the index of the table object.

4. The method of claim 1 to enable PPT table support using formulas and functions in Excel tables, wherein, In step S34, the table objects in the table object List set are traversed, and for each table object of the PPT, the row objects of the PPT table object are traversed in ascending order of row index, and the row objects of the Sheet objects in the Excel object are created corresponding to the row index; for each row object, the TableCell objects contained therein are traversed in ascending order of column index, and the cell Cell objects of the Sheet objects in the Excel object are created corresponding to the column index.

5. The method of claim 1 to enable PPT table support using formulas and functions in Excel tables, wherein, In step S35, it is judged by a regular expression whether the value of the TableCell object matches the formula format, i.e. $data format=Excel formula, and the data format and Excel formula are obtained; If the value of the TableCell object is in the formula format, the data format and Excel formula in the value are obtained, the value of the cell Cell object is set to the Excel formula, the type of the cell Cell object is set to the formula, and the data format of the cell Cell object is set to the above-mentioned obtained data format; The data format is the data format rule supported by Excel, which can exist or not; The Excel formula is the formula written after "=" in the Excel cell, which must exist; If the value of the TableCell object is not in the formula format, it is converted into the Excel number or date type according to the integer or decimal format of the string, and the remaining cases are kept as the string type.

6. The method of claim 1 to enable PPT table support using formulas and functions in Excel tables, wherein, In step S44, if it is judged that the value of the TableCell object is a formula, the cell Cell object with the same row and column position in the Sheet object of the Excel object obtained in S42 is found according to the row and column position of the TableCell object in the Table table object; the cell Cell object of the Excel object is stored as the key and the TableCell object of the PPT as the value in the cell formula mapping Map set.

7. The method of claim 1 to enable PPT table support using formulas and functions in Excel tables, wherein, In step S51, the formula calculator includes a formula parser and a dependency processor; the formula parser is used to disassemble the syntax structure of the formula, and the dependency processor is used to identify other cells referenced in the formula.

8. The method of claim 1 to enable PPT table support using formulas and functions in Excel tables, wherein, In step S53, the evaluate method of the formula calculator is called with the current Excel cell object as a parameter; the formula calculator first extracts the Excel formula text in the Cell object, disassembles it into function names, parameter lists, and cell references through the built-in parser, and then checks the reference cells by the dependency processor: if the reference cells are uncalculated formula types, the evaluate method is recursively called for calculation, and if they are normal data types, the values are directly read; After calculation, the evaluate method returns the calculation results of numerical, boolean, text, or error types, and sets the type of the current Excel cell Cell object to the specified data type and the value of the current Excel cell Cell object to the calculation value of the data type of the calculation results according to the data type of the calculation results.

9. The method of claim 1 to enable PPT table support using formulas and functions in Excel table, wherein, In step S62, for the TableCell object of the PPT table corresponding to the key in the cell formula mapping set, all text paragraph objects of the TableCell object of the PPT table are traversed, and for each text paragraph object, the text block objects contained therein are traversed to clear the contents of all text blocks; Then, the calculation value of the Cell object of the Excel table cell is obtained, the data formatting tool is obtained, the calculation value is formatted into the final value of the specified data format, the data format is the percentage format, the first text block object is obtained in the first text paragraph object of the TableCell object of the PPT table, and the final value is written into the content.

Images