Problem with formatting a cell to text in Excel

Question

CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(format.getFormat("@"));

Set<Integer> forceTextIndexes = new HashSet<>();
if ("LE_27".equalsIgnoreCase(excelName)) {
    forceTextIndexes.add(3);
    forceTextIndexes.add(6);
} else if ("LE_28".equalsIgnoreCase(excelName)
    || "LE_29".equalsIgnoreCase(excelName)) {
    forceTextIndexes.add(3);
    forceTextIndexes.add(4);
}

for (int i = COPY_VALUES_START_INDEX; i < columnValues.length; i++) {
    int cellIndex = WRITE_START_COLUMN + (i - COPY_VALUES_START_INDEX);
    Cell cell = row.createCell(cellIndex);
    Object value = columnValues[i];

    boolean forceAsText = forceTextIndexes.contains(i);

    if (value == null) {
        cell.setCellType(CellType.BLANK);
    } else if (forceAsText) {
        cell.setCellStyle(textStyle);
        cell.setCellValue("'" + value);
    }
}

I use Apache POI, and I am trying to change the formatting of some cells to text in Excel. Every time I fetch an nvarchar from my SQL database that looks like a number, it keeps on writing it in Excel as a number and not as text. Help!

OUTATIME121GW · Accepted Answer · 2025-07-08 12:26:22Z

0

cell.setCellValue("'" + value);

This is just putting a literal apostrophe in the cell - it's a workaround when typing manually but in this case, it writes it as actual data

Try changing it to:

cell.setCellValue(String.valueOf(value));

answered Jul 8 at 12:26

OUTATIME121GW

171 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

siggemannen Sep 15 at 18:54

That shouldn't help because "'" + value is also a string type

Collectives™ on Stack Overflow

Problem with formatting a cell to text in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related