Locking Cells, Setting Column Width, Row Height, Font, and Merging Cells in Excel with Apache POI

Apache POI allows Java programs to generate Excel files, but real‑world requirements often include protecting sheets, customizing column widths, row heights, fonts, colors, and merging cells.

1. Locking cells – Protect the whole sheet with sheet.protectSheet("zgd") , then create a CellStyle unlockCell = workbook.createCellStyle(); unlockCell.setLocked(false); and apply it to cells that should remain editable, e.g., dataRow.getCell(i).setCellStyle(unlockCell); .

2. Setting column width – After protecting the sheet, automatic sizing is unavailable, so you can use one of the following methods:

Auto‑size: sheet.autoSizeColumn(1); or sheet.autoSizeColumn(1, true); (available in newer POI versions).

Manual width array: define an int[] width = {xxx, xxx}; and call sheet.setColumnWidth(i, width[i]); .

Calculate width from the longest string in a column: int length = str.getBytes().length; sheet.setColumnWidth((short)col, (short)(length*256)); . The author recommends limiting the width to 10,000‑15,000 units to avoid errors.

For large datasets, auto‑size can be very slow (e.g., 2 minutes for 1,000 rows), so manual calculations are preferred.

3. Setting row height – Use titleRow.setHeightInPoints(20); . Note that a fixed row height disables automatic height adjustment when cells wrap text.

4. Setting font and color – Create a CellStyle , then a HSSFFont , configure it (e.g., redFont.setColor(Font.COLOR_RED); redFont.setFontHeightInPoints((short)10); ), attach the font to the style, and apply the style to a cell.

5. Merging cells – Define a range with CellRangeAddress cellRange1 = new CellRangeAddress(0, 0, (short)0, (short)10); and add it to the sheet via sheet.addMergedRegion(cellRange1); . Set the value in the top‑left cell of the merged region.

All code snippets are provided in the article and can be copied directly into a Java project using POI.