POI读取Excel表格时遇到科学计数法处理

在使用Apache的POT库读取Excel文档时候，出现了一个问题，就是，读取到的科学计数法时的值和Excel显示的不一样，网上给的做法一般都是使用NumberFormat来对字符进行格式化，但是存在一个问题，就在读取到到的科学计数法的字符串已经不是Excel文档显示的字符串了，所以即使格式化后，读取出来的字符串也是错误的，正确的做法是，把表格的Cell格式设置成字符串，然后再用字符串的方式来读取，如下代码所示：

Double d = cell.getNumericCellValue();
String s = d.toString();
if (s.contains("E")) {
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
} else {
        return s;
}

完整的代码如下：

Maven依赖：

<dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>3.1.0</version>
</dependency>
<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
</dependency>
<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
</dependency>

Java读取代码：

public class App {

	public static void main(String[] args) throws Exception {

		String fileName = "C:\\Users\\master\\Desktop\\test111.xls";
		Workbook workBook = null;
		InputStream inputStream = null;
		int columnCount = 0;

		String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
		inputStream = new FileInputStream(new File(fileName));

		if ("xls".equalsIgnoreCase(fileType)) {
			workBook = new HSSFWorkbook(inputStream);
		} else {
			if (!"xlsx".equalsIgnoreCase(fileType)) {
				throw new Exception("文件格式" + fileType + "不支持");
			}
			workBook = new XSSFWorkbook(inputStream);
		}

		Sheet sheet = workBook.getSheetAt(0);
		int firstRowNum = sheet.getFirstRowNum();
		int lastRowNum = sheet.getLastRowNum();	
		columnCount = sheet.getRow(firstRowNum).getLastCellNum();

		for (int i = firstRowNum; i <= lastRowNum; i++) {

			Row row = sheet.getRow(i);
			if (row == null) {
				continue;
			}

			for (int j = 0; j < columnCount; j++) {
				Cell cell = row.getCell(j);
				if (cell == null) {
					continue;
				}

				String value = null;
				switch (cell.getCellType()) {
				case STRING:
					value = formatStringValue(cell);
					break;
				case NUMERIC:
					value = formatDoubleValue(cell);
					break;
				case BOOLEAN:
					value = formatBooleanValue(cell);
					break;
				case FORMULA:// 公式
					value = formatFormulaValue(cell);
				case BLANK:
					value = null;
					break;

				default:
					throw new Exception("表格有不支持的列类型:" + cell.getCellType());
				}
				System.out.print(value + "    ");
			}
			System.out.println();
		}
		workBook.close();
		inputStream.close();
	}

	private static String formatStringValue(Cell cell) {
		return cell == null ? null : cell.getStringCellValue();
	}

	private static String formatDoubleValue(Cell cell) {
		Double d = cell.getNumericCellValue();
		String s = d.toString();
		if (s.contains("E")) {
			cell.setCellType(CellType.STRING);
			return cell.getStringCellValue();
		} else {
			return s;
		}
	}

	private static String formatBooleanValue(Cell cell) {
		Boolean b = cell.getBooleanCellValue();
		return String.valueOf(b);
	}

	private static String formatFormulaValue(Cell cell) {
		return cell.getCellFormula();
	}
}

效果如下：