Poi/excel/html Cell Formula problem

First, the problem description

Use MyBatis to get the data from the database, and then populate the data in the Excel template with a POI to generate the final XLS file. Convert the final XLS file to an HTML file and return it to the foreground displayed in the panel.

In Excel templates, there are some calculation formulas in addition to data point characters. Since the data referenced by these formulas is a dot in the template, the cell is displayed as "#VALUE!". See:

Generates an Excel file, and the value of the calculated cell is displayed correctly after you have added the associated code for the recalculation (see below). After converting to HTML, these calculated cells are not recalculated and still appear as "#VALUE!".

Second, the problem analysis

The problem has been around for about two weeks, and it has taken some time to study intermittently, but the problem has not been solved.

This use of third-party components caused by problems, troubleshooting is more difficult. Searched the internet for a large circle and did not find a description of the similar problem. There are a few things you can refer to to recalculate Excel cell formulas using POI.

Download the POI 3.11 related source code, set up a test environment in this machine, for tracking debugging. Found to be a exceltohtmlconverter class that always returns Cell.cell_type_error when getting the cachedformularesulttype of a cell containing a formula, Causes the method to get the value of the cell (that is, #value!) and return it only through Cell.geterrorcellvalue ().

Looking at the relevant source code, POI set the _record and other properties to private, the call cannot be modified. Another search on the internet, there is no reference to the value of information.

Third, problem solving

When you open and close the XLS file that was generated after populating the data with Excel, Excel always prompts you to save, but it doesn't actually make any changes. If you "save" the template and then generate HTML, the cell formula will be calculated and displayed as normal. It is always felt that the XLS generated by the POI file actually has compatibility issues, but Ms has not developed the format and description of the relevant files before Office2007, so it is not a poi.

After you generate Excel, recalculate the formula cells:

"Only Setforceformularecalculation (boolean.true) is invalid when converting to HTML"

In the formal engineering and the first Test code, the fetch number generates Excel, and the conversion to HTML is written in a function. It's not easy to feel in the test. The result is that Excel and HTML are split into two function to make it easier to control whether or not to regenerate Excel. Did not expect to split up a run of test code, the calculation unit in the HTML has a value! That's almost it!

Then the code in the formal project also split a bit, this maddening problem, in this unreasonable way to solve.

Iv. Summary of issues

There is a problem in generating the XLS file in the same method, converting it to HTML, and splitting it into two methods is not a problem. This is a rather bizarre phenomenon, and there should be a problem with the code level. For the time being, I will not delve.

Poi/excel/html Cell Formula problem