Java 如何将单元格格式设置为文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36018660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set a cell format to Text
提问by Steve H.
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:
我正在使用 Apache-POI 3.14。我需要将单元格锁定为“文本”格式。我的单元格中的数据可能都是数字，但仍被视为字符串。当我写单元格时，我这样做：
cell.setCellValue("001");
cell.setCellType(Cell.CELL_TYPE_STRING);
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".
当我在 Excel 中打开输出工作簿时，单元格包含正确的值（“001”），并在角落显示一个小绿色三角形。将鼠标悬停在感叹号上会显示悬停文本The number in this cell is formatted as text or preceded by an apostrophe。当我查看单元格格式（右键单击 - > 设置单元格格式）时，“类别”显示为“常规”。我希望这是“文本”。
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.
当用户通过仅输入数字来修改单元格中的值时，就会出现问题。由于“类别”是“常规”，因此该值被输入并显示为数字，删除前导零并右对齐。
How can I achieve the same result as Excel's "Format cells" dialog?
如何获得与 Excel 的“设置单元格格式”对话框相同的结果？
采纳答案by centic
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);
Note: CellStyles shoudl be re-used for all applicable cells, do not create new ones for every cell.
注意：应该为所有适用的单元格重新使用 CellStyles，不要为每个单元格创建新的单元格。
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 46136and Issue 58641for some ongoing discussion.
您也可以尝试使用“忽略错误”功能在.XLSX格式，但支持它尚未完全完成，请参阅发行46136和发行58641对一些正在进行的讨论。
See also this MSDN pagefor some additional information
另请参阅此 MSDN 页面以获取更多信息
回答by Sebastian B
I have no means of testing this, but have you tried using a template - an empty excel file with all cells formated as text?
我无法对此进行测试，但是您是否尝试过使用模板 - 一个所有单元格都设置为文本格式的空 excel 文件？
回答by Ramana Viswanadha
Look like OP was asking for Apache solution. After some searching I found this answer:
看起来 OP 正在寻求 Apache 解决方案。经过一番搜索，我找到了这个答案：
HSSFCellStyle style = book.createCellStyle();
style.setDataFormat(BuiltInFormats.getBuiltInFormat("text"));
回答by Shubhashish Mishra
For HSSF,
对于 HSSF，
DataFormat fmt = workbook.createDataFormat();
CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(fmt.getFormat("@"));
sheet.setDefaultColumnStyle(0, textStyle);
It just sets the whole column style as Text and set category as Text .
它只是将整个列样式设置为 Text 并将类别设置为 Text 。
However, if you are using XSSF format, it doesn't work(I am using Apache Poi 3.15 and didn't work for me). In this case you have set style to each cell you want to treat as text in addition to above code using:
但是，如果您使用的是 XSSF 格式，则它不起作用（我使用的是 Apache Poi 3.15 并且对我不起作用）。在这种情况下，除了使用上述代码之外，您还为要视为文本的每个单元格设置了样式：
cell.setCellStyle(textStyle);
Regarding error, you could use
关于错误，您可以使用
sheet.addIgnoredErrors(new CellRangeAddress(0,9999,0,9999),IgnoredErrorType.NUMBER_STORED_AS_TEXT );
It ignores the NUMBER_STORED_AS_TEXT error for row 0 till 9999 and column 0 till 9999 and you wont see it.
它会忽略第 0 行到 9999 和第 0 列到 9999 的 NUMBER_STORED_AS_TEXT 错误，您不会看到它。
回答by Algorithm
In this case, I'm using Apache-POI 3.15, and I had the same problem, so I validated the data in my style, I need numbers >0 and strings:
在这种情况下，我使用的是 Apache-POI 3.15，我遇到了同样的问题，所以我以我的风格验证了数据，我需要数字 > 0 和字符串：
try {
if (Integer.parseInt(field + "") >= 0) {
int valor = Integer.parseInt(field + "");
cell.setCellValue(valor); //Int
}
} catch (NumberFormatException nfe) {
// no int
try {
if (Double.parseDouble(field + "") >= 0) {
double valor = Double.parseDouble(field + ""); //double
cell.setCellValue(valor);
}
} catch (NumberFormatException nfe2) {
cell.setCellValue(field + ""); //String
}
}
回答by Laurent
For Apache POI 4.0.1 :
对于 Apache POI 4.0.1 ：
XSSFSheet sheet = workbook.createSheet("MySheetName");
sheet.addIgnoredErrors(new CellRangeAddress(0, 9999, 0, 9999), IgnoredErrorType.NUMBER_STORED_AS_TEXT);
Be careful to cast your sheet to org.apache.poi.xssf.usermodel.XSSFSheetand not to org.apache.poi.ss.usermodel.Sheet, otherwise the method addIgnoredErrors wil be unknown.
小心将您的工作表转换为org.apache.poi.xssf.usermodel.XSSFSheet而不是org.apache.poi.ss.usermodel.Sheet，否则方法 addIgnoredErrors 将是未知的。
