如何将单元格格式设置为文本 [英] 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")，并且在角落显示一个小绿色三角形.将鼠标悬停在感叹号上会显示悬停文本 此单元格中的数字格式为文本或前面有撇号.当我查看单元格格式(右键单击 - > 设置单元格格式)时，类别"显示为常规".我希望这是文本".

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?

推荐答案

可以尝试通过

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 格式的忽略错误"功能，但尚未完全支持它，请参阅 问题 46136 和 发布 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屋！