如何将单元格格式设置为文本 [英] How to set a cell format to Text

问题描述

我正在使用Apache-POI 3.14.我需要将单元格锁定为文本"格式.我单元格中的数据可能是全数字，但仍被认为是字符串.当我编写单元格时，我会像这样:

I am using Apache-POI 3.14. I have a need to lock-down a cell to a "Text" format. The data in my cell might be all digits, but it is still considered a string. When I write the cell, I do it like this:

cell.setCellValue("001");
cell.setCellType(Cell.CELL_TYPE_STRING);

当我在Excel中打开输出工作簿时，该单元格包含正确的值("001")，并且在角落显示有一个小的绿色三角形.将鼠标悬停在感叹号上会显示悬停文本The number in this cell is formatted as text or preceded by an apostrophe.当我查看单元格格式(右键单击->设置单元格格式)时，类别"显示为常规".我希望这是文字".

When I open the output workbook in Excel, the cell contains the correct value ("001") and it displays with a small green triangle in the corner. Hovering over the exclamation point displays the hover text The number in this cell is formatted as text or preceded by an apostrophe. When I look at the cell formatting (Right-click -> Format cells), the "Category" is displayed as "General". I expected this to be "Text".

当用户通过仅输入数字来修改单元格中的值时，就会出现问题.由于类别"是常规"，因此将值输入并显示为数字，并删除前导零和右对齐.

The problem arises when a user modifies the value in the cell by entering only digits. Because the "Category" is "General", the value is entered and displayed as a number, removing leading zeroes and right-justified.

如何获得与Excel的设置单元格格式"对话框相同的结果?

How can I achieve the same result as Excel's "Format cells" dialog?

推荐答案

您可以尝试通过以下方式将单元格格式设置为文本:

You can try to set the cell-format to text via

DataFormat fmt = wb.createDataFormat();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
    fmt.getFormat("@"));
cell.setCellStyle(cellStyle);

注意:应将CellStyles重用于所有适用的单元格，而不是为每个单元格创建新的单元格.

Note: CellStyles shoudl be re-used for all applicable cells, do not create new ones for every cell.

您还可以尝试使用.xlsx格式的忽略错误"功能，但是尚未完全完成对它的支持，请参见问题58641 进行一些持续的讨论.

You could also try to use the "Ignore errors" feature in the .xlsx format, however support for it is not fully done yet, see Issue 46136 and Issue 58641 for some ongoing discussion.

另请参见此MSDN页面以获得其他信息

See also this MSDN page for some additional information

这篇关于如何将单元格格式设置为文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！