Java 使用 POI 在 excel 中存储为文本警告的数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21925560/
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
Number stored as text warning in excel using POI
提问by rick
I am getting Number stored as text warningfor the created excel file using POI. I am trying to display percentage. This questiondiscusses the same, but it's for python. Can some one please suggest me how to avoid this in java using POI?
我正在Number stored as text warning使用POI获取创建的 excel 文件。我正在尝试显示百分比。这个问题讨论了同样的问题，但它是针对python的。有人可以建议我如何使用 POI 在 Java 中避免这种情况吗？
Below are the lines where I get this warning.
以下是我收到此警告的行。
workbook= new XSSFWorkbook();
sh1 = wb.createSheet("Data Sheet");
cell = row.createCell(3);
cell.setCellValue(37 + "%");
Based on Gagravarr answer I did it this way.
根据 Gagravarr 的回答，我是这样做的。
XSSFDataFormat df = workbook.createDataFormat();
CellStyle cs = wb.createCellStyle();
cs.setDataFormat(df.getFormat("%"));
cell.setCellValue(0.37);
cell.setCellStyle(cs);
But it just shows up as 0.37 with no warning now, not 37%.
但现在它只是显示为 0.37，没有警告，而不是 37%。
采纳答案by Gagravarr
You're getting the warning because, as it says, you're storing a number as text.
您收到警告是因为，正如它所说，您将数字存储为文本。
What you probably want to do is:
你可能想要做的是：
CellStyle cs = wb.createCellStyle();
cs.setDataFormat(df.getFormat("%"));
cell.setCellValue(0.37);
cell.setCellStyle(cs);
That will store the number 37 as a number, and tell excel to apply a percentage format string to it. Oh, and since 37% is 0.37, you need to store 0.37 not 37!
这会将数字 37 存储为数字，并告诉 excel 对其应用百分比格式字符串。哦，因为 37% 是 0.37，所以你需要存储 0.37 而不是 37！
EditBy popular request, here's a standalone program you can use to see it in action, for both .xls and .xlsx files. Tested with POI 3.10 final, and with all the required dependencies and component jarson the classpath.
编辑根据流行的要求，这里有一个独立的程序，您可以使用它来查看 .xls 和 .xlsx 文件的运行情况。使用 POI 3.10 final 进行测试，并在类路径上使用所有必需的依赖项和组件 jar。
public class TestPercent {
public static void main(String[] args) throws Exception {
System.out.println("Generating...");
for (Workbook wb : new Workbook[] {new HSSFWorkbook(), new XSSFWorkbook()}) {
Sheet sheet = wb.createSheet("Data Sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(3);
DataFormat df = wb.createDataFormat();
CellStyle cs = wb.createCellStyle();
cs.setDataFormat(df.getFormat("%"));
cell.setCellValue(0.37);
cell.setCellStyle(cs);
String output = "/tmp/text.xls";
if (wb instanceof XSSFWorkbook) { output += "x"; }
FileOutputStream out = new FileOutputStream(output);
wb.write(out);
out.close();
}
System.out.println("Done");
}
}
回答by Rob
Try also setting the CellType:
还尝试设置 CellType：
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
回答by Indian
This may be a bit old but try this:
这可能有点旧，但试试这个：
df.getFormat("0.00%")
回答by Thamarai Selvan Dhandapani
if(NumberUtils.isDigits(text)){
titleCell.setCellValue(Integer.parseInt(text));
}else{
titleCell.setCellValue(text);
}
回答by fjkjava
XSSFWorkbook xSSFWorkbook = new XSSFWorkbook();
CreationHelper createHelper = xSSFWorkbook.getCreationHelper();
XSSFCellStyle numberStyle = xSSFWorkbook.createCellStyle();
numberStyle.setDataFormat(createHelper.createDataFormat().getFormat("###.00"));
double d = 50.0;
XSSFRow dataRow = sheet.createRow(1);
Cellcel1 = dataRow.createCell(1);
cel1.setCellValue(d);
