poi | Write Excel file using Java and Apache POI | Object-Relational Mapping library

Creating cell styles for each single cell is not a good idea using apache poi. Cell styles are stored on workbook level in Excel. The sheets and cells share the cell styles if possible.

And there are limits for maximum count of different cell styles in all Excel versions. The limit for the binary *.xls is less than the one for the OOXML *.xlsx.

The limit alone cannot be the only reason for the result you have. But it seems as if Excel is not very happy with the 50 exactly same cell styles in workbook. Those are memory waste as only one shared style would be necessary as all the 50 cells share the same style.

Solutions are:

Do creating the cell styles on workbook level outside cell creating loops and only set the styles to the cells in the loop.

Example:

The whole problem goes away when the text-replacement works correctly.

The problem here is how Word stores texts in different text runs. Not only different formatting splits text in different text runs, also marking grammar and spelling check problems do and multiple other things. So one can impossible predict how a text gets split into text runs when typed in Word. That's why your text-replacement approach is not good.

Apache POI provides TextSegment to solve those kind of problems. And using current apache poi 5.2.0 this also seems to work correctly. Former versions had have bugs in XWPFParagraph.searchText - see Apache POI: ${my_placeholder} is treated as three different runs for a workaround.

Using TextSegment one can determine the begin and end of a seached text and so doing the text-replacement better.

Following example should show this.

My Reference.docx looks like so:

There ${firstname}, ${lastname} and ${address} in head are bookmarked as firstname. lastname and address. And their occurences in text are references as { REF firstname } , { REF lastname} and { REF address}

After running following code:

According to your uploaded sample files your content controls are in a table. The code you had found only gets content controls from document body directly.

Tables are beastly things in Word as table cells may contain whole document bodies each. That's why content controls in table cells are strictly separated from content controls in main document body. Their ooxml class is CTSdtCell instead of CTSdtRun or CTSdtBlock and in apache poi their class is XWPFSDTCell instead of XWPFSDT.

If it is only about reading the content, then one could fall back to XWPFAbstractSDT which is the abstract parent class of XWPFSDTCell as well as of XWPFSDT. So following code should work:

The problem with the lines

You should use double l rather than int l, e.g.,

As @Axel Richter mentioned using == is invalid.

cell.setCellFormula("IF(AND(LEN(A1:A3)>0,(B1:B3)==UK),1,0)");

Mistakes with your formula.

#1. The error...

Your code only appends multiple CTBody elements into the document. But that is not how a Word document is structured. "It works" because Microsoft Word is tolerant enough to interpret it. But it fails when it comes to references within the Word document structure.

For example to numbering definitions are referenced by IDs. And that can be the same ID for different definitions in different documents. So ID 1 in first document might be pointing to a decimal numbering while ID 1 in second document might be pointing to a bullet numbering. So the numIDs needs to be merged and not only copied.

Embedded media (images for ex.) are referenced by rIDs. So the CTBody only contains the IDs. The media itself is stored outside the document body. So if the document body refers to a picture having rID12 and this picture is not stored, then the document gets corrupted.

Same is with many other document elements.

So that approach is not usable at all.

The need is traversing all body elements of the document, which shall be appended. Then append each found body element to the first document and update it's references.

The following is a working draft to show the principle. It is not ready yet. For example it does not considering hyperlinks, footnotes, comments, structured document tags and much more things. And as you see from the sheer amount of code needed, considering all possible things will be a very laborious task to do. To avoid even more code I simply copy the underlying XML bean if possible. This also should be better formed out for productive usage. But the principle should be clear from this code.

The code is commented when names of methods and variables are not self explained.

The code is tested and works using current apache poi 5.1.0. Former versions are not tested and also should not be used since they offer even less support for XWPF.

The code needs the full jar of all of the schemas as mentioned in Apache POI FAQ.

Precision of 6 decimal places gives you ~10 centimeter accuracy. Mapbox is using GeoJSON specification which has following recommendation about precision:

The size of a GeoJSON text in bytes is a major interoperability consideration, and precision of coordinate values has a large impact on the size of texts. A GeoJSON text containing many detailed Polygons can be inflated almost by a factor of two by increasing coordinate precision from 6 to 15 decimal places. For geographic coordinates with units of degrees, 6 decimal places (a default common in, e.g., sprintf) amounts to about 10 centimeters, a precision well within that of current GPS systems. Implementations should consider the cost of using a greater precision than necessary.

GPS-enabled smartphones are typically accurate to within a 4.9 m (16 ft.) radius under open sky. However, their accuracy worsens near buildings, bridges, and trees.ref

Here is a demo of distance accuracy when precision of 6 decimal places is used:

A piece as it says about a zip archive in the error. There is no actual stream assigned with the place e,g, you have a String path but analogous there is not actually so much as an actual .io.File related part of the code. There is however a constructor for such document that takes a Stream argument on construction.