Stamping a Company Seal on Excel Invoices: No Private Storage, a Lying resize(), and Three Coordinate Systems

The task

In our internal business system, I built a feature that stamps a company seal (the square kaku-in used on Japanese business documents) onto invoices, quotes, and purchase orders. The documents are Excel templates filled with data and converted to PDF via LibreOffice. The requirements:

• Never distribute the seal image by itself — no public URL, no image-serving API. It may only be seen stamped on a document
• Per-document ON/OFF toggle
• Once a document is finalized, replacing the seal later must not change it — but copying the image per document is forbidden
• Position it relative to the issuer block, slightly overlapping the address; same physical size across all three templates

"Just paste one image," I thought. I then got stuck in three different layers: infrastructure, the library, and the coordinate systems.

Problem 1: there was no private storage

My first idea was the existing S3 bucket that stores attachments and logos. Then I read its bucket policy:

{
  "Effect": "Allow",
  "Principal": "*",
  "Action": "s3:GetObject",
  "Resource": "arn:aws:s3:::<bucket>/*"
}

Principal: * with s3:GetObject — anyone who knows the key can read the object. No matter how careful the application code is, putting the seal there breaks the "never distribute" requirement at the storage layer.

The seal is a PNG of a few dozen KB, so I stored it in a PostgreSQL bytea column instead. If you never build a GET endpoint for the image, a public URL cannot exist by construction. The only consumer is the document renderer.

The seemingly contradictory pair "no copies, yet finalized documents are immutable" was solved by an append-only design:

• Replacing the seal inserts a new row and repoints the company's "current seal" reference
• Deleting the seal keeps the row and only cuts the reference
• Finalizing a document snapshots the seal's row ID onto the document

Finalized documents keep pointing at the old row, so later changes never touch them. There is always exactly one copy of each image.

As a bonus finding, finalized PDFs were also sitting in that same public bucket under guessable keys. That was out of scope for this feature, so I filed it as an infrastructure task (prefix-level deny in the bucket policy) and handed it over.

Problem 2: POI's resize() trusts the font

Next, placing the image. With Apache POI I anchored the picture at the right edge of column F in the issuer block, set the anchor's dx, and called Picture.resize(scale). The seal ended up in the middle of the cell.

The cause is inside resize(): it re-derives the horizontal offset by estimating cell widths from font metrics. The template uses MS PGothic, and the estimate came out at roughly 60% of the real column width — so my carefully computed dx was overwritten based on a wrong premise.

The fix was to drop resize() entirely and compute all four corners of the twoCellAnchor (from/to col, row, dx, dy, in EMUs) myself:

XSSFClientAnchor anchor = new XSSFClientAnchor(
    fromDx, fromDy, toDx, toDy,
    fromCol, fromRow, toCol, toRow);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
Picture pic = drawing.createPicture(anchor, pictureIdx);

Row heights read back accurately from the template, so vertically I just accumulate rows until the required height is reached. I also set the shape's xfrm off/ext to the same values. Convenience methods hide their assumptions — here, the font.

Problem 3: there were three coordinate systems

The PDF output was still off in position and size. It turned out three separate layers transform coordinates, each modified at a different stage:

1. Cell width in "characters". Before PDF conversion, our pipeline enlarges fonts by 6pt and shrinks cell widths (which Excel stores in character units) proportionally to preserve the layout — e.g. 45.66 chars → 29.5 chars. Computing pixels from the shrunken value gives you a too-narrow cell. I multiply the font ratio back in first.

2. Print scale. The templates print at 61%, so 66 px on the sheet is 10 mm on paper. A kaku-in is conventionally 18–21 mm, which back-calculates to a ~110 px square on the sheet.

3. Millimeters on paper. The only layer anyone actually cares about. Luckily all three templates use the same 61% scale, so equal sheet pixels automatically satisfied the "same size everywhere" requirement.

Characters on the sheet, pixels on the sheet, millimeters on paper: ignore any one of the three and it looks right on screen and wrong on paper.

Verification: what ships on paper must be checked on paper

Unit-test templates are empty sheets, so they can't tell you what the paper will look like. I rendered 12 documents (3 real templates × long/short address × ON/OFF) with a dummy seal PNG, converted them with local LibreOffice, and cropped the top of each PDF to a PNG for eyeballing:

libreoffice --headless --convert-to pdf out/*.xlsx
pdftoppm -png -r 100 -y 0 -H 350 out/invoice.pdf check/invoice

All three "not yet" verdicts (not overlapping the address yet, too big, etc.) came from those images. The harness was deleted, not committed; what stayed in the repo were six unit tests: ON puts an anchor at column F with dx > 0, OFF renders no picture, ON with no registered seal renders no picture, text positions are identical between ON and OFF, and a finalized document keeps its seal after the company removes it.

Three small ones

• H2 (PostgreSQL compatibility mode) in tests doesn't understand blob: a byte[] column with a 4 MB limit made Hibernate choose blob and the schema failed; lowering the limit to 1 MB switched it to varbinary
• "The file won't select" → a WebP file against accept="image/png,image/jpeg"
• The browser file input's default "no file selected" text looked out of place, so the input is hidden behind register/replace/delete buttons

Takeaways

• "Private" breaks at the infrastructure, not in your code. Read the bucket policy before choosing a storage location. If the storage is public, your access control isn't
• Convenience methods hide assumptions. POI's resize() estimates cell widths from fonts. When placement is off, go back to the low-level API (twoCellAnchor + EMU) and your own math
• What ships on paper must be verified on (something close to) paper. Of the three coordinate layers, users only ever see the last one — verify in that layer

A "just paste one image" feature ended up spanning bucket policies to EMUs. Next time someone says "you just place it," I'll start by counting the coordinate systems.