使用 Java 设置所有类型的 Excel 单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16709221/
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
set Excel cell values of all type using Java
提问by Java Developer
I have a requirement to copy Excel row from one sheet to another. But after copying instead of actual values, I am getting different values. There is no problem with cells having string values, but with numeric values. Excel cell can contain any type of value. My code should accept all of them and copy the same in another sheet. Please help me in this case.
我需要将 Excel 行从一张纸复制到另一张纸。但是在复制而不是实际值之后，我得到了不同的值。具有字符串值的单元格没有问题，但具有数字值。Excel 单元格可以包含任何类型的值。我的代码应该接受所有这些并将其复制到另一张纸中。在这种情况下，请帮助我。
if(row.getCell(getLastCell)!=null && ((row.getCell(getLastCell).toString().equalsIgnoreCase(FAIL))|| (row.getCell(getLastCell).toString().equalsIgnoreCase("Invalid CR Statement"))))
{
failuresRow=failuresSheet.createRow(createNewRow++);
for(int ii=0;ii<=getLastCell;ii++)
{
failuresCell=failuresRow.createCell(ii);
failuresCell.setCellValue(row.getCell(ii)+"");
failuresCell.setCellType(row.getCell(ii).getCellType());
failuresCell.setCellStyle(row.getCell(ii).getCellStyle());
}
}
I know that row.getCell(ii)+"" is to convert type Cell to string, but I want to set setCellValue to accept cell which can have any type of data(eg: Date, Bollean, String, Number, .........)
我知道 row.getCell(ii)+"" 是将类型 Cell 转换为字符串，但我想将 setCellValue 设置为接受可以具有任何类型数据的单元格（例如：Date、Bollean、String、Number、... ……）
I have tried using DataFormatter and in some other way also as below, but no use.
我曾尝试使用 DataFormatter 并以其他方式也如下所示，但没有用。
failuresCell.setCellValue(df.formatCellValue(row.getCell(ii)));
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
failuresCell.setCellValue(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
failuresCell.setCellValue(cell.getDateCellValue());
} else {
failuresCell.setCellValue(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
failuresCell.setCellValue(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
failuresCell.setCellValuec(cell.getCellFormula());
break;
default:
// some code
}
If cell have the numeric value 9200278, getting output as 1717. I don't understand where I am doing wrong. Please help me in this case.
如果单元格的数值为 9200278，则输出为 1717。我不明白我哪里做错了。在这种情况下，请帮助我。
回答by Michael 'Maik' Ardan
This is because you are getting the cell instead of the value. You may use the following method instead of row.getCell(int).toString():
这是因为您正在获取单元格而不是值。您可以使用以下方法代替row.getCell(int).toString()：
row.getCell(int).getStringCellValue();
row.getCell(int).getNumericCellValue();
and etc.
Just check the type of cell before invoking the method. Here's an example:
只需在调用该方法之前检查单元格的类型。下面是一个例子：
if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
row.getCell(int).getBooleanCellValue();
} else if (cellType == HSSFCell.CELL_TYPE_ERROR) {
row.getCell(int).getErrorCellValue();
} else if (cellType == HSSFCell.CELL_TYPE_FORMULA) {
row.getCell(int).getCellFormula();
} else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
row.getCell(int).getNumericCellValue();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
row.getCell(int).getStringCellValue();
}
回答by nikon0iT
I just set them all to string beforehand.
我只是事先将它们全部设置为字符串。
try {
FileInputStream fileInputStream = new FileInputStream(excelTemplate);
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
for (int i=0; i < workbook.getNumberOfSheets(); i++) {
for (Row row : workbook.getSheetAt(i)) {
for (Cell cell : row) {
if (cell != null)
cell.setCellType(Cell.CELL_TYPE_STRING);
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
